Mysql优化(二)

Mysql优化(二)

一、什么是索引

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

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.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-树所有的叶子节点都在同一层。

   一颗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+树

   • 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_nameG

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最右。

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

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

2. 最左前缀法则

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

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

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

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

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

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

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

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

6.2.2、mysql中索引失效的问题

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 通过全文索引

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

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

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

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

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

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

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

6.3、查看索引使用情况

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

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

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

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

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

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

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