mysql 创建索引规范

本是古典何须时尚 2023-09-29 10:46 2阅读 0赞

## **1.索引用途、优缺点** ##

> 用途：主要用于提高检索(排序)效率  
> 优点：提高检索效率  
> 缺点：增加新增、修改、删除删除索引的维护成本,需要增加额外的存在空间

## **2.索引的分类** ##

### **2.1存储结构类型**  ###

> **1）B-tree  
> 有序可以用与等值匹配与范围查询，可以提高索引字段排序效率，创建删除变更索引效率稍低(需要考  
> 虑索引排序，索引分裂等问题)。  
> mysql使用的是B+tree索引，B+tree不同于b-tree主要是在所有数据都只存放在叶子节点上(减少了非  
> 叶子节点存在信息量，减少了树的整体高度)，同时增加了顺序指针，每个叶子点上都存储了相邻叶子节  
> 点的指针（范围查询效率功能）。**
> 
> **2）Hash（仅仅Memory存储引擎）  
> 无序只可以用于等值匹配（等值匹配效率高\[需要高区分度\]），无法使用部分索引，无法提高排序效率，  
> 创建删除变更索引的成本优于B-tree索引。**
> 
> **3）R-tree**
> 
> **4）Full-index**

### **2.2应用类型** ###

> **1）普通索引 idx\_ 开头  
> 2）唯一索引 uniq\_ 开头  
> 3）联合索引 union\_ 开头  
> 4）全文索引 ft\_ 开头  
> 5）函数索引（8之后） fc\_ 开头**

### **2.3聚簇索引与非聚簇索引** ###

> Innodb引擎B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储  
> 与索引放到了一块，找到索引也就找到了数据。
> 
> Innodb引擎主键索引即聚簇索引【避免回表查询，非聚簇索引只有在索引覆盖的场景下才不会回表】，  
> 其他索引均为非聚簇索引。

## **3.字段选取规范** ##

主键自增索引（表中必须创建主键索引）

> 1）保证数据的唯一性（尽最大努力）  
> 2）保证数据的有序性（尽最大努力）  
> 3）减少索引分裂（尽最大努力）

字段查询频率高，字段区分度高，更新频率低（查询频率与区分度同样重要）

> 1）查询频率高的字段是创建索引的首要条件（保证有需求）  
> 2）字段的区分度是是否创建索引的次要条件（保证有可行性）  
> 3）字段的变更频率是创建索引的关键因素（保证无较大的附带伤害）  
>

唯一性的业务字段必须创建唯一索引（避免脏数据）  
字段过长时指定索引的长度（前缀索引，索引长度越小越好，需计算区分度，【无法排序】）

> mysql innodb  
> 5.7- 单个索引字段长度不超过191,联合索引字段长度不超过768  
> 5.7+ 单个索引字段长度不超过767,联合索引字段长度不超过768

字段值设置非NULL，NULL使得索引与索引统计变得复杂。  
索引失效问题

> != 、 <> 、 NOT IN、NOT EXISTS 索引失效
> 
> 前缀模糊或者全模糊索引失效 a like '%a' \\ a like '%a%'
> 
> 索引字段运算操作后索引失效 a + 1 = 10
> 
> 索引字段加函数后索引失效（非函数索引） date('2022-04-26') = '2022-04-26'
> 
> 字段类型不匹配导致索引失效 int a a = '0'
> 
> or 引起索引失效  
>

## **4.联合索引，最左匹配原则** ##

> 由于mysql一次查询只能使用到一个索引，所以再多个查询条件的场景下需要考虑创建联合索引，联合索引需要考虑将查询评率更高的字段放在前面（最左匹配原则）。
> 
> 联合索引并非在所有使用场景都一定要满足最左匹配原则。在查询条件都是等值匹配时mysql的查询优化器可以优化查询顺序，保证查询时可以使用到该索引【规范最好遵守】。

## **5.索引下推** ##

> mysql 5.6+ 之后Innodb引擎模式下默认开启了索引下推优化【减少回表的次数】。
> 
> 举例：联合索引 union\_a\_b\_c(a,b,c)
> 
> 查询：select \* from table\_name where a=1 and b like '%b%' and c like '%c%';
> 
> 无索引下推时：此次查询只能先使用a字段索引查询，再回表查询 b 与 c。
> 
> 索引下推时：先使用a字段索引查询，然后直接根据索引来判断查询b 与 c。

## **6.explain：解析查询计划** ##

id：查询序号  
selecttype：查询类型（用于区分普通查询联合查询子查询等复杂查询）  
table：查询所针对的表,表名标的别名 derived (from后是子查询) null 直接查询不经过表  
type:

> system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge  
> > unique\_subquery > index\_subquery > range > index > ALL
> 
> all:全表扫描,  
> index:全索引扫描,  
> range:索引范围搜索,  
> ref:使用非唯一索引或者前唯一索的缀扫描,  
> eq\_ref:使用唯一索引扫描,  
> const/system:单表且最多有一行记录匹配  
> ref\_or\_null==>ref:查询条件中包含对null的查询，  
> index\_merge:索引合并优化，  
> unique\_subquery：in 的后面是一个查询主键的子查询，  
> index\_subquery: in 后面是一个非唯一索引的子查询】  
> 一般来说，得保证查询至少达到range级别，最好能达到ref，  
> type=NULL【mysql不用访问表或者索引，直接获得结果 select 2\*5;

possible\_key:可能用到的索引  
key：最终用到的索引  
key\_len:使用key的长多  
ref：显示哪个字段或常数与key一起被使用  
rows:这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的  
extra：

> 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。  
> 如果是where used，就是使用上了where限制。  
> 如果是impossible where 表示用不着where，一般就是没查出来啥。  
> 此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引  
> 经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引Usingfilesort，  
> 这就要看是先过滤再排序划算，还是先排序再过滤划算【use index 直接查询索引 use where 需要回表查询】

explain extended sql； + show warning；获取经过优化器处理的sql（去除恒等条件 1=1）  
explain partitions sql；查看sql所访问的分区