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