mysql 索引

数据库的索引，索引的出现是为了提高数据查询的效率，就像书的目录一样。

那它是怎么实现的，于是引入了它的模型。

有三种

• 哈希表
• 有序数组
• 搜索树

哈希表是一种以键-值对存储数据的结构，只要输入待查找的值即key,就可以找到对应的值value.但是相应的也有缺点，它的适用场景，只有等值查询的场景。为啥呢？当哈希冲突时，拉链法，遍历时间增加。

有序数组在等值查询和范围查询中的性能都很优秀。等值即递增，用二分法，或者范围查询，也是二分，再查找，很快。

所以有序数组索引只适用于静态存储引擎。

其次就是二叉搜索树，为了保持平衡，二分法，降低查找的时间复杂度，同时，方便插入和删除。（同时满足快速查找，方便插入和删除）

缺点也有，当二叉树，层数变多，数据量太大时，由于数据保存在内存中，（内存大小存在局限性），于是便会增加IO次数，增加查找时间。

反而是二叉树的变种，被广泛用在数据库引擎中。

 

Innodb的索引模型：

在Innodb中，表都是根据主键顺序以索引的形式存放的，这个存储方式的表叫做索引组织表。

又因为Innodb使用了B+树模型索引，所以数据都是存放在B+树中的。

每一个索引在Innodb中对应一棵B+树。

同时，索引分为主键索引和非主键索引。

主键索引的叶子节点存放的是整行数据。在INnodb里，主键索引也叫聚簇索引。

非主键索引的叶子节点内容是主键的值。在Innodb里，非主键索引也被称为二级索引。

那么基于主键索引和普通索引的查询的区别在哪里?

• select * from t where 主键=xx,这是主键查询方式，只需要搜索主键这个颗树
• 如果语句select * from T where k = 5;k是索引，那么走的便是普通索引插叙方式，则就需要先去搜索k索引树，得到主键，再拿主键到主键索引表（索引组织表）中查询，这个过程叫做回表。

于是，基于非聚簇索引（不是走主键索引的sql语句），需要多扫描主键树，所以，尽量使用主键查询。（聚簇索引）、

 

索引维护，B+树为了维护索引有序性，插入值时需要对树做维护，如果所在数据页满了，需要申请一个新的数据页，然后挪动部分数据过去，这叫页分裂。 这个需要复制数据过去，性能受到影响。除了性能问题，页分裂还影响数据页的利用率。

有分裂，就有合并，当相邻的页由于删除了数据，利用率降低，会做数据合并，于是叫合并页。

 

用主键是自动递增，和不是递增（业务字段），主键递增字段，导致，非主键索引，查询会回表，但是非主键索引的叶子节点是主键值，所以比用业务字段做主键，非主键索引写入成本低，存储空间也低。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

从性能和空间来看，自增主键往往是合理选择。

那么什么场景不适合自增字段，适合业务字段呢？、

1.只有一个索引。

2.该索引必须是唯一索引。

最好直接将这个字段做主键，就避免了2次查询。

 

上面讲到，非主键索引会进行回表，那么怎么避免回表呢?

答案就是，覆盖索引。

select id from T where k between 3 and 5;

这时只要查id值，而id值在K索引树上，可以直接提供结果，不需要回表。（需要查询的id在索引树上，不需要回表）

也就是说，我们需要查询里，索引K已经覆盖了我们的查询需求，我们称为覆盖查询。 

由于覆盖索引，可以减少树的搜索次数，提升显著的性能，覆盖索引是一个常用的性能优化手段。

 

其次就是索引的最左匹配原则

由于为每一个查询建立一个索引，那么索引就太多了。

这个查询需求出现概率不高，但还是不要走全表扫描，如果单独创建索引，又会太浪费，怎么做呢？

B+树索引结构，利用索引的“最左前缀”原则。

如果你的需要是查询name,只要你能用上索引，那么就会走索引（同时支持，最左字段匹配，比如单个）

where name like'张%'".这时，也能用上这个索引。

只要满足最左前缀，就可以利用索引来加速检索，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

