查询优化
1.内查询
隐式内连接:
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 …;
显式内连接:
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 …;
显示内连接查询性能优于隐式内连接查询
2.联合查询
SELECT 字段列表 FROM 表A …
UNION [ALL]
SELECT 字段列表 FROM 表B …
联合查询比使用or效率高,不会使索引失效(UNION ALL 会有重复结果,UNION 不会)
性能分析
1.profile
查看 MySQL 是否支持 profile 操作:
SELECT @@have_profiling;
profiling 默认关闭,可以通过set语句在session/global级别开启 profiling:
SET profiling = 1;
查看所有语句的耗时:
show profiles;
查看指定query_id的SQL语句各个阶段的耗时:
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
2.explain
explain:查看执行计划,如:
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
EXPLAIN 各字段含义:
id:select 查询的序列号,表示查询中执行 select 子句或者操作表的顺序(id相同,执行顺序从上到下;id不同,值越大越先执行)
select_type:表示 SELECT 的类型,常见取值有 SIMPLE(简单表,即不适用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type:表示连接类型,性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
possible_key:可能应用在这张表上的索引,一个或多个
Key:实际使用的索引,如果为 NULL,则没有使用索引
Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
rows:MySQL认为必须要执行的行数,在InnoDB引擎的表中,是一个估计值,可能并不总是准确的
filtered:表示返回结果的行数占需读取行数的百分比,filtered的值越大越好
查看执行频次
查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次:
SHOW GLOBAL STATUS LIKE ‘Com_______’; 或者 SHOW SESSION STATUS LIKE ‘Com_______’;
例:show global status like ‘Com_______’
3.查看执行频次
查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次:
SHOW GLOBAL STATUS LIKE '%Com%';
4.慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
重启MySQL服务,日志文件位置:/var/lib/mysql/localhost-slow.log
查看慢查询日志开关状态:
show variables like 'slow_query_log';