文章目录
- mysql优化的准备工作
- 慢查询日志
- mysql查询的相关参数
- 使用方式
- EXPLAIN详解
- 重要的字段解析
- 创建索引的原则
- 建议创建索引
- 不建议创建索引
- 索引失效的场景
- 索引调优的技巧索引调优的技巧
- JOIN优化
- join的种类
- join算法
- 驱动表和被驱动表
- JOIN调优的原则
- 分页查询优化
- COUNT优化
- ORDER BY优化
mysql优化的准备工作
慢查询日志
记录执行超过指定时间的sql语句
mysql查询的相关参数
参数 | 作用 | 默认值 |
set global log_output | 表示日志输出的文件 可以设置为多种格式 FILE,TABLE | FILE |
set global long_query_time | 执行sql超过的时间记录到慢sql日志中 单位秒 | 10 |
set global slow_query_log | 是否开启慢sql查询 | OFF |
set global log_queries_not_using_indexes | 记录没有使用索引的查询 | OFF |
使用方式
方式一:修改my.cnf , 需要重启mysql
slow_query_log = 'ON';
log_output = 'FILE,TABLE';
long_query_time = 10;
log_queries_not_using_indexes = 'ON';
方式二:全局上设置
set global slow_query_log = 'ON';
set global log_output = 'FILE,TABLE';
set global long_query_time = 10;
set global log_queries_not_using_indexes = 'ON';
查询慢sql的日志
select * from `mysql`.slow_log; #基于TABLE
主要看sql_text字段的数据,记录了是那条sql语句
show variables like '%slow_query_log_file%' #基于FILE
通过命令行去服务器上看日志
EXPLAIN详解
EXPLAIN 使用 在sql语句前面加上这个关键字
explain select * from employees;
重要的字段解析
id:
id越大越在前面执行,id一样顺序执行
type:
system:只有一行数据被扫描。
const:表最多有一个匹配行,针对主键或唯一索引。
eq_ref:就是两张表通过其中一张表的主键或者唯一非空索引进行关联时,查询结果是一对一的关系。当使用了索引的全部组成部分,索引为 UNIQUE等才能使用该类型。
ref:当满足索引的最左匹配原则,或者索引不是主键或者唯一索引才能触发,
fulltext:使用了全文索引。
range:使用索引去检索范围内的行,输出行中的键列指示使用哪个索引。基本上是用到了以下操作符: !=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()。
index:全索引扫描。
all:全表扫描,性能最差。
rows:
扫描的行数(优化的目的减少扫描的行数,sql才能提升性能)。
Extra:
using filesort:包含order by 操作,排序字段没有用到索引。
using temporary:MySQL需要创建一个临时表来保存结果。如果查询包含GROUP BY和ORDER BY,通常会发生这种情况。
创建索引的原则
建议创建索引
1、 频繁作为where条件的字段。(单独的索引、组合索引)
- 注意组合索引需要遵从最
左匹配原则
`最左匹配原则:指的是索引按照最左优先的方式匹配 如给A,B,C三个字段创建索引
where A=1;
WHERE A=1 AND B=2 ;
WHERE A=1 AND B=2 AND C=3; 可以使用
where B=2;
WHERE A=1 AND C=3; 不可以使用
2、频繁作为分组、排序的字段。
3、distinct的字段。
4、字段的只有唯一性约束。
5、多表关联查询的字段需要创建索引注意数据类型必须保持一致否则会有隐式转换问题,导致索引失效
。
不建议创建索引
- where条件种用不到的字段。
- 表数据少。
- 有大量重复的数据 比如枚举,年龄,性别等。
- 频繁需要修改更新的数据,如果创建索引要考虑到索引的开销。
索引失效的场景
- 索引列不独立。字段进行了表达式计算,字段是函数的参数
where emp_no + 1 = 10003;
解决方案:事先计算好表达式的值,再传过来,避免在SQLwhere条件 = 的左侧做计算
where SUBSTRING(first_name, 1, 3) = 'Geo';
解决方案:预先计算好结果,再传过来,在where条件的左侧,不要使用函数;或者使用等价的SQL去实现
- 使用了左模糊。
where first_name like '%A%';
解决方案:尽量避免使用左模糊,如果避免不了,可以考虑使用搜索引擎去解决
- 使用OR查询的部分字段没有索引。
where A = 'A'
or B = 'B';
解决方案:分别为A以及B字段创建索引
- 字符串条件未使用’'引起来。
where dept_no = 3;
解决方案:规范地编写SQL
- 不符合最左前缀原则的查询。
- 索引字段建议添加NOT NULL约束。
MySQL官方建议尽量把字段定义为NOT NULL
:https://dev.mysql.com/doc/refman/8.0/en/data-size.html - 隐式转换导致索引失效。
索引调优的技巧索引调优的技巧
- 覆盖索引
select的字段只需要从索引就能获取,不需要到表数据里面获取,这个的索引就叫覆盖索引
select 后面更想的字段,减少网络的开销,还可能使用到覆盖索引 - 冗余、重复索引的优化
- 重复索引
在相同的列上按照相同的顺序创建的相同类型的索引。
应该避免创建这样的重复索引,发现之后也应该立即移除。
- 冗余索引
如果已经存在索引index(A,B),有创建了index(A),那么index(A)就是index(A,B)的冗余索引
JOIN优化
join的种类
cross join:笛卡尔连接 会把A表里面的数据和B表里面的数据任意的连接
如果cross join带有on子句,就相当于inner join
join算法
Table Join Type
t1 range
t2 ref
t3 ALL
- Nested-Loop Join(NLJ-嵌套循环join)
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}
- Block Nested-Lopp Join(BNLJ块嵌套循环join)
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
empty join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}
扫描次数计算公式:(S*C)/join_buffer_size+1
驱动表和被驱动表
外层循环的表是驱动表,内层循环的表是被驱动表
JOIN调优的原则
1、 用小表驱动大表
- 优化器回自动选择最优来执行
- 当优化器没有选择的时候可以用STRSIGHT_JOIN
2、where条件使用索引,减少外层循环的数据量
3、join on 的字段类型保持一致,否则会有隐式转换索引失效
4、join的表不要太多
阿里规范建议不要稍多三张
分页查询优化
方案一:覆盖索引
方案二:覆盖索引+join
方案三:覆盖索引+子查询
方案四:禁止传入过大的页码(参考百度)
COUNT优化
- 当没有非主键索引时,会使用主键索引
- 如果存在非主键索引的话,会使用非主键索引
- 如果存在多个非主键索引,会使用一个最小的非主键索引
count(1) 和 count(*)
count(*)和count(1)没有区别
官方文档:https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
对于MyISAM表,COUNT(*)如果SELECT从一个表中检索,没有检索到其他列,并且没有 WHERE子句, 则优化为非常快速地返回
方案一:创建一个更小的非主键索引
方案二:把数据库引擎换成MyISAM (建议不药使用)
方案三:information_schema.tables
select TABLE_ROWS from `information_schema`.TABLES where TABLE_SCHEMA ='employees' and TABLE_NAME = 'employees';
--好处:employees,不管employees有多少数据,都可以迅速地返回结果
-- 缺点:估算值,并不是准确值
方案四:使用explain 有优缺点同上
方案五:
show table status where Name = 'salaries';
优缺点同上
ORDER BY优化
我们在使用order by 可以使用索引,因为它是天然有序性的链表
推荐看一下
无法利用索引避免排序,升降序不一致 一个字段使用desc,一个使用asc