MySQL笔记二—索引

索引

    如同书籍的目录，索引存在的价值就是加速查询，合适的索引可以显著的提高查询效率，需要注意的是 合适 的索引，索引不是万能的，但是不用索引是万万不能的

    索引的主要用处有三

    1，减少扫描数据的行数

    2，避免服务器排序和临时表

    3，将随机io变成顺序io

 

索引的类型

    索引主要有Btree，哈希，全文和其他，重点在于Btree索引

 

    Btree索引

    B-tree和B+tree的区别在于 B+tree的叶子节点上包含着指向下一个叶子节点的指针，可以理解为链表结构，这样的结构使得对于范围查找的效率非常高，where ID > 5这样的场景，既可以通过索引先找到id=5的数据（很快），然后顺序的读数据即可，无需再次查找。

    Btree的索引结构特点是按照索引字段进行了顺序的排列，如index(id)在进行存储时，就是按照id列由小到大的顺序排列的。如index（id，name）,则是按照第一个字段id从小到大，如果两行数据id相同，再按照第二个字段name列排序，如果有更多索引字段，以此类推。

    由于innodb是索引组织表，所有数据都是放在主键索引的叶子节点里的，那么这时数据是如何排序的呢。显然首先应该按照主键字段进行排序，而其他的未定义在主键中的字段是按照create table语句中的定义顺序排序的（实际场景中，业务在新增字段时常常会制定新字段在表中的位置，他们是为了将新字段置前以获得更好的排序效果么？不，大概率是为了兼容程序，如select *）

    btree索引的限制也很明显，由于是按照索引字段的顺序进行顺序排列，如index（id,age）在where age> 1的场景就无法使用该索引了，如index（name）在where name like ('%tom')场景下也无法使用，想象下字符串是不是由很多字符组成，index（字符串） == index(字符1，字符2，字符3.........)，两种情况其实本质上是一样的。（充分的使用Btree索引似乎不是一件简单的事情，总有着这样那样的限制，这恰恰说明建立一个合适的索引是多么不容易的事，一定要根据线上的环境进行，这就是dba的职责，当然也不用过于担心，innodb/mysql也是有优化手段的）

 

    哈希索引

    哈希索引基于哈希表实现，只能应用于精确查找

    哈希索引只存储哈希值和行指针，并不存储字段值，且不能用于范围查找，部分查找

    在能使用哈希索引的场景下，哈希索引是最快的。

    innodb中有一个特殊的功能--自适应哈希（AHI）,innodb发现某些索引值被频繁的使用，会自动加增加哈希索引，此行为不可控，可以关闭此功能。

    哈希还存在哈希冲突的问题，即两个数据的哈希值一样，这次会通过链表存储，所以当使用哈希索引检索时，需要在where语句中包含常量，否则可能获得冲突的值。

    

    全文索引

    与以上的索引完全不相同，全文索引查找的是文章中的关键词，更像是搜索引擎而不是where查询，以后再说

    

    其他索引

    空间索引：用于地理位置数据存储

    分型树索引：tokuDB的索引

    

 

 

使用索引的策略

 

1，独立的列

    where id + 1 = 2 是无法使用索引的，即等号左边必须是独立的列，where id = 2-1 是可以使用索引的

 

2，前缀索引和索引选择性

    当需要检索一个很长的字段时，将完整字段建立索引会占用大量空间，可以尝试使用前缀索引add key (num(5)),以num的前5个字符建立索引，这样索引的长度就会大大减小。那是不是长度越小越好呢，显然不是，假设num是身份证号，身份证号的前几位是所在的省份代号，如果以前几位做前缀索引，那么一个省份内的所有num的索引值都是一样的即为省份代码。这样的索引显然是无用的，因为索引无法起到过滤数据的作用，起码应该覆盖到生日部分，至少可以过滤355/356的人。即索引的区分度要高，更专业的说法是前缀索引的基数要尽可能接近完整列的基数

    （不用前缀索引，需要注意基数么？显然是要的，业务有张表存储的是用户的邮件内容，有个字段是is_delete,代表中改邮件是否被删除，业务会每天定时清理一次删除的数据。为了在正常的select中过滤已删除的数据，业务在每条sql中都加上了where is_delete=0，还加了index（is_delete）,但是表中有几百万数据，is_delete只有几百条，这个索引就是失败的，因为根本过滤不了几条数据，还增加了计算的成本）

 

