InnoDB索引

逃离我推掉我的手 2021-08-13 13:24 345阅读 0赞

其数据文件本身就是索引文件。  
相比myISAM，索引文件和数据文件是分离的，表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。  
这个索引的key就是数据表的主键，  
因此innoDB表数据文件本身就是主索引，被成为聚蔟索引，也叫聚集索引。  
其余的索引都为辅助索引，  
辅助索引data域存储相应记录主键的值而不是地址。  
在根据主索引搜索时，直接找到key所在的节点即可取出数据，  
根据辅助索引查找时，需要先取出主键的值，再走一遍主索引，  
设计表时，不建议使用过长的字段作为主键，  
也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

--------------------

通过 explain 命令打印sql语句的执行计划，判断是否命中索引，  
type：all 表示全表扫描  
key：表示使用的索引，就是主键  
extra：using filesort 需要额外的步骤来发现如何对返回的行排序  
using temporary 需要创建一个临时表来存储结果，说明查询需要优化

**参数说明**  
id : 表示SQL执行的顺序的标识,SQL从大到小的执行

select\_type：表示查询中每个select子句的类型

table：显示这一行的数据是关于哪张表的，有时不是真实的表名字

type：表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有：ALL, index, range, ref, eq\_ref, const, system, NULL（从左到右，性能从差到好）

possible\_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。

key\_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的）

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好

Extra：该列包含MySQL解决查询的详细信息

EXPLAIN的特性

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

当全表扫描，或者索引字段数据重复率太高，将不会使用索引

--------------------

*  最左前缀原则  
    查询的适合查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到
 *  避免使用where 子句对字段是假函数，to\_date() 等。会造成无法命中索引。
 *  使用与业务无关的自增主键作为主键，即使用逻辑主键，不要使用业务主键
 *  避免冗余索引
 *  索引列为not null， 避免全表扫描
 *  删除不必要的、长期未使用的索引
 *  联表查询使用索引提高性能
 *  group by 和order by 中只设计一个表中的列，才有可能使用索引来优化
 *  查询条件的字段应使用正确的数据类型，否则mysql会自动做数据类型转换。导致无法命中索引。

--------------------

慢查询优化基本步骤

先运行看看是否真的很慢，注意设置SQL\_NO\_CACHE

where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

order by limit 形式的sql语句让排序的表优先查

了解业务方使用场景

加索引时参照建索引的几大原则

观察结果，不符合预期继续从0分析

--------------------

> https://mp.weixin.qq.com/s?\_\_biz=MzI0MjQxNjAyOQ==&mid=2247485299&idx=4&sn=d1e575cdc39de5e842a7d9882d0d150b&chksm=e97de781de0a6e979ce76d49d3fa38d37539cadc70cfdd8762d31c0c51d7da0defd5a30148db&scene=0&xtrack=1\#rd