今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记，来纪念一下随便的字段定义的问题。
回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例.
查询语句:
| SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel; |
该表(client_id,channel)是一个组合索引.
利用explain,看一下执行计划,对于索引使用上看上非常完美
| mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+| 1 | SIMPLE | xxx_sources | index | idx_client_channel | idx_client_channel | 1032 | NULL | 20207319 | Using where; Using index |+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+1 row in set (0.00 sec) |
看一下实际执行:
| mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;+---------+----------+| channel | visitors |+---------+----------+| NULL | 0 |+---------+----------+1 row in set (11.69 sec) |
实际执行的情况非常的糟糕.传通的想法,这个执行从索引上执行计划上看非常完美了,好象和MySQL没什么关系了. 在去看一下表的设计会发现client_id也是设计成了
varchar(255).看到这里不防可以使用下面的方法试一下:
| mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+| 1 | SIMPLE | xxx_sources | ref | idx_client_channel | idx_client_channel | 258 | const | 457184 | Using where; Using index |+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+1 row in set (0.00 sec) |
从执行计划上来看,差不多,但实际差多了.具体上来看key_len从1032降到了258,执行计划变成了const基于等于的查找,行数从原来千万级到了十万级了.不算也能明白IO
新闻热点
疑难解答
