MySQL索引

索引的常见模型

哈希表

一种以键 - 值（key-value）存储数据的结构，把值放在数组里，用一个哈希函数把
key 换算成一个确定的位置，然后把 value 放在数组的这个位置。


多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的
一种方法是，拉出一个链表。


新增时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。


哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些
NoSQL 引擎

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀


但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高


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

树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。
搜索时间复杂度是 O(log(N))
更新的时间复杂度也是 O(log(N))


二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。
其原因是，索引不止存在内存中，还要写到磁盘上


为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。
那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小

InnoDB 的索引模型

InnoDB 使用了 B+Tree 索引模型，数据都是存储在 B+Tree 中的
B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数


在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下

索引类型分为主键索引和非主键索引


主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引


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

基于主键索引和普通索引的查询


如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；


如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引 树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。


基于非主键索引的查询需要多扫描一棵索引树
“尽量使用主键查询”原则，可以避免每次查询需要搜索两棵树

• 重建索引
  索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间

重建索引 k (合理的，可以达到省空间的目的)
alter table T drop index k;
alter table T add index(k);


重建主键索引
alter table T drop primary key;
alter table T add primary key(id);


InnoDB 这种引擎,虽然删除了表的部分记录,但是它的索引还在
重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。
用这个语句代替 ： alter table T engine=InnoDB

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护


B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护


以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。


如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。


而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。


除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。


当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

哪些场景下应该使用自增主键，而哪些场景下不应该
1、性能方面


自增主键:每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。


业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高

2、存储空间的角度


由于每个非主键索引的叶子节点上都是主键的值，显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

覆盖索引

create table T
(
    ID    int primary key,
    k     int         NOT NULL DEFAULT 0,
    s     varchar(16) NOT NULL DEFAULT '',
    index k(k)
) engine=InnoDB;

• 执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行

SQL 查询语句的执行流程:

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。
   
   
   在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引

• 扫描行数

引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2

覆盖索引可以减少树的搜索次数，显著提升查询性能
组合索引（覆盖索引）

最左前缀原则

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

• 在建立联合索引的时候，如何安排索引内的字段顺序

索引的复用


当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了


如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的


原则就是空间


如果既有联合查询，又有基于 a、b 各自的查询;查询条件里面只有 b 的语句， 是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引,需要同时维护 (a,b)、(b) 这两个索引.


如果 b 字段比 a 字段大，则创建一个 (b,a) 的联合索引和一个 (a) 的单字段索引

索引下推

联合索引（name, age）

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

前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”


MySQL 5.6 之前
只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
需要回表 4 次

MySQL 5.6 引入的索引下推优化
在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次

普通索引和唯一索引

• 查询过程

  对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
  对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  
  
  这个不同带来的性能差距微乎其微
  
  
  InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时 候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
  
  
  那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
  如果这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页（出现这种情况的概率会很低）

• 更新过程

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


将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭 （shutdown）的过程中，也会执行 merge 操作

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

1、对于唯一索引来说：

所有的更新操作都要先判断这个操作是否违反唯一性约束
这个记录要更新的目标页在内存中，判断到没有冲突，插入这个值，语句执行结束
这个记录要更新的目标页不在内存中，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；


2、普通索引
这个记录要更新的目标页在内存中，找到位置，插入这个值，语句执行结束
这个记录要更新的目标页不在内存中，则是将更新记录在 change buffer，语句执行就结束了

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

使用场合

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，尽量选择普通索引。


如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。 而在其他情况下，change buffer 都能提升更新性能。

Mysql为什么有时候会选错索引？

#慢查询日志配置,slow_query_log的值为OFF,表示慢查询日志是禁用的
show variables  like '%slow_query_log%';
#开启慢查询日志,1表示开启,0表示关闭
set global slow_query_log =1;
# 将慢查询日志的阈值设置为 0,表示这个线程接下来的语句都会被记录入慢查询日志中  
set long_query_time=0;

优化器的逻辑

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

• 扫描行数是怎么判断的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。


这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

• MySQL 是怎样得到索引的基数的呢？

MySQL 采样统计的方法
InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。


而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。


在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
1、设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
2、设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行


统计信息不对，那就修正。analyze table t 命令，可以用来重新统计索引信息

字符串加索引

• 1、直接创建完整索引，这样可能比较占用空间；
• 2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

  前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询 成本

  -- 计算前缀索引区分度
  select count(distinct email) as L from SUser;
  
  select
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
  from SUser;
  
  

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