mysqlQueryoptimization - juedaiyuer/researchNote GitHub Wiki

#MySQL慢查询优化#

##MySQL索引原理##

select
   count(*) 
from
   task 
where
   status=2 
   and operator_id=20839 
   and operate_time>1371169729 
   and operate_time<1371174603 
   and type=2;

索引的最左匹配特性

##建立索引的几大原则##

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

• MySQL索引原理及慢查询优化---美团技术博客(evernote)

---

##如何获取有性能问题的SQL##

1. 通过用户反馈获取存在性能问题的SQL
2. 通过慢查日志获取存在性能问题的SQL
3. 实时获取存在性能问题的SQL

##慢查询日志##

开销:磁盘IO和存储日志所需要的磁盘空间

slow_query_log=on 启动停止记录慢查日志

set global 启动

通过脚本来定时的开关

#默认情况下保存在MySQL的数据目录中
#存储在不同的磁盘分区上
slow_query_log_file 指定慢查日志的存储路径及文件

#默认值为10s
#记录所有符合条件的SQL:查询,修改,已经回滚的SQL
#对于繁忙的系统来说,通常改为0.001秒可能比较合适
long_query_time 指定记录慢查日志SQL执行时间的阀值

log_queries_not_using_indexes 是否记录未使用索引的SQL

###配置###

mysql> show variables like 'slow_query_log';

mysql> set global slow_query_log=on;

mysql> set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'

mysql> set global log_queries_not_using_indexes=on

mysql> set global long_query_time=1

---

##常用的慢查日志分析工具##

###mysqldumpslow###

汇总除查询条件外其它完全相同的SQL,并将分析结果按照参数中所指定的顺序输出

mysqldumpslow -s r -t 10 slow-mysql.log

-s, 是表示按照何种方式排序,参数如下:

c:总次数
t:总时间
l:锁的时间,查询时间
r:总数据行

-t top
指定取前几条作为结束输出

-g 后边可以写一个正则匹配模式，大小写不敏感的

使用实例

mysqldumpslow -s r -t 10 slow-mysql.log

###pt-query-digest###

• mysql慢查询工具---pt-query-digest

---

##如何通过慢查日志发现有问题的SQL##

1. 查询次数多且每次查询占用时间长的SQL(pt-query-digest分析的前几个查询)
2. IO大的SQL(注意pt-query-digest分析中的Rows examine)
3. 未命中索引的SQL(Rows examine和Rows Send的对比)

---

##count()和max()的优化方法##

#查询最后支付时间---优化max()函数
select max(payment_date) from payment;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16545
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

rows比较大,查询效率不高

#建立索引
mysql> create index idx_paydate on payment(payment_date);

mysql> explain select max(payment_date) from payment\G;
*************************** 1. row ***************************
    id: 1                                                             
    select_type: SIMPLE
    table: NULL                                                          
    partitions: NULL
    type: NULL                                                          
    possible_keys: NULL
    key: NULL                                                          
    key_len: NULL
    ref: NULL                                                          
    rows: NULL                                                          
    filtered: NULL
    Extra: Select tables optimized away                                  
1 row in set, 1 warning (0.00 sec) 


#在一条SQL中同时查出2006年和2007年电影的数量-优化count()函数
SELECT COUNT(release_year='2006' OR NULL) AS '2006年电影数量',COUNT(release_year='2007' OR NULL) AS '2007年电影数量' from film;

##子查询优化##

通常情况下,需要把子查询优化为join查询,但在优化时要注意关联键是否有一对多的关系,要注意重复数据

#查询sandra出演的所有影片

explain SELECT title,release_year,LENGTH
    FROM film
    WHERE film_id IN (
        SELECT film_id FROM film_actor WHERE actor_id IN (
            SELECT actor_id FROM actor WHERE first_name='sandra'
        )
    )

##group By优化##

explain SELECT actor.first_name,actor.last_name,COUNT(*)
FROM sakila.film_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

#优化group by查询
explain SELECT actor.first_name,actor.last_name,c.cnt
FROM sakila.actor INNER JOIN(
SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY
actor_id
) AS c USING(actor_id);

##limit优化##

常用于分页处理,时常会伴随order by从句使用,因此大多时候会使用Filesorts 这样会造成大量的IO问题

SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;	

mysql> explain SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  949 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

###使用有索引的列或主键进行order by操作###

SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5;

mysql> explain SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | PRIMARY | 2       | NULL |   55 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

rows明显减少,没有使用filesort

##source##

• MySQL慢查询分析mysqldumpslow
• pt-query-digest查询日志分析工具
• MySQL索引原理及慢查询优化---美团技术博客(evernote)