什么是索引

数据库索引是一种能够改善表操作速度的数据结构（哈希表、有序数组和搜索树等）。就像是书的目录。在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

回表,覆盖索引

主键索引的叶子节点储存的是整行记录

非主键索引的叶子节点储存的是主键值

当通过非主键索引检索时，需要到主键索引上查询出整行记录，这个过程叫回表。

当我们执行”select id from table where id_card = xxx” 时，不需要回表。因为ID 的值已经在 k 索引树上了。所以建立合适的联合索引可以减少回表，优化查询。这个就是覆盖索引。

最左前缀原则

(name,age) 联合索引会先对name 进行排序在对age排序，相当于order by name ,age

最左优先，在检索数据时从联合索引的最左边开始匹配。

（name,age）索引上的数据顺序示意

name	age	id
axx	1	1
axx	2	4
ba	1	2
c	18	3

sql “…..where name like ‘a %’” 模糊查询会使用（name,age）索引检索。因为name 是有序的，所以 “a”开头的用户在逻辑上是相邻的。所以能使用索引

sql “…..where name like ‘%a’” 模糊查询则不能使用索引

sql “…..where age >18 “ 模糊查询则不能使用索引

在建立联合索引的时候，一定要注意顺序。

索引下推

sql “…..where name like ‘a % ’ and age =1”。

mysql 5.6 这条sql 会查询id 为1，4的记录回表对比age.查询出id=1的记录。

MySQL 5.6 引入的索引下推优化（index condition pushdown)。可以在（name,age）索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引维护

普通索引会储存主键索引的值。所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

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

change buffer

当需要更新一个数据页时，如果数据页在内存中，则直接更新；否则，在不影响数据一致性的前提下，InnoDB 将这些操作缓存在 change buffer 中，这样就不必从磁盘中读取数据，当下次查询需要访问这个数据页时，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，最后将查询结果返回。

merge

将 change buffer 的操作应用到数据页的过程称为 merge。除了访问数据页会触发 merge 外；系统后台有线程会定期 merge；数据库正常关闭的过程中也会触发 merge 操作。

使用条件

对于唯一索引，所有更新操作都需要做唯一性约束的判断，必须将数据页读入内存，直接在内存中更新，不使用 change buffer 。

对于普通索引，当数据页在内存中时，直接进行更新操作即可；当数据页不在内存中时，直接将更新操作写入 change buffer 即可。

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

在能保证业务正确性的前提现，可以优先考虑普通索引（非唯一索引）

changer buffer 和 redo log

change buffer 主要节省的是随机读磁盘的IO 消耗；

redo log 主要是节省随机写磁盘的IO消耗；

优化器选错索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。受否使用临时表、是否排序,扫描的行数等因素的影响

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

因为不准确的索引基数，会导致MySQL误判需要扫描的行数，从而选错索引。索引基数统计不准可以用 analyze table 来解决

解决办法

1.一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。

2.二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。例如加上order by 等。

3.建立一个更合适的缩影。例如建立联合索引等。

优化项

1.索引上有函数计算，会导致不能走索引的树搜索

2.隐式类型转换，mysql在比较字符和数字时会默认讲字符转换成数字

3.隐式字符编码转换也无法使用索引的树搜索

索引对锁的影响

begin;
select * from t where c=5 for update;
commit;

RR隔离级别下，为保证binlog记录顺序，非索引更新会锁住全表记录，且事务结束前不会对不符合条件记录有逐步释放的过程。即C上没有索引，会锁住全表。

参考丁奇 ·《MySQL实战45讲》

参考《高性能MySQL》

MySQL 索引