MySQL索引

1.概述

索引就是一种提高查询效率的数据结构。其优点是大大加快了数据的查询速度。缺点是索引需要消耗数据库资源，对表进行增删改时由于需要维护索引，其速度有影响。

1.1.创建索引的原则

1）最左侧原则（见第二章节第4小节）

2）区分度高的列做索引，也就是字段不重复的比例

3）索引不要参与运算（使用函数或算术运算）

4）尽量对索引进行扩展，而不是创建新的索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

1.2.索引是越多越好吗？

索引就相当于目录，并不是越多越好。索引需要空间来存储，当增加或删除数据以及修改索引列的数据时，索引都会被修改，就意味着增删改都会多几次的IO磁盘操作，增加查询的时间。

2.索引基础分类

为了下面的测试方便，首先创建一个表并插入数据：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL COMMENT '用户名',
  `china_name` varchar(30) DEFAULT NULL COMMENT '中文名',
  `password` varchar(20) DEFAULT NULL COMMENT '密码',
  `id_card` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(null,'zhangsan','张三','1234','19991010','15645254258'),
(null,'lisi','李四','1234','19970101','17545254258'),
(null,'zhangwu','张五','1234','19991012','13645254258'),
(null,'liliu','李六','1234','19990203','15648554258'),
(null,'zhaoliu','赵柳','1234','19990509','15475254258'),
(null,'lifei','李飞','1234','19970210','1564524148'),
(null,'zhaohong','赵虹','1234','19960315','15645254328'),
(null,'songqian','宋倩','1234','19970310','15645254257');

2.1主键索引

当字段被设置为主键后数据库就会自动创建索引，此列的值不能为空。此索引是在表创建时就自动创建了。

使用下面的语句查询表的索引：

show index from user;

查询的截图如下：

其中第一条就是主键的索引，索引名称是primary，索引的列名是id列。第二条是唯一索引，下面介绍。

2.2唯一索引

当字段被设置为唯一约束后数据库就会自动创建索引，此列的值是唯一的，但允许有且仅有一个NULL。此索引是在表创建时就自动创建了。

上面在创建表时，指定了身份证号码（id_card列）为唯一约束，就创建了一个索引，索引的名称就是列名：

当然还可以在创建表后再添加唯一索引

CREATE UNIQUE INDEX indexname  ON tablename (propname);

例如

CREATE UNIQUE INDEX idx_id_card ON user(id_card);

2.3普通索引

普通索引又称单值索引、单列索引，也就是一个索引值包含一列。一个表可以有多个普通索引。

假如给china_name属性添加索引，方法有两种，如下:

1）创建表时创建索引（不常用）

CREATE TABLE `user` (
    ...
  `china_name` varchar(30) DEFAULT NULL COMMENT '中文名',
   key(`china_name`)

)

这种当时不能指定索引的名称，默认是字段的名称。

2）创建表后创建索引（推荐）

语法：

create index 索引名称 on 表名(列名);

真实的sql：

create index index_user_china_name on user(china_name);

如果需要删除索引，语法如下：

drop index 索引名称 on 表名;

2.4复合索引（最左侧原则）

复合索引是一个索引包含多列。其创建方式也有两种，和单值索引类似，这里只介绍第二种方式

下例是给username和password添加复合索引

create index index_user_username_password on user(username,password)

截图如下：

上述给username和password创建了复合索引，相当于是创建了单列索引username和联合索引(username,password)。

需要注意的是，在使用复合索引时，需要遵循最左侧原则，而且在查询过程中会动态调整字段的顺序。也就是说，在查询时，需包含创建索引时其左侧的字段作进行条件查询，才能利用索引提高效率，条件的顺序可不按照创建索引时的顺序。其底层的实现原理就是按照创建索引时的顺序进行排序后生成的B+Tree，也就是说先按照username进行排序，在此基础上再根据password进行排序，只有先找到username才能根据索引有效的找到password的，否则不会使用索引。

且看下面的几种情况：

1）只根据username查询：可以有效利用索引提高查询效率。

2）只根据password查询：不可以。原因是在创建索引时其左侧的字段是username，并未根据此条件查询。

3）根据username和password查询：可以有效利用索引提高查询效率。原因是按创建索引时顺序查询的。

4）根据password和username查询：可以有效利用索引提高查询效率。原因是包含了创建索引时其左侧的字段。虽然没按顺序，但也可以。

3.索引的其他分类方式

3.1.聚簇索引与非聚簇索引

1）聚簇索引：叶子节点存储了数据，也就是把数据和索引放在一起，并且是按照一定的顺序组织的，找到索引也就找到了数据。（例如主键索引，是根据主键建立B+树）

2）非聚簇索引：叶子节点不存储数据，而是存储数据的行地址，通过行地址可以在磁盘找到对应的数据。需要通过行地址再次去磁盘查找数据的过程叫做回表。（例如复合索引）

3.2.覆盖索引

覆盖索引指的是在查询时使用了索引并且所查询的列都在索引中可以全部找到。以上述的用户表已经存在的索引 "index_user_username_password" 进行说明：

