目录
6.1. 索引的常见模型
哈希表
是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。（ 哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎）（插入快，范围查询慢）
有序数组
在等值查询和范围查询场景中的性能就都非常优秀。 但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。
二叉搜索树
二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。
6.2. InnoDB 的索引模型？
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
6.3. 主键索引和非主键索引的存储区别？
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）；
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
6.4. 基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询
6.5. 索引的维护？
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
6.6. 如何重建索引？
alter table T drop index k;
alter table T add index(k);
6.7. 如何重建主键索引？
alter table T drop primary key;
alter table T add primary key(id);
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB
6.8. 覆盖索引？
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
select * from T where k between 3 and 5；
上边的查询语句可以使用覆盖索引进行优化？
select * from T where ID IN(select ID from T where k between 3 and 5)
6.9. 联合索引？
联合索引是指 联合索引就是一棵B+树，不同的是联合索引的键值的数量不是1，而是>=2。
联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理
对于查询select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的；
对于单个列a的查询select * from table where a=xxx,也是可以使用（a,b）这个索引的。
但对于b列的查询select * from table where b=xxx,则不可以使用（a,b） 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引。
6.10. 最左前缀原则？
如果为每一种查询都设计一个索引，索引是不是太多了，但是又不能让业务查询进行全表扫描把？
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。
那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。 这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
6.11. 索引下推
图 1 中，在 (name,age) 索引里面去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。
图 2 跟图 1的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
6.12. 普通索引和唯一索引，应该怎么选择？
查询过程：
对于普通索引来说 ，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录；
对于唯一索引来说，由于索引定义的唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
由于mysql的innodb引擎是按数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出，而是以页为单位将其整体读入内存。再Innodb中每个数据页的大小默认为16KB.
更新过程：
当需要更新一个数据页时，如果数据页在内存中就直接更新，否则Innodb会先将这些更新操作缓存到change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作；通过这种方式就能保证这个数据逻辑的正确性。
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，而这必须要读入到内存才能判断，如果已经读入到内存则直接更新内存会更快，就不需要使用change buffer了。
change buffer也就只能是普通索引才能使用
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
merge 的执行流程是这样的：
从磁盘读入数据页到内存（老版本的数据页）；
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log；
这个 redo log 包含了数据的变更和 change buffer 的变更；
到这里 merge 过程就结束了；
这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。
6.13. mysql为什么会选错索引？
优化器、扫描行数
mysql在真正开始执行语句之前，并不能精确地直到满足条件的记录有多少条，而只能根据统计信息来估算记录数；
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好；而一个索引上
不同的值的个数，我们称之为“基数”，也就是说，这个基数越大，索引的区分度越好。可以使用 ”show index from 表名“方法，看到一个索引的基数。
mysql是怎么得到索引的基数？
mysql是选用采样统计的方式，因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高，所以只能选择
“采样统计”。
采样统计的方法？
采样统计的时候，InnoDB默认会选择N个数据页，停机这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到这个索引的基数。
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。
索引统计的行数不准确？
如果只是索引统计不准确，通过 analyze 命令可以解决很多问题，但是前面我们说了，优化器可不止是看扫描行数。
索引选择异常和处理？
一种方法是，采用 force index 强行选择一个索引；
第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。
第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
6.14. 怎么给字符串字段加索引？
1、直接创建完整索引，这样可能比较占用空间；
2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4、创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
6.15. InnoDB刷脏页的控制策略？
mysql中的redolog日志是一个循环读写的日志，写满了就需要把内存中的数据flush到磁盘中，以便于从新开始写日志，以保证数据的一致性；当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
1、一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2、日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。
正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
设计策略控制刷脏页的速度，会参考哪些因素呢？
这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是 redo log 写满。
所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字，计算这个数字的伪代码类似这样：
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
2979
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
