MySQL数据库之索引的应用

服务器

浏览数:206

2019-3-18

前面几篇文章详细介绍了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的维护篇多实例搭建,如有其它需要介绍的也可以留言,欢迎大家转发评论!