MySQL索引，看这一篇就够了

前言

公司业务开发中为了提高搜索的效率，经常需要申请“加索引”，而本人对索引知识的掌握得不是很好，所以写这篇博客来总结一下索引的使用，从理论到实践，全面地介绍一下索引。

1.什么是索引

简单地说，如果把数据库当做一本书，就是给这本树加一个目录以提高数据库查询的效率。
本质上，添加索引就是在数据库中新增一棵B+Tree¹ 在查询中进行使用（如下图1.1）。

2.索引的创建

1.建立数据库时创建²

1.普通索引
CREATE table score(
    -> id  int(11)  AUTO_INCREMENT  primary  key  not  null,
    -> name  varchar(50)  not null,
    -> math  int(5)  not null,
    -> English  int (5)  not null,
    -> Chinese  int (5) not  null,
    -> index(id)
2.唯一索引:使用  UNIQUE  参数进行约束
mysql> CREATE  table address(
    -> id  int(11)  auto_increment  primary  key  not  null,
    -> name  varchar(50),
    -> address  varchar(200),
    -> UNIQUE  INDEX  address(id  ASC)
    -> );
3.全文索引
mysql> CREATE  table address(
    -> id  int(11)  auto_increment  primary  key  not  null,
    -> name  varchar(50),
    -> address  varchar(200),
    -> UNIQUE  INDEX  address(id  ASC)
    -> );
4.单列索引
mysql> create  table  telephone(
    -> id  int(11)  primary key auto_increment  not  null,
    -> name  varchar(50)  not  null,
    -> tel  varchar(50)  not null,
    -> index  tel_num(tel(20))
    -> );
5.多列索引
mysql> create table  information(
    -> inf_id  int(11)  auto_increment  primary  key  not  null,
    -> name  varchar(50)  not  null,
    -> sex  varchar(5)  not null,
    -> birthday  varchar(50)  not  null,
    -> index  name_sex(name,sex)
    -> );

2.创建索引

1.ALTER TABLE：用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
mysql>
    -> ALTER TABLE table_name ADD INDEX index_name (column_list)
    -> ALTER TABLE table_name ADD UNIQUE (column_list)
    -> ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2.CREATE INDEX：可对表增加普通索引或UNIQUE索引
mysql>
    -> CREATE INDEX index_name ON table_name (column_list)
    -> CREATE UNIQUE INDEX index_name ON table_name (column_list)

1.使用组合索引注意事项

3.覆盖索引、最左前缀原则、索引下推

1.覆盖索引：查询的内容已经在索引树中，不需要回表（根据主键再次查询数据库）
2.最左前缀原则：联合索引的最左N个字段， 也可以是字符串索引的最左M个字符可以用来进行索引查询。
3.索引下推：MySQL 5.6之后，可以对索引中的字段进行判断再回表以提高查询效率

4.聚簇索引与非聚簇索引

1.聚簇索引：再物理位置上连续的，一张表只有一个聚簇索引，一般为主键。
（innoDB引擎支持事务，使用的聚簇索引）
2.非聚簇索引：MyISAM引擎：不需要按顺序查找，直接根据地址进行检索，插入速度快

注：本文参考林晓斌的《MySQL实战45讲》

---

1. 什么是B+Tree ↩︎

2. 原文链接 ↩︎