DBA整理的万字详解MySQL性能优化,值得收藏!
码农编程进阶笔记
共 17614字,需浏览 36分钟
·
2021-08-15 19:25
SELECT *
、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?MySQL逻辑架构
MySQL查询过程
客户端/服务端通信协议
max_allowed_packet
参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。SELECT *
以及加上LIMIT限制的原因之一。查询缓存
NOW()
或者CURRENT_DATE()
会因为不同的查询时间,返回不同的查询结果,再比如包含CURRENT_USER
或者CONNECION_ID()
的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存 如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗
用多个小表代替一个大表,注意不要过度设计 批量插入代替循环单条插入 合理控制缓存空间大小,一般来说其大小设置为几十兆比较合适 可以通过 SQL_CACHE
和SQL_NO_CACHE
来控制某个查询语句是否需要进行缓存
query_cache_type
设置为DEMAND,这时只有加入SQL_CACHE
的查询才会走缓存,其他查询则不会,这样可以非常自由地控制哪些查询需要被缓存。语法解析和预处理
查询优化
last_query_cost
的值来得到其计算当前查询的成本。mysql> select * from t_message limit 10;
...省略结果集
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+
重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序) 优化 MIN()
和MAX()
函数(找某列的最小值,如果该列有索引,只需要查找B+Tree索引最左端,反之则可以找到最大值,具体原理见下文)提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询) 优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于I/O密集型应用,效率会高很多)
查询执行引擎
返回结果给客户端
客户端向MySQL服务器发送一条查询请求 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划 MySQL根据执行计划,调用存储引擎的API来执行查询 将结果返回给客户端,同时缓存查询结果
性能优化建议
Scheme设计与数据类型优化
通常来说把可为 NULL
的列改为NOT NULL
不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOT NULL。对整数类型指定宽度,比如 INT(11)
,没有任何卵用。INT使用16为存储空间,那么它的表示范围已经确定,所以INT(1)
和INT(20)
对于存储和计算是相同的。UNSIGNED
表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT
存储范围是通常来讲,没有太大的必要使用DECIMAL
数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用TIMESTAMP
使用4个字节存储空间,DATETIME
使用8个字节存储空间。因而,TIMESTAMP
只能表示1970 - 2038年,比DATETIME
表示的范围小得多,而且TIMESTAMP
的值因时区不同而不同。大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用 ALTER TABLE
(如果只只是在列表末尾追加元素,不需要重建表)。schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。 大表 ALTER TABLE
非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。当然有一些奇淫技巧可以解决这个问题,有兴趣可自行查阅。
创建高性能索引
索引相关的数据结构和算法
B-Tree
索引,它是目前关系型数据库中查找数据最为常用和有效的索引,大多数存储引擎都支持这种索引。使用B-Tree
这个术语,是因为MySQL在CREATE TABLE
或其它语句中使用了这个关键字,但实际上不同的存储引擎可能使用不同的数据结构,比如InnoDB就是使用的B+Tree
。B+Tree
中的B是指balance,意为平衡。需要注意的是,B+树索引并不能找到一个给定键值的具体行,它找到的只是被查找数据行所在的页,接着数据库会把页读入到内存,再在内存中进行查找,最后得到要查找的数据。B+Tree
前,先了解一下二叉查找树,它是一种经典的数据结构,其左子树的值总是小于根的值,右子树的值总是大于根的值,如下图①。如果要在这课树中查找值为5的记录,其大致流程:先找到根,其值为6,大于5,所以查找左子树,找到3,而5大于3,接着找3的右子树,总共找了3次。同样的方法,如果查找值为8的记录,也需要查找3次。所以二叉查找树的平均查找次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3
次,而顺序查找的话,查找值为2的记录,仅需要1次,但查找值为8的记录则需要6次,所以顺序查找的平均查找次数为:(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3
次,因为大多数情况下二叉查找树的平均查找速度比顺序查找要快。B+Tree
就是一种多路搜索树。理解B+Tree时,只需要理解其最重要的两个特征即可:第一,所有的关键字(可以理解为数据)都存储在叶子节点(Leaf Page
),非叶子节点(Index Page
)并不存储真正的数据,所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次,所有的叶子节点由指针连接。如下图为高度为2的简化了的B+Tree。页是计算机管理存储器的逻辑块,硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(许多OS中,页的大小通常为4K)。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。
h-1I/O
(根节点常驻内存),复杂度O(h)=O(logMN)
。实际应用场景中,M通常较大,常常超过100,因此树的高度一般都比较小,通常不超过3。B+Tree
节点的操作,在整体上对索引的维护有一个大概的了解,虽然索引可以大大提高查询效率,但维护索引仍要花费很大的代价,因此合理的创建索引也就尤为重要。Index Page
中查询后得知应该插入到50 - 70
之间的叶子节点,但叶子节点已满,这时候就需要进行也分裂的操作,当前的叶子节点起点为50,所以根据中间值来拆分叶子节点,如下图所示。Index Page
和Leaf Page
都满了,就需要做两次拆分,如下图所示。B+Tree
为了保持平衡,对于新插入的值需要做大量的拆分页操作,而页的拆分需要I/O操作,为了尽可能的减少页的拆分操作,B+Tree
也提供了类似于平衡二叉树的旋转功能。当LeafPage
已满但其左右兄弟节点没有满的情况下,B+Tree
并不急于去做拆分操作,而是将记录移到当前所在页的兄弟节点上。通常情况下,左兄弟会被先检查用来做旋转操作。就比如上面第二个示例,当插入70的时候,并不会去做页拆分,而是左旋操作。高性能策略
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(`m`,`f`) not null,
key(last_name,first_name,dob)
);
last_name、first_name
、dob
列的值,下图展示了索引是如何组织数据存储的。1、MySQL不会使用索引的情况:非独立的列
select * from where id + 1 = 5
id = 4
,但是MySQL无法自动解析这个表达式,使用函数是同样的道理。2、前缀索引
3、多列索引和索引顺序
actor_id
和film_id
两个列上都建立了独立的索引,然后有如下查询:select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1
select film_id,actor_id from film_actor where actor_id = 1
union all
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1
当出现多个索引做相交操作时(多个AND条件),通常来说一个包含所有相关列的索引要优于多个独立索引。 当出现多个索引做联合操作时(多个OR条件),对结果集的合并、排序等操作需要耗费大量的CPU和内存资源,特别是当其中的某些索引的选择性不高,需要返回合并大量数据时,查询成本更高。所以这种情况下还不如走全表扫描。
explain
时如果发现有索引合并(Extra字段出现Using union
),应该好好检查一下查询和表结构是不是已经是最优的,如果查询和表都没有问题,那只能说明索引建的非常糟糕,应当慎重考虑索引是否合适,有可能一个包含所有相关列的多列索引更适合。SELECT * FROM payment where staff_id = 2 and customer_id = 584
staff_id,customer_id
)的索引还是应该颠倒一下顺序?执行下面的查询,哪个字段的选择性更接近1就把哪个字段索引前面就好。select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*) from payment
select user_id from trade where user_group_id = 1 and trade_amount > 0
user_group_id,trade_amount
),如果不考虑特殊情况,这看起来没有任何问题,但实际情况是这张表的大多数数据都是从老系统中迁移过来的,由于新老系统的数据不兼容,所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下,通过索引扫描的行数跟全表扫描基本没什么区别,索引也就起不到任何作用。4、避免多个范围条件
select user.* from user where login_time > '2017-04-01' and age between 18 and 30;
login_time
列和age
列,MySQL可以使用login_time
列的索引或者age列的索引,但无法同时使用它们。5、覆盖索引
索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量 索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
6、使用索引扫描来排序
explain
的结果中type
列的值为index
表示使用了索引扫描来做排序。ORDER BY
子句的顺序完全一致,并且所有列的排序方向也一样时,才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有ORDER BY
子句引用的字段全部为第一张表时,才能使用索引做排序。ORDER BY
子句和查询的限制是一样的,都要满足最左前缀的要求(有一种情况例外,就是最左的列被指定为常数,下面是一个简单的示例),其他情况下都需要执行排序操作,而无法利用索引排序。-- 最左列为常数,索引:(date,staff_id,customer_id)
select staff_id,customer_id from demo where date = '2015-06-01' ``order by staff_id,customer_id
7、冗余和重复索引
(A,B)
,再创建索引(A)
就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B)
,但这个索引不是扩展已有的索引(A)
。8、删除长期未使用的索引
explain
后再提测是一种美德。特定类型查询优化
优化COUNT()查询
COUNT()
可能是被大家误解最多的函数了,它有两种不同的作用,其一是统计某个列值的数量,其二是统计行数。统计列值时,要求列值是非空的,它不会统计NULL。如果确认括号中的表达式不可能为空时,实际上就是在统计行数。最简单的就是当使用COUNT(*)
时,并不是我们所想象的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计行数。COUNT(*)
,意义清晰,且性能更好。COUNT
值,可以用近似值来代替,EXPLAIN
出来的行数就是一个不错的近似值,而且执行EXPLAIN
并不需要真正地去执行查询,所以成本非常低。通常来说,执行COUNT()
都需要扫描大量的行才能获取到精确的数据,因此很难优化,MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题,只有从架构层面解决了,比如添加汇总表,或者使用redis这样的外部缓存系统。优化关联查询
确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器关联的顺序是A、B,那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担,一般来说,除非有其他理由,只需要在关联顺序中的第二张表的相应列上创建索引(具体原因下文分析)。 确保任何的 GROUP BY
和ORDER BY
中的表达式只涉及到一个表中的列,这样MySQL才有可能使用索引来优化。
SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)
outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(inner_row) {
output[inner_row.yy,outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
A.xx
列来查询的,A.c
上如果有索引的话,整个关联查询也不会使用。再看内层的查询,很明显B.c
上如果有索引的话,能够加速查询,因此只需要在关联顺序中的第二张表的相应列上创建索引即可。优化LIMIT分页
ORDER BY
字句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。LIMIT 10000 20
这样的查询,MySQL需要查询10020条记录然后只返回20条记录,前面的10000条都将被抛弃,这样的代价非常高。SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);
OFFSET
,比如下面的查询:SELECT id FROM t LIMIT 10000, 10;
SELECT id FROM t WHERE id > 10000 LIMIT 10;
优化UNION
UNION
的策略是先创建临时表,然后再把各个查询结果插入到临时表中,最后再来做查询。因此很多优化策略在UNION
查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY
等字句“下推”到各个子查询中,以便优化器可以充分利用这些条件先优化。UNION ALL
,如果没有ALL关键字,MySQL会给临时表加上DISTINCT
选项,这会导致整个临时表的数据做唯一性检查,这样做的代价非常高。当然即使使用ALL
关键字,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。虽然很多时候没有这个必要,比如有时候可以直接把每个子查询的结果返回给客户端。结语
有非常多的程序员在分享时都会抛出这样一个观点:尽可能不要使用存储过程,存储过程非常不容易维护,也会增加使用成本,应该把业务逻辑放到客户端。既然客户端都能干这些事,那为什么还要存储过程? JOIN
本身也挺方便的,直接查询就好了,为什么还需要视图呢?
来源:https://www.cnblogs.com/zhangyinhua/p/7620964.html
如果喜欢这篇文章,请点个关注,分享给更多的人,小编将持续更新,谢谢啦!
关注下方公众号,分享硬核知识
关注上方公众号,回复 面试 获取大厂面试宝典
评论