MySQL索引基础-索引的创建以及设计原则
MySQL索引基础-索引的创建以及设计原则
一、MySQL索引基础
首先，我们将从索引基础开始介绍一下什么是索引，分析索引的几种类型，并探讨一下如何创建索引以及索引设计的基本原则。
为了测试索引，我们创建一个user表并插入几条数据，建立几个索引：
1 CREATE TABLE `user` (
2 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
3 `name` varchar(20) NOT NULL COMMENT '姓名',
4 `gender` int(1) NOT NULL DEFAULT '0' COMMENT '性别',
5 `age` int(3) NOT NULL COMMENT '年龄',
6 `status` int(1) NOT NULL COMMENT '状态',
7 `remark` varchar(255) DEFAULT NULL COMMENT '备注',
8 PRIMARY KEY (`id`)
9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
10
11 # 插入几条测试数据
12 INSERT INTO `user` (`name`, `gender`, `age`, `status`, `remark`) VALUES ('lily','0','16','0','test1');
13 INSERT INTO `user` (`name`, `gender`, `age`, `status`, `remark`) VALUES ('tom','0','20','1','test2');
14 INSERT INTO `user` (`name`, `gender`, `age`, `status`, `remark`) VALUES ('kate','1','21','2','12');
15 INSERT INTO `user` (`name`, `gender`, `age`, `status`, `remark`) VALUES ('jack','0','22','3','test3');
16 INSERT INTO `user` (`name`, `gender`, `age`, `status`, `remark`) VALUES ('john','0','23','4',null);
17
18 # 添加索引
19 ALTER TABLE `user` ADD UNIQUE ( `name`);
20 ALTER TABLE `user` ADD INDEX index_name( `name`);
21 ALTER TABLE `user` ADD INDEX index_gender( `gender`);
# 删除索引
ALTER TABLE table_name DROP INDEX index_name;
执行完上面sql语句，再来查看下user表结构，执行命令：desc user，结果如下图：
1、什么是索引
索引是帮助MySQL高效获取数据的数据结构。
2、索引类型
查看索引详情：show index from table_name;
1） 主键索引
主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
注意：一个表只有一个主键。
2） 唯一索引
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建索引：
ALTER TABLE table_name ADD UNIQUE (column);
创建唯一组合索引：
ALTER TABLE table_name ADD UNIQUE (column1,column2);
3）普通索引
这是最基本的索引，它没有任何限制。
创建普通索引： ALTER TABLE table_name ADD INDEX index_name (column);
4）组合索引
即一个索引包含多个列，多用于避免回表查询。
创建组合索引： ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);
5）全文索引 （FULLTEXT）
ALTER TABLE table_name ADD FULLTEXT (column);
说明：索引一经创建不能修改，如果要修改索引，只能删除重建。
删除索引：DROP INDEX index_name ON table_name;
3、索引设计的原则
1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
2）基数较小的列，索引效果较差，没有必要在此列建立索引。
3）使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。
4）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。因此只保存需要的索引有利于查询即可。
4、MySQL索引优化实战
在介绍索引优化实战之前，首先要介绍两个与索引相关的重要概念，这两个概念对于索引优化至关重要：基数和回表。
1）基数
基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。
select count(distinct name),count(distinct gender) from user;
user表的总行数是5，gender列的基数是2，说明gender列里面有大量重复值，name列的基数等于总行数，说明name列没有重复值，相当于主键。
现在问题来了，假设name、gender列都有索引，那么下面的语句1和语句2都能命中索引吗？
语句1：SELECT * FROM user WHERE gender = 0;
语句2：SELECT * FROM user WHERE name = 'kate';
我们用explain/desc来分析测试下，如下图：
可以看到，语句1没有命中索引，注意filtered的值就是上面我们计算的返回记录的比例数。 语句2命中了索引index_name，因为走索引直接就能找到要查询的记录，所以filtered的值为100。
说明：
一般惯用的百分比界线是"30%"。也就是说，返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。
2）回表
当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在rowid。通过索引中记录的 rowid 访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。
Explain 命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。
5. 索引优化实战
有些时候虽然数据库有索引，但是并不被优化器选择使用。
查看索引使用的情况：
show status like 'Handler_read%';
Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。
Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。
索引优化规则：
1）如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。
返回数据的比例是重要的指标，比例越低越容易命中索引，后面所讲的关于索引优化都是建立在返回数据的比例在30%以内的基础上。
2）前导模糊查询不能命中索引
explain select * from user where name like '%s%';
可优化为使用非前导模糊查询：explain select * from user where name like 's%';
3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
如下图：
4）复合索引的情况下，查询条件不包含索引列最左部分（不满足最左原则），不会命中索引。
注意：最左原则并不是说是查询条件的顺序，而是查询条件中是否包含索引最左列字段。优化器会自动调整。
5） union、in、or都能够命中索引，建议使用in
查询的CPU消耗： or>in>union
6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
7）负向条件查询不能使用索引，可以优化为in查询。
负向条件有：!=、<>、not in、not exists、not like等。
8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
a. 范围条件查询可以命中索引：
b. 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：
c. 如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：
9）数据库执行计算不会命中索引。
10）利用覆盖索引进行查询，避免回表。
被查询的列，数据能从索引中取得，而不用通过定位符row-locator再到row上获取，即被查询列要被所建的索引覆盖，这能够加速查询速度。
为了方便测试，我们给status列添加上索引：ALTER TABLE `user` ADD INDEX index_status( `status`);
因为status字段是索引列，所以直接从索引中就可以获取值，不必回表查询：
其中，Extra中的 Using Index 代表从索引中查询。
当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一，如下图：
11） 建立索引的列，不允许为null
单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。
为了方便测试，我们给remark列添加上索引：ALTER TABLE `user` ADD INDEX index_remark( `remark`);
如下图：
说明： 虽然 is null 可以命中索引，但是 null 本身就不是一种好的数据库设计，应该使用 not null 约束以及默认值。
总结：
a. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
b. 区分度不大的字段上不宜建立索引：类似于性别、地址、类型等这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。
c. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
d. 多表关联时，要保证关联字段上一定有索引。
e. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。
参考链接：
https://zhuanlan.zhihu.com/p/61687047
