今天看了一個優化案例覺的挺有代表性,這里記錄下來做一個標記，來紀念一下隨便的字段定義的問題。
回憶一下,在表的設計中很多人習慣的把表的結構設計成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
新聞熱點
疑難解答
