MySQL数据库之索引的应用

前面几篇文章详细介绍了MySQL数据库的DML，DDL，DCL，DQL常用操作，本篇文章将介绍MySQL中一块对于开发和维护都比较重要的内容--MySQL索引的应用！微信搜索公众号：”菜鸟封神记“，定期分享一线大厂常用技术干货。

1、索引的作用
（1）如果索引为唯一索引，可以保证数据库中每一行数据的唯一性
（2）索引如果创建的合适，会大幅度提高数据库的查询性能，这也是索引最大的作用
（3）索引能够使得在查询过程中，使用到数据库的查询优化器，极大提高系统的性能

2、索引的分类
（1）按照数据结构和使用的算法划分：

B+Tree索引

内部实现采用了B+Tree数据结构，数据全部存在叶子节点上。本质上是一棵平衡排序树，由二叉树进化而来，各个叶结点由指针相连，按照从左到右的顺序读取叶子结点上的数据，会得到一个有序的数列。

Hash索引

内部实现采用了Hash算法，数据的保存方式为一对一，一个键对应一条唯一的记录，类似于Redis或者Memcached中的K-V存储结构。

R-Tree索引

内部实现采用了R-Tree数据结构，R-Tree是一种空间索引的数据结构，它是B树向多维空间发展的另外一种形式，在地理位置测绘领域有所应用，其他场景几乎没有应用，了解即可。

（2）按照类型划分：

普通索引

普通索引是一种最基本的索引，只是为了提高数据的查询效率，也是开发中使用比较多的一种索引，允许索引列重复。

主键索引

用来唯一标识数据库中的一条记录，常用于保证数据库中记录的参照完整性，既不可为空，也不能重复。

唯一索引

用来唯一标识数据库中的一条记录，但是与主键索引稍有不同，唯一索引允许索引列的值为空，但是不允许索引列的值发生重复。

联合索引/组合索引

指在数据库表中的某几个字段上同时建立的索引，即：这个索引会关联不止一个列。使用的时候需要特别注意，这种索引遵循最左前缀匹配规则，在下面的索引使用中会详细介绍。

全文索引

用来完成某一段文字中的关键字查找，可以简单理解为like的加强版，不过使用方法和like不同，全文索引比较像一个搜索引擎。它目前支持的数据类型有：char，varchar和text类型。

3、索引的创建和查看
（1）索引的创建
语法：

方法一：使用CREATE INDEX方法

CREATE <unique> INDEX index_name ON table_name(<field1,field2,...>);

方法二：使用修改表结构的方法

ALTER TABLE table_name ADD <unique> INDEX index_name ON table_name(<field1,field2,...>);

方法三：在创建表的时候指定

CREATE TABLE table_name (
    field1 INT NOT NULL AUTO_INCREMENT,
    field2 INT ,
    field3 INT ,
    PRIMARY KEY(field_name),
    UNIQUE index_name(field(len)),
    INDEX index_name(field(len))
);

示例：
示例1：创建一张t_user测试表，字段包含：[id（主键），user_no（用户编号），login_name（登录名称），login_pass（登录密码），phone（手机号）]，要求：id作为主键，user_no列上建立唯一索引，login_name和login_pass两个列上建立联合索引，phone列上建立普通索引：

方法一：创建表的时候指定

