您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

18条MySQL优化技巧

时间:2020-05-09 10:08:44  来源:  作者:

刚参加工作的我们,都以为使用 MySQL 是非常的简单的,无非都是照着 【select from where group by order by】 这个格式套来套去;从来不会关注 SQL 的耗费时长,更不会关注查询的性能。

但是当用户量上来了,表数据不断暴增,导致我们以前写的 SQL 的查询时间越来越长,最后还被 DBA 和领导疯狂吐槽一波。那么,此时我们是不是应该学习一下如何去优化我们的烂 SQL 呢?

下面,我将从多方面去深入讲解如何优化 SQL 。

一、索引优化

索引的数据结构是 B+Tree,而 B+Tree 的查询性能是比较高的,所以建立索引能提升 SQL 的查询性能。

1、建立普通索引

对经常出现在 where 关键字后面的表字段建立对应的索引。

2、建立复合索引

如果 where 关键字后面常出现的有几个字段,可以建立对应的 复合索引。要注意可以优化的一点是:将单独出现最多的字段放在前面。

例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面:

select * from t where a = 1 and b = 2;   * Q1 *

也有很多 SQL 会单独使用字段 a 作为查询条件:

select * from t where a = 2;   * Q2 *

此时,我们可以建立复合索引 index(a,b)。因为不但 Q1 可以利用复合索引,Q2 也可以利用复合索引。

3、最左前缀匹配原则

如果我们使用的是复合索引,应该尽量遵循 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

假如此时我们有一条 SQL :

select * from t where a = 1 and b = 2 and c > 3 and d = 4;

那么我们应该建立的复合索引是:index(a,b,d,c) 而不是 index(a,b,c,d)。因为字段 c 是范围查询,当 MySQL 遇到范围查询就停止索引的匹配了。

大家也注意到了,其实 a,b,d 在 SQL 的位置是可以任意调整的,优化器会找到对应的复合索引。

还要注意一点的是:最左前缀匹配原则不但是复合索引的最左 N 个字段;也可以是单列(字符串类型)索引的最左 M 个字符。

  • 例如我们常说的 like 关键字,尽量不要使用全模糊查询,因为这样用不到索引;
  • 所以建议是使用右模糊查询:select * from t where name like '李%'(查询所有姓李的同学的信息)。

4、索引下推

很多时候,我们还可以复合索引的 索引下推 来优化 SQL 。

例如此时我们有一个复合索引:index(name,age) ,然后有一条 SQL 如下:

select * from user where name like '苏%' and age = 10 and sex = 'm';

根据复合索引的最左前缀匹配原则,MySQL 匹配到复合索引 index(name,age) 的 name 时,就停止匹配了;然后接下来的流程就是根据主键回表,判断 age 和 sex 的条件是否同时满足,满足则返回给客户端。

但是由于有索引下推的优化,匹配到 name 时,不会立刻回表;而是先判断复合索引 index(name,age) 中的 age 是否符合条件;符合条件才进行回表接着判断 sex 是否满足,否则会被过滤掉。

那么借着 MySQL 5.6 引入的索引下推优化 ,可以做到减少回表的次数。

5、覆盖索引

很多时候,我们还可以 覆盖索引 来优化 SQL 。

情况一:SQL 只查询主键作为返回值。

主键索引(聚簇索引)的叶子节点是整行数据,而普通索引(二级索引)的叶子节点是主键的值。

所以当我们的 SQL 只查询主键值,可以直接获取对应叶子节点的内容,而避免回表。

情况二:SQL 的查询字段就在索引里。

复合索引:假如此时我们有一个复合索引 index(name,age) ,有一条 SQL 如下:

select name,age from t where name like '苏%';

由于是字段 name 是右模糊查询所以可以走复合索引,然后匹配到 name 时,不需要回表,因为 SQL 只是查询字段 name 和 age,所以直接返回索引值就 ok 了。

6、普通索引

尽量 使用普通索引 而不是唯一索引。

首先,普通索引和唯一索引的查询性能其实不会相差很多;当然了,前提是要查询的记录都在同一个数据页中,否则普通索引的性能会慢很多。

但是,普通索引的更新操作性能比唯一索引更好;其实很简单,因为普通索引能利用 change buffer 来做更新操作;而唯一索引因为要判断更新的值是否是唯一的,所以每次都需要将磁盘中的数据读取到 buffer pool 中。

7、前缀索引

我们要学会巧妙的使用 前缀索引,避免索引值过大。