3，多列索引

    为每一个列都增加索引是错误行为，除非你的每条sql的where条件都只有一个（别想了，不可能的，所以不要这么干）

    mysql5.0后引入索引合并，一定程度上可以更好的利用多个单列索引，但是劣大于优

    1，索引合并和浪费大量cpu和内存资源在算法的缓存，排序和合并上

    2，索引合并的成本不会计算到查询成本中，会导致严重的性能问题

    3，既然mysql使用的索引合并，为何不直接建立联合索引呢

    如果索引合并，恰恰说明索引设置的不合理，需要优化了（某业务就遇见过，加了索引后导致出现索引合并拖慢整个集群的场景，最后不得不紧急删除索引）

 

4，聚簇索引

    聚簇的意思是索引和数据紧紧的存储在一起，且索引是有序的（详见Btree索引），即索引的叶子节点上就存储了所有的数据，显然一张表只能有一个聚簇索引。这样的表也称索引组织表

    inndodb引擎的主键总是聚簇索引，所以innodb的数据都在主键索引表上。如果create table时没有指定主键呢，数据会放在那里？这时会优先选择 非空且唯一的索引做主键，否则会自动生成UUID作为主键。（当然我们要尽量避免这种情况，原因是主键最好是顺序自增的，至少大部分情况是这样的，原因在下方）

    下面我想分类讨论下聚簇索引的优缺点，

    当聚簇索引是int自增时，

        insert数据时，由于id自增，所以数据可以顺序（不会写满一个页，会默认剩余1/16的空间）的写入，新写入的数据一定是顺序往后排列，不会影响之前的数据，顺序写远远优于随机写，insert效率高。

自增也是有缺点的，在高并发场景下，可能会导致间隙锁竞争。

    当聚簇索引非自增时，比如是身份证号

        insert数据时，数据需要找到自己该处的位置，这时写入是随机写，效率低。如果你要写的页已经从缓存中移除并存储到磁盘中，还需要从磁盘中读出数据，也导致大量随机io。更严重的是，数据是存贮在页上的，如果插入数据时会超过当前页的容量，那么会引发页分裂，即一个页分裂成两个页，首先会浪费cpu，其次这两个页都会比较空，如果之后没有数据去填充这个空处，那么就会造成磁盘的浪费，

        不管聚簇索引是否自增，对聚簇索引的更新操作代价都很大，因为需要移动所有的数据到正确的位置。

        二级索引中的叶子节点存储的不是数据而是聚簇索引即主键，如果主键过大，会导致二级索引也很大，索引主键不宜过长。

        由于二级索引存储的主键值，当通过二级索引查询数据时，可能需要按照查询到的主键值再去聚簇索引上查找其他数据字段，这叫做回表。回表是个非常消耗资源的操作，应当尽量避免（覆盖索引可破）。

        聚簇索引上还有什么东西呢，还有trxid 和undo指针，（详见mvcc，undo）

        最后说下MyISAM，与innodb的聚簇索引不同，MyISAM的所有索引结构都一样，其叶子结点存储的是数据的指针，索引不是聚簇索引。

 

5，覆盖索引

    二级索引中，如果叶子结点中已经包含了所有要查询的字段，那么就不需要回表，这称之为覆盖索引，由于省去了代价高的回表操作，所以应当尽量使用覆盖索引

    explain的extra列如果有Using index 信息，说明使用了覆盖索引。、

 

索引的优化

    cardinality  该值来自于 show index from table,表示索引中唯一值数据的估算数，如果这个数过小，可以考虑移除该索引。优化器会根据这个值计算使用该索引的代价，而实时更新代价很大，所以可能出现由于估算值不准而导致优化器选错索引的情况。那么何时对cardinality 进行更新呢，1，当表中1/16的数据发生过变化，2，发生变化的次数超过20亿次（可能是一行变化了20亿次）

    

    MRR  MySQL5.6支持 MRR的目的是减少范围查询和join查询的磁盘随机访问，并且将随机io转换成顺序io，其工作方式如下：1.将查询得到的辅助索引键值放到缓存，此时是按照辅助索引键值排序；2，将键值按主键id排序；3，根据主键id来查找实际的数据。

    为什么能按主键排序呢，因为辅助索引的叶子节点存储就是主键数据

    什么时候用了MRR,extra列显示 USING MRR

    如何开启MRR，参数optimizer_switch=on

 

    ICP索引下推  MySQL5.6支持 ICP使得数据库在取出索引的同时，判断其是否可以进行where条件的过滤，也就是将where条件的过滤放到引擎层，减少了server层读取的数据量。使用ICP可以在extra列看到Using index condition