SQL优化+索引分类+查询分析

待我称王封你为后i 2023-10-08 05:12 77阅读 0赞

### 1，索引的建立和使用 ###

大数据量时，建立各种各样的索引可以提高查询。然而索引数量过多又会影响增删改的效率，并且也会占用更多额外的空间去存储索引，因此我们要懂得怎么去正确地建立索引，避免索引滥用。

#### 1.1 索引建立规则 ####

1，索引不要包含选择性过低字段（例如逻辑删除字段）
    2，选择性高的字段前置或者单独建立索引
    	假设选择性col_c>col_b>col_a，抛开业务本身需要，组合索引建立的顺序尽可能建为index(col_c,col_b,col_a)。
    3，尽量使用覆盖索引（在经过索引过滤后数据量依旧很大的情况下可以考虑通过覆盖索引优化。）
    	SELECT sum(col_c) FROM my_table where col_a=1 and col_b=1
    	如果col_a和col_b过滤完后还有大量数据，那么建议建一个 index(col_a,col_b , col_c) 索引，
    	否则MySQL需要通过大量回表去查询col_c的数据再去求和。
    4，具有唯一性或者高选择性的字段无需与其他字段建立组合索引。

#### 1.2 合理使用索引 ####

1，最左匹配截断
    	index(col_a,col_b)
    	组合索引的匹配规则是从左往右匹配，无论是作为过滤条件还是排序条件都要遵循这个原则。
    	如果要使用col_b字段走索引，查询条件则必须要携带col_a字段。
    	col_b作为排序字段如果走索引，只要保证组合索引中col_b前面字段都包含在过滤条件或排序条件中即可，
    	不需要保证col_b作为组合索引中的最后一个字段。
    	select * from my_table order by col_a,col_b
    	col_a和col_b都可以走索引。
    2，隐式转换
    	字段类型和查询数据的值类型不一致，会导致字段上的索引失效。
    3，in + order by 导致索引失效
    4，范围查询阻断组合索引
    5，后缀匹配不能走索引（'%name%'）
    6，or查询导致组合索引失效
    7，不等于、不包含索引失效
    8，查询字段选择性过低，mysql放弃索引直接走全表
    9，desc 和asc混用时会导致索引失效，不建议混用。

#### 1.3 索引分类 ####

按存储结构来分
        哈希索引
        	memory引擎的，无序存储，范围查询不支持,等值查询效率高，联合索引支持不好
        Btree索引（B+tree或B-tree) 
        	 InnoDB和MylSAM存储引擎，都会默认采用B+树索引
        full-index全文索引
        	MyISAM的一个特殊索引类型，innodb的5.6以上版本也支持，
        	它查找的是文本中的关键词主要用于全文检索。
        B+索引特点
        	1，和hash比支持范围查询，更少的I/O消耗，利用磁盘预读原理，树的一个节点大小设置为等于1页，每个节点一次I/O即可全部导入
        	2，和B树相比I/O效率更高。B+树的内部结点有指向关键字具体信息的指针而不保存具体信息，一次性可读入更多关键字。
    		3、B+树的查询效率更加稳定.任何关键字的查找必须走一条从根结点到叶子结点的路。关键字查询的路径大致相同。
    		4，和B树相比更好的支持范围查询。b+树叶子节点之间有指针连接，首尾遍历叶子节点链表即可，B树需要重复中序遍历
    		5，不使用红黑树，红黑树是平和二叉树，一次读入数据少，更适合内存级别查找
    
    按应用层次上来划分
        主键索引，索引列的值必须唯一，没有空值，比如数据库表中id自增列
        唯一索引，索引列的值必须唯一，但允许有空值
        普通索引，即一个索引只包含单个列，一个表可以有多个单列索引
        复合索引，索引包含多个列
        
    按表记录的排列顺序和索引的排列顺序是否一致来划分
        聚集索引：
        	表记录的排列顺序和索引的排列顺序一致（以主键创建的索引）
        	在叶子节点存储的是表中的数据
        非聚集索引：
        	表记录的排列顺序和索引的排列顺序不一致
        	以非主键创建的索引（也叫做二级索引）
        	在叶子节点存储的是索引列和主键（查询不包含索引的列会回表）

### 2， 编写高效sql ###

1，避免深分页（深分页，会导致大量的无效回表（前1000条进行了回表，实际上可能只需要1000-1010的数据）。
    		避免深分页： 我们可以改成id过滤，每次都只查询大于上次查询的数据id。这样每次只查询100条，回表也只需要回表100条。
    		select * from my_table where id>上次查询的数据id值 limit 100。
    		延迟关联： 通过延迟关联，通过编写完全基于索引查询数据的SQL，再根据id查询详细的字段数据。
    		SELECT * FROM my_table t1,(select id from my_table where col_c=1 limit 1000,100) t2 where t1.id=t2.id
    2，order by id
    	在不能保证id排序可以走索引的情况下，建议改用其他字段排序。
    	如果查询结果集确定会比较少排序字段可以不在索引上，如果结果集较大还是要把排序字段加到索引中
    3，避免出现select * （取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，
    	也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗）
    4，优化group by语句：查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序
    5，优化order by语句：MySQL 可以使用一个索引来满足 ORDER BY 子句，
    	而不需要额外的排序。where 条件和 order by 使用相同的索引，
    	并且 order by 的顺序和索引顺序相同 ，并且 order by 的字段都是升序或者都是降序。
    6，优化join语句：连接查询代替子查询（尤其时连接的字段包含索引效果会更好）	
    7，优化or条件： or子句，如果要利用索引，则or之间的每个条件列都必须用到索引；如果没有则应该考虑增加
    8，优化union查询：非必要使用union aLL。union会给每个查询结果的临时表加上distinct
    9，拆分复杂SQL为多个小SQL，比如in 1K 个值可以拆分为10个in 100个值使其能选中索引

### 3，慢查分析 ###

1，执行计划：我们可以通过explain关键字查看SQL的执行计划，通过执行计划分析SQL的执行情况。  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5peg5b2i6aOO_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]extra字段常见值：

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5peg5b2i6aOO_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]**using index condition补充说明：**

using index condition表示使用了ICP（Index Condition Pushdown索引下推），
    ICP是MySQL5.6之后新增特性，部分字段因某些情况无法走索引查询，会展示using where（在server层进行数据过滤），
    ICP是在存储引擎层进行数据过滤，不是在服务层过滤，利用索引现有的数据过滤调一部分数据。
    
    ICP
    select * from my_table where col_a="value" and col_b like "%value%"
    若本质上索引树包含col_b索引字段，只是col_b不能利用索引树二分查找特性（例如使用了前模糊匹配），但是可以利用索引上现有的数据进行遍历，减少无效回表。有了ICP后，基于索引就可以过滤col_a和col_b字段，过滤后只会剩下1000条数据，减少了大量的回表操作。

### 收藏加关注，再来不迷路！！！ ###

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5peg5b2i6aOO_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/38498cb127cd43b7830f2d989ab753f6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5peg5b2i6aOO,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5peg5b2i6aOO_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: https://img-blog.csdnimg.cn/73c3336f025e400093bd561da4142573.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5peg5b2i6aOO,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center