一次mysql调优过程

发布时间:2017-7-1 10:55:33编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"一次mysql调优过程 ",主要涉及到一次mysql调优过程 方面的内容,对于一次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 |+----+-------------+--------------+--------+----------------+------+---------+------+---------+----------------------------------------------+
并没有命中索引

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


上一篇:MongoDB查询语句(转)
下一篇:CSS3学习手记(2) 伪类选择器

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款