一次mysql调优过程
由于经常被抓取文章内容，在此附上博客文章网址：，偶尔会更新某些出错的数据或文字，建议到我博客地址 : --> 点击这里
前几天进行了一个数据库查询，比较缓慢，便查询了一下，在这里记录一下，方便以后翻阅，
1）先复习一下查询索引
(Tue Jun 27 12:33:24 2017) db_1 >>show keys from xxxx; ==>(与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 索引注释
我们要注意：
1 cardinality 的值越大，那么命中此索引的几率越高 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;
Empty set (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) tpp2
where 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 | <derived2> | 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 | +----+-------------+--------------+--------+----------------+------+---------+------+---------+----------------------------------------------+
并没有命中索引
那么我们就要尽量的去优化它，没有命中索引下查询起来是比较慢的。
源码面前，了无秘密
