一個mysql數據庫查詢性能的問題
這周工作時曾遇到一個問題。在一個MYSQL的表裡做類似下面這一個很簡單查詢的時候耗時接近1秒鐘的時間。
www.2cto.com
1
select sum(col5) , sum(col6) from table_name
2
where col_key_2='value1' and col_key_3 = 'value2'
表定義如下：
01
CREATE TABLE `table_name` (
02
`col_key_1` date NOT NULL default '0000-00-00',
03
`col_key_3` varchar(32) NOT NULL default '',
04
`col_key_2` varchar(32) NOT NULL default '',
05
`col5` bigint(20) unsigned default NULL,
06
`col6` bigint(20) unsigned default NULL,
07
`col7` bigint(20) unsigned default NULL,
08
`col8` bigint(20) unsigned default NULL,
09
`col_key_4` varchar(32) NOT NULL default '',
10
PRIMARY KEY (`col_key_1`,`col_key_2`,`col_key_3`,`col_key_4`)
11
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
整個表裡大概只有200多萬條數據。但查詢的速度居然會慢到1秒鐘才能查詢出來，完全不可以忍受。
然後我給這張加上了另一個索引：KEY `class` (`col_key_2`,`col_key_3`)
www.2cto.com
查詢的速度立馬提高到0.00秒。
於是認真的查看了一下mysql 手冊的8.3小節。
MySQL索引的種類和作用
mysql的索引分成：primary key, unique, index, fulltext index。 primary key是主鍵， unique是唯一索引， index是普通的索引。fulltext index是全文索引。 索引的作用就像C語言裡的指針那樣，直接指向表的一行。
可以對用col_name(N) 對符串的前N個字節做索引。 text類型和blob類型則必須要對前N個字節做索引。MYISAM最多支持1000個字節的索引， INNODB最多支持767字節的索引。
索引有下列作用：
1 幫助where語句快速查詢。
2 進行多表連接
3 找到最大值和最小值（應該只有B-tree索引有這個功能，hash索引沒有這個功能）
4 sort（應該只有B-tree索引有這個功能，hash索引沒有這個功能）和group
多列索引
多列索引在對多個列同時進行查詢的時候特別有用。多列索引最多支持16列。可以這樣理解多列索引：
把多個列concat在一起，然後再對這個concat的值做一個索引。
比較神奇的一點是，比如你有一個索引針對col1 col2 col3這3個列時， 只查詢col1和只查詢col1 col2時也能用到這個索引。
比如有這個表：
1
CREATE TABLE test (
2
id INT NOT NULL,
3
last_name CHAR(30) NOT NULL,
4
first_name CHAR(30) NOT NULL,
5
PRIMARY KEY (id),
6
INDEX name (last_name,first_name)
7
);
下面這些查詢都可以用到多列索引：
01
SELECT * FROM test WHERE last_name='Widenius';
02
03
SELECT * FROM test
04
WHERE last_name='Widenius' AND first_name='Michael';
05
06
SELECT * FROM test
07
WHERE last_name='Widenius'
08
AND (first_name='Michael' OR first_name='Monty');
09
10
SELECT * FROM test
11
WHERE last_name='Widenius'
12
AND first_name >='M' AND first_name < 'N';
下面這些查詢不能用到多列索引：
1
SELECT * FROM test WHERE first_name='Michael';
2
3
SELECT * FROM test
4
WHERE last_name='Widenius' OR first_name='Michael';
你可以在sql語句前使用explain語句來確定是否用到了索引。
比如下面這個查詢就可以用到class這個索引
01
mysql> explain select sum(col5) , sum(col6) from table_name
02
where col_key_2='value1' and col_key_3 = 'value2' \G
03
*************************** 1. row ***************************
04
id: 1
05
select_type: SIMPLE
06
table: table_name
07
type: ref
08
possible_keys: class
09
key: class
10
key_len: 68
11
ref: const,const
12
rows: 1
13
Extra: Using where
14
1 row in set (0.00 sec)
而下面這個查詢則不能使用到索引：
01
mysql> explain select sum(col5) , sum(col6) from table_name
02
where col5='value1' and col_key_3 = 'value2' \G
03
*************************** 1. row ***************************
04
id: 1
05
select_type: SIMPLE
06
table: table_name
07
type: ALL
08
possible_keys: NULL
09
key: NULL
10
key_len: NULL
11
ref: NULL
12
rows: 2357455
13
Extra: Using where
14
1 row in set (0.00 sec)
索引的好壞
MySQL使用一個指標value group size來衡量索引的好壞。什麼是value group呢？ 就是具有相同索引key值的行數。這個指標顯然是越小越好。最理想的情況就是每一個key值只對應1行， 這樣的話我們的每次搜索一個key值都只返回一行，顯然速度非常快。
可以用mysql提供的工具查看一個表的索引的好壞。可以先用analyze table語句更新統計，然後用show index來查看統計：
1
mysql> analyze table table_name;
2
+-----------------+---------+----------+----------+
3
| Table | Op | Msg_type | Msg_text |
4
+-----------------+---------+----------+----------+
5
| stat.table_name | analyze | status | OK |
6
+-----------------+---------+----------+----------+
7
1 row in set (3.13 sec)
8
9
mysql> show index in table_name;
table_name這張表有兩個索引PRIMARY和class，PRIMARY這個索引是一個包含4列的多列索引。
Cardinality這個值表示索引值的不同的行數。
例如：
col_key_1值有18行。
col_key_1＋col_key_2 值有392909行。
col_key_1 + col_key_2 + col_key_3 值有235745行。
col_key_1 + col_key_2 + col_key_3 + col_key_4值有235745行。
通過索引值的行數，我們就可以看出來索引好還是不好了。索引值不同的行數越多索引就越好。當索引值不同的行數=表的總行數就達到最理想的情況 value group size = 1了。
B-tree索引和Hash索引的比較
默認情況下MySQL都是使用B-tree索引。來談一下Hash索引的缺陷:
1 只能處理’=‘ 這種where 子句，而對於< >是無能為力的。 這和B-tree索引是有序的，Hash無序的有關。
2 無法處理order by。 原因同上。
3 無法得知兩行之間的距離。 原因同上。
4 只能搜完整的字段，不能只搜字段的一部分。 而對於B-tree索引， 支持搜索字符串最左邊的一部分。例如"police%" 。
