MySQL索引

索引的目的是为了提高数据查询的效率。常见的索引模型：哈希表、有序数组和搜索树。
    哈希表：键-值(key-value)，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。
解决哈希冲突的方式是链表。适用于只有等值查询的场景。
    有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))。查询效率高，更新效率低。适用于静态存储引擎。
    二叉搜索树：每个节点的左儿子小于父节点，右儿子大于父节点。查询和更新的时间复杂度都为O(log(N))。
数据库存储大多不适用二叉树，因为树太高，访问的数据块太多。使用"N叉树"，"N叉树"中的"N"取决于数据块的大小。
InnoDB使用了B+树索引模型，所有数据都存储在B+树中。每一个索引在InnoDB里面对应一颗B+树。

// 主键列为id的表，表中有字段k，并且在k上有索引。
mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
insert into T(id,k) values(100,1),(200,2),(300,3),(500,5),(600,6),(700,7);

主键索引(聚簇索引)的叶子节点存的是整行数据
非主键索引(二级索引)的叶子节点内容是主键的值

# 覆盖索引

select * from T where k between 3 and 5
//查询过程中读了k索引树的3条记录(步骤1、3、5)，回表两次(步骤2、4)。

select id from T where k between 3 and 5
// 只需要查id的值，而id的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。

也就是说，在这个查询里，索引k已经"覆盖"了我们的查询需求，称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

# 最左前缀原则（联合索引）
B+树这种索引结构，可以利用索引的"最左前缀"，来定位记录。
Q：在建立联合索引的时候，如何安排索引内的字段顺序？
1、如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。已经有(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的。此时就需要同时维护(a,b)、(b)这两个索引。
2、考虑空间。如果a字段比b字段大，则使用(a,b)、(b)减少空间。

# 索引下推
以联合索引(name, age)为例，检索出表中"名字第一个字是张"，而且年龄是10岁的所有男孩。

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

根据前缀索引规则，这个语句在搜素索引树时，只能用"张"，找到第一个满足条件的记录(假设为id3)，然后判断其他条件是否满足。
MySQL5.6之前，只能从id3开始一个个回表，到主键索引上找出数据行，再对比字段值。
MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足的记录，减少回表次数。

# 索引维护
B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。如果插入新的行id值为700，则只需在R5的记录后面插入一条新记录。如果新插入的id值为400，需要逻辑上挪动后面的数据，空出位置。
如果一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。
当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

Q：建表语句里为什么要有自增主键？
定义自增主键：NOT NULL PRIMARY KEY AUTO_INCREMENT
自增主键的插入模式，每次插入一条新记录，都是追加操作，不涉及到挪动其他记录，也不会触发叶子节点的分裂。
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
从性能和存储空间方面考量，自增主键往往是更合理的选择。

Q：有没有什么场景适合用业务字段直接做主键？
有，要求只有一个索引，并且该索引必须是唯一索引。

Q：对于上面表T，通过两个alter语句重建索引k，以及通过两个alter语句重建主键索引是否合理？

// 重建索引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);

首先回答，为什么要重建索引？
    索引可能因为删除或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，
这样页面的利用率最高，也就是索引更紧凑、更省空间。
    重建索引k的做法是合理的，可以达到省空间的目的。但是重建主键的过程是不合理的。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句可以用这个语句代替：

alter table T engine=InnoDB

Q：下面的索引设置是否合理？

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

由于历史原因，这个表需要a、b做联合主键，单独在字段c上创建一个索引，就已经包含了三个字段，为什么要创建"ca" "cb"这两个索引？业务里有这样的两个语句，这两个索引是否都是必须的？

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

结论：ca可以去掉，cb需要保留。
表记录
--a--|--b--|--c--
  1 2 3
  1 3 2
  1 4 3
  2 1 3
  2 2 2
  2 3 4
主键a, b的聚簇索引组织顺序相当于order by a, b，也就是先按a排序，再按b排序，c无序。

索引 ca 的组织是先按c排序，再按a排序，同时记录主键
–c--|–a--|–主键部分b-- （注意，这里不是ab，而是只有b)
  2 1 1
  2 2 2
  3 1 1
  3 1 1
  3 2 2
  4 2 2
由于主键的聚簇索引本身就是按order by a, b排序，无需重新排序，所以ca和索引c的数据是一模一样的。

