Mysql深入四：索引优化与执行计划（十条规则 索引策略）
目录
一、索引分类
- 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值(主键索引不允许为空）
- 复合索引：即一个索引包含多个列index(a,b,c）
引擎：
- 聚集索引：索引和数据存放在一起，在b+tree的叶子节点中，在mysql中也是在存放同一个文件中 .ibd （data+index）
- 非聚集索引：索引和数据不是存放在一起，只有索引是在叶子节点，在mysql中分开存储在两个文件中.MYD （data 数据）、.MYI （index 索引）
二、Mysql创建索引基本命令
| 查看索引 | show index from table |
| 创建索引 | create [unique] index index_name on mytable(columnname) |
| 删除索引 | drop index indexName on mytable |
演示
创建复合索引：CREATE INDEX age_name_sex_index ON id(age,name,sex)
查看索引：SHOW INDEX FROM id
删除索引：DROP INDEX age_name_sex_index ON id
三、执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
语法：explain sql语句
执行计划的作用
表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询
执行计划包含信息
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
重要研究：type、key、key_len、extra
| type | 访问类型，表示找到所查询数据的方法 |
| key | 当此值存在时，说明索引生效了，但是充分利用就不知晓（是否利用了索引） |
| key_len | 来计算索引是否被充分利用（是否充分用了索引） |
| extra |
1、Using where说明，SQL使用了where条件过滤数据 2、Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录 |
对type解释的比较好的博客：https://www.jianshu.com/p/b5c01bd4a306
对extra解释的比较好的博客：https://www.cnblogs.com/linjiqin/p/11254247.html
执行计划之key_len字段计算分析
计算：类型（int+0，char+0，varchar+2）、长度、字符编码、是否允许为空（空+1 不空+0）有关
key_len=字符长度*字节数+类型+是否允许为空
其中：char和varchar的长度是指字符数，一个字符在编码gbk为2个字节、utf-8为3个字节，需要字符数*字节
例如：
| name varchar(50) | 表示是varchar类型，长度为50，允许为空，假设是utf8编码 | key_len=50*3+2+1=153 |
| age int(255) | 表示int类型，字节数为4，允许为空，跟长度和编码无关 | key_len=4+1=5 |
对于Mysql的整数类型说明补充：这里显示的宽度和数据类型的取值范围是没有任何关系的，显示宽度只是指明Mysql最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入，而且能够显示出来。
演示：
EXPLAIN
SELECT age,name,sex FROM id WHERE age=2 AND name='lisi' AND sex='1'
分析：
- key值存在，说明使用了索引
- key_len=40（age=4，name=10*3+2=32，sex=1*3+1(sex是可以为Null+1)）
- 复合索引每个列都需要计算，全部列都生效了才是充分利用，上面例子三个列都生效，所以是充分利用了索引
key_len总结
- 变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。
- NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。
- 复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。
- 一张表中不能全是索引，底层维护索引效率太低了。并且之后的查询都是从索引拿，就是都使用覆盖索引了
四、索引策略优化
索引一般都是根据sql语句来作的index(a,b,c)，但是作成之后，索引的顺序就是对sql造成影响
sql没有索引时， where扫描是从右到左的
有索引，按照索引定义的方式来执行 比如有index(a,b,c)，执行顺序就是a,b,c，这个跟后面where的顺序无关，但是where中有a才能执行，有bc没a还是不能执行
十大索引策略：
| 1、尽量全值匹配 | 就是准确不用like |
| 2、最佳左前缀法则 | 如果是复合索引，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列 |
| 3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描 | |
| 4、范围条件放最后 | 意思是sql有范围查询，定义复合索引应该把它放最后（存储引擎不能使用索引中范围条件及范围右边的列） |
| 5、尽量使用覆盖索引 | 只访问索引的查询(索引列和查询列一致)，减少select * 查出来的都是索引，叫做覆盖索引 |
| 6、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描 | |
| 7、注意null/not null对索引的可能影响 | |
| 8、Like查询要当心，%不要放第一 | |
| 9、字符类型加引号 | |
| 10、OR改UNION效率高 | |
CREATE INDEX age_name_sex_index ON id(age,name,sex)
2、最佳左前缀法则
如果是复合索引，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
索引全有效
索引全失效（首个age索引不在，失效）
age索引有效（跳过了name，导致c索引失效）
4、范围条件放最后（索引定义时是age在第一，使用范围，导致其后的索引都失效）
设置索引应该根据SQL来定义索引，索引应该设置成CREATE INDEX age_name_sex_index ON id(name,sex,age)
5、尽量使用覆盖索引
针对下列情况，可以使用全索引覆盖
使用全索引覆盖，SELECT age,name,sex FROM id WHERE age<2 AND name='lisi' AND sex='1'（age、name、sex都是索引列）
7、Like查询要当心
like 后第一个加%时，影响索引及后续索引
所以Like的%不要加在第一个
索引优化口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VARCHAR引号不可丢， SQL优化有诀窍
题目：
index(a,b,c)
| Where语句 | 索引是否被使用 |
| where a = 3 | a被使用 |
| where a = 3 and b = 5 | ab被使用 |
| where a = 3 and b = 5 and c = 4 | abc被使用 |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | 没有被使用 |
| where a = 3 and c = 5 | a被使用 |
| where a = 3 and b > 4 and c = 5 | ab被使用 |
| where a = 3 and b like 'kk%' and c = 4 | abc被使用 |
| where a = 3 and b like '%kk' and c = 4 | a被使用 |
| where a = 3 and b like '%kk%' and c = 4 | a被使用 |
| where a = 3 and b like 'k%kk%' and c = 4 | abc被使用 |