這篇文章主要介紹了設置MySQL中索引的數據類型來優化運行速度的實例,主要是適當使用短字節的數據類型來處理短索引,需要的朋友可以參考下

今天看了一個優化案例覺的挺有代表性,這里記錄下來做一個標記，來紀念一下隨便的字段定義的問題。

回憶一下,在表的設計中很多人習慣的把表的結構設計成Varchar(64),Varchar(255)之類的,雖然大多數情況只存了5-15個字節.那么我看一下下面這個案例.

查詢語句:

1. 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,看一下執行計劃,對于索引使用上看上非常完美

1. mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel; 
2. +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ 
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
4. +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ 
5. | 1 | SIMPLE | xxx_sources | index | idx_client_channel | idx_client_channel | 1032 | NULL | 20207319 | Using where; Using index | 
6. +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ 
7. 1 row in set (0.00 sec) 

看一下實際執行:

1. mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel; 
2. +---------+----------+ 
3. | channel | visitors | 
4. +---------+----------+ 
5. | NULL | 0 | 
6. +---------+----------+ 
7. 1 row in set (11.69 sec) 

實際執行的情況非常的糟糕.傳通的想法,這個執行從索引上執行計劃上看非常完美了,好象和MySQL沒什么關系了. 在去看一下表的設計會發現client_id也是設計成了

varchar(255).看到這里不防可以使用下面的方法試一下:

1. mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel; 
2. +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ 
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
4. +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ 
5. | 1 | SIMPLE | xxx_sources | ref | idx_client_channel | idx_client_channel | 258 | const | 457184 | Using where; Using index | 
6. +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ 
7. 1 row in set (0.00 sec) 

從執行計劃上來看,差不多,但實際差多了.具體上來看key_len從1032降到了258,執行計劃變成了const基于等于的查找,行數從原來千萬級到了十萬級了.不算也能明白IO

節省了很多.

再來看實際執行:

1. mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel; 
2. +---------+----------+ 
3. | channel | visitors | 
4. +---------+----------+ 
5. | NULL | 0 | 
6. +---------+----------+ 
7. 1 row in set (0.25 sec) 

哇,從11.69秒變成了0.25秒,這是什么概念,優化了多少倍,算一下吧.

看到這里在想什么呢,記住這個案例,嗯,不錯,以后還可以加引號優化一下.那為什么不問一下,能不能在優化了,為什么會這樣呢?

我們先來看一下第一個問題：

能不能在優化了?

答案是當然可以了.從索引的長度上來看258還是一個非常大的數據,對于client_id這個字段從名字上來看,也只會存數據型的值,那為什么不用的一個int unsigned去存呢,

索引的長度馬上會從258降到4。這樣不是又節省了很多嗎?

接下來看一下第二個問題，為什么會這樣呢?

原因有兩點，同時基于一個原則，基于成本的優化器。對于client_id在表的定義時定義成了字符型的值，在查詢時傳入了數值型的值，需要經過一個數值轉換，悲劇的開始，最終

導致MySQL選擇了一個完成的索引去掃描。

從這個案例上，我們需要注意什么呢?

合理的選擇數據類型，基本工太重要了，就這叫贏在起跑線，一切都不能隨便了，別把一個表定義成了降了主建外其它全是Varchar(255)。對數據庫的double/float這種字段做索引時一定要小心。