MySQL——索引优化分析

最新推荐文章于 2024-06-29 09:34:00 发布

福建选手阿俊

最新推荐文章于 2024-06-29 09:34:00 发布

阅读量211

点赞数 2

分类专栏： MySQL学习笔记

本文链接：https://blog.csdn.net/qq_44891295/article/details/104555523

版权

MySQL学习笔记专栏收录该内容

35 篇文章 0 订阅

订阅专栏

为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。加油！！！！

1：SQL性能下降的原因

主要原因：
1：查询语句写的烂
2：索引失效（单值，复合）
3：关联查询太多join ( 设计缺陷或不得已的需求)
4：服务器调优及各个参数设置(缓冲、线程数等)

2：常见通用的Join查询

2.1 SQL执行顺序

首先我们写一条查询语句

SELECT DISTINCT
    < select_ list >
FROM 
     <left_ table> < join _type >
JOIN < right_ table> ON < Joln_condition >
 WHERE
     < where condition >
GROUE BY
      <group by list >
HAVING
     < having _ condition >
ORDER BY
      <order by condition >
  LIMIT <limit number>

mysql读这条语句是按如下顺序执行的（机读）
在这里插入图片描述
如下是SQL执行顺序图解

2.2 七种JOIN理论

在这里插入图片描述
MySQL 的七种 join用法

3：索引简介

MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。（素引的目的在于提高查询效率，可以类比字典，如果要查“mysql"这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能高要从a–Z开始找。如果我想找到Java开头的单词呢?或者Oracle开头的单词呢？
）
你可以简单理解为"排好序的快速查找数据结构”（在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（引用）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引，如下图）
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

为了加快Col2的查找，可以维护一个右边所示的二又查找树，每个节点分别包含索引键值和-一个指向对应数据记录物理地址的指针，这样就可以运用二叉者找在一定的复朵度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的index以外，还有哈希索引等等。

3.1 索引优劣势

索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。桌子越大，花费越多。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。
优势：

类似大学图书馆建书日索引，提高数据检索的效率，降低数据库的I0成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：

实际上索引也是一张表，该表保存了主键与索引字段。并指向实体表的记录，所以索引列也是占用空间的
虽然索引大大提高了查询速度，有时却会降低更新表的速度，如对表进行INSERT, UPDATE 和DELETE. 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需费花时间研究建立最优秀的索引，或优化查询

3.2 索引分类和建索引命令语句

索引分类：

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

建索引命令语句

# 创建：
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 
ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
# 删除：
DROP INDEX [indexName] ON mytable;
# 查看：
SHOW INDEX FROM table_ name

#   使用ALTER命令（四种方式来添加数据表索引）
#  1：该语句添加一个主键，意味着索引值必须是唯一的，且不可以为null
ALTER TABLE b ,name ADD PRIMARY KLY (column list);
# 2：这条语句创建索引的值必须是唯一的（除了null外，null可呢会出现很多次）
 ALTER TABLE tb name ADD UNKQUL index_name ;
# 3：添加普通索引，索引值可呢出现很多次
 ALIER TABLE tb_name ADO INDEX index name (clumnm _list);
 # 4：该语句指定了索引为FULLTEXT，用于全文索引
 АLТЕR ТАВLЕ tb_nаmе АDО FULLТЕХІ іndе. nаmе (соumm_list);

3.3 索引结构和原理

BTree索引
在这里插入图片描述

[初始化介绍]
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黃色所示)，如磁盘块1包含数据项17和35.包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜素方向的数据项，如17. 35并不真实存在于数据表中。
[查找过程]
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次I0,在内存中用二分查找确定29在17和35之间，锁定磁盘换1的P2指针，内存时间因为非常短(相比磁盘的IO)可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次I0，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次I0，同时内存中做二分查找找到29，结束查询，总计三次IO.

真实的情况是，3层的b+树可以表示上百万的效据，如果上百万的数据查我只需要三选I0，性能提高将是巨大的，如果没有索到增每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

3.4 是否要建索引的情况

以下情况要建索引

  1.主键自动建立唯一索引
  2.频繁作为查询条件的字段应该创建索引
  3.查询中与其它表关联的字段，外键关系建立索引
  4.频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
  5.Where条件里用不到的字段不创建索引
  6.单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
  7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  8.查询中统计或者分组字段

以下情况不要建索引

 1.表记录太少
 2.经常增删改的表
 3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引.
 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
 （    假如一个表有10万行记录， 有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一.般不会提高数据库的查询速度。
 索引的选择性是指索引列中不同值的数目与表中记录数的比。
 如果一个表中有2000条记录，表索引列有1980个宁不同的值，那么这个索引的选择性就是1980/2000=0. 99。  一个索引的选择性越接近于1,这个索引的效率就越高。
）

4：性能分析

4.1 MySql Query Optimizer

在这里插入图片描述
1 ：Mysq|中有专门负责优化SELECT语句的优化器模块（Optimizer），主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
2：当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

4.2 MySQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top,free, iostat和vmstat来查看系统的性能状态

4.3 explain使用简介

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

4.3.1 explain可以干嘛

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

4.3.2 explain怎么玩

explain+SQL语句

例如：
在这里插入图片描述

4.3.2.1 id介绍（表的读取顺序）

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

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

举例：在数据库中创建三张表
在这里插入图片描述用explain+SQL语句查看id的三种情况

这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。

这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。
在这里插入图片描述
这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。

4.3.2.2 select_type（数据读取操作的操作类型）

在这里插入图片描述

SIMPLE ：简单的select查询,查询中不包含子查询或者UNION
PRIMARY ：查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED (衍生)，MySQL 会递归执行这些子查询，把结果放在临时表里。
若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DERIVED
6：UNION RESULT ：从UNION表获取结果的SELECT

在这里插入图片描述

4.3.2.3 type

type显示查询使用了何种类型。(较为重要的一个指标）
从最好到最差依次是：

System>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好可以达到ref

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

一般来说，得保证查询至少达到range级别，最好能达到ref.
简单地说是const是直接按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询。

在这里插入图片描述

4.3.2.3 possible_keys和key（哪些索引可以使用和哪些索引被实际使用）

1：possible_ keys ：显示可 能应用在这张表中的索引，一个或多个。
查询涉及到的字段 上若存在索引，则该索引将被列出，但不一定 被查询实际使用
2：key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

在这里插入图片描述

4.3.2.3 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_ len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_ len是根据表定义计算而得，不是通过表内检索出的

4.3.2.4 ref

显示索引的那一列被使用了，如果可以的话，是一个常数（const），那些列或常量被用于查找索引列上的值

4.3.2.5 rows（每张表有多少行被优化器查询）

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

4.3.2.5 Extra

包含不适合在其他列中显示，但又十分重要的信息

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询grqup by。
Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
覆盖索引含义：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可seleat *,因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

5：索引优化案例和索引失效情况

这一章小编写不来，就看看别人的例子把，加油，自己乱写的在这
索引失效的10种情况

可以参考索引优化中的例子，特别好
参考文章索引失效的七种情况

福建选手阿俊

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL——索引优化分析

文章目录1：SQL性能下降的原因2：常见通用的Join查询2.1 SQL执行顺序2.2 七种JOIN理论3：索引简介1：SQL性能下降的原因主要原因：1：查询语句写的烂2：索引失效3：关联查询太多join ( 设计缺陷或不得已的需求)4：服务器调优及各个参数设置(缓冲、线程数等)2：常见通用的Join查询2.1 SQL执行顺序首先我们写一条查询语句SELECT DISTINCT...
复制链接

扫一扫