例如有一个字段是 addr varchar(255),但是如果一整个建立索引 [ index(addr) ],会很浪费磁盘空间,所以会选择建立前缀索引 [ index(addr(64)) ]。

建立前缀索引,一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低,只要出生地一样,前面好多个字符都是一样的;这样的话,最不理想时,可能会扫描全表。

前缀索引避免不了回表,即无法使用覆盖索引这个优化点,因为索引值只是字段的前 n 个字符,需要回表才能判断查询值是否和字段值是一致的。

怎么解决

倒序存储:像身份证这种,后面的几位区分度就非常的高了;我们可以这么查询:

select field_list from t where id_card = reverse('input_id_card_string');增加 hash 字段并为 hash 字段添加索引。

8、干净的索引列

索引列不能参与计算,要保持索引列“干净”。

假设我们给表 student 的字段 birthday 建立了普通索引。

下面的 SQL 语句不能利用到索引来提升执行效率:

select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';

我们应该改成下面这样:

select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');

9、扩展索引

我们应该尽量 扩展索引,而不是新增索引,一个表最好不要超过 5 个索引;一个表的索引越多,会导致更新操作更加耗费性能。

二、SQL 优化

1、Order By 优化

order by 后面的字段尽量是带索引的,这样能避免使用 sort_buffer 进行排序。

  • 假如有一条 SQL,根据生日查询所有学生的信息:select * from student order by birthday desc;
  • 那么为了提升 SQL 的查询性能,我们可以为 birthday 字段建立索引:
CREATE INDEX index_birthday ON student(birthday);

select 后面不要带上不必要的字段,因为如果单行长度太长导致查询数据太多,MySQL 会利用 rowid 排序来代替全字段排序,这样会导致多了回表的操作。

  • 如果我们只是查询学生的姓名、年龄和生日,千万不要写 select *;
  • 而是只查询需要的字段:select name, age, birthday from student order by birthday desc;

2、Join 优化

  • 在使用 join 的时候,应该让小表做驱动表。小表:总数据量最小的表
  • 使用 join 语句,最好保证能利用被驱动表的索引,不然只能使用 BNL(Block Nested-Loop Join)算法,还不如不用。
  • 启用 BKA(Batched Key Access) 算法,使得 NLJ 算法也能利用上 join_buffer,被驱动表可以批量查询到符合条件的值,然后可以利用 MMR(Multi-Range Read) 的顺序读盘特性来提升回表效率。
  • 如果一定要用 join,而且被驱动表没有索引可以使用,那么我们可以利用临时表(create temporary table xx(...)engine=innodb;)来让 BNL 算法转为 BKA 算法,从而提升查询性能。
  • join_buffer 是一个无序数组,所以每次判断都需要遍历整个 join_buffer。我们可以在业务端实现 hash join 来提升 SQL 的执行速度。

3、Group By 优化

  • 如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null。
  • 尽量让 group by 过程用上表的索引,不但不需要临时表,还不需要额外的排序。
  • 如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表。
  • 如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。

4、OR 优化

在 Innodb 引擎下 or 关键字无法使用组合索引。

假设现在关于订单表有一条 SQL :

select id,product_name from orders where mobile = '12345678900' or user_id = 6;

一般我们为了提升上面 SQL 的查询效率,会想着为字段 mobile 和 user_id 建立一个复合索引 index(mobile,user_id);

可是我们使用 explain 可以发现执行计划里面并没有提示到使用复合索引,所以 or 关键字无法命中 mobile + user_id 的组合索引。

那么我们可以分别为两个字段建立普通索引,然后采用 union 关键字,如下所示:

(select id,product_name from orders where mobile = '12345678900') 
union
(select id,product_name from orders where user_id = 6);

此时 mobile 和 user_id 字段都有索引,查询才最高效。

5、IN 优化

in 关键字适合主表大子表小,exist 关键字适合主表小子表大。由于查询优化器的不断升级,很多场景这两者性能差不多一样了,可以尝试改为 join 查询。

假设我们现在有一条 SQL ,要查询 VIP 用户的所有订单数据:

select id from orders where user_id in (select id from user where level = 'VIP');

我们可以发现不会有任何关于索引的优化,所以我们可以采用 join查询,如下所示:

select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';

此时被驱动表应该是 user,那么可以利用到 user 表的主键索引,即可以使用 BKA 算法来提升 join 查询的性能。

6、Like 优化

like 用于模糊查询,但是如果是全模糊查询,将不能命中对应字段的索引。

假设现在关于学生表有一条 SQL:

SELECT name,age,birthday FROM student WHERE name like '%苏%';

