索引简介

MySQL 索引是一中特殊的文件，它们包含着对数据表所有记录的引用指针。就像书本的目录一样，在没有索引的情况下，数据库会遍历所有相关记录，找出符合条件的记录；而有索引之后，数据库会先在索引中查找，再定位到具体的物理位置去除数据信息。

查看表中的索引信息：

1
SHOW INDEX FROM `table_name` \G;

索引的优缺点

优点：

  • 索引可以加快数据库检索速度

缺点：

  • 索引本身也是表，因此会占用存储空间。
  • 数据库的添加、修改、删除等操作的效率会降低，因为同时需要维护索引表。

索引分类

常见的索引有：普通索引、主键索引、唯一索引、组合索引、全文索引、空间索引等。

普通索引

普通索引是最基本的索引，一个列组成一个索引，基本没有任何限制，一张表中可以有多个普通索引。普通索引主要是为了加快查找速度。

  • 创建表的时候同时创建普通索引：

    1
    2
    3
    4
    5
     CREATE TABLE `users` (
       `id` int(11) NOT NULL,
    `name` varchar(32) NOT NULL,
    INDEX `index_name` (name(16))
     );

    在name创建普通索引index_name，(16)表示用name列的前 16 个字符创建索引，不指定长度 (如INDEX index_name (name))，则使用列的完整值。
    建立索引的时候，很多时候不必要使用相应列定义的长度，如果前一部分的值具有唯一性，则只截取前面一部份值影响也不大 (如这里的name一般不会超过 10 个字符)。这样会加速索引查询速度，而且还减少索引文件的大小，提高插入修改的速度等。

  • 创建表之后添加索引：

    1
    2
    3
    CREATE INDEX `index_name` ON users(name(32));
    # or
    ALTER TABLE `users` ADD INDEX index_name(`name`);

  • 删除索引：

    1
    DROP INDEX `index_name` ON `users`;

唯一索引

唯一索引，与普通索引类似。不过，索引列的值必须唯一，但可以为空值，而且每列的空值可以有多个。

  • 创建表的时候指定唯一索引：

    1
    2
    3
    4
    5
     CREATE TABLE `users` (
       `id` int NOT NULL,
    `name` varchar(32) NOT NULL,
    UNIQUE `index_name` (`name`),
     );

  • 创建表之后添加索引：

    1
    2
    3
    CREATE UNIQUE INDEX `index_name` ON `users` (`name`);
    # Or
    ALTER TABLE `users` ADD UNIQUE `index_name` (`name`);

主键索引

即主键，一张表中只能够有一个主键，并且不允许重复值和空值。主键索引可以加速查找和唯一约束。

  • 创建表的时候添加主键索引：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     CREATE TABLE `users` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL,
    PRIMARY KEY (`id`)
     );
     # Or
     CREATE TABLE `users` (
       `id`  int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(32) NOT NULL
     );

  • 创建表之后添加主键索引：

    1
    ALTER TABLE `users` ADD PRIMARY KEY(`id`);

普通索引、唯一索引和主键索引比较：

  • 三者都是单列索引，即索引只能由一列组成
  • 主键索引，每张表只能有一个，不能为空值，而且值不能够重复
  • 唯一索引，每张表可以有多个，可以设置为空值，而且每列可以出现多个空值，但是不为空的值不能重复。
  • 普通索引，基本没什么限制。每张表可以有多个，而且不为空的值能够重复。

全文索引

当需要根据文本中的关键字来检索的时候，普通的索引就无效了，如果不用索引而且数据量很大的话，全表检索将消耗太多的时间，这时候，可以使用 FULLTEXT 索引。

如上面的users表，当插入大量的数据的时候，执行查找语句：

1
2
3
SELECT * FROM `users` WHERE `name` LIKE 'lizs%' \G;
# 当检索关键字的时候，普通索引并不生效
SELECT * FROM `users` WHERE `name` LIKE '%lizs%' \G;

这时候，就需要使用 FULLTEXT 索引了。

只有InnoDB (MySQL5.6开始支持)和MyISAM的CHAR，VARCHAE和TEXT列支持 FULLTEXT 索引。MySQL 5.7.6 开始支持中文的全文索引。

  • 创建表的时候添加全文索引：

    1
    2
    3
    4
    5
    6
     CREATE TABLE `users` (
       `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL,
    PRIMARY KEY (`id`),
    FULLTEXT (`name`),
     );

  • 创建表之后添加索引：

    1
    2
    3
    CREATE FULLTEXT INDEX `index_name` ON `users` (`name`);
    # Or
    ALTER TABLE `users` ADD FULLTEXT `index_name` (`name`);

组合索引 (复合索引)

MySQL 查询只会使用一个索引，所以当频繁使用多个列作为查询条件的时候，建立多个索引来优化查询速度也只有前面一个有效，查询速度优化不理想。这时候，可以考虑使用组合索引。

组合索引遵循最左前缀原则。如，组合索引(a, b, c)只有查询条件为a, a, b, a, b, c才会使用索引。

1
2
3
4
SELECT * FROM table_name WHERE b='x' AND c='x';  # Extra: Using where
SELECT * FROM table_name WHERE a='x';  # Extra: Using index condition
SELECT * FROM table_name WHERE a='x' AND b='x';  # Extra: Using index condition
SELECT * FROM table_name WHERE a='x' AND b='x' AND c='x';  # Extra: Using index condition

组合索引中，如果列中含有 NULL 值，那么这一列对于此组合索引就是无效的。所以，在数据库设计时，对于需要有可能使用组合索引的列，不要设置默认值为 NULL。

  • 创建表的时候添加组合索引：

    1
    2
    3
    4
    5
    6
    7
     CREATE TABLE `users` (
       `id` int NOT NULL AUTO_INCREMENT, 
    `name` varchar(32) NOT NULL,
    `city` varchar(32) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name_city (name(16), city)
     );

  • 创建表之后添加索引：

    1
    2
    3
    CREATE INDEX index_name_city ON `users` (name(16), city(16));
    # Or
    ALTER TABLE `users` ADD INDEX index_name_city (name(16), city(16));

删除索引：

1
DROP INDEX index_name ON table_name;

索引的使用

什么时候使用索引？

  • 经常作为查询条件在WHERE子句中出现的列最好建立索引
  • 经常用来排序在ORDER BY子句中出现的列最好建立索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 高并发条件下倾向组合索引

什么时候不要使用索引？

  • 经常增删改的列
  • 有大量重复值的列
  • 表数据不多的情况下也不建议使用索引

索引使用注意事项

  • 组合索引中，如果列中含有 NULL 值，那么这一列对于此组合索引就是无效的。所以，在数据库设计时，对于需要有可能使用组合索引的列，不要设置默认值为 NULL。
  • 定义索引的时候，如果可能应该指定前缀长度。如果索引列的值的前一部份具有唯一性，合理指定索引的前缀长度，不但可以提高查询速度，还可以提高插入修改的速度，而且还节省了索引占用的磁盘空间。
  • MySQL 查询只使用一个索引。因此，如果WHERE子句中使用了索引的话，ORDER BY排序子句就不会使用索引了。或者多列排序的时候，只有前面一个索引生效，所以在一定要使用多列排序的情况下，最好创建组合索引。
  • 在查询条件中使用<>，IS NULL会导致索引失效
  • 在查询中使用OR连接多个查询条件会导致索引失效，这时候可以分多次SELECT，然后用UNION ALL连接起来。