一个mysql数据库查询性能的问题_MySQL

一个mysql数据库查询性能的问题

这周工作时曾遇到一个问题。在一个MYSQL的表里做类似下面这一个很简单查询的时候耗时接近1秒钟的时间。

1

select sum(col5) , sum(col6) from table_name

2

where col_key_2=&＃39;value1&＃39; and col_key_3 = &＃39;value2&＃39;

表定义如下：

01

CREATE TABLE `table_name` (

02

`col_key_1` date NOT NULL default &＃39;0000-00-00&＃39;,

03

`col_key_3` varchar(32) NOT NULL default &＃39;&＃39;,

04

`col_key_2` varchar(32) NOT NULL default &＃39;&＃39;,

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 &＃39;&＃39;,

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`)

查询的速度立马提高到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=&＃39;Widenius&＃39;;

02

03

SELECT * FROM test

04

WHERE last_name=&＃39;Widenius&＃39; AND first_name=&＃39;Michael&＃39;;

05

06

SELECT * FROM test

07

WHERE last_name=&＃39;Widenius&＃39;

08

AND (first_name=&＃39;Michael&＃39; OR first_name=&＃39;Monty&＃39;);

09

10

SELECT * FROM test

11

WHERE last_name=&＃39;Widenius&＃39;

12

AND first_name >=&＃39;M&＃39; AND first_name <&＃39;N&＃39;;

下面这些查询不能用到多列索引：

1

SELECT * FROM test WHERE first_name=&＃39;Michael&＃39;;

2

3

SELECT * FROM test

4

WHERE last_name=&＃39;Widenius&＃39; OR first_name=&＃39;Michael&＃39;;

你可以在sql语句前使用explain语句来确定是否用到了索引。

比如下面这个查询就可以用到class这个索引

01

mysql> explain select sum(col5) , sum(col6) from table_name

02

where col_key_2=&＃39;value1&＃39; and col_key_3 = &＃39;value2&＃39; /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=&＃39;value1&＃39; and col_key_3 = &＃39;value2&＃39; /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%" 。