索引概念

索引是一种特殊的文件(InnoDB引擎的数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

SELECT id,name FROM students WHERE name='zhangsan'

上面这个查询语句中，如果这个students表有200万行数据，在没有索引的情况下，数据库会顺序遍历全部数据后选择符合条件的数据，这将导致浪费大量时间和性能。

而给name字段设置索引之后，数据库会直接在索引中查找符合条件的数据，像查字典一样，从目录中直接找到数据所在的位置，节省大量时间和性能。

 

聚簇索引与非聚簇索引

索引分为聚簇索引和非聚簇索引两种，聚簇索引是数据存放时的顺序与索引顺序相同，因此一张数据表只允许存在一个聚簇索引，可以存在多个非聚簇索引。

类似于字典只能有一种实际排序方式，但每个人都可以将自己常用的字与页数单独记在一张纸上，查询时不必查目录，而是从这张纸上获取。

一般数据库默认都会为主键生成聚簇索引。

聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

 

索引的类型

普通索引

这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

直接创建索引

#CREATE INDEX index_name ON table_name(column_name(length))
CREATE INDEX nameIndex ON students(name)

修改表结构的方式添加索引

#ALTER TABLE table_name ADD INDEX index_name (column_name(length))
ALTER TABLE students ADD INDEX nameIndex (name)

创建表的时候同时创建索引

CREATE TABLE `students` (
    `id` int NOT NULL AUTO_INCREMENT ,
    `name` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (name(255))
)

查看表中索引

SHOW INDEX FROM table_name

删除索引

DROP INDEX index_name ON table

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

直接创建索引

#CREATE UNIQUE INDEX index_name ON table(column(length))
CREATE UNIQUE INDEX nameIndex ON students(name)

修改表结构的方式添加索引

#ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name(length))
ALTER TABLE students ADD UNIQUE INDEX nameIndex (name)

创建表的时候同时创建索引

CREATE TABLE `students` (
    `id` int NOT NULL AUTO_INCREMENT ,
    `name` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE index_name (name(255))
)

组合索引

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如针对scores表的name和score字段建立一个组合索引：

ALTER TABLE scores ADD INDEX index_name_score (name,score)

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–name,score
–name

为什么没有-score这样的组合索引呢？

这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

使用到上面的索引:

SELECT * FROM scores WHREE name='zhangsan' AND score=100;
SELECT * FROM scores WHREE name='zhangsan';

未使用到上面的索引：

SELECT * FROM scores WHREE score='100';

以上就是常用的三种索引类型及其创建使用的方法，当然还有其他的类型，这里仅列举平时最常用的几种。

 

索引的优化

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE等操作，MySQL不仅要修改数据，还要修改一下索引文件，类似于在字典中新增、删除、更新字时，必须要更新目录。

另外建立索引会使索引文件占用磁盘空间，一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果MySQL表中有大量数据，就需要花时间研究建立最优秀的索引，或优化查询语句。

下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

 

1.何时使用聚集索引或非聚集索引？

 

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。

比如某个表有一个时间列，恰好把聚合索引建立在了该列，这时查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为这本字典正文是按日期进行排序的。

聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

 

2.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

 

3.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

 

4.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

 

5.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

 

6.不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。

而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引会引起反作用，索引虽好用，可不要贪杯噢。

 

唯一索引与主键

以如下数据表为例：

CREATE TABLE `scores` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
    `score` int DEFAULT NULL,
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生分数表';

其中创建了唯一索引name，在这个表中不能存在重名学生，在插入重复name的数据时会报错。

而在其中还存在一个主键id，同样是表中唯一不能重复。并且设置了auto_increment自增，当插入数据没有设置id时，默认从1开始自动添加。而如果插入的数据带有id数据时，若表中没有该id值则顺利插入，如果重复则报错。

 

unique与primary key的区别

简单来说，primary key=unique+not null。

具体的区别：

(1) 唯一索引所在的列允许空值，但是主键所在的列不允许空值。

(2) 可以把唯一索引放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性索引所在的列并不是表的主键列。

(3) 在默认情况下，创建唯一索引的是非聚簇索引，但是，当没有设置主键时也可以指定所创建的索引是聚簇索引。

(4) 一个表最多只有一个主键，但可以有很多唯一索引

(5) 建立主键的目的是让外键来引用，因此主键应当是对用户没有意义的。

(6)主键应当由数据库自动生成，不应该动态变化更不应该用户去更新。

 

唯一列冲突

当往数据表中插入数据时发现唯一列重复导致插入失败时，有以下几种处理方法：

 

insert ignore

insert ignore会跳过数据库中已经存在的数据（根据主键和唯一索引判断），只插入数据库中没有的数据。简而言之，重名数据不修改。

insert ignore into scores (name,score) values ('zhangsan',77)
//Warning: (1062, "Duplicate entry 'zhangsan' for key 'scores.name'")

可以看到有警告提示，但并不会报错中断，不影响后面其他数据的插入。

虽然重名数据未进行修改，但后方插入的数据主键id的自增却会增加。

 

replace into

replace into会先删除数据库中已存在的数据（根据主键和唯一索引判断），再插入新的数据。简而言之，重名数据被替换。

replace into scores (name,score) values ('zhangsan',77)

由于涉及到删除操作，因此操作用户需要有当前数据表的delete权限。

数据修改后重名数据的主键id和创建时间都会更新到最新，因此replace into的操作并不是替换更新，而是删除追加。

 

insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update，则在出现重复值的行执行UPDATE更新。简而言之，重名数据被更新。

insert into sc (name,score) values ('zhangsan',80) on duplicate key update score=59;

 

运行后，zhangsan的score将被更新为59分，主键id和创建时间不变，修改时间变为当前时间，因此这种操作实际是在重名数据上进行update修改。

但即使是这种修改更新操作，同样会造成主键id的自增增加。

 

死锁

insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误。

如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作。

然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock死锁，例如

解决办法：

1. 尽量对存在多个唯一键的table使用该语句
2. 在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

 

本文转载自逆风前行的小强和hongda’s blog，有整理和修改。

---

人能四处走走，

遇见形形色色的人、事、物，

总比闷在家里要好。

别老是借口夏天热、冬天冷，

多到外面看看去。

或许会遇上许许多多新的、旧的回忆。

——岛田洋七