mysql 创建索引规范

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

const/system:单表且最多有一行记录匹配。

eq_ref:使用唯一索引扫描。

ref:使用非唯一索引或者前唯一索的缀扫描。

fulltext:全文检索。

ref_or_null==>ref:查询条件中包含对null的查询。

index_merge:索引合并优化。

unique_subquery：in 的后面是一个查询主键的子查询。
index_subquery: in 后面是一个非唯一索引的子查询】。

range:索引范围搜索。
index:全索引扫描。
all:全表扫描。

一般来说，得保证查询至少达到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所访问的分区