MySql性能调优-索引

前言

在对mysql的查询语句优化过程中，我们最常用的手段就是添加索引，可见索引对数据库优化的重要性，下面就说一说在优化过程中，索引的一些用法，鉴于本人水平有限，可能会有一些说得不对的地方，欢迎指正。

准备

下面例子中需要使用1张表，表结构如下：

CREATE TABLE `order` (
  `order_id` int(11) NOT NULL COMMENT,
  `order_serial` varchar(20) NOT NULL COMMENT,
  `pay_money` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT,
  `order_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT,
  `shop_id` int(11) NOT NULL COMMENT,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

单列索引

我们来看一下这个语句

SELECT *
FROM `order`
WHERE order_serial = '000011610250002';

通过explain命令分析，如图：

通过分析结果，可以看出，这个查询语句进行了全表扫描。现在我们给order_serial字段添加索引，

ALTER TABLE `order` ADD INDEX order_serial(order_serial);

再使用explain命令分析，如图：

从结果可以看出，添加经索引后，该查询语句使用了索引，并且只需要读取一行数据，而没有优化之前，却需要读取10万行数据，这就是索引的魅力。

索引基数

当查询语句中，能够使用的索此不至1个时，mysql会通过分析每个可以使用的索引的统计信息来找到最高效的索引。基数越高，表示该列的唯一值越多，使用该索引读取的数据越少。为了演示例子， 我们再将order表的shop_id字段添加索引。

ALTER TABLE `order` ADD INDEX shop_id(shop_id);

使用explain分析如下查询语句

SELECT *
FROM `order`
WHERE shop_id = 7  AND order_serial = '000011610250002';

结果如图：

我们发现虽然有2个可以使用的索引，但是使用的是索引order_serial，为什么呢？我们来看一下这两个索引基数，执行如下语句：

SHOW INDEX FROM `order`;

我们看一下列Cardinality的值，这个值表示索引基数。可以看出索引order_serial的基数比shop_id的基数大很多，也就是说使用索引order_serial更优。
当然，一般来说在执行查询语句时，一个表只会选择一个索引，但从5.0开始，也可能出现索引合并操作。
我们再添加一个索引order_status

ALTER TABLE `order` ADD INDEX order_status(order_status);

使用explain分析下面查询语句

SELECT *
FROM `order`
WHERE shop_id = 7  AND order_status = 3;

我们再来看看另外一种情况
使用explain分析下面查询语句(说明：-7这个shop_id不存在)

SELECT *
FROM `order`
WHERE shop_id = -7  AND order_status = 3;

使用explain分析下面查询语句(说明：-3这个order_status不存在)

SELECT *
FROM `order`
WHERE shop_id = 7  AND order_status = -3;

可以看出，虽然是同样的sql语句，只是值不一样，结果所使用的索引也不一样，所以具体使用那个索引，基数大小是一个重要的指标，但是mysql也会参考具体条件的范围与容量等信息。（当然，我这个例子举得不是特别的好，主要是为了说明这个问题）

多列索引

为了演示多列索引，我们执行以下操作

ALTER TABLE `order` 
ADD INDEX shop_id_order_status(shop_id,order_status)
,DROP INDEX order_status
,DROP INDEX shop_id;

为了提高DML语句的执行效率，我们应该合并执行语句
使用explain分析下面查询语句

SELECT *
FROM `order`
WHERE 
1=1
AND shop_id = 7  
AND order_status = 3;

使用explain分析下面查询语句

SELECT *
FROM `order`
WHERE 
1=1
AND shop_id = 7  
-- AND order_status = 3;

使用explain分析下面查询语句

SELECT *
FROM `order`
WHERE 
1=1
-- AND shop_id = 7  
AND order_status = 3;

通过上面执行情况，我们发现，多列索引中最左边的列，可以当成单列索引来使用。

当然索引还有很多要说的，比如索引的重复的问题（多列索引中，将最左边的列又单独创建一个索引，这就是索引重复，不断没有带来好处，还多出了一些额外的开销），比如局部索引，等等。