【MySQL】索引知识

https://blog.csdn.net/weixin_39922374/article/details/110665140

https://blog.csdn.net/weixin_45699541/article/details/126518330

正确地创建和使用索引是实现高性能查询的基础。

检验索引是否适合某个查询的“三星系统”：

索引将相关的记录放到一起获得一星；

索引中的数据顺序和查找中的排列顺序一致则获得二星；

索引中的列包含了查询中需要的全部列则获得三星；

 

索引的选择性 = 不重复的索引值 和 数据表的记录总数 的比值。

范围 0 到 1 之间。索引的选择性越高则查询效率越高，高选择性的索引可以在查询时过滤掉更多行，索引选择性等于1的性能也是最好的。

 

索引失效的情况：

like '%hello%' 前有通配符索引不能生效。但是最左前缀匹配索引可以生效

 

索引生效的原则：

1、独立的列

表达式索引不生效，如：where age + 1 = 30 ，to_days(col_d) - to_days(col_f) <= 10

2、前缀索引和索引选择性

索引字段字符串很大，会让索引变得很慢。对于BLOB、TEXT或很长的VARCHAR必须使用前缀索引，诀窍在于选择足够长的前缀以保证较高的选择性。

查找前缀，前缀统计的个数不能太高，不断尝试找到合适的前缀长度

select count(*) as cnt, left(your_field, 3) as prefix from your_table group by prefix order by cnt dest limit 10;

3、多列索引

 在多个列上建立独立的单列索引大部分情况下并不能提高查询性能。

创建多列索引，优化索引列的顺序，尽量是全覆盖索引

选择合适的索引列顺序原则：（没有一个放之四海而皆准的原则）

不考虑排序和分组时，将选择性最高的列放到索引的最前面通常时好的。

统计条件的数据件数来决定索引字段顺序  select count(*) as cnt,  sum(age_ = 22), sum(name_ = 'tom') from test

4、覆盖索引

如果一个索引包含所有要查询的字段的值，我们称之为覆盖索引。索引(A,B,C)，查询 select A,B,C from table where A=1。这样索引就能覆盖查询，从而直接使用索引上的数据不用再查表，性能大为提升。

5、使用索引扫描来做排序

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

不能使用索引做排序查询：

order by f1 desc, f2 asc; 两种不同的排序方向

order by中引用了不在索引中的字段

order by中的列无法组合成索引的最左前缀

where f3 > '2022-05-12' order by f1,f2 查询在索引列的第一个列上是范围条件

where f in(2,3) 列上有多个条件（in），对排序来说也是范围

6、冗余和重复索引

unique(id_)和index(id_)就是重复索引，没有必要。

创建索引(A,B)再创建(A)就是冗余索引，因为索引(A)只是索引(A,B)的前缀索引。

二级索引的叶子节点包含了主键，索引(A)相当与(A,ID)上的索引

索引案例：

尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

可以使用IN把缺失的条件加入进来以满足索引，但不能滥用，IN的数据不能太多，否则组合情况会暴增。

查询索引基数 Cardinality，存储引擎估算索引列有多少个不同的取值

show index from test;