使用 explain 可以发现执行计划提示查询未命中索引。

因为本来需求就是查询姓张的所有同学信息,所以没必要使用全模糊查询,使用右模糊查询即可。

换成下面的写法:

SELECT name,age,birthday FROM student WHERE name like '苏%';

但是产品经理一定要前后模糊匹配呢?全文索引 FULLTEXT 可以尝试一下,但是 MySQL 的全文索引不支持中文查询的。

所以说 Elasticsearch 才是终极武器!

三、数据表设计优化

1、数据类型:应该选择更简单或者占用空间更小的类型

  • 整型选择:可以根据长度选择 tinyint、smallint、medium_int,而不是直接使用 int。
  • 字符串选择:能确定字符串长度的,尽量使用 char 类型,而不是变长的 varchar 类型。
  • 浮点型选择:精度要求比较高的使用 decimal 而不是 double;也可以考虑使用 BIGINT 来保存,小数位保存可以使用乘以整百来解决。
  • 日期选择:尽量使用 timestamp 而不是 datetime。

2、避免空值

  • NULL 值依然会占用空间,并且会使索引更新更加复杂,更新 NULL 时容易发生索引分裂的现象。
  • 可以使用有意义的值来代替 NULL 值,例如 “none” 字符串等等。

3、超长字符串

  • 一般超长字符串,varchar 难以存储,我们一般会使用 text 类型。
  • 但是 text 类型的字段尽量避免放在主表中,而是抽出来在子表里,用业务主键关联。


Tags:MySQL优化   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
说起MySQL的查询优化,相信大家收藏了一堆奇技淫巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解其...【详细内容】
2021-04-06  Tags: MySQL优化  点击:(232)  评论:(0)  加入收藏
前言我最近由于换工作,博客更新暂缓,后面争取一周两篇。Mysql 系列到这里就差不多了,Mysql集群、分库分表及分布式事务由于我还是停留在理论上,没在生产环境上玩过,又怕写不好,这...【详细内容】
2021-03-25  Tags: MySQL优化  点击:(224)  评论:(0)  加入收藏
刚参加工作的我们,都以为使用 MySQL 是非常的简单的,无非都是照着 【select from where group by order by】 这个格式套来套去;从来不会关注 SQL 的耗费时长,更不会关注查询的...【详细内容】
2020-05-09  Tags: MySQL优化  点击:(52)  评论:(0)  加入收藏
一、概述1. 为什么要优化 一个应用吞吐量瓶颈往往出现在数据库的处理速度上 随着应用程序的使用,数据库数据逐渐增多,数据库处理压力逐渐增大 关系型数据库的数据是存放在...【详细内容】
2020-01-13  Tags: MySQL优化  点击:(57)  评论:(0)  加入收藏
首先采用Mysql存储千亿级的数据,确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据,只是这个时候性能非常差,项目中大量的实验证明,Mysql单表容量在500万左右,性能处于...【详细内容】
2019-11-11  Tags: MySQL优化  点击:(94)  评论:(0)  加入收藏
说起MySQL的查询优化,相信大家收藏了一堆奇技淫巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?...【详细内容】
2019-10-16  Tags: MySQL优化  点击:(108)  评论:(0)  加入收藏
上次在mysql8上导入一个1000万数据之后,今天就想验证看看mysql对于单表大数据的分页的表现情况,并探讨一下单表大数据分页的优化思路。 测试环境简单说明下测试环境。 mysql版...【详细内容】
2019-10-11  Tags: MySQL优化  点击:(254)  评论:(0)  加入收藏
作者:zhangqhsegmentfault.com/a/1190000012155267 一、EXPLAIN做MySQL优化,我们要善用 EXPLAIN 查看SQL执行计划。下面来个简单的示例,标注(1,2,3,4,5)我们要重点关注的数据...【详细内容】
2019-08-21  Tags: MySQL优化  点击:(199)  评论:(0)  加入收藏
在进行MySQL的优化之前,必须要了解的就是MySQL的查询过程,很多查询优化工作实际上就是遵循一些原则,让MySQL的优化器能够按照预想的合理方式运行而已。 图-MySQL查询过程一、优...【详细内容】
2019-08-14  Tags: MySQL优化  点击:(173)  评论:(0)  加入收藏
应用程序慢如牛,原因多多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。那么如何提高数据库SQL语句执行速度呢?有人会说性能调优是数据库管理员(DBA)的事,然...【详细内容】
2019-07-11  Tags: MySQL优化  点击:(329)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条