目录
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;