Mysql优化(二)-索引

一、什么是索引

​ Mysql官方对索引的定义为:索引是帮助Mysql高效获取数据的数据结构,简单来说索引就是数据结构,且是一种可以高效快速查询的数据结构。 在数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。索引本身也比较大,因此索引往往以索引文件的形式存储在磁盘中。mysql目前提供了以下四种索引

  • BTREE 索引 : 最常见的索引类型,大部分索引都支持 B 树索引。
  • HASH 索引:只有Memory引擎支持 , 使用场景简单 。
  • R-tree 索引(空间索引):空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。
  • Full-text (全文索引) :全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引,InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

我们平常所说的索引,如果没有特别指明,都是指B+树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引,统称为 索引。

补充:

​ 还有一种索引叫做函数索引,mysql8.0.13以上支持,对于mysql7我们可以通过以下两种方式实现函数索引的功能

  • 前缀索引:即对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符进行索引,这个特性可以大大缩小索引文件的大小,但前缀索引也有缺点,在排序order by和分组group by 操作的时候无法使用

    -- 对blog表的title字段的前10字符进行索引
    create index index_title on blog(title(10));
    
  • 虚拟列索引:在MySQL5.7之后可以通过虚拟列索引的方式来实现函数索引的方式,给表中增加一个虚拟列,再给这个虚拟列创建一个索引。

    下面的sql没有测试,是在书上抄的列子

    -- 下面创建虚拟索引的语句没有测试,是在书上抄的例子
    -- 这条查询语句不会走索引
    select * from salaries where round(salary/1000) < 10
    -- 给salaries表增加一个虚拟列
    alter table salaries add column salary_by_1k int generated always as (round(salary/1000));
    -- 给虚拟列创建索引
    alter table salaries add key index_salary_by_1k(salary_by_1k);
    

二、索引的优缺点

  1. 优势

    • 索引大大减小了服务器需要扫描的数据量,提高了数据检索的效率,降低数据库的IO成本。
    • 可以通过索引对数据列排序,降低了数据排序的成本。
  2. 劣势

    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
    • 建立索引会占用磁盘空间的索引文件。如果你在一个大表上创建了多种组合索引,索引文件会占用很大的存储空间。
    • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
    • 对于非常小的表,大部分情况下简单的全表扫描更高效

因此在经常查询和排序的数据列建立索引最合适,而表记录太少或、经常增删改的表或者字段、where条件里用不到的字段以及过滤性比较差的字段(例如性别)就不适合建立索引。MySQL里同一个数据表里的索引总数限制为16个。*

三、B-树和B+树

​ Mysql的索引主要使用的是B+树,谈到所以就必需要谈到B树和B+树,这里只做简单的介绍。

  1. B-树

    B-tree树即B树,B即Balanced,平衡的意思。有人把B-tree翻译成B-树,容易让人产生误解。会以为B-树是一种树,而B树又是另一种树。实际上,B-tree就是指的B树(2-3树是最简单的B树,2-3树的所有叶子节点都在同一层.(只要是B树都满足这个条件)有两个子节点的节点叫二节点,二节点要么没有子节点,要么有两个子节点。有三个子节点的节点叫三节点,三节点要么没有子节点,要么有三个子节点.)

    • B树的阶:节点的最多子节点个数。比如2-3树的阶是3,2-3-4树的阶是4。

    • B-树的搜索,从根结点开始,对结点内的关键字(有序)序列进行二分查找,如果命中则结束,否则进入查询关键字所属范围的儿子结点;重复,直到所对应的儿子指针为空,或已经是叶子结点

    • 关键字集合分布在整颗树中, 即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子结点结束。

    • 其搜索性能等价于在关键字全集内做一次二分查找

    • B-树所有的叶子节点都在同一层。

    image-20200924105240866

    一颗B-树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)。如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO。

  2. B+树

    image-20200924105305613

    • B+树的搜索与B树也基本相同,区别是B+树只有达到叶子结点才命中(B树可以在非叶子结点命中),其性能也等价于在关键字全集做一次二分查找。
    • 所有关键字都出现在叶子结点的链表中(即数据只能在叶子节点【也叫稠密索引】),且链表中的关键字(数据)恰好是有序的,
      不可能在非叶子结点命中。
    • 非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层,更适合文件索引系统。
  3. B+树为什么适 合做索引

    • B+树的内部结点并没有指向关键字具体信息的指针(数据并没有防砸非叶子结点)。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
    • 由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

四、索引的分类

  1. 单值索引

    ​ 一个索引只包含单个列,一个表中可以有多个单列索引。

    • 主键索引:为表设定主键后,数据库会自动的建立索引,Innodb为聚簇索引。
    • 普通索引::MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
  2. 组合索引

    ​ 即一个索引包含多个列,在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合,例如,这里由id、name和age3个字段构成的索引,索引行中就按id/name/age的顺序存放,索引可以索引下面字段组合(id,name,age)、(id,name)或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age或者(name,age)组合就不会使用索引查询

  3. 唯一索引

    索引列的值必须是唯一的,但允许有空值。

五、索引设计的原则

  • 对查询频次较高,且数据量比较大的表建立索引。
  • 最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
  • 使用唯一索引,区分度越高,使用索引的效率越高。
  • 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
  • 利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。

六、索引的操作

6.1创建索引

-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建普通复合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

-- 使用Alter命令创建索引
-- 该语句添加一个主键,所自动添加主键索引,也意味着这和列不能为空且唯一
ATTER TABLE table_name ADD PRIMARY KEY(col_name); 

