mysql|索引相关

索引

索引的出现是为了提高数据的查询效率，就行书本的目录一样，mysql中索引的实现是基于存储引擎的。

实现

哈希表

说明:以键值存储的数据结构问题:哈希冲突的处理范围:适用于等只查询的场景

有序数组

说明:用顺序存储，查询时使用二分法可以快速定位问题:更新效率低范围:适用于静态存储引擎，即更新少或不更新的情况

搜索树

说明:使用树来处理，搜索和插入的效率高问题:数据存储不适用于二叉树，因为树高过高，可以使用N叉树

innodb索引模型

innodb使用B+树作为处理底层索引的数据结构，B+树能够很好的配合磁盘的读写特性，减少单次查询的磁盘访问次数，减少磁盘的访问耗时，提高查询效率

主键索引(聚类索引)

值存的是整行内容，因此不需要回表操作，尽量使用主键索引

非主键索引(二级索引)

值存的是主键内容，因此需要回表操作，多一次索引。

覆盖索引

未来解决回表问题，可以建立相关覆盖索引，覆盖索引名称由来，通过索引查询到的数据覆盖了我们的查询需求，则称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能。一般通过联合索引的建立实现覆盖索引功能，但索引的冗余和覆盖索引的优势之间需要程序员自己权衡。

最左前缀原则

联合索引的使用规则是需要符合最左前缀原则的，即索引使用的是联合索引中从左到右的字段来搜索的，这个原因是索引的基础结构B+树，树的节点存储的数值就是索引的数值，数值如何比较自然是通过从左到右的数值排序来分，因此需要按最左原则进行使用索引，不然那就查不了了。

我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推

mysql 5.6 引入的索引下推功能，即联合索引查出来的数据，如果where子句还有对其他索引字段的判断，则直接通过索引值判断，而不用回表取整行数据来判断。即联合索引(a,b,c)如果where子句里是a=1 and b>0 and c<5则通过a获取的索引可以继续拿b和c针对where子句判断，是否满足条件，满足则回表或直接返回。

索引效率

普通索引和唯一索引的对比

查询效率

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
由于innoDB的数据是按数据页为单位来读写的，因此针对普通索引的查询下一个不满足条件的记录，很大程度上就是一次指针寻址和计算，因此效率上相差不大

更新效率

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer使用场景

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

和redo log的区别

redo log记录所有操作，包括change buffer里的记录的。redo log是为了确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

索引错选

索引选择的条件

选择索引是优化器的工作。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数如何统计

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

我们可以使用 show index from table_name，可以查询到一个索引的基数。

MySQL 采样统计的方法。为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

如何修复错误统计

使用analyze table t命令，可以用来重新统计索引信息。

如何修复索引选择错误

采用 force index 强行选择一个索引
修改查询语句，引导 MySQL 使用我们期望的索引
在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

前缀索引

数据直接创建完整索引，这样可能比较占用空间，可以创建前缀索引，即基于字段的前缀部分建立索引，节省空间

前缀索引的缺点

但会增加查询扫描次数，并且不能使用覆盖索引

前缀索引的选择

可以通过以下语句检测table中T字段的前N个字符建立索引的大概区分度

select count(distinct left(T,N)) as L from table_name;

特殊情况考虑

如果数据的前缀部分建立索引的区分度不高，则可以考虑以下两种方式

倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

索引使用

函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。如果统计后发现索引的量比主键更少，就好遍历索引，进行全索引扫描。

解决办法:

拆分语句，例如针对月份的查找，拆分为时间段的起止时间

优化器偷懒

如下语句不走索引

select * from table where aid + 1 = 10000