【数据库的三大特性】——数据库中索引优化的基础知识（索引覆盖、索引失效）

在这里插入图片描述

MySQL索引

概述

性能下降，SQL慢，执行时间长，等待时间长

有以下原因：1. 查询语句写的烂；2. 索引失效；3. 关联查询太多join（设计缺陷或不得已的需求）；4. 服务器调优及各个参数设置（缓冲、线程数等）

参考链接：一条sql语句执行很慢的原因有哪些？

索引分两种：

单值索引
select * from user where name = ’ ';
#建立单值索引
create index idx_user_name on user(name)——建立了索引会在后台给你进行排序，操作会快很多

复合索引
select * from user where name = ’ ’ and email = ’ ’ ;
#建立单值索引
create index idx_user_nameEmail on user(name,email)

唯一索引

常见的join查询——两个表查询

SQL执行顺序

在sql中，你写的sql的执行顺序和机器读到的顺序是不一样的

手写的顺序是：

在这里插入图片描述

机读的顺序是：

在这里插入图片描述

SQL解析：

在这里插入图片描述

Join图

在这里插入图片描述

七种join

七种Join
- 内连接：inner join
  
  select A.*,B.* from A inner join B on a.id = b.id
- 左连接：left join
  
  共有部分+右表没有匹配补NULL
  
  select A.*,B.* from A left join B on a.id = b.id
  
  左表独有
  
  select A.*,B.* from A left join B on a.id = b.id where b.id is null
- 右连接：right join
  
  共有部分+左表没有匹配补NULL
  
  select A.*,B.* from A right join B on a.id = b.id
  
  右表独有
  
  select A.*,B.* from A right join B on a.id = b.id where a.id is null
- 全连接：union
  
  合并去重(full outer join)
  
  select A.*,B.* from A left join B on a.id = b.id union
  select A.*,B.* from A right join B on a.id = b.id
  
  左右表独有
  
  select A.*,B.* from A left join B on a.id = b.id where b.id is null
  union
  select A.*,B.* from A right join B on a.id = b.id
  where a.id is null

索引简介

什么是索引

定义：

索引是帮助数据库高效获取数据的数据结构
目的在于提高查询效率, 可类比字典

索引是帮助MySQL高效获取数据的数据结构——本质是数据结构，理解为“排好序的快速查找数据结构”——结论 B树索引

详情（重要）
结论 B树索引

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

我们平常说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。**其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）**等

详细数据库索引参考：

数据库索引——B+树索引（为什么使用B+树作为MySql的索引结构，用什么好处？）

优势和劣势

优势：

提高数据检索效率, 降低数据库IO成本
通过索引列对数据进行排序, 降低数据排序成本, 降低CPU消耗

劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是占用空间的
索引提高了查询效率, 但同时也会降低更新表的速度,如 insert , update , delete. 数据库更新表格除了需要更新数据以外还需要更新索引信息
索引知识提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

mysql索引分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引

索引列的值必须唯一，但允许有空值
复合索引

即一个索引包含多个列
复合索引产生覆盖索引

覆盖索引：查询的列刚好与创建的索引列的列名及顺序全部匹配或者部分匹配

SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。
- 聚集索引
  
  聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。
- 辅助索引
  
  辅助索引，也叫非聚集索引。和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。
- 覆盖索引
  
  InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
  
  使用覆盖索引有啥好处？
  - 可以减少大量的IO操作
  - 有助于统计
- 联合索引
  
  联合索引是指对表上的多个列进行索引。
具体参考：聚集索引、辅助索引、覆盖索引、联合索引

mysql索引结构

BTree索引

数据库索引——B+树索引（为什么使用B+树作为MySql的索引结构，用什么好处？）

Hash索引

Full-text全文索引

R-Tree索引

什么情况需要建立索引，什么情况不要建立索引

哪些情况需要创建索引
1. 主键主动建立唯一索引

