mysql——索引优化分析

男娘i 2021-11-09 04:24 420阅读 0赞

# 一、索引是什么 #

*  在数据之外，**数据库系统还维护着满足特定查找算法的数据结构**，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。即可以简单的理解为“**索引是排好序的快速查找的数据结构**”。
 *  一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
 *  我们平常所说的索引，如果没有特别说明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。

#  #

# 二、索引的优势和劣势 #

1、优势：

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

2、劣势

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

# 三、SQL索引分类及每种分类的语法 #

## 1、分类 ##

### （1）单值索引 ###

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

### （2）、唯一索引 ###

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

### （3）、复合索引 ###

一个索引包含多个列

## 2、基本语法 ##

### （1）、查看索引 ###

#查看索引
    mysql>show index from table_name;

### （2）、创建索引 ###

##############################添加数据表索引的方式：alter##############################
    #主键索引：意味着索引值必须唯一，且不能为null
    mysql>alter table table_name add primary key(column_list);
    
    #唯一索引：这条语句创建的索引值是唯一的，但允许有null，null可能出现多次
    mysql>alter table table_name unique index_name(column_list);
    
    #普通索引：索引值可重复
    mysql>alter table table_name add index index_name(column_list);
    
    #全文索引：指定索引为fulltext
    mysql>alter table table_name add FULLTEXT index_name(column_list);
    
    
    ##############################添加数据表索引的方式：create##############################
    mysql>create index index_name on table_name(column_list);

### （3）、删除索引 ###

#删除索引
    drop index index_name on table_name;

**PS：一般一张表的索引最多不要超过5个**  
  
  
\*\*

# 四、mysql索引结构 #

## 1、BTree索引 ##

![image.png][]  
图说明：磁盘块（浅蓝色）、数据项（深蓝色）、指针（黄色）、

【初始化】

*  一颗B+树，可以看到每个磁盘块包含几个数据项和指针，如磁盘块1包含数据项17和35，包含指针p1（表示小于17的磁盘块） p2（表示17和35之间的磁盘块） p3（表示大于35的磁盘块）
 *  真实数据存在叶子节点
 *  非叶子节点存储指引搜索方向的数据项

【查找过程】  
如果要查找数据项28，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定28在17和35之间，锁定磁盘块1的p2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，28在26和30之间，锁定磁盘块3的P2指针，通过指针加载到磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到28，结束查询，总计三次IO

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

## 2、Hash索引 ##

## 3、full-text全文索引 ##

## 4、R-Tree索引 ##

# 五、索引适用场景 #

1、需要创建索引的情况

*  主键自动建立唯一索引
 *  频繁作为查询条件的字段应该创建索引
 *  查询中与其它表关联的字段，外键关系建立索引
 *  单键/组合索引的选择问题？高并发下倾向创建组合索引
 *  查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
 *  查询中统计或者分组字

2、不用创建索引的情况

*  表记录太少
 *  经常增删改的表
 *  数据重复且分布平均平均的表字段，因此应该只为最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没太大的实际效果
 *  频繁更新的字段不适合创建索引
 *  where条件里用不到的字段不创建索引

3、举例  
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度  
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

PS：提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update、delete，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，所以使用索引要分场合。

# 六、性能分析 #

## 1、优化器（Mysql Query Optimizer） ##

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

## 2、mysql常见瓶颈 ##

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

## 3、Explain ##

### （1）、是什么？——查看执行计划 ###

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

### （2）、能干嘛？ ###

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

### （3）、怎么用？ ###

mysql>explain sql语句

解释执行计划包含的信息  
![image.png][image.png 1]  
**①id**  
表示select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，分为三种情况：

<table> 
 <thead> 
  <tr> 
   <th>id相同</th> 
   <th>执行顺序由上至下</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id不同</td> 
   <td>如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行</td> 
  </tr> 
  <tr> 
   <td>id相同不同，同时存在</td> 
   <td>id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行</td> 
  </tr> 
 </tbody> 
</table>

**②select\_type——查询类型**

<table> 
 <thead> 
  <tr> 
   <th>查询类型</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单的select查询，查询中不包含子查询或者UNION</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>在select或where列表中包含了子查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>衍生，在FROM列表中包含的子查询被标记为DERIVED，mysql会递归执行这些子查询，把结果放在临时表里</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在from子句的子查询中，外层select将被标记为DERIVED</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION表获取结果的select，两个表union后的结果集</td> 
  </tr> 
 </tbody> 