-- 添加普通索引,索引值可出现多次
ALTER TABLE table_name ADD INDEX index_name(col_name1,col_name2);

-- 添加唯一索引,这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE  table_name ADD UNIQUE index_name (col_name);

-- 全文索引(需要通过FULLTEXT关键字)
ALTER TABLE table_name ADD FULLTEXT index_name(col_name);

-- 删除索引
DROP INDEX idx_customer_name  on customer;
ALTER TABLE table_name DROP INDEX index_name;

-- 查看索引
SHOW INDEX FROM table_name\G

6.2、使用索引

建表语句

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

6.2.1、mysql中使用索引的典型场景

现在tb_seller表上有四个索引,sellerid是主键索引,下面三个是一个组合索引,组合的顺序是name最左,status中间,addr最右。

image-20200924144832253

  1. 全值匹配 ,对索引中所有列都指定具体值。该情况下,索引生效,执行效率高。

    where条件的各个字段都是索引,查询效率很好

image-20200924145010867

  1. 最左前缀法则

    image-20200924145957408

    where中只使用name作为条件,使用到了索引

    image-20200924145443522

    image-20200924145516421

    where中三个条件用and连接时,无论怎么那个条件在前那个条件在后,三个索引都用到,因为mysql会自动优化。不会违反最左前缀法则。

    image-20200924150808894

    这里我们没有使用name作为查询条件,即使status和address存在索引,也没有使用索引,这是因为name作为最左边的索引没有使用name条件则违反了最左前缀法则,索引会失效

    image-20200924150157876

    where中使用了左边的name和status作为查询条件,使用到了索引,索引长度为410。

    image-20200924150410632

    where中使用name和addres作为查询条件,我们发现虽然使用到了索引,但是却只用到了name索引,没有用到address索引,这是因为如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:status作为中间的索引,他没有用到,则status后面的索引都不会用到。

    image-20200924150925161

    where后边使用到了三个条件但是我们发现只用到了右边两个索引,是因为,范围查询右边的列,不能使用索引 :status使用了>符号,status右边的列不能使用索引。

    注意:这里索引的左右先后顺序不能看where条件后面使用的先后,而是要看我们创建索引时哪个字段在前,那个字段在后

还是直接说说索引失效吧。。

6.2.2、mysql中索引失效的问题

  1. 违反最左前缀索引会失效

    image-20200924150808894

    name是最左边的索引,没有使用name,索引失效

  2. 如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:

    image-20200924150410632

    中间跳过了status索引,所以只有左侧索引生效

  3. 范围查询右边的列,不能使用索引 。

    image-20200924153742239

    status使用的范围查询,右边的索引失效

  4. 不要在索引列上进行运算操作, 索引将失效。

    image-20200924154610448

    虽然name列有索引但是对name进行了字符串截取操作导致索引失效

  5. 字符串不加单引号索引会失效

    image-20200924154136592

    image-20200924154205875

    status是字符串必需要加上单引号,如果不加索引会失效,因为mysql会进行自动类型转换相当于对列进行了运算

  6. 用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。

image-20200924160152400

因为createtime没有索引,那么后面的查询肯定要走全表扫描,在存在全表扫描的情况下,就没必要多一次索引扫描增加I/O访问,所以索引会失效。

  1. 以通配符(% ,_)开头的Like模糊查询索引会失效

    如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。

image-20200924160602548

image-20200924160602548

对于like模糊匹配索引失效问题,我所了解到的有三种解决方案

  • 使用覆盖索引,select 后面查询的字段都有索引

    image-20200924160835955

    但是select后面的字段有一个没有索引,则这么情况下模糊匹配不走覆盖索引

    image-20200924160944050

  • 首先扫描二级索引name获得满足模糊匹配条件的主键的列表,在通过主键回表检索所有的字段。

    image-20200924162217627

    可以看到子查询走的是覆盖索引查到主键,通过主键索引查询所需要的所有列。

  • 通过全文索引

  1. 如果mysql估计使用索引比不使用索引慢,就不会使用索引,特别是在表的数据少的情况下。另外<>,!=,in ,not in,is null,is not null这些可能会走索引,也可能不走索引。

    网上有很多人都说 !=,<>,is not null,这些不会走索引,根据我所查的资料发现,好像这种观点是不正确的,走不走索引是要mysql自己判断使用索引是不是比不使用索引效率高来决定使不使用索引

    image-20200924163016466

    我们查询tb_seller表的所有内容,此时我们通过addres(有索引)作为条件来查询内容

    image-20200924163249627

    image-20200924163349753

    image-20200924163406863

    image-20200924163452323

    如上面四张图我们分别使用address !=‘北京市’,address !=‘西安市’,address=‘西安市’,address=‘北京市’,我们可以发现在使用=的情况下即使

    address有索引也不一定会使用索引例如address=‘北京市’,因为大多地址都是北京市过滤性不好,不如全表扫描。

    在使用!=的情况下也有可能会使用索引,例如address !=‘北京市’,以为只有一个地址不是北京市,过滤性很好,就走索引。

在查询中能使用索引就使用索引,有组合索引就优先使用组合索引,select 后边的列能使用覆盖索引就使用覆盖索引,尽量别写*

6.3、查看索引使用情况

-- 查看本次会话的索引使用情况
show status like 'Handler_read%';	

-- 查看服务器启动以来索引的使用情况
show global status like 'Handler_read%';

image-20200924165545967

  • Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。

  • Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)。

  • Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。

  • Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。

  • Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。

  • Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。(越低越好)

©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页