Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of slow query optimization in mysql, which is very detailed and has certain reference value. Friends who are interested must finish it!

A user reports that the execution time of an online SQL statement is unacceptably slow. The SQL statement looks simple (the table name and field name have been modified in this article description): SELECT count (*) FROM a JOIN b ON a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00 FROM a JOIN b ON 55' AND a.`L`

< '2014-03-30 01:00:00' ; 且查询需要的字段都建了索引，表结构如下：CREATE TABLE `a` ( `L` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00', `I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `F` tinyint(4) DEFAULT NULL, `V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '', `N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, KEY `IX_L` (`L`), KEY `IX_I` (`I`), KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `b` ( `R` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00', `V` varchar(32) DEFAULT NULL, `U` varchar(32) DEFAULT NULL, `C` varchar(16) DEFAULT NULL, `S` varchar(64) DEFAULT NULL, `I` varchar(64) DEFAULT NULL, `E` bigint(32) DEFAULT NULL, `ES` varchar(128) DEFAULT NULL, KEY `IX_R` (`R`), KEY `IX_C` (`C`), KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;从语句看，这个查询计划很自然的，就应该是先用a作为驱动表，先后使用 a.L和b.S这两个索引。而实际上explain的结果却是： +----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using index || 1 | SIMPLE | a | ref | IX_L,IX_S | IX_S | 195 | test.b.S | 1 | Using where |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+ 分析 从explain的结果看，查询用了b作为驱动表。上一篇文章我们介绍到，MySQL选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？MySQL Tips: MySQL提供straight_join语法，强制设定连接顺序。 explain SELECT count(*) FROM a straight_join b ON a.`S` = b.`S` WHERE a.`L` >

'2014-03-30 00 55 00 AND a.`L`

< '2014-03-30 01:00:00' ; +----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+| 1 | SIMPLE | a | range | IX_L,IX_S | IX_L | 4 | NULL | 63 | Using where || 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1038165 | Using where; Using index; Using join buffer |+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+MySQL Tips: explain结果中，join的查询代价可以用依次连乘rows估算。join顺序对了，简单的分析查询代价：普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL没有错。但一定哪里不对！ 发现异常 回到我们最初的设想。我们预计表a作为驱动表，是因为认为表b能够用上IX_S索引，而实际上staight_join的时候确实用上了，但这个结果与我们预期的又不同。我们知道，索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素，那么是不是b.S的过滤性不好呢？MySQL Tips: show index from tbname返回结果中Cardinality的值可以表明一个索引的过滤性。show index的结果太多，也可以从information_schema表中取。mysql>

Select * from information_schema.STATISTICS where table_name='b' and index_name='IX_S'\ gateway * 1. Row * * TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: B NON_UNIQUE: 1 INDEX_SCHEMA: test INDEX_NAME: IX_S SEQ_ IN_INDEX: 1 COLUMN_NAME: s COLLATION: a CARDINALITY: 1038165 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: CARDINALITY: 1038165 for this index It's already big. What is the estimated row of this table? Show table status like 'b'\ gateway * 1. Row * * Name: B Engine: InnoDB Version: 10 Row_format: Compact Rows: 1038165 Avg_row_length: 114Data_length: 119160832Max_data_length: 0 Index_length: 109953024 Data_free: 5242880 Auto_increment: NULL Create_time: 2014-05-23 00:24:25 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) see from Rows: 1038165 The distinguishability of the index IX_S is considered to be very good, which is close to the unique index. MySQL Tips: the Rows seen in the show table status result is used to represent the current number of rows in the table. This is an exact value for the MyISAM table, but an estimate for InnoDB. Although it is an estimate, the optimizer is guided by this, that is, the data in one of the explain above does not meet expectations at all: the rows in the second row of the staight_join result.

So far

We find that the logic of the whole error is as follows: for the execution plan of the table driven by a, because the rows of index b.S is estimated at 1038165, the optimizer thinks that the cost is greater than that of the table driven by b. In fact, the discrimination degree of this index is 1. (of course, students who are familiar with explan results will find that the type field in the second row is weird with the Extra field.) that is to say, every row straight_join gets has a full table scan when it is queried in b. The most common occurrence of this in MySQL is type conversion. For example, a string field, although it contains all numbers, is not in a string format when querying. In this case, both are strings. Therefore, the character set is relevant. Going back to the two table structures, it is found that the declaration difference of the S field lies in COLLATE utf8_bin-which is the root cause of this case: the S value obtained from table an is utf8_bin, and the optimizer believes that the type is different and cannot be filtered directly by index b.IX_S. As for why indexes are still used, this is because overwriting indexes brings "misunderstanding". MySQL Tips: if all the results of the query can be obtained completely from an index, the traversal index will be preferred instead of traversing data. For verification, mysql > explain SELECT * FROM a straight_JOIN b ON binary a.`S` = b.`S`S` WHERE a.`L` > '2014-03-30 00 straight_JOIN b ON binary 5500' AND a.`L`

< '2014-03-30 01:00:00' ; +--+-----+---+---+-----+--+---+--+---+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--+-----+---+---+-----+--+---+--+---+----------------+ | 1 | SIMPLE | a | range | IX_L | IX_L | 4 | NULL | 63 | Using where | | 1 | SIMPLE | b | ALL | IX_S | NULL | NULL | NULL | 1038165 | Range checked for each record (index map: 0x4) | +--+-----+---+---+-----+--+---+--+---+----------------+ 由于结果是select *， 无法使用覆盖索引，因此第二行的key就显示为NULL. (笔者泪：要是早出这个结果查起来可方便多了）。 优化 当然最直接的想法就是修改两个表的S字段的定义，改成相同即可。这个方法可以避免修改业务代码，但DDL代价略大。这里提供两种在SQL语句方面的优化。1、select count(*) from b join (select s from a WHERE a.`L` >

'2014-03-30 00 55 00 AND a.`L`

< '2014-03-30 01:00:00') ta on b.S=ta.s;这个写法比较直观，需要注意最后b.S和ta.S的顺序2、SELECT count(*) FROM a JOIN b ON binary a.`S` = b.`S` WHERE a.`L` >

From the previous analysis, we know that the b.S is defined as utf8_bin.MySQL Tips: in the character set naming rules of utf8_bin.MySQL Tips: MySQL, the difference between XXX_bin and XXX is case sensitivity. Here we all add the binary qualification to A.s, which is converted to lowercase, that is, the temporary result set is converted to utf8_bin, and then the index can be used directly when using b.S matching. In fact, the essence of the two rewriting methods is the same, the difference is that the writing method 1 is implicit conversion. In theory, method 2 is faster. The above is all the contents of the article "sample Analysis of slow query Optimization in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.