Mysql系列（三）Mysql索引优化技术总结

本文总结下Mysql索引优化需要注意的要点。

存储引擎分为innoDB和MyISAM：

下图中是innoDB存储引擎，左边是主键索引，右边是非主键索引：

下图为MyISAM存储引擎的索引结构：

一、索引类型

类型
1.单值索引：一个索引只包含一个列，一个表可以有多个单列索引；
2.唯一索引：索引列的值必须唯一，但允许有空值；
3.复合索引：一个索引包含多各列。

基本语法
说明：中括号[]表示可以省略；
1.创建
CREATE [UNIQUE] INDEX indexName ON myTable (cloumname(length)); //建表的时候同步创建索引；

向已有表添加索引：
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (cloumname(length));

2.删除
DROP INDEX [indexName] ON mytable;

3.查看
SHOW INDEX FROM tablename;

二、索引结构

• BTree索引
• Hash 索引
• fulll-text全文索引
• R-TRee索引

三、索引优化

3.1 哪些情况下需要创建索引

1.主键自动创建唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.查询中需要排序的字段，创建索引可大大提高排序速度
5.查询中统计或者分组的字段

3.2 哪些情况下不需要创建索引

1.频繁增删改的字段不适合创建索引
2.where条件中用不到的字段不需要创建
3.表记录太少，百万以下
4.如果某个列包含太多重复的数据，建立索引效果不大，比如性别列

3.3 explain

explain用来查询sql的具体执行计划，比如是否用到索引等，是优化sql最常用的手段。
语法：explain + SQl语句

表头信息：

下面介绍下表头含义：

1.id: select 在查询时的序号，表查询中执行或操作表的顺序；
包含3种情况：

• id相同，执行顺序由上到下；
• id不同，id越大越优先执行；
• id有相同的，又有不相同的，先执行数字最大的，相同的，由上到下执行；

2.select_type: 查询类型，主要用于区分普通查需、联合查询、子查询等复合查询；

• SIMPLE： 简单的select查询，比如select * from tablename，不包含子查询或者union；
• PRIMARY：查询中若包含任何复杂的子部分，最外层则被标记为primary；
• SUBQUERY：在select或者where中包含了子查询；
• DERIVED ：from中包含子查询，生成了临时表；
• UNION：若第二个select查询出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select被标记为derived；
• UNION RESULT：从union表获取结果的select；

3.table：显示这一行执行是关于哪各表的；

4.type：访问类型
从好到坏依次是：
system -> const -> eq_ref -> ref -> range -> index ->all

• system :表只有一行记录（比如系统表），是const的特殊情况，平时不会出现这个，可以忽略不记；
• const:通过索引一次就能找到，比如使用primary_key或者unique索引；比如where id=1；
• eq_ref:唯一性索引扫描，相当于精确的只能查询到一行记录；where t1.id = t2.id ;
• ref:非唯一性索引扫描，用到了索引，但是查询到了多行记录；比如where name=‘abc’；
• range: 使用索引列查询一定范围的行；比如where中出现了between、>、<、in 等；
• index: full index scan ，扫描整个索引；
• all: full table scan，扫描全表；

5.possible_keys: 显示可能应用在这张表中的索引，一个或者多个；实际不一定被实际使用；

6.key: 实际使用的到的索引，如果为null，表示没有使用索引；

7.key_len: 表示索引使用的字节数；长度越短越好；

8.ref：显示索引列的具体使用情况，比如const表示给索引列指定了具体的值；如果显示列名，表示使用了此索引列，值不确定。

9.rows: 查询实际扫描的行数，越小越好。

10.extra: 额外的一些重要信息；

• Using filesort： 文件排序，mysql无法利用索引完成排序时使用的排序方式；出现这种情况比较糟糕。
• Using temporary： 使用了临时表保存中间结果，mysql在对查询结果排序时，使用了临时表，常见于order by和group by；出现这个比较糟糕。
• Using index： 表示使用了覆盖索引，避免了扫描行数据，性能不错。
• Using where： 表示使用了where查询条件；
• Using join buffer：使用了连接缓存；
• impossible where ：where 子句的值是false，where id=1and id=2；
• select tables optimized away
• distinct

3.4 索引失效

1.最左前缀原则，且不能跳过中间列
建立联合索引的时候，比如a，b，c列，在where条件中使用a，ab，abc是索引生效的；其他均失效，比如b，bc，c，ac只能使用a。

2.在索引列上使用函数、计算、类型转换会导致索引失效。

3.尽量使用覆盖索引（查询的列等于索引列），不要select *;

4.mysql在使用不等于（!=或者<>）,无法使用索引；

5.is null 或者 is not null 导致索引失效

6.like %通配符放在开头：‘%abc…’

7.字符串不加单引号

8.少用or，会导致全部索引失效

9.联合索引时，范围条件右边的列索引会失效，比如 a>10 and b=2,索然ab都有索引 但是b的失效。