mysql> USE test;
mysql> CREATE TABLE t_user(
    id INT NOT NULL AUTO_INCREMENT,
    user_no VARCHAR(30) NOT NULL,
    login_name VARCHAR(50) NOT NULL,
    login_pass VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    PRIMARY KEY(id),  #主键索引
    UNIQUE user_no_ind(user_no),  #唯一索引
    INDEX name_pass_ind(login_name,login_pass),  #联合索引
    INDEX phone_ind(phone)  #普通索引
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;

方法二：使用CREATE INDEX创建索引，此种方式不能创建主键索引

mysql> USE test;
#创建表的时候先不指定索引：
mysql> CREATE TABLE t_user(
    id INT NOT NULL AUTO_INCREMENT,
    user_no VARCHAR(30) NOT NULL,
    login_name VARCHAR(50) NOT NULL,
    login_pass VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用CREATE INDEX命令创建表上的索引
mysql> CREATE UNIQUE INDEX user_no_ind ON t_user(user_no);
mysql> mysql> CREATE INDEX name_pass_ind ON t_user(login_name,login_pass);
mysql> CREATE INDEX phone_ind ON t_user(phone);

方法三：使用ALTER TABLE修改表结构的方式创建索引

mysql> USE test;
#创建表结构
mysql> CREATE TABLE t_user(
    id INT NOT NULL,
    user_no VARCHAR(30) NOT NULL,
    login_name VARCHAR(50) NOT NULL,
    login_pass VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用ALTER TABLE命令创建索引
mysql> ALTER TABLE t_user ADD PRIMARY KEY(id);
mysql> ALTER TABLE t_user ADD UNIQUE INDEX user_no_ind(user_no);
mysql> ALTER TABLE t_user ADD INDEX name_pass_ind(login_name,login_pass);
mysql> ALTER TABLE t_user ADD INDEX phone_ind(phone);

示例2：创建一张帖子内容表，并在帖子内容列创建全文索引

方法一：创建表结构的时候指定索引

mysql> USE test;
mysql> CREATE TABLE t_note(
    id BIGINT NOT NULL AUTO_INCREMENT,
    note_content TEXT NOT NULL,
    create_time DATETIME,
    PRIMARY KEY(id),
    FULLTEXT(note_content) #添加全文索引
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

方法二：使用CREATE INDEX方式创建全文索引

mysql> USE test;
mysql> CREATE TABLE t_note(
    id BIGINT NOT NULL AUTO_INCREMENT,
    note_content TEXT NOT NULL,
    create_time DATETIME,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
mysql> CREATE FULLTEXT INDEX note_ind ON t_note(note_content);

方法三：使用ALTER TABLE修改表结构的方式创建全文索引

mysql> USE test;
mysql> CREATE TABLE t_note(
    id BIGINT NOT NULL AUTO_INCREMENT,
    note_content TEXT NOT NULL,
    create_time DATETIME,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
mysql> ALTER TABLE t_note ADD FULLTEXT note_ind(note_content);

（2）索引信息的查看

语法：

SHOW INDEX FROM table_name <WHERE key_name = ''>;
SHOW INDEXES FROM table_name <WHERE key_name=''>;
注意：SHOW后面可以为INDEX或者INDEXES，可以使用WHERE条件根据索引名称查看索引信息

示例：查看t_user表上所创建的索引

mysql> USE t_user;
mysql> SHOW INDEXES FROM t_user \G
*************************** 1. row ***************************
        Table: t_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
...其他行略...

输出字段解释：

Table：索引所在的表名称
Non_unique：是否为非唯一的索引，对唯一索引和主键索引，此值为0，因为主键和唯一键必须唯一
Key_name：索引的名称
Seq_in_index：索引中该列的位置，在
Column_name：索引所在列的列名称
Collation：列使用哪种方式存储在索引中，B+数索引总是A，表示排过序的。对于其他的如Hash索引，此处可能为NULL，因为Hash索引并未排序
Cardinality：索引中非胃一直的数目的估计值，通常用于优化器去判断是否查询时使本索引
Sub_part：是否只是用了列的一部分作为索引。比如：在某个非常长的字段上的前多少个字符上创建索引的情况
Packed：关键字如何被压缩，Null表示未被压缩
Null：索引的列中是否含有Null值，主键索引，此处为空，表示不含有Null值
Index_type：索引类型，InnoDB存储引擎，此处为B+树
Comment：索引列的注释
Index_comment：索引的注释

注意：上述字段中，Cardinality字段相对来说比较重要，可以通过该字段来判断当前的索引是否最优，通常如果索引的利用率比较高的话，这个值会比较接近于表中的记录数，即：和表中的记录数接近于1:1，但是这个值并不是实时维护，索引当相差比较大的时候，可以使用"ANALYZE TABLE table_name"命令去更新下这个值，有利于优化器对索引使用的判断。

4、索引的修改和删除
（1）索引的删除
语法：

DROP INDEX index_name ON table_name;

示例：
示例1：删除t_user表中phone列上的phone_ind索引

mysql> USE test;
mysql> DROP INDEX phone_ind ON t_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

示例2：删除t_user表中的id列上的主键索引

mysql> USE test;
mysql> ALTER TABLE t_user DROP PRIMARY KEY;   
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

（2）索引的修改
索引的修改过程其实是先删除索引，在重新创建索引，可以按照上述的删除索引和创建索引步骤完成。

5、索引使用注意事项
（1）使用场景

a.业务场景中，读多写少的场景
b.SQL查询场景中，常用于WHERE语句之后的过滤条件；区分度大于80%；WHERE语句之后的过滤字段在过滤时不参与运算；

（2）以下的情况，对于有索引的列，查询时也不会使用索引

a.当优化器判断使用索引和不适用索引差别不大时，将不会使用索引，比如：性别列创建的索引
b.查询条件中发生计算，优化器将不使用索引，比如：WHERE SUBSTR(name,5) = 'BING'
c.查询条件中包含了隐式类型转换，比如：WHERE phone = 13520277199
d.反向查询不会使用索引，比如：!=,<>,NOT IN,NOT LIKE等，比如：WHERE name != 'BING';
e.LIKE的左模糊匹配，将不会使用索引，比如：WHERE name LIKE '%BING'，右匹配查询会走索引;
f.联合索引中，不满足左前缀规则，则MySQL不会使用索引。比如：对于name,pass,user_no列的联合索引，下述情况将不会使用索引：
        WHERE pass = 'value'
        WHERE user_no = 'value'
        WHERE pass = '123' AND user_no = '123'
    而如下的情况将会使用到索引：
        WHERE name = 'bing'
        WHERE name = 'bing' AND pass = '123';
        WHERE name = 'bing' AND user_no = '021250';
        WHERE pass = '123' AND name = 'bing';
        WHERE name = 'bing' AND pass = '123' AND user_no = '021250';
g.某个带索引的列和不带索引的列中间使用OR连接，则带索引的列也不会使用索引，如：user_no列带有索引，phone_未带索引，则：
        不会使用索引：WHERE user_no = '123' OR phone = '13520277898'
        会使用索引：WHERE user_no = '123' AND phone = '15265648758'
h.如果在联合索引中有范围查询，如果字段之间使用OR连接，则整个查询条件不会使用索引，如果字段之间使用AND连接，则从第一个范围查询开始之后的条件都不会使用索引
    比如：name,score,usre_no列上的联合索引，则：
        不会使用索引：WHERE name = 'bing' OR score = 123 OR user_no = '02311';
        不会使用索引：WHERE name = 'bing' AND score = 123 OR user_no = '01231';
        会使用索引：WHERE name = 'bing' AND score = 123 AND user_no = '021321';
        name列会使用索引，name之后的列不会使用索引：WHERE name = 'bing' AND score > 120 AND user_no = '021321';

6、执行计划查看
语法：

EXPLAIN <select statement>

示例：
示例1：查看t_user表上面的id列查询执行计划：

mysql> USER test;
mysql> EXPLAIN SELECT * FROM t_user WHERE id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

输出字段说明：

id:查询编号，如果有多个大小相同的编号，则执行顺序为从上到下，如果大小相同，则编号大的先执行
select_type:表示查询类型，有以下几种值：
    SIMPLE:表示该查询为一个简单查询，如上述的根据id查询就是一个简单查询
    SUBQUERY:表示该查询是WHERE字句中的子查询
    DERIVED:表示该查询是FROM字句中的子查询
    PRIMARY:表示一个负责嵌套查询最外层的查询
    UNION:表示UNION查询的第二个SELECT子查询
    UNION RESULT:表示该结果是从UNION表中查询出的结果
table:表示查询时所关联的表
type:表示关联类型或者访问类型，常见的几种值如下：
    ALL:表示未使用索引，扫描全表
    index:表示扫描所有的索引，通过扫描索引树去定位所有待查询数据
    range:表示按照索引的范围扫描，即：从某索引的某个位置开始，到索引的另外一个位置结束，找出这个范围内每个索引对应的数据，范围查询会出现range
    ref:非唯一索引扫描，MySQL将返回匹配这个索引的所有行
    eq_ref:唯一索引扫描，即：通过该索引只能定位到表中的一条数据，主键索引和唯一索引属于这种类型
possible_keys:查询优化器可能使用到的索引，但是不一定使用
key:查询优化器真正使用的索引
key_len:使用的索引长度，比如：在某个比较长的列上，通常只会给前多少个字符创建索引，这个长度就表示索引字符的长度
ref:表示表的连接匹配条件中，哪些列或者常量被用于查找索引列上的值。可以理解为如果要完成这个查询，需要关联其他表中的哪个列或者常量
rows:要查询到目标数据，需要扫描的行数
Extra:其他额外信息，常见的有以下几种：
    Using Where:表示查询结果需要在存储引擎层通过Where条件完成过滤
    Using index:表示该Where条件的查询使用到了覆盖索引，即：该索引包括了满足查询所有数据的需求；
    Using tempory:表示该查询使用到了临时表来存储中间结果集，通常在排序或者分组查询中会出现
    Using filesort:表示该查询使用到了写磁盘的方式来存储结果集，出现这种情况，表示查询性能极差，已经发生了磁盘IO

以上的查询计划中主要关注的列：

type:查询类型，该列出现了ALL的查询类型，就表示查询语句有问题，需要根据索引使用的注意事项来排查
key:表示查询是否用到了索引，如果该列为NULL，表示索引未起到实际作用
rows:查询行数，如果特别大，和表中的数据条数相差不大，则表示索引利用率特别低，需要优化索引
Extra:如果发现有Using filesort，表示索引的效率特别差，已经发生了磁盘IO，需要排查对应的语句和索引使用情况

示例2：查询t_user表中id大于4的记录，观察其执行计划

mysql> USE test;
mysql> EXPLAIN SELECT * FROM t_user WHERE id > 4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_user
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

从上述输出结果看出：type列为range，possible_keys和key都为PRIMARY，表示该查询使用到了范围查询，而且用到了主键索引。
示例3：查看t_user表中login_name列的索引使用情况

mysql> USE test;
mysql> EXPLAIN SELECT * FROM t_user WHERE login_name = 'aaa' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_user
         type: ref
possible_keys: name_pass_ind
          key: name_pass_ind
      key_len: 152
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

由结果可以看出，login_name列的条件查询用到了login_name,login_pass列的联合索引。
示例4：查看t_user表中id最大的记录的执行计划

mysql> USE test;
mysql> EXPLAIN SELECT * FROM t_user WHERE id = (SELECT MAX(id) FROM t_user) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t_user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
2 rows in set (0.00 sec)

由上述结果可以看出，位于WHERE之后的子查询的查询类型为SUBQUERY，直接查询最大id不会使用到索引。

7、索引优化
（1）对于主键索引，最好采用整型数字的格式，因为在普通索引中，索引树的叶子节点上存储的是主键索引的值，这个值如果过大，会导致普通索引会变的特别大
（2）在更新频繁的列上最好少创建索引，因为更新需要维护索引树，而这个维护过程是很耗时的
（3）创建联合索引时，应该考虑哪些组合列上的查询需求最大，从而确定联合索引的顺序，因为联合索引有左前缀规则
（4）索引并不是越多越好，索引如果过多，会导致磁盘大量浪费，而且在更新这些查询较少的列时，会产生很大的IO操作，造成服务器资源浪费
（5）索引可以在后期通过监控MySQL数据库中的慢SQL再来优化和添加。最好的办法还是开始就考虑周全，创建好索引
（6）添加索引的时候，需要注意是否有慢SQL，如果有慢SQL，会阻塞索引的添加操作，一直处于等待中

至此，MySQL索引相关内容介绍完毕，理论性的东西较多，上述讲解理论的同时，也举了一些实际例子。内容比较丰富，如有不清楚的可以留言！下篇文章将开始介绍MySQL的维护篇多实例搭建，如有其它需要介绍的也可以留言，欢迎大家转发评论！

后续更多文章将更新在个人小站上，欢迎查看。

另外提供一些优秀的IT视频资料，可免费下载！如需要请查看https://www.592xuexi.com