一个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