</table>

**③table**  
显示这一行的数据是关于哪张表的  
**④type——访问类型**  
显示查询使用了哪种类型，从最好到最差：system>const>eq\_ref>ref>fulltest>ref\_or\_null>index\_merge>unique\_subquery>range>index>all  
**一般来说，得保证查询至少达到range级别，最好能达到ref**

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

**⑤possible\_keys**  
显示可能应用在这张表中的索引，一个多多个  
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用  
**⑥key**  
实际使用的索引，如果为null，则没有使用索引  
查询中若使用了**覆盖索引（解决like查询问题，查询字段和建的符合索引个数顺序吻合）**，则该索引仅出现在key列表中  
**⑦key\_len**  
索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好  
key\_len显示的值为索引字段的最大可能长度，**并非实际使用长度**，即它是根据表定义计算而得，不是通过表内检索出的  
**⑧ref**  
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的  
**⑨rows**  
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数  
**⑩Extra**  
包含不适合在其他列中显示但十分重要的额外信息

<table> 
 <thead> 
  <tr> 
   <th>Using filesort</th> 
   <th>说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。<br>mysql中无法利用索引完成的排序操作称为“文件排序”</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using temporary</td> 
   <td>使用临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by</td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。<br>如果同时出现using where，表明索引被用来执行索引键值的查找；<br>如果没有同时出现using where，表明索引用来读取数据而非执行查找动作</td> 
  </tr> 
  <tr> 
   <td>Using where</td> 
   <td>使用了where过滤</td> 
  </tr> 
  <tr> 
   <td>Using join buffer</td> 
   <td>使用了连接存储</td> 
  </tr> 
  <tr> 
   <td>impossible where</td> 
   <td>where子句的值总是false，不能用来获取任何元组</td> 
  </tr> 
  <tr> 
   <td>select tables optimized away</td> 
   <td>在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化</td> 
  </tr> 
  <tr> 
   <td>distinct</td> 
   <td>优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作</td> 
  </tr> 
 </tbody> 
</table>

# 七、索引优化 #

## 1、索引分析 ##

### （1）、join语句的优化： ###

*  尽可能减少join语句中的nestedloop的循环总次数；“永远用小结果集驱动大的结果集”；
 *  优先优化nestedloop的内层循环；
 *  保证join语句中被驱动表上join条件字段已经被索引；当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置；
 *  mysql无法利用索引对range类型查询字段后面的字段进行检索，也就是range类型查询字段后面的索引无效
 *  left join条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引
 *  right join条件用于确定如何从左边表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引

### （2）、索引失效：建了没用 ###

失效场景：

*  全值匹配
 *  最佳左前缀法则：**查询从索引的最左前列开始并且不跳过索引中的列**
 *  不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表
 *  存储引擎不能使用索引中范围条件右边的列
 *  尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select \*
 *  mysql在使用不等于的时候无法使用索引，会导致全表扫描
 *  is null，is not null也无法使用索引
 *  like以通配符开头（’%abc…"）mysql索引失效会变成全表扫描的操作
    
    \*\* 解决：like百分号写右边，两个百分号就使用覆盖索引\*\*
 *  字符串不加单引号索引会失效
 *  少用or，用or连接时会索引失效

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

案例：  
![image.png][image.png 2]

PS：定值、范围还是排序，一般order by是给个范围；group by基本上都需要进行排序，会有临时表产生

### （3）、优化口诀 ###

**全值匹配我最爱，最左前缀要遵守；**  
**带头大哥不能死，中间兄弟不能断；**  
**索引列上少计算，范围之后全失效；**  
*like百分写最右，覆盖索引不写；*\*  
**不等空值还有or，索引失效要少用；**  
**var引号不能丢，SQL优化并不难。**

#  #

# 八、覆盖索引 #

*  理解1：
    
        select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
 *  理解2：
    
        索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫覆盖索引

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

[image.png]: /images/20211109/5a79807a534d4c07ae50faf87f9d4e4a.png
[image.png 1]: /images/20211109/4cc68315ea9748a8b82ac108a642bbf6.png
[image.png 2]: /images/20211109/d8fa2c00caed4d72b8b8edcf370beedb.png