mysql调优全过程_一次mysql调优过程

由于经常被抓取文章内容，在此附上博客文章网址：，偶尔会更新某些出错的数据或文字，建议到我博客地址 :  --> 点击这里

前几天进行了一个数据库查询，比较缓慢，便查询了一下，在这里记录一下，方便以后翻阅，

1)先复习一下查询索引

(Tue Jun 27 12:33:24 2017) db_1 >>show keys fromxxxx; ==>(与show index from xxxx 是一样的)+--------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| xxxx | 0 | PRIMARY | 1 | iId | A | 1337960 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_dPayTime | 1 | dPayTime | A | 1337960 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_sUserName | 1 | sUserName | A | 445986 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_sPlatName | 1 | iProxyId | A | 19 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_sPlatName | 2 | iServerId | A | 1359 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_sPlatName | 3 | id | A | 1337960 | NULL | NULL | YES | BTREE | | |

| xxxx | 1 | index_dPayDate | 1 | dPayDate | A | 19 | NULL | NULL | | BTREE | | |

| xxxx | 1 | Index_iPlatServerId_iPlayerId | 1 | iPlatServerId | A | 3126 | NULL | NULL | | BTREE | | |

| xxxx | 1 | Index_iPlatServerId_iPlayerId | 2 | iPlayerId | A | 334490 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_dPayDate_yue_iProxyId | 1 | dPayDate_yue | A | 19 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_dPayDate_yue_iProxyId | 2 | iProxyId | A | 545 | NULL | NULL | | BTREE | | |

| xxxx | 1 | index_dPayDate_yue_iProxyId | 3 | iPayType | A | 545 | NULL | NULL | YES | BTREE | | |

类型介绍：

1、Table 表的名称。

2、 Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。

3、 Key_name 索引的名称

4、 Seq_in_index 索引中的列序列号，从1开始。

5、 Column_name 列名称。

6、 Collation 列以什么方式存储在索引中。在mysql中，有值‘A’(升序)或NULL(无分类)。

7、Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

8、Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

9、 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。

10、 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。

11、 Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

12、 Comment 索引注释

我们要注意：

1cardinality 的值越大，那么命中此索引的几率越高2 我们可以强制进行命中哪个索引

3 Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值，在实际应用中，Cardinality/n_row_in_table应尽可能的接近1，

如果非常小,那用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对于字段添加B+树索引是非常有必要的

如果要查看Cardinality更为详细的信息，可以在：http://www.cnblogs.com/olinux/p/5140615.html 中查看

2 ) 同事在线上进行了一次查询操作：

select * from t_xxxx where pay_id in(select pay_id from t_xxxx where dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31" group by pay_id having count(pay_id) > 1)

and dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31"

然后执行半天都出不来结果，问我这边怎么了，我看了一下：

explain select * from t_xxxx where pay_id in(select pay_id from t_xxxx where dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31" group by pay_id having count(pay_id) > 1)

得到结果：

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+--------------+-------+----------------+----------------+---------+------+---------+----------------------------------------------+

| 1 | PRIMARY | t_xxxx | ALL | NULL | NULL | NULL | NULL | 1395826 | Using where |

| 2 | DEPENDENT SUBQUERY | t_xxx | range | index_dPayTime | index_dPayTime | 8 | NULL | 176284 | Using where; Using temporary; Using filesort |

这条sql包含了子查询和父查询，如果我们先查子查询，可以看到执行非常快：

select pay_id from t_xxxx where dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31" group by pay_id having count(pay_id) > 1;

Emptyset (0.42 sec)

0.42秒执行完毕

explain可以看到：

+----+-------------+--------------+-------+----------------+----------------+---------+------+--------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+-------+----------------+----------------+---------+------+--------+----------------------------------------------+

| 1 | SIMPLE | t_xxxx | range | index_dPayTime | index_dPayTime | 8 | NULL | 176284 | Using where; Using temporary; Using filesort |

+----+-------------+--------------+-------+----------------+----------------+---------+------+--------+----------------------------------------------+

然后我们再尝试父查询查询，替换一下子查询，直接给出子查询的结果：

select * from t_xxxx where pay_id in(-> '89762026','2017062812150312013002','20170628120112465107');

3 rows in set (0.50 sec)

0.56 秒查询完毕

那么两者结合起来，岂不是只需要1秒钟就可以结束查询。

然而真实情况并非如此，这不是简单的1+1，而是乘法，如果用in去操作，子查询影响的行数是多少条，就会乘以父查询影响的行数。这样的乘积量是非常大的，所以查询非常的久。

因此这里推荐改成联表==>

select * from t_xxxx tpp1, (select pay_id from t_xxxx where dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31" group by pay_id

having count(pay_id) > 1) tpp2where tpp1.pay_id=tpp2.pay_id and dPayTime>="2017-06-21 12:12:31" AND dPayTime<="2017-06-28 12:12:31"

如果是联表，那么就会控制两者计算好后，才开始进行联表。而其中子查询的结果是很少的，只有几条，联表起来因此计算很快，2秒完成

这里我们也要注意，如果查询的时间跨度很大，条数很大，那么索引就不会被命中，会自动采用普通查询。

explain select * from t_xxxx tpp1, (select pay_id from t_xxxx where dPayTime>="2017-06-01 12:12:31" AND dPayTime<="2017-06-28 12:12:31" group by pay_id having

count(pay_id) > 1) tpp2 where tpp1.pay_id=tpp2.pay_id and dPayTime>="2017-06-01 12:12:31" AND dPayTime<="2017-06-28 12:12:31"

+----+-------------+--------------+--------+----------------+------+---------+------+---------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+--------+----------------+------+---------+------+---------+----------------------------------------------+

| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |

| 1 | PRIMARY | tpp1 | ALL | index_dPayTime | NULL | NULL | NULL | 1396694 | Using where |

| 2 | DERIVED | t_xxxx | ALL | index_dPayTime | NULL | NULL | NULL | 1396694 | Using where; Using temporary; Using filesort |

+----+-------------+--------------+--------+----------------+------+---------+------+---------+----------------------------------------------+

并没有命中索引

那么我们就要尽量的去优化它，没有命中索引下查询起来是比较慢的。