所以，建立索引时，如何安排索引内的字段顺序就很重要，最好做到复用，所以有了（a,b）这个联合索引后，一般就不需要单独在a上建立索引了。第一原则，通过调整顺序，可以少维护一个索引，那么这个顺序往往就是优先考虑。

如果必须建立多个空余的，那么最好，其次考虑的原则，就是空间，name字段比age字段大的，那么就建议创建一个（name,age）的联合索引和一个（age）单字段索引。

 

其次就是索引下推：

在MySQL5.6之前，是没有索引下推。而在5.6之后，就有索引下推，比如(name,age)当查询name相关的就会进行最左前缀匹配，但是需要变成还要加上年龄有要求。

select * from t where name like '张%' and age=10 and ismale=1;

5.6之前还会进行回表，而5.6及之后在索引遍历过程中，对索引包含的字段进行判断，直接过滤不满足条件的记录，减少回表次数。

 

补充：

索引不是最优的解决方案，中大型的表适合索引，但是也只在需要快速查询的情况下，如果不是为了快速查询，那么没必要。
超大的表，那么建立索引就不是最好的方案，是分区技术。

1.BLOB,TEXT,VARCHAR很长的，一般使用前缀索引。因为mysql不允许这些列的完整长度。alter table city_demo add key (city(6));

 

2.多列索引
多个列上建立单独的单列索引大部分情况下并不能提高mysql的查询性能，mysql5.0和更新版本引入了一种叫“索引合并”策略，算法有3变种：OR条件的联合，AND条件的相交，组合前2种情况的联合及相交。
在sql语句前加explain在extra列看到走不走索引。
出现索引合并，其实就是索引建的。

1.出现多个对多个索引做相交操作多个AND操作，意味需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2.多个索引做联合操作多个OR操作，耗费大量cpu和内存，资源
3.更重要的是，优化器不会把这些计算到“查询成本”，优化器只关心随机页面读取，使得查询成本，被低估，导致该执行计划，还不如直接走全表扫描，这样做不但会消耗更多cpu和内存资源，影响查询的并发性。
可以通过optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

 

 

聚簇索引
一个表有主键，那么就会建立以主键为索引的聚簇索引。
如果一个表没有主键，Innodb会选择一个唯一的非空索引代替，如果没有这样的索引Innodb会隐式定义一个主键来作为聚簇索引，Innodb只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。
聚簇主键可能对性能有帮助，但也可能导致严重的性能问题，需要仔细考虑聚簇索引，尤其是将表的存储引擎从innodb改成其他引擎。
聚集的数据页有一些优点：
1.可以把相关数据保存起来，实现电子邮箱，根据Id来聚集数据，这样只需要从磁盘读取少数的数据页就能读取全部邮件，没有聚簇索引，会导致查询一次，就导致一次磁盘io.
2.数据访问更快。
3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引，的每一个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针，以及所有的剩余列（这里是col2），
innodb的二级索引和聚簇索引很不相同，Innodb二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作，使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，Innodb在移动时，无须更新二级索引中的这个指针。

其实使用uuid作为聚簇索引会很糟糕，聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
这是字段变长还有页分裂和碎片导致的。    

顺序的主键什么时候会造成更坏的结果？
对于高并发的工作负载，在innodb中按主键的插入顺序造成明显的争用，主键的上界会成为“热点”。所有的插入都发生在这里。所有的并发擦入导致间隙锁竞争。另外一个热点是AUTO_INCREMENT锁机制，遇到这个问题，需要重新设计表，或者更改innodb_autoinc_lock_mode配置。
 

检测覆盖索引
explain sql语句时，会在extra:using index

 

使用索引扫描来做排序
mysql有两种方式可以生成有序的结果，1.通过排序操作，2.或者按索引顺序扫描。如果EXPLAIN出来的type列的值“index”,则说明mysql使用了索引扫描来做排序。只有当索引的列顺序和ORDER BY子句的顺序完全一致。并且所有列的排序方向（倒序或正序）都一样时，mysql才能使用索引来对结果进行排序。

1.情况是只有当索引的列顺序和ORer by子句的顺序完全一致，并且所有列的排序方向（desc asc）都一样时，mysql才能使用索引来对结果做排序。