索引 cb 的组织是先按c排序，再按b排序，同时记录主键
–c--|–b--|–主键部分a-- （同上）
  2 1 2
  2 3 1
  3 1 2
  3 2 1
  3 4 1
  4 3 2
order by b的顺序与主键顺序不一致，需要额外排序，cb索引可以把排序优化。

Q：普通索引和唯一索引，应该怎么选择？
从查询过程和更新过程来分析
一、查询过程
select id from T where k=5;
普通索引：查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
InnoDB的数据是按数据页为单位来读写的，也就是说，当需要读一条记录的时候，是以页为单位，将其整体读入内存。InnoDB中，每个数据页的大小默认是16KB。
因为引擎是按页读写的，所以说，当找到k=5的记录时，它所在的数据页就都在内存里了。
对于普通索引，要多做的那一次"查找和判断下一条记录"的操作，就只需要一次指针寻址和一次计算。
因此，两者的性能差距是微乎其微的。

二、更新过程
在表中插入新记录(4,400)，InnoDB的处理流程。
1、这个记录要更新的目标页在内存中
唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束。
普通索引：找到3和5之间的位置，插入这个值，语句执行结束。
这种情况下，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。
2、这个记录要更新的目标页不在内存中
唯一索引：将数据页读入内存中，判断到没有冲突，插入这个值，语句执行结束。
普通索引：将更新记录在change buffer，语句执行结束。

# change buffer概念
    当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问到这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。
change buffer是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。
merge：把change buffer应用到旧的数据页，得到新的数据页的过程，称为merge。

Q：触发merge的几种情况：
1、访问到这个数据页
2、系统有后台线程会定期merge
3、数据库正常关闭(shutdown)的过程中，也会执行merge操作。

Q：merge的过程是否会把数据直接写会磁盘？
merge的流程：
1、从磁盘读入数据页到内存（老版本的数据页）
2、从change buffer里找出这个数据页的change buffer记录(可能有多个)，依次应用，得到新版数据页。
3、写redo log。这个redo log包含了数据的变更和change buffer的变更。
到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，
之后各自刷回自己的物理数据，就是另外一个过程了。

    将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升，同时，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。
    change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

// 查看change buffer的设置
mysql> show variables like "innodb_change_buffer_max_size";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.01 sec)

# change buffer的使用场景
change buffer对更新过程起到加速作用。
（1）唯一索引的更新不能使用change buffer。
（2）主键索引不能使用change buffer。
    merge的时候是真正进行数据更新的时刻，change buffer的主要目的是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多(也就是这个页面上要更新的次数越多)，收益就越大。
    对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。（账单类、日志类的系统）。相反，一个业务的更新模式是写入之后马上做查询，change buffer反而起到了副作用。
原因是，将更新先记录在change buffer，但之后马上要访问这个数据页，会立即出发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

# change buffer和redo log

insert into t(id,k) values(id1,k1),(id2,k2);

假设当前k索引树的状态，查找位置后，k1所在的数据页在内存中(InnoDB buffer pool)中，k2所在的数据页不在内存中。

执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

Q：如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据？
虽然是只更新内存，但是在事务提交的时候，change buffer的操作记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。

# MySQL有时候为什么会选错索引？
    选择索引是优化器的工作。优化器选择索引考虑的因素：扫描行数、是否使用临时表、是否排序等。
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

Q：扫描行数是怎么判读的？
MySQL根据统计信息来估算记录数。统计信息就是索引的"区分度"。一个索引上不同的值的个数，称为基数(Cardinality)。这个基数越大，索引的区分度越好。

Q：MySQL是怎样得到索引的基数呢？
MySQL采用采样统计。InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M时，会自动触发重新做一次索引统计。
MySQL中有两种存储索引统计的方式，通过参数innodb_stats_persistent控制。
设置为on，表示统计信息会持久化存储。默认的N是20，M是10。
设置为off，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

mysql> show variables like "innodb_stats_persistent";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.36 sec)

// 查看索引的基数
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100421 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a        |            1 | a           | A         |      100421 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b        |            1 | b           | A         |      100421 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.11 sec)

Q：索引选择异常时如何处理？
1、采用force index强行选择一个索引
2、修改语句，引导MySQL使用我们期望的索引
3、在某些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或者删掉误用的索引。
    MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

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

Q：怎么给字符串字段加索引？
1、直接创建完整索引，这样可能比较占用空间。
2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。
4、创建hash字段索引，查询性能稳定，有额外的存储和计算消耗。
第3、4种方式不支持范围扫描。