Mysql : Mysql索引

目录：

索引类型

单列索引

普通索引(Normal)：基本索引类型，没有唯一性限制。如果列字段类型为CHAR、VARCHAR，length可以小于字段的实际长度，我们称为前缀索引；如果是BLOB、TEXT类型必须制定长度。

唯一索引(Unique)：这种索引和前面的“普通索引”基本相同，但有一个区别:索引列的所有值都只能出现一次,即必须唯一。但是他允许有null值。

主键 (PK)：主键是一种唯一性索引，不允许有null值，在innoDB中，主键很重要，上一篇讲过如果不指定主键，mysql会自动生成6位主键。而且主键int优于VARCHAR，一般设置为AUTO_INCREMENT的。

全文索引(Full Text)： MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

组合索引(多列索引)

索引可以单列的，也可以是多列的。通俗一点讲 组合索引是:包含多个字段但是只有索引名称

其sql格式是 CREATE INDEX IndexName On TableName(字段名(length),字段名(length),…);

如果你建立了 CREATE INDEX index1 ON award(a, b, c);那么实际包含了三个索引（a）、（a，b）、（a，b，c）在查询时遵循”最左前缀”，举几个例子：

select * from table where b = ‘1’；-不使用索引
select * from table where c = ‘1’；-不使用索引
select * from table where b = ‘1’ and c = ‘1’；-不使用索引

select * from table where a = ‘1’；-使用索引
select * from table where a = ‘1’ and b = ‘1’；-使用索引
select * from table where a = ‘1’ and b = '1' and c = '1'；-使用索引

索引方法

B+TREE索引

B+TREE数据结构如下图:

MyISAM引擎和innoDB引擎都是B+TREE,B+TREE是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

HASH索引

哈希索引是采用哈希算法,把键值转换成新的哈希值,检索的时候不用像B+TREE一样从根节点到叶子节点逐级查找,只需一次哈希算法即可立即定位到相应位置,不考虑哈希碰撞的情况下速度非常快.

hash索引仅HEAP,MEMORY和NDB引擎才支持,MyISAM都是不能显式支持hash索引的.innoDB引擎比较特殊,innoDB引擎支持hash索引,但是他是自适应的,需要我们手动启用这个功能.启用此功能后,hash索引的创建由innoDB引擎自动化完成,我们是干预不了的.详见:mysql InnoDB引擎支持hash索引吗

B+TREE和HASH比较

• Hash 索引仅仅能满足”=”,”IN”和”<=>”查询

• 如果值唯一,那么hash索引有绝对优势,因为只需要进行一次hash运算就能准确找到响应位置.比如上图中的[001],[153],[254].

• 如果值不唯一,那么hash索引效率不一定比B+TREE效率高.就像途中[152]这里发生了hash碰撞后,hash索引也是要逐行查找.如果hash碰撞很多,那效率也许比B+TREE低

• 如果范围查询或排序查询,hash索引将无法使用,hash索引仅能满足等值查询原因是原有的键值做完hash运算后变成了不连续的值.如:>或者order by, 同时like ‘abc%’这种模糊查询本质也是范围查询

• hash索引也不支持多列联合索引的最左匹配规则,原因是hash索引是将组合索引键合并后再一起进行hash计算.并不是单独计算每一个键的hash值.所以最左匹配规则也会失效.

PS:=和<=>区别:

mysql> select '1' = null , null = null , '1' <=> null , null <=> null;
+------------+-------------+--------------+---------------+
| '1' = null | null = null | '1' <=> null | null <=> null |
+------------+-------------+--------------+---------------+
|       NULL |        NULL |            0 |             1 |
+------------+-------------+--------------+---------------+
1 row in set (0.00 sec)

索引使用

这一节介绍如何测试自己建立的索引效率.包含如何关闭查询缓存,如何查看索引是否命中等.

查询缓存

在我们测试索引时最好关闭查询缓存,以免影响测试结果.
关闭查询缓存移步:启用MySQL查询缓存
查询缓存详解移步:MySQL 查询缓存

我这边由于测试过程中使用的mysql开发人员也在用,所以采用如下方式关闭
select SQL_NO_CACHE count(*) from users where email = ‘shangmingtao@126.com’;

explain命令

通过类似explain select name from t_user where id = '123'的命令我们看到sql是否使用了索引,和具体使用情况

+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | index1        | index1 | 392     | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+

其中type,key,rows和extra四个比较重要,分别介绍含义:

• type : 依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

• key : 实际用到的索引

• rows : 执行计划中估算的扫描行数，不是精确值

• extra : 这个列可以显示的信息非常多，有几十种,下边是常用的

type 值	type 说明
system	表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
const	使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
eq_ref	出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
ref	不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
fulltext	全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null	与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery	用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range	索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
index	索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all	这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

extra 值	extra 说明
distinct	在select部分使用了distinc关键字
no tables used	不带from字句的查询或者From dual查询
using filesort	排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
using index	查询时不需要回表查询，直接通过索引就可以获取查询的数据
using intersect	表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
using union	表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
using sort_union和using sort_intersection	在select部分使用了distinc关键字
using temporary	表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
using where	表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
firstmatch(tb_name)	5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
loosescan(m..n)	5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

profiling使用

MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。

set profiling=1; -开启
show profiles; -查看
show profile cpu,block io for query 3; -查看3号query的cpu和io消耗

详见:MySQL Profiling 的使用

索引的选择性

索引的选择性 = 不重复的索引值/数据表的记录总数
选择性越高,性能越好,选择性为1证明没有重复的值

计算选择性:

select count(distinct [字段])/count(*) from [表名];

参考文章:

MySQL的btree索引和hash索引的区别:https://www.cnblogs.com/vicenteforever/articles/1789613.html
MySQL B+树索引和哈希索引的区别:https://www.cnblogs.com/heiming/p/5865101.html
mysql InnoDB引擎支持hash索引吗:http://blog.csdn.net/doctor_who2004/article/details/77414742
‘=’和’<=>’区别:http://www.jb51.net/article/63893.htm
MYSQL explain详解:http://blog.csdn.net/zhuxineli/article/details/14455029
mysql explain执行计划详解:https://www.cnblogs.com/xiaoboluo768/p/5400990.html
一次 MySQL 索引优化的经历:http://blog.csdn.net/wwh578867817/article/details/50763290
细说mysql索引:https://www.cnblogs.com/chenshishuo/p/5030029.html
mysql索引详解(转):https://www.cnblogs.com/ggjucheng/archive/2012/11/04/2754128.html