Mysql索引

索引的本质是为了提高检索数据效率的排好序的数据结构。

B-tree：

叶节点具有相同高度

所有叶节点的指针为null

节点中的数据从左到右递增排列

B+tree(B-tree的变种):

非叶子节点不存储数据，只存储索引冗余（为了能够放下更多索引数据）

叶子节点包含所有索引字段

叶子节点用指针连接（为了提高区间查询的效率）

Hash

对索引进行一次hash运算就能找到索引，定位到数据存储的位置

很多情况下Hash索引查询效率要高于B+tree索引，但不支持范围查询，仅能满足=，in，并且有hash冲突问题。

tips：MySIAM的索引文件和数据文件是分离的

InnoDB索引实现：

表数据本身就是按照B+Tree数据结构组织的一个索引结构文件

聚集索引叶子节点包含了完整的数据目录

为什么InnoDB索引必须建主键？为什么推荐使用整型的自增主键？

为什么非主键索引的叶子节点存储的是主键值？

Explain工具

Explain会模拟优化器执行SQL,分析查询语句的性能瓶颈，在select语句前加上Explain关键字，不会执行sql，而是返回执行计划，如果from里有子查询，还是会去执行，只是将结果放到临时表中。

explain extend:会在explain的基础上提供一些查询优化的信息，在其后追加show warnings命令可以得到优化后的语句

explain partitions:比explain多一个partitions字段，如果查询是基于分区表的话会显示查询将访问的分区。

explain的列

id:该列是select语句的执行序号，有几个select就有几个id，并且id的顺序是按select出现的顺序递增的，序号越多优先级越高，序号相同则从上向下执行，Null最后执行。

select_type列：表示对应行是简单还是复杂的查询，simple(简单查询，不包含子查询和union),primary(复杂查询的最外层select),subquery(包含在select里的子查询，不包含from里的子查询)，derived(包含在from里的子查询)，union（union中的第二个和随后的select）

table列：表示访问的是哪个表，当from子句中有子查询时，table的格式是<derivenN>格式，表示当前查询依赖id=N的查询，先执行id=N的查询。当有union时，UNION RESULT的table列的值为<union1,2>,1和2表示参与union的select行的id。

type列：这一列表示关联类型或访问类型，即Mysql如何查询表中的行，查询记录行的大致范围，

以此从最优到最差分别是 system>const>eq_ref>ref>range>index>ALL，一般来说，要求达到range级别，最好能达到ref级别

        Null:代表查询时不需要访问表或索引，例如查询列的最小值，可以单独查找索引完成而不需要执行时访问表

        const,system:mysql能对查询优化并将其转化为一个常量，用于primaryKey或unique key的所有列与常数对比时，所以表只有一行记录匹配，读取一次，速度比较快，system是const的特例，表里只有一行数据且匹配时。

        eq_ref:primary key或unique key索引的所有部分被链接使用，只返回一条符合条件的记录，这可能是const外最好的链接类型，简单的select不会出现这种type。

        ref:使用普通索引或唯一索引的部分前缀和某个值比较，符合条件的结果可能有多个。

        range:代表范围查询，例如< ，>，between ，in，使用一个索引来检索给定范围行等

        index:扫描全索引能拿到结果，使用某个普通索引，直接遍历叶子节点，速度比较慢，通常比ALL快一点

        ALL:即全表扫描

possible_keys列：本列显示可能会用到那些索引来检索，explain时可能出现possible_keys有列，而key列中null，这种情况是因为表的记录数很少，mysql判断索引对查询帮助不大，直接全表扫描。如果该列是null,可以根据where子句判断是否可以创建一个合适的索引来提高查询效率

key列：这一列显示mysql实际使用的是哪一个索引来优化查询，如果没有使用索引，则该列为null。如果想强制使用或忽略某个索引，可以使用force index，ignore index。

key_len列：这一列显示了mysql在索引里使用的字节数，通过这个字段可以计算出使用了索引中的哪些列。例如order_product_index索引是由(orderid(int),productid(int))两个字段组成的联合索引，key_len=4代表只使用了该联合索引的orderid列。

        
key_len计算规则如下：
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
        char(n)：如果存汉字长度就是 3n 字节
        varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
        varchar是变长字符串
数值类型
        tinyint：1字节
        smallint：2字节
        int：4字节
        bigint：8字节
时间类型
        date：3字节
        timestamp：4字节
        datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列：这一列显示了在keys列中的索引，表检索的时候用到了哪些列或常量，常见的由const,字段名。

rows列：这一列是mysql估计要扫描的行数（并发实际查询的结果数）

Extra列：这一项显示的额外信息。常见的有：

        1）Using index：使用覆盖索引

        2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

        3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

        4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。

        5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。

        6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

索引实践常用规范：

1.全值匹配

2.最左前缀法则

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 = 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

7.is null,is not null 一般情况下也无法使用索引

8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

9.字符串不加单引号索引失效

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

11.范围查询优化，mysql或根据检索比例，表大小等多个因素来决定是否使用索引，当范围查询区间过大时可能导致不走索引，可以将查询范围分成多个小的区间。