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`);
执行完上面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);
创建组合索引： ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);
4）全文索引 （FULLTEXT）
ALTER TABLE table_name ADD FULLTEXT (column);
说明：索引一经创建不能修改，如果要修改索引，只能删除重建。
删除索引：DROP INDEX index_name ON table_name;
3、索引设计的原则
1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
2）基数较小的列，索引效果较差，没有必要在此列建立索引。
3）使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。
4）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。因此只保存需要的索引有利于查询即可。
4、MySQL索引优化实战
与索引相关的重要概念 ，这两个概念对于索引优化非常重要
1）基数
单个列唯一键（distinct_keys）的数量叫做基数
select count(distinct name),count(distinct gender) from user;
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%‘;
3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
EXPLAIN SELECT * FROM user WHERE name=1;
EXPLAIN SELECT * FROM user WHERE name=‘1’;
4）复合索引的情况下，查询条件不包含索引列最左部分（不满足最左原则），不会命中索引。
注意：最左原则并不是说是查询条件的顺序，而是查询条件中是否包含索引最左列字段：。优化器会自动调整。
5） union、in、or都能够命中索引，建议使用in
查询的CPU消耗： or>in>union
6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
7）负向条件查询不能使用索引，可以优化为in查询。
负向条件有：!=、<>、not in、not exists、not like等。
8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
9）利用覆盖索引进行查询，避免回表。
被查询的列，数据能从索引中取得，而不用通过定位符row-locator再到row上获取，即被查询列要被所建的索引覆盖，这能够加速查询速度。
10） 建立索引的列，不允许为null
单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。
虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值
索引下沉
key_len表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。