【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;