mysql性能调优
1. 服务器性能参数查询
SHOW [GLOBAL|SESSION] STATUS LIKE '参数'; 一些常用的性能参数如下: • Connections:连接MySQL服务器的次数。 • Uptime:MySQL服务器的上线时间。 • Slow_queries:慢查询的次数。 • Innodb_rows_read:Select查询返回的行数 • Innodb_rows_inserted:执行INSERT操作插入的行数 • Innodb_rows_updated:执行UPDATE操作更新的行数 • Innodb_rows_deleted:执行DELETE操作删除的行数 • Com_select:查询操作的次数。 • Com_insert:插入操作的次数。对于批量插入的 INSERT 操作,只累加一次。 • Com_update:更新操作的次数。 • Com_delete:删除操作的次数。
• last_query_cost:上次查询涉及到的数据页数。
2. 慢查询
1. 查看慢查询是否打开 show variables like '%slow_query_log%' 2. 开关慢查询(只有在需要调优的时候打开,其余时间应该关闭,因为它会影响性能) set global slow_query_log='ON';
set global slow_query_log='OFF';
3. 查看慢查询的阈值 show variables like '%long_query_time%'; 4. 修改慢查询阈值(也可以通过配置文件修改,但是需要重启服务) set global long_query_time = 1; # 全局修改,下次重启会失效 set long_query_time=1; #本次回话修改 ,下次重启会失效
5. 查看慢查询的条数
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
3. 慢查询日志分析工具 :mysqldumpslow
4. 查看sql执行成本
1. 查看是否开启 show variables like 'profiling'; 2. 修改状态 set profiling = 'ON'; # 'OFF' 3. 查看当前回话的所有profiles show profiles; 4. 查看具体sql的执行成本 show profiles; # 最近一条sql show profile cpu,block io for query 2; # 查看第2条sql的执行成本,2来自于show profiles结果的序号
5. explain
1. 各个字段的含义
![](https://img2022.cnblogs.com/blog/1815864/202202/1815864-20220206115334349-1728297114.png)
1. 关于id
id如果相同,可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
关注点:id号每个号码,表示一趟独立的查询, 一个sql的查询趟数越少越好
2. select_type
mysql为每一个select关键字代表的小查询都定义了一个select_type属性,它表示了小查询在大查询汇总扮演的角色
3. type(重点)
结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts级别。(阿里巴巴开发手册要求)
4. rows: 预估需要扫描的条数,值越小越好
5. extra,额外提示信息,很重要,遇到的时候百度吧
6. explain的不同输出格式
1. explain select ... # 传统格式 2. explain format=json select ... # json格式,输出的信息额外包含执行成本 3. explain format=tree select ... # 输出的信息量很少
7. 索引失效的情况
计算、函数、类型转换(自动或手动)会导致索引失效
联合索引有最左前缀原则
联合索引时,范围字段右侧的索引全部失效
不等于(!= 或者<>)索引失效
is null可以使用索引,is not null无法使用索引(大部分情况下,列中不应该出现null,即使有,应该给个默认值)
like以通配符%开头索引失效
OR 前后存在非索引的列,索引失效。非索引列会导致扫描全表,如果两个都是索引列,会index_merge,两个索引都走,读最后的结果union
数据库和表的字符集应该统一使用utf8mb4
8. join连表
1. left join 右边一定要建立索引,不然会很慢(right join同理) 2. inner join 优化器会自动选择驱动表,一般选择条数较少的那个为驱动表
3. join原理,以t1为驱动表,t2为被驱动表
1. 从表t1中读入一行数据 R; 2. 从数据行R中,取出a字段到表t2里去查找; 3. 取出表t2中满足条件的行,跟R组成一行,作为结果集的一部分; 4. 重复执行步骤1到3,直到表t1的末尾循环结束。
4. 结论
保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段,数据类型保持绝对一致。
LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表 。减少外层循环的次数。
INNER JOIN 时,MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
衍生表建不了索引