select * from user;#1
select * from user where id=1;#2
select username,password from user where username='1';#3
select username,password,phone from user where username='1';#4
select id,username,password from user where username='1';#5

上述sql中有哪些是覆盖索引呢，答案是2,3,5个。原因是这3个个查询的列都包含在了索引中，而其他的sql由于索引中不包含所有字段，故需要根据主键再去数据库查询对应的数据。因此在日常开发中尽量避免使用 " select * "，而是明确要查询的字段。

4.使用场景

4.1逻辑删除

对于重要数据，都不会使用物理删除（直接delete），而是采用的逻辑删除。也就是给表额外添加一个字段（如is_valid，1表示正常，0表示已删除），那么在应用中执行查询时也是查询正常的数据，因此给此字段添加索引（推荐单列索引），从而来提高查询的速度。

推荐单列索引而不是复合索引的原因是，在查询时，若事先无法预估有其他的查询条件，则不太好创建复合索引。若已明确查询条件时，可使用复合索引，需要遵循最左侧原则，还需要防止索引失效（如因like '%val%'）。

4.2排序

大多情况下都会需要排序，见的最多的就是根据数据的更新时间做降序。对于排序来说，也是一件非常耗时的事情，那么可对此字段添加索引（推荐单列索引），来提高排序的速度。

5.索引失效情况

对索引来说，很多情况下都会失效，而且索引也不是越多越好。

1）使用 != 或 <>，not in或not exists，is not null。（in会走索引，但数据量大超过一定范围时也会失效）

2）查询时列两边类型不一致

3）在索引列使用函数或进行运算

4）使用or。当or的前后都是同一字段时会走索引，反之会失效

5）模糊查询like。'%'放在索引字段前面会失效，反之走索引。

6）不遵循最左侧原则。

6.explain关键字

explain用来查看所执行的查询语句是否使用了索引，以及索引的使用情况。下面还是以上述的用户表进行说明

1）查询全表

explain select * from user;

执行的过程如下，主要看的就是图中圈出来的地方，通过key和key_len判断是否命中了索引

type：sql连接的类型，主要有下面表的几种，表详见Extra后。

possible_keys：可能使用到的索引

key：当前sql实际使用的索引

key_len：索引占用的大小

Extra：额外的建议。其中分为两种，如下表

Extra	含义
Using where; Using Index	查找使用了索引，需要的数据都在索引列中能找到，不需要回表
Using index condition	查找使用了索引，但是需要回表查询数据

type类型分类表

类型	说明(性能从上到下逐渐变差)
system	查询系统中的表，一般不会使用
const	根据主键查询
eq_ref	主键索引查询或唯一索引查询，数据只有一条
ref	索引查询，数据可能有多条
range	范围查询
index	索引树扫描
all	全盘扫描

因此，在开发中需要合理的使用索引，查询效率就会高很多。那么下面看几张使用索引的情况，就可以很好的理解其含义了：

2）根据主键查询

explain select * from user where id=1;

结果如下

3）根据唯一索引查询

explain select id_card from user where id_card=1;

结果如下

4）根据复合索引查询

explain select * from user where username='a' and password='B';
explain select id,username,password from user where username='a' and password='B';

结果分別如下（可以看出，查询所有列数据和只查询索引列数据，额外的建议是不同的，因为索引列的数据可以在索引中就直接查询，不需要回表，而查询所有列数据是需要根据主键回表。下图查询所有列的截图有问题，暂未发现问题的根源todo）

7.MySQL的超大分页要怎么处理？

所谓的超大分页就是数据有百万级别的，当使用limit越往后查询效率就越低。

下面使用案例进行说明，使用函数给上述的用户表添加100万条数据

CREATE DEFINER=`root`@`localhost` FUNCTION `ins_data`( `num` INT ) RETURNS int
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num DO
     INSERT INTO `user` ( `username`, `china_name`, `password`, `id_card`, `phone`)
        VALUES
            (
                concat( 'zhangsan', i ),
                concat( '张三', i ),
                '000',concat(FLOOR( rand()*( 999999999-100000000 )+ 100000000 ),i),
                CONCAT('18',FLOOR( rand()*( 999999999-100000000 )+ 100000000 ))
            );
    
    SET i = i + 1;
    
END WHILE;
RETURN i;

END

然后调用此函数

select ins_data(1000000)

然后分别查询1~100条和第990001~990100条的数据，使用常规方式效果如下(由于查询的结果很多，这里截取了两个sql已经执行的时间)：

由此可以看出，这种情况下当数据量越大时，后续的分页查询就越耗时，原因在于MySQL在查询时需要对990100条数据进行排序，然后返回990000~990100这100条数据，把其他的数据做丢弃，导致查询排序的代价非常大。那么如何去解决这个问题呢？

解决方案：覆盖索引+子查询

select * from user b,(select id from user order by id limit 990001,10) a where a.id=b.id

在子查询中只查id，走的覆盖索引，然后再和外表进行等价查询，下面的执行时间显著降低了。

posted @ 2021-05-10 09:13 钟小嘿阅读(97) 评论(0) 编辑收藏举报

刷新页面返回顶部

笨兮兮