频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引，加重IO负担
5. where条件里用不到的字段不创建索引
单键/组合索引的选择问题，who？(高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或分组字段

哪些情况不要创建索引
- 1. 表记录太多
- 1. 经常增删改的表
  - Why：提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update、delete，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件
- 1. 数据重复却分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
    注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析

主要通过explain进行索引的分析，下面主要讲explain是什么，怎么用，还有各字段的解释。

（1）explain是什么

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或者表结构的性能瓶颈

（2）explain能干嘛

表的读取顺序
数据读取操作的操作类型
哪些索引可以实际使用
表之间的引用
每张表有多少行被优化器查询

（3）怎么玩？

explain+ SQL语句可以查看执行计划包含的信息

（4）explain各字段解释

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

分为三种情况：
- id相同——执行顺序从上到下
- id不同——如果是子查询，ID的序号会递增，ID值越大优先级越高，越先被执行
- id相同不同——序号大的先执行，序号相同的顺序执行（衍生 = DERIVED）
select_type

有哪些类型：simple、primary、subQuery、derived、union、union result
- 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
  1. SIMPLE
    - 简单的select查询，查询中不包含子查询或者UNION
  2. PRIMAARY
    - 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
  3. SUBQUERY
    - 在select或where列表中包含了子查询
  4. DERIVED
    - 在from列表中包含的子查询被标记为derived（衍生） MySQL会递归执行这些子查询，把结果放在临时表里
  5. UNION
    - 若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
  6. UNOIN RESULT
    - 从union表获取结果的select
table

显示这一行的数据是关于哪张表的
type——访问类型排列

性能从高到低：
system>const>eq_ref>ref>range>index>ALL
- system
  
  表只有一行记录（等于系统表），这就是const类型的特例，平时不会出现，这个可以忽略不计
- const
  
  表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
- eq_ref
  
  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  
  一个公司的CEO，查找是只查找出一个
- ref
  
  非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
  
  查找一个公司的程序员，能找出很多个
- range
  
  只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询
  这种范围扫描索引扫描比全表扫描要好，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
  
  范围内的查询，不用全表扫描
- index
  
  Full Index Scan，index与ALL区别为index类型值遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
  （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- all
  
  Full Table Scan，将遍历全表以找到匹配的行
possible_keys（理论上要用的索引）

显示可能应用在这张表中的索引，一个或多个

查询涉及到字段上若存在，则该索引将被列出，但不一定被查询实际使用
key （实际上要用的索引）
是否使用到了索引，是否索引失效
在多个索引竞争的前提下，MySQL用到了哪个索引

查询中若使用了索引覆盖，则该索引仅出现在key列表中（using index）
key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度性的情况下，长度越短越好

key_len显示的值为索引字段的最大可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

越小越好——每张表多少行被优化器查询

extra

- Using filesort

	  表示mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
	  mysql无法利用索引完成的排序操作称为文件排序。
	  出现该情况时表示查询性能已经很差

	- Using temporary

	  使用列临时表来保存中间结果，常见与排序 order by 及分组 group by 查询.
	  性能最差，需要优化
	  
	  可能会创建临时表文件，当百万级数据进行查找时，临时文件大，申请内存多，搬家也慢，释放内存也慢

	- Using index
		（这个与using where 同时出现了，覆盖索引，性能增加了，好）
	  表示相应的查询使用到了覆盖索引（Convering index）,避免访问表的数据行，效率不错。
	  如果同时出现using where , 表明索引被用来执行索引键值的查找；
	  如果没有同时出现using where，表明索引用来读取数据而非查询动作
		
		注意：如果要用覆盖索引，一定要注意select列表中只取出需要的列，不可select *	
			因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降.
			但是如果用了指定字段进行查找，性能提升

	- using where
		表明使用了where过滤
	- using join buffer
		
	- impossible where

	  如  where name='jim' and name ='jack'

	- select table optimized away
		在没有groupby子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
	- distinct
		优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引优化

在这里插入图片描述

上图是针对单表、两表、三表的索引分析

索引优化主要是针对索引失效来优化sql语句的，主要从以下几个方面进行优化：

在这里插入图片描述

参考：MySQL的索引失效

一般性建议

对于单值索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，尽量选择可以能够包含当前query中的where中的where字句中更多字段的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

posted @ 2021-05-13 21:59 your_棒棒糖阅读(187) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

your_棒棒糖

程序员的本质就是不断地学习，一直更新自己！！

【数据库的三大特性】——数据库中索引优化的基础知识（索引覆盖、索引失效）

MySQL索引

概述

性能下降，SQL慢，执行时间长，等待时间长

常见的join查询——两个表查询

SQL执行顺序

七种join

索引简介

什么是索引

优势和劣势

mysql索引分类

mysql索引结构

BTree索引

Hash索引

Full-text全文索引

R-Tree索引

什么情况需要建立索引，什么情况不要建立索引

性能分析

索引优化

公告

your_棒棒糖

程序员的本质就是不断地学习，一直更新自己！！

【数据库的三大特性】——数据库中 索引优化 的基础知识（索引覆盖、索引失效）

MySQL索引

概述

性能下降，SQL慢，执行时间长，等待时间长

常见的join查询——两个表查询

SQL执行顺序

七种join

索引简介

什么是索引

优势和劣势

mysql索引分类

mysql索引结构

BTree索引

Hash索引

Full-text全文索引

R-Tree索引

什么情况需要建立索引，什么情况不要建立索引

性能分析

索引优化

公告

【数据库的三大特性】——数据库中索引优化的基础知识（索引覆盖、索引失效）