一个mysql多列索引的问题

最新推荐文章于 2024-07-13 18:10:19 发布

weixin_33815613

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量95

点赞数

文章标签：数据库 python c/c++

原文链接：https://my.oschina.net/costaxu/blog/99170

版权

2019独角兽企业重金招聘Python工程师标准>>>

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

select sum(col5) , sum(col6) from table_name
where col_key_2='value1' and col_key_3 = 'value2'

表定义如下：

CREATE TABLE `table_name` (
  `col_key_1` date NOT NULL default '0000-00-00',
  `col_key_3` varchar(32) NOT NULL default '',
  `col_key_2` varchar(32) NOT NULL default '',
  `col5` bigint(20) unsigned default NULL,
  `col6` bigint(20) unsigned default NULL,
  `col7` bigint(20) unsigned default NULL,
  `col8` bigint(20) unsigned default NULL,
  `col_key_4` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`col_key_1`,`col_key_2`,`col_key_3`,`col_key_4`)
) 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时也能用到这个索引。

比如有这个表：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

下面这些查询都可以用到多列索引：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

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

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

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

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

mysql> explain select sum(col5) , sum(col6) from table_name 
where col_key_2='value1' and col_key_3 = 'value2' \G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_name
         type: ref
possible_keys: class
          key: class
      key_len: 68
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

而下面这个查询则不能使用到索引：

mysql> explain select sum(col5) , sum(col6) from table_name
 where col5='value1' and col_key_3 = 'value2' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_name
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2357455
        Extra: Using where
1 row in set (0.00 sec)

索引的好坏

MySQL使用一个指标value group size来衡量索引的好坏。什么是value group呢？就是具有相同索引key值的行数。这个指标显然是越小越好。最理想的情况就是每一个key值只对应1行，这样的话我们的每次搜索一个key值都只返回一行，显然速度非常快。

可以用mysql提供的工具查看一个表的索引的好坏。可以先用analyze table语句更新统计，然后用show index来查看统计：

mysql> analyze table table_name;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| stat.table_name | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (3.13 sec)

mysql> show index in table_name;