内容为慕课网的"高并发 高性能 高可用 MySQL 实战"视频的学习笔记内容和个人整理扩展之后的笔记,本节内容讲述的索引优化的内容,另外本部分内容涉及很多优化的内容,所以学习的时候建议翻开《高性能Mysql》第六章进行回顾和了解,对于Mysql数据的开发同学来说大致了解内部工作机制是有必要的。
由于文章内容过长,所以这里拆分为两部分,上下部分的内容均使用sakila-db,也就是mysql的官方案例。第一部分讲述优化的理论和Mysql过去的优化器设计的缺陷,同时会介绍更高的版本中如何修复完善这些问题的(但是从个人看来新版本那些优化根本算不上优化,甚至有的优化还是照抄的Mysql原作者的实现的,发展了这么多年才这么一点成绩还是要归功于Oracle这种极致商业化公司的功劳)。
如果内容比较难,可以跟随《Mysql是怎么样运行》个人读书笔记专栏补补课,个人也在学习和同步更新中。 地址如下:https://juejin.cn/column/7024363476663730207。
sakila-db是什么?国外很火的一个概念,指的是国外的电影租赁市场使用租赁的方式进行电影的观看十分受外国的喜欢。这里介绍是因为后续的内容都用到了这个案例。所以我们需要提前把相关的环境准备好,从如下地址进行下载:
下载地址:https://dev.mysql.com/doc/index-other.html
《高性能Mysql》的SQL 案例也是使用官方的example
work-bench是官方开发的数据库关系图的可视化工具,使用官方案例的具体关系图展示效果如下,通过这些图可以看到Sakila-db之间的大致关系:
work-bench也是开源免费软件,下载地址如下: https://dev.mysql.com/downloads/workbench/
安装workbench和下载sakila-db的方式这里不做记录,在运行的时候需要注意先建立一个数据库运行sheme文件,然后执行data的sql文件,最终在navicat中查看数据:
首先是索引的特点以及作用:
下面介绍Mysql相关的索引类型:
其他索引类型注意事项:
Archive 在5.1之后才支持单列自增索引。
MyISAM 支持压缩之后的前缀索引,使得数据结构占用更小。
哈希索引
在Mysql中唯一显式实现哈希索引的存储引擎为Memory,Memory是存在非唯一哈希索引,同时BTree也支持“自适应哈希索引的方式“兼容哈希索引。
下面是哈希索引特点:
聚簇索引
聚簇表示数据行的值紧凑存储在一起。而innodb聚簇的值就是主键的值,所以通常使用都是主键上的索引,针对主键索引的选择十分重要。由于本部分着重索引优化,聚簇索引这里就不再讲述了。
MyISam和Innodb的主键索引区别是MyISam的索引很简单,因为数据行只包含行号,所以索引直接存储列值和行号,数据单独存放另一处,类似于一个唯一非空索引,索引和数据不在一处,MyISam的索引设计比InnoDB简单很多,这和MyIsam不需要支持事务也有直接关系,而innodb将索引和行数据放入一个数据结构,将列进行紧凑的存储。
聚簇索引有下面优点
当然索引也有下面的缺点:
压缩索引
压缩索引的特点是使用更少的空间存放尽可能多的内容,但是这样的处理方式仅仅适用于IO密集型的系统,压缩前缀存储形式最大的缺陷是无法使用二分法进行查找,同时如果使用的倒序索引的方式比如order by desc 的方式可能会因为压缩索引的问题存在卡顿的情况。
Bree索引的特点
自适应哈希索引
当innodb发现某些索引列和值使用频繁的时候,BTree会在此基础上自动创建哈希索引辅助优化,但是这个行为是不受外部控制的,完全是内部的优化行为,如果不需要可以考虑关闭。
Btree查询类型
针对Innodb的Btree索引,有下面几种常见的查询方式:
下面是关于建立索引的一些常见策略:
对于索引的策略我们还需要了解下面的细节
索引碎片优化
Innodb的数据结构和特性会导致索引存在数据碎片,对于任何存储结构来说顺序的存储结构是最合适的,并且索引顺序访问要比随机访问快更多,数据存储的碎片比索引本身复杂很多,索引碎片通常包含下面的情况:
对于上面几点,对于myisam 都有可能出现,但是innodb的行碎片不会出现,内部会移动碎片重写到一个片段。
索引碎片的处理方式:在Mysql中可以通过optimize table
导入和导出的方式重新整理数据,防止数据碎片问题。
索引规则
接着是索引顺序问题,由于BTree的结构特性,索引都是按照建立顺序进行查找的,通常不包含排序和分组的情况下,把选择性最高的索引放在最左列是一个普遍正确策略。
如何查看索引基数:show index from sakila.actor
,还有一种方式是通过information_schema.statistics
表查询这些信息,可以编写为一个查询给出选择性较低的索引。
当innodb打开某些表的时候会触发索引信息的统计,比如打开information_schema
表或者使用show table status
和show index
的时候,所以如果在系统要运行压力较大的业务时期尽量避开这些操作。
冗余重复索引
Mysql允许同一个列上创建多种类型的索引,有时候会因为建表的特性问题给字段重复建索引造成不必要的性能浪费。冗余索引和重复索引有什么区别?
冗余索引:是符合最左匹配法则的情况下重复对相同列建立索引。
重复索引:是对于不最做的方式创建的索引就有可能是重复创建索引。
比如联合索引:(A,B) 如果在创建 (A)或者(A,B)都是重复索引,但是创建(B)就不是重复索引而是冗余索引。另外某些十分特殊的情况下可能用到冗余索引,但是这会极大的增加索引维护的开销,最为直观的感受是插入、更新、删除的开销变得很大。
多列索引
首先多列索引不是意味着where
字段出现的地方就需要加入,其次多列索引虽然在现在主流使用版本中(5.1版本之后)实现了索引内部合并,也就是使用and or
或者and
和or
合并的方式相交使用索引,但是他存在下面几个缺点
文件排序
文件排序遵循Innodb的Btree索引的最基本原则:最左前缀原则,如果索引列的顺序和order by排序一致,并且查询列都和排序列都一样才会用索引替代排序,对于多表查询则排序字段全为第一个表才能进行索引排序。但是有一个特例那就是排序字段的前导列为常量的时候依然可以使用索引排序。
案例:rental 表的联合索引列进行排序
Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项,它可以从索引的后面往前面读,性能上比加索引提示要好的多
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc;
-- Backward-index-scan
-- Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项,它可以从索引的后面往前面读,性能上比加索引提示要好的多
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Backward index scan
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id, staff_id;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
-- 无法使用索引
EXPLAIN select rental_id,staff_id from rental where rental_date > '2005-05-25' order by inventory_id, customer_id;
-- 1 SIMPLE rental ALL rental_date 16008 50.00 Using where; Using filesort
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;
-- 1 SIMPLE rental range rental_date,idx_fk_inventory_id rental_date 8 2 100.00 Using index condition; Using filesort
explain select actor_id, title from film_actor inner join film using(film_id) order by actor_id;
-- 1 SIMPLE film index PRIMARY idx_title 514 1000 100.00 Using index; Using temporary; Using filesort
-- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 100.00 Using index
查询优化的排查意味着我们需要先了解Mysql的各个组件在各步骤中做了哪些事情,下面这张图来自于《高性能Mysql》,对于一次客户端的请求,大致分为下面的流程:
对于关系型的数据库来说,核心部分在于查询优化器和执行计划的部分,因为不管我们如何编写SQL语句,如果没有强大的优化器和执行计划那么一切都是空谈,所以本部分的重点也会围绕优化器进行讲解,在此之前我们先看看其他组件的工作:
首先查询缓存不需要过多解释,他的作用是当用户重复执行一个查询的时候会内部对于结果进行缓存,但是一旦用户修改查询条件,缓存就失效了,在早期的互联网环境中这种处理很不错,可以减少磁盘IO和CPU的压力,但是到了现在的环境下显然不适合,所以8.0删除也是可以理解的。
接着是解析器,解析器这部分主要工作是通过解析语法形成解析树对于语句进行预处理,预处理可以类看作我们编译器把我们写的编程语句“翻译”为机器代码的过程,让下一步的优化器可以认识这颗解析树去进行解析,
如果想要了解SQL解析优化的底层过程,可以从这篇文章入手:
SQL解析在美团的应用 - 美团技术团队 (meituan.com)
在上面的博客中提到了一个DBA必须掌握的工具pt-query-digest,分析慢查询日志,下面这个文章中提供了一个实际的案例来排查和优化,案例较为简单适合刚接触这个工具的人进行学习和思考,这里一并列出来了。
使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)
SQL解析部分笔记: 词法分析:核心代码在sql/sql_lex.c文件中的,
MySQLLex→lex_one_Token
MySQL语法分析树生成过程:全部的源码在sql/sql_yacc.yy
中,在MySQL5.6中有17K行左右代码 最核心的结构是SELECT_LEX,其定义在sql/sql_lex.h
中
下面我们来深入看看优化器的部分工作内容以及Mysql优化历史:
由于讲述优化器的内容较少,这里直接总结《高性能Mysql》的内容,优化器也不需要研究和记忆,因为随着版本的迭代不断更新优化器会不断调整,一切要以真实实验为准:
1. 子查询关联:
下面的查询在通常情况下我们会认为先进行子查询,然后通过for循环扫描film表进行匹配操作,然后从explain的结果中可以看到这里的查询线进行了全表扫描,然后通过关联索引进行第二层的for循环查询,这样的写法类似exists
。
explain select * from sakila.film where film_id in (select film_id from film_actor where actor_id)
-- 1 SIMPLE film ALL PRIMARY 1000 100.00
-- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 90.00 Using where; Using index; FirstMatch(film)
优化这个子查询的方式使用关联查询替代子查询,但是需要注意这里存在where条件才会走索引,否则和上面的结果没有区别:
explain select film.* from sakila.film film join film_actor actor using (film_id) where actor.actor_id = 1
另一种是使用exists的方式进行关联匹配。
explain select * from film where exists (select * from film_actor actor where actor.film_id = film.film_id and actor.actor_id = 1);
可以看到哪怕到了5.8的版本,Mysql的子查询优化既然没有特别大的改进,所以通常情况下如果不确定in查询的内容大小,建议用exists或者join进行查询,另外也不要相信什么in查询就一定慢点说法,在不同的mysql优化器版本中可能会有不同的效果。
2. union查询
虽然多数情况下我们会用union替换or,但是更多的情况是应该尽量避免使用union,因为union查询会产生临时表和中间结果集容易导致优化索引失效,需要注意的是 union会触发内部的排序动作,也就是说union会等价于order by
的排序,如果数据不是强烈要求不能重复,那么更建议使用union all,对于优化器来说这样工作更加简单,直接把两个结果集凑在一起就行,也不会进行排序。
union查询能不用就不用,除非是用来代替or查询的时候酌情考虑是否有必要使用。
最后注意union的产生排序不受控制的,可能会出现意料之外的结果。
3. 并行查询优化
并行查询优化在8.0中终于有了实现,可以根据参数:innodb_parallel_read_threads =并行数
来验证。
由于个人是M1的CPU,读者可以根据自己的实际情况进行实验。
set local innodb_parallel_read_threads = 1;
select count(*) from payment;
set local innodb_parallel_read_threads = 6;
select count(*) from payment;
从执行结果可以看到仅仅是1万多条数据的count(*)查询就有明显直观的差距:
4. 哈希关联
官方文档的介绍地址:Mysql官方文档哈希关联
在MySQL 8.0.18中Mysql终于增加了哈希关联的功能。在此之前的版本中,Mysql的优化器通常只支持for循环嵌套关联,曲线救国的方法是建立一个哈希索引或者使用Memory存储引擎,而新版本提供的哈希关联则提供了一种新的对关联方式,哈希关联的方式如下:
把一张小表数据存储到内存中的哈希表里,通过匹配大表中的数据计算哈希值,并把符合条件的数据从内存中返回客户端。
对于Mysql的哈希关联,我们直接使用官方的例子:
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
EXPLAIN
SELECT * FROM t1
JOIN t2 ON t1.c1=t2.c1;
-- Using where; Using join buffer (hash join)
除开等值查询以外,Mysql的8.0.20之后提供了更多的支持,比如在 MySQL 8.0.20 及更高版本中,连接不再需要包含至少一个等连接条件才能使用哈希连接,除此之外它还包括下面的内容:
-- 8.0.20 支持范围查询哈希关联
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1;
-- 8.0.20 支持 in关联
EXPLAIN SELECT * FROM t1
WHERE t1.c1 IN (SELECT t2.c2 FROM t2);
-- 8.0.20 支持 not exists 关联
EXPLAIN SELECT * FROM t2
WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c2);
-- 8.0.20 支持 左右外部连接
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
注意8.0.18版本的哈希关联仅仅支持join查询,对于可能会带来笛卡尔积的左连和右连接查询是不支持的。但是在后续的版本中提供了更多查询条件支持 另外,8.0.20版本之前想要查看是否使用hash join,需要结合
format=tree
选项。
最终Mysql在8.0.18版本中曾经提供过开关哈希索引和设置优化器提示optimizer_switch
等参数来判定是否给予hash join的提示,真是闲的蛋疼(官方自己也这么认为)所以在8.0.19立马就把这些参数给废弃。
注意哈希连接不是没有限制的,了解哈希关联的流程就会发现如果哈希表过大,会导致整个哈希关联过程在磁盘中完成其速度可想而知,所以官方提供了下面的建议:
join_buffer_size
,也就是增加哈希关联的哈希表缓存大小,防止进入磁盘关联。open_files_limit
数量,这个参数什么意思这里就不介绍了,意义是增加这个参数可以增加关联的时候关联次数。吐槽:说句心里话自Mysql被Oracle收购之后,越来越商业化的同时进步也越来越小,in查询优化这一点其实在很多开源库甚至Mysql的原作者给解决了,但是Mysql到了8.0依然和多年前的《高性能Mysql》结果没有差别。哎。。。。。 Mysql数据库的发展也告诉我们时刻保持开放的心态,吸取教训正视不足和改进,才不会被时代逐渐淘汰。
5. 松散索引
松散索引在Mysql5.6之后已经支持,松散索引简单理解就是在进行多列索引扫描的时候,即使次索引不是有序的,但是跳过索引是有序的,也可以走索引来快速匹配数据。
松散索引的优化细节放到了下半部分的文章,这里简单讲述一下大致的工作原理。
在Postgresql中,支持下面的语法:
update tbl_info
set name = tmp.name
from
(select name from tbl_user where name ='xxx')
tmp
[where ....]
-- 比如下面的写法:
UPDATE `sakila`.`actor` SET `first_name` = 'PENELOPE'
from
(select address,address_id from address where address_id = 1) tmp
WHERE `actor_id` = 1 and actor.actor_id = tmp.address_id;
但是很可惜这种语法在Mysql是没有办法实现也是不支持的,哪怕到了8.0.26依然没有支持,这和Mysql的优化器设计有着本质的关系。
优化器提示没有多少意义,这里直接略过了。
从实际的情况来看Mysql最大值和最小值这两个函数使用并不是很多所以不再进行介绍了,另外无论什么样的数据库都不是很建议频繁使用函数,而是改用业务+简单SQL实现高效索引优化。
其他慢查询优化
对于慢查询的优化我们需要清楚优化是分为几种类别的,在Mysql中优化策略分为动态优化和静态优化:静态优化主要为优化更好的写法,比如常数的排序和一些固定的优化策略等,这些动作通常在一次优化过程中就可以完成。而动态优化策略要复杂很多,可能会在执行的过程中优化,有可能在执行过后重新评估执行计划。
静态优化是受优化器影响的,不同版本有不同情况,所以这里讲述动态优化的情况,而动态优化主要包含下面的内容:
上半部分以理论为主,下半部分将会着重实战内容进行介绍。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。