一、发现哪些SQL语句有性能问题
开启MySQL慢查询日志对SQL语句进行监控
进行select语句查询
打开慢查询日志,查看日志信息
从第四行开始,日志分为5个部分:
1.time:查询语句执行开始时间
2.User@Host:用户名和ip
3.Query_time 查询所用时间 Lock_time 锁定时间 Rows_sent 发送的行数 Rows_examined 扫描行数
4.timestamp:查询语句执行时间的时间戳格式
5.查询语句内容
慢查询日志分析工具:MySQL dump slow和pt-query-digest
发现慢查询日志中有性能问题的SQL:
1.查询次数多,每次查询占用时间长的SQL(
pt-query-digest分析top前几个的查询语句)
2.IO大的SQL(rows examine扫描行数多的SQL语句,扫描行数越多,IO消耗越大)
3.未命名索引的SQL(对比Rows examined扫描行数 和 Rows Send实际发送结果的行数,如果扫描行数远远大于发送行数,则说明查找的
命中率不高,效率低)
二、通过explain+SQL语句查询SQL的执行计划
例如:
explain select id, province_name,city_name from city;
得到SQL执行这个select语句的执行计划:
select_type;select语句的类型,如果没有where条件,则为simple
table:表名
type:连接使用的类型:const、eq_reg、ref、range、index和ALL
possible_keys:可能使用的索引
key:
key_len:索引长度
ref:索引某一列被使用
rows:表扫描的行数(
可以通过建立索引来减少扫描行数)
Extra:
三、Count()和Max()函数优化
max()函数:查询大值,时间最后
select max(pay_date) from payment;-- 查询最后一次的支付时间
执行max函数可能遇到的性能问题:
没有加入索引,导致select语句对全表进行扫描
优化方法:
加入索引,减少表扫描的行数
count()函数优化:
count(*)优化成count(条件)
count(*)包含为null值的数据行
count(id OR NULL)不包含为null值的数据行
四、子查询优化
优化思路:把子查询优化为join查询
五、GROUP BY查询优化
六、LIMIT优化(分页功能)
LIMIT 语句经常伴随 ORDER BY 从句使用,经常需要排序(fileSort),产生IO问题
优化思想:避免过多的行扫描
七、索引优化
优化思想:避免过多的行扫描
1.建立索引:where从句、group by、order by、on从句
2.索引字段越小越好(数据库中存储数据以页为单位,字段越小,每页存的数据越多,IO操作越少)
3.建立联合索引(离散度大的放在前面,可选择性越高)
4.索引不是越多越好
建立索引可以提高查询效率(SELECT),但会降低写入(UPDATE\INSERT\DELETE)效率。索引越多,运行时分析索引所用时间越多。
优化:找到冗余索引,将其删除