🍰 个人主页:不摆烂的小劉
🍞文章有不合理的地方请各位大佬指正。
🍉文章不定期持续更新,如果我的文章对你有帮助➡️ 关注🙏🏻 点赞👍 收藏⭐️
前言:MySQL
性能优化是一个复杂过程,通常从发现问题开始。首先,通过监控和分析慢查询日志,找出执行时间较长的 SQL 语句(慢 SQL
), 针对慢查询,通常从三个方面入手:表结构优化、索引优化、SQL
优化。
如何系统地优化MySQL
?优化是否有效?表结构、索引优化有什么注意点?
一、前置知识:
1.Explain
执行计划
用于分析查询语句执行计划的命令,Explain
可以查看到MySQL
如何选择表、索引、连接方式等细节信息。
有几个需要重点关注的执行计划字段信息:
type
:MySQL
查找数据的方式,按效率从好到差的顺序排列:
const
:常量,表示仅查找一行数据eq_ref
:通过主键或者唯一索引查找数据ref
:通过非唯一索引查找数据range
:索引范围查找index
:全索引扫描all
:全表扫描,效率最差
key
:MySQL
实际使用的索引。如果该列为null
,表示没有使用索引,可能是全表扫描
rows
:MySQL
预计需要扫描的行数
Extra
:显示额外的执行信息,包括:
Using index
:表示查询是通过覆盖索引进行的,避免了回表查询,效率较高。Using where
:表示查询需要通过where
条件过滤数据。一般在没有使用到索引的时候会出现。Using temporary
:表示使用了临时表,通常出现在复杂的join
或group by
查询中,可能导致性能下降。Using filesort
:表示需要额外的排序操作,这可能会导致性能下Using index condition
:表示查询优化器选择使用了索引条件下推这个特性Index Merge
:索引合并当一个查询涉及多个索引时,MySQL
通过结合多个索引来执行查询。
2.如何查看一条Sql
执行效率高低?
- 响应时间
执行到结束花费的时间。时间越短越好。 - 扫描的行数、返回的行数
理想情况下扫描的行数和返回的行数应该是相同的。
可以通过Explain
查看
通过Explain
查看,上面的Sql
扫描的行数14条,type
是all
类型全表扫描,额外的执行信息Using where
,返回的行数1条,查询效率较低
3.回表
举例
- 去图书馆,翻阅书单(索引),找到你想借的书编号123
- 根据书单(索引),你找到这本书的大致位置(行号)
- 然后你需要回到书架(表)上去,拿到书的实际内容
回表就是通过索引找到了某个数据的位置,但如果需要的字段不在索引里,还得返回原始数据表去查找缺失的字段
二、如何优化
1. 表结构优化
- 表名推荐使用业务前缀
- 选择一个合适的字段类型和表示数据的最小数据类型。更小的数据类型通常更快,因为它们占用的磁盘、内存和CPU缓存的空间更少。
- 禁止使用
MySQL
关键字order
、range
、match
、desc
等 - 主键索引名为
pk_
字段名;唯一索引名为uk_
字段名;普通索引名则为idx_
字段名 - 小数类型为
decimal
,在一些大容量的场景,可以考虑使用bigint
代替decimal
,将需要存储的货币单位根据小数的位数乘以相应的倍数 - 如果存储的字符串长度几乎相等,使用
char
定长字符串类型。当存储char
值时,MySQL
删除所有尾随空格
varchar
需要额外使用记录字符串的长度。MySQL
在排序时,对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长才能容纳其中最长的字符串,例如,如果是varchar
列,则需要分配该字段完整长度,避免varchar
设置过大,排序时过度消耗临时空间而降低效率varchar
字段长度不要超过5000,如果存储长度大于此值,定义字段类型为text
,独立出来一张表,用主键来对应- 尽量避免存储
null
,null
值,需要占用额外空间;null
值可能会导致索引失效;null
值只能用is null
或者is not null
判断 - 为整数类型指定宽度,基本没意义,对于存储和计算来说,
int(1)
和int(20)
是相同的 - 避免使用
enum
类型,enum
底层存储的是整数,内部整数值排序的 - 不要用字符串存储日期,优先推荐使用
datetime
类型保存日期和时间,可以保存的时间范围更大一些 - 推荐表中添加自增主键
json
数据类型将使用更多空间来存储用于定义json
的额外字符(大括号、方括号、冒号等)以及空格。 除非特殊业务场景json
发挥便捷性,一般不推荐使用。- 存储
IP
地址使用MySQL
提供的INET_ATON()
和INET_NTOA()
函数;INET_ATON()
把IP
转为无符号整型 (4-8 位),INET_NTOA()
把整型的IP
转为地址 - 文件(比如图片)这数据通常存储于 文件服务器或云存储 ,数据库只存储文件地址信息
2.索引设计
索引最主要作用是快速的找到数据位置,这不是唯一作用,覆盖索引还可以存储数据信息
主键索引:不可为空,不重复,只能有一个主键索引, 一般是聚集索引,即数据按主键顺序存储。
普通索引:加快查询
唯一索引: 唯一索引保证索引列的值是唯一的
联合索引:多列创建的索引, 查询时,通常会按索引的列顺序使用
覆盖索引: 查询时所需的所有数据都可以通过索引来提供,避免了回表操作。
前缀索引: 索引列的部分值,常在varchar
或text
类型的列上创建
全文索引: 全文索引是一种特殊类型的索引,主要用于文本搜索。它允许对长文本字段(如 text
类型的列),一般不使用
-
在
varchar
字段上建立索引时,指定索引长度,没必要对全字段建立索引,使用count(distinct left(列名, 索引长度))/count(*)
的区分度来确定
避免在多列上独立地创建多个单列索引,复合索引通常比单列索引更加高效,一个表中有a,b,c
,建立索引(a,b,c)
复合索引包含了(a)(a,b)(a,b,c)
三个索引 -
避免重复索引,设置某个字段
id
既是主键索引,又设置该字段id
唯一索引 -
建议在一个表中创建的索引数量不超过
5
个,修改数据时,数据库需要更新索引,会增加写操作的时间,增删改操作性能下降; 每个索引都会占用额外的存储空间,多个索引会导致数据库需要占用大量的磁盘空间;多个索引都可以用于查询,就会增加MySQL
优化器生成执行计划的时间 -
推荐建立索引字段:最频繁使用的列放在联合索引的左侧;
where
后的列order by 、group by、distinct
中的字段;join
的关联列 -
覆盖索引,包含了查询所需要的所有列的索引,假设有一个表
users
,包含id、name 和 age
三个字段,并且你创建了一个索引idx_name_age
,该索引包含了name 和 age
两个字段。select name, age from users where name = 'John';
数据库就可以直接从这个索引中获取查询所需的所有数据,避免回表操作
三、索引失效
索引失效:查询语句的某些操作使得数据库无法利用已有的索引来加速查询
- 使用
like
进行前缀匹配时,like '%xxx'
用不到索引,需要扫描整个表来匹配;%开头会使得索引失效。避免使用%开头的like
模糊匹配 - 多个条件通过
or
连接,当or
右的条件包含不同列时,索引不能覆盖所有条件,所以尽量避免 尽量避免使用or
- 索引列进行了计算、函数、类型转换等操作,索引是基于原始列的值构建的,函数等操作需要先计算再查询,导致索引不能使用
- 索引列的数据类型和查询条件类型不匹配,例如索引列是
int
类型,而查询条件是字符串类型时,可能会失效,即使走索引但类型转换会有额外开销 - 使用不等于。当进行相等
=
、范围<, >, <=, >=
查询时,B+
树可以通过查找和范围扫描定位,但使用不等于会全表扫描 - 使用
between
索引失效,between
本质上是一个范围查询,在复合索引(a, b)
上,如果查询条件是a = 1 and b between 10 and 20
,那么B+
树能够利用索引。但如果查询条件是b between 10 and 20
,则无法利用复合索引(a, b)
来加速查询,必须扫描整个b
的范围 in
中包含了过多的值,MySQL
处理大量的in
值时,可能需要对每个值进行比较,这可能导致索引失效
参考:
https://dev.MySQL.com/doc/refman/5.7/en/
《阿里巴巴java开发手册》
《高性能MySQL(第四版)》
MySQL高性能优化规范建议总结
美团暑期实习一面:MySQl 索引失效的场景有哪些?
聊聊数据库建表的15个小技巧
技术同学必会的MySQL设计规约,都是惨痛的教训
🍉文章不定期持续更新,如果我的文章对你有帮助 关注🙏🏻 👍点赞👍 收藏⭐️