Mysql数据库优化之SQL及索引优化

1. 如何发现有问题的SQL?  使用mysql慢查询日志对有效率问题的Sql进行监视

(1) show  variables like 'slow_query_log';     查看慢查询日志是否开启

(2) set global slow_qeury_log_file = '/home/mysql/sql_log/mysql_slow.log'     设置慢查询日志文件的位置

(3) set global log_queries_not_using_indexes = on     把没有使用索引的SQL存入慢查询日志

(4) set global long_query_time = 1        设置时间限制,即超过这个时间的SQL就记录到日志中

这里可以使用查看变量的方式,查看上面参数的默认值 比如:show variables like 'slow%' 可以看到慢查询日志的默认存放位置

2. 慢查询日志包含的内容

3. 常用的慢查询日志分析工具

(1) mysqldumpslow 工具(一般在安装mysql时就已经有了) 用法: mysqldumpslow  + 参数 + 慢查询日志文件路径

常用参数:

-t 数字: 显示前n条日志       可以使用mysqldumpslow -h  查看所有可携带的参数

(2) pt-query-digest 工具

 

使用这个工具分析慢查询日志时的输出 共有三部分:

第一部分:显示日志的时间范围,总的SQL数量和不同的SQL数量

第二部分:

第三部分:显示具体的SQL语句

4.根据日志中的指标发现有问题的SQL

(1) 查询次数多且每次查询占用时间长的SQL        通常为pt-query-digest分析的前几个查询

(2) IO大的SQL    注意pt-query-digest 分析中的Rows examine (即扫描的行数)项

(3) 未命中索引的SQL     注意pt-query-digest 分析中Rows examine  和 Rows  Send 的对比

 5. 有问题的SQL被发现后,使用explain从句查询SQL的执行计划,explain返回的是一个表格,下面是各列的含义:

const: 通常是对主键或唯一索引的查找

eq_reg: 范围查找

ref: 连接查找,基于索引

range: 基于索引范围查找

index: 对索引的扫描

ALL: 对整个表的扫描

 5. 优化子查询

尽量使用连表查询代替子查询

当有重复数据时,可以使用distinct进行去重。

 6. 优化limit查询

(1) 优化方案:使用有索引的列或主键进行order by 操作

(2) 优化方案:记录上次返回的主键,在下次查询时使用主键过滤(方向就是避免扫描过多的记录)

select film_id, description from film where  film_id > 55 and film_id <= 60 order by film_id limit 1,5

转载于:https://www.cnblogs.com/lty-fly/p/9285842.html

发布了0 篇原创文章 · 获赞 0 · 访问量 1646
展开阅读全文
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览