MySQL 的唯一索引

近期一直在看effective mysql之SQL语句最优化，本身倒感觉这书一般，但是偶尔有些还是值得记录下，加深下理解。

我们都知道唯一索引优于普通索引，具体优势在哪儿？

1、提供数据完整性以保证在列中任何值都只出现一次；

2、告知优化器对给定的记录最多只能有一行结果返回，这点很重要，因为有了这些信息就可以避免额外的索引扫描。

其中第1点很容易理解，第2点，我们就做了如下的实验

原先的表结构如下，name字段是个普通索引

CREATE TABLE `artist` (
  `artist_id` int(10) unsigned NOT NULL,
  `type` enum('Band','Person','Unknown','Combination') NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` enum('Male','Female') DEFAULT NULL,
  `founded` year(4) DEFAULT NULL,
  `country_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`artist_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

查看下状态：

mysql> show session status like 'Handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> show session status like 'Handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 0   
+-------------------+-------+
1 row in set (0.00 sec)

mysql> use book;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select name from artist where name='Enya';
+------+
| name |
+------+
| Enya |
+------+
1 row in set (0.02 sec)

mysql> show session status like 'Handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 1   |
+-------------------+-------+
1 row in set (0.00 sec)

把索引重新建成唯一索引：

mysql> alter table artist drop index name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table artist add unique index(name);
Query OK, 0 rows affected (12.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show session status like 'Handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 0    |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> select name from artist where name='Enya';
+------+
| name |
+------+
| Enya |
+------+
1 row in set (0.00 sec)

mysql> show session status like 'Handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 0   |
+-------------------+-------+
1 row in set (0.01 sec)

显然的，优化器没有继续往下一个值进行查找。

Handler_read_next  session/global级别（单位：次）

根据索引依序读取下一个索引键的次数，如索引范围扫描，全索引扫描等。