mysql索引怎么设计,数据库怎么设计索引

mysql怎么设计索引

1.添加PRIMARY KEY（主键索引）

站在用户的角度思考问题，与客户深入沟通，找到西工网站设计与西工网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站设计、成都网站建设、企业官网、英文网站、手机端网站、网站推广、国际域名空间、网络空间、企业邮箱。业务覆盖西工地区。

mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysqlALTER TABLE `table_name` ADD UNIQUE (

`column`

)

3.添加INDEX(普通索引)

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

mysql如何建立索引

我们可以通过查看索引的属性来判断创建索引的方法。

查看索引的语法格式如下：

SHOW INDEX FROM 表名 [ FROM 数据库名]

语法说明如下：

表名：指定需要查看索引的数据表名。

数据库名：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息，SQL 语句和运行结果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要参数说明如下：

参数 说明

Table 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。

Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 显示评注。

MySQL性能优化之索引设计

上一篇给小伙伴们讲了关于SQL查询性能优化的相关技巧，一个好的查询SQL离不开合理的索引设计。这篇小二就来唠一唠怎么合理的设计一个索引来优化我们的查询速度，要是有不合理的地方...嗯..

当然啦，开个玩笑，欢迎小伙伴们指正！

通常情况下，字段类型的选择是需要根据业务来判断的，通常需要遵循以下几点。

下列各种类型表格内容来自菜鸟教程，权当备忘。

优化建议：

注意： INT(2)设置的为显示宽度，而不是整数的长度，需要配合 ZEROFILL 使用 。

例如 id 设置为 TINYINT(2) UNSIGNED ，表示无符号，可以存储的最大数值为255，其中 TINYINT(2) 没有配合 ZEROFILL 实际没有任何意义，例如插入数字200，长度虽然超过了两位，但是这个时候是可以插入成功的，查询结果同样为200；插入数字5时，同样查询结果为5。

而 TINYINT(2) 配合 ZEROFILL 后，当插入数字5时，实际存储的还是5，不过在查询是MySQL会在前面补上一个0，即查询出来的实际为 05 。

优化建议：

优化建议：

通常来说，考虑好表中每个字段应该使用什么类型和长度，建完表需要做的事情不是马上建立索引，而是先把相关主体业务开发完毕，然后把涉及该表的SQL都拿出来分析之后再建立索引。

尽量少建立单值索引( 唯一索引除外 )，应当设计一个或者两三个联合索引，让每一个联合索引都尽量去包含SQL语句中的 where、order by、group by 的字段，同时确保联合索引的字段顺序尽量满足SQL查询的最左前缀原则。

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，性别一共有三个值：男、女、保密，那么该字段的基数就是3。

如果对这种小基数字段建立索引的话，因为索引树中只有男、女、保密三个值，根本没法进行快速的二分查找，同时还需要回表查询，还不如全表扫描嘞。

一般建立索引，尽量使用那些基数比较大的字段，那么才能发挥出B+树快速二分查找的优势来。

在 where 和 order by 出现索引设计冲突时，是优先针对where去设计索引？还是优先针对order by设计索引？

通常情况下都是优先针对 where 来设计索引，因为通常情况下都是先 where 条件使用索引快速筛选出来符合条件的数据，然后对进行筛选出来的数据进行排序和分组，而 where 条件快速筛选出来的的数据往往不会很多。

对生产实际运行过程中，或者测试环境大数据量测试过程中发现的慢查询SQL进行特定的索引优化、代码优化等策略。

终于轮到实战了，小二最喜欢实战了。

写到这里不得不吐槽一下，这个金三银四的跳槽季节，年前提离职了，结果离职还没办完就封村整整两个礼拜了，呜呜呜...

上节小二就提到会有个很有意思的小案例，那么在疫情当下，门都出不去的日子，感觉这个例子更有意思了，咱们来讨论一下各种社交平台怎么做的用户信息搜索呢。

社交平台有一个小伙伴们都喜欢的功能，搜索好友信息，比如小二熟练的点开省份...城市..性别..年龄..身高...

咳咳咳...小二怎么可能干这种事情，小二的心里只有代码，嗯...没错，就是这样。

这个就可以说是对于用户信息的查询筛选了，通常这种表都是非常大数据量的，在不考虑分库分表的情况下，怎么通过索引配合SQL来优化呢？

通常我们在编写SQL是会写出类似如下的SQL来执行，有 where、order by、limit 等条件来查询。

那么接下来小二一个一个慢慢增加字段来分析分析，怎么根据业务场景来设计索引。

针对这种情况，很简单，设计一个联合索引 (provice, city, sex) 就完事了。

那么这时候有小伙伴就会说了，很简单啊，范围字段放最后咱还是知道的，联合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，这么干没毛病，但是小伙伴们有没有想过有些人万一既喜欢帅哥又喜欢美女，别想歪了哈...，挺多小姐姐就既喜欢帅哥又喜欢美女的。

那么这个时候小姐姐就不搜索性别了，那么这个时候联合索引只能用到前两个字段了，那么不符合咱们的专业标准啊，咋办呢？这时候还是有办法的，咱们只需要动动小脑袋改改SQL就行了，在没有选择性别时判断一下，改成下面这样就可以了。

咋办嘞，同样往联合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。

针对这种多个范围查询的话，为了比较好的利用索引，在业务允许的情况下可以使用固定范围，然后数据库字段存储范围标识就可以了，这样就转化为了等值匹配，就可以很好地利用索引了。

例如最后登录时间字段不记录最后登录时间，而是记录设置字段 is_login_within_seven_days 在7天内有登录则为1，否则为0，最后索引设计成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那么根据场景最后设计出来的这个索引可能已经可以覆盖大部分的查询流量了，那么如果还有其他一部分热度比较高的查询怎么办呢，办法也很简单啊，再加一两个索引即可。

例如通常会查询这个城市比较受欢迎(评分：score)的小姐姐，这时候添加一个联合索引 (provice, city, sex, score) 那么就可以了。

可以看出，索引时必须结合场景来设计的，思路就是尽量用不超过3个复杂的联合索引来抗住大部分的80%以上的常用查询流量，然后再用一两个二级索引来抗下一些非常用查询流量。

以上就是小二要给大家分享的索引设计，如果能动动你发财的小手给小二点个免费的赞就更好啦~

下篇小二就来讲讲MySQL事务和锁机制。

如何正确合理的建立MYSQL数据库索引

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对，=，=，，=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate‘2007-01-01’;

◆不使用NOT IN和操作