mysql---索引优化

mysql---索引优化,第1张

索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引,没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引,不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构,

第二类是innodb存储引擎使用的叫做聚簇结构(也是一种 b-tree)。 如下图:

注意:

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

​ 有索引列参与计算的查询条件对索引不友好(甚至无法使用索引),如from_unixtime(create_time) = '2014-05-29'。

​ 原因很简单,如何在节点中查找到对应key?如果线性扫描,则每次都需要重新计算,成本太高;如果二分查找,则需要针对from_unixtime方法确定大小关系。

因此,索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

​ 如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c >3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

​ ​ 冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应当尽量避免这种索引,发现后立即删除。比如有一个索引(A,B),再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B),但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

        select user.* from user where login_time >'2017-04-01' and age between 18 and 30

​ 比如想查询某个时间段内登录过的用户:它有两个范围条件,login_time列和age列,MySQL可以使用login_time列的索引或者age列的索引,但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

​ 如果一个索引包含或者说覆盖所有需要查询的字段的值,那么就没有必要再回表查询,这就称为覆盖索引。覆盖索引是非常有用的工具,可以极大的提高性能,因为查询只需要扫描索引会带来许多好处:

1.索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量2.索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如,用性别作索引,那么索引仅能将1000w行数据划分为两部分(如500w男,500w女),索引几乎无效。

区分度的公式是count(distinct ) / count(*),表示字段不重复的比例,比例越大区分度越好。唯一键的区分度是1,而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数),选择性越高索引的效果越好、价值越大,唯一索引的选择性最高;

组合索引中字段的顺序,选择性越高的字段排在最前面;

where条件中包含两个选择性高的字段时,可以考虑分别创建索引,引擎会同时使用两个索引(在OR条件下,应该说必须分开建索引);

不要重复创建彼此有包含关系的索引,如index1(a,b,c) 、index2(a,b)、index3(a);

组合索引的字段不要过多,如果超过4个字段,一般需要考虑拆分成多个单列索引或更为简单的组合索引;

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销,对于插入、删除、更新 *** 作也会增加处理上的开销,而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。

索引合并检索方法可以检索多个范围扫描并将结果合并。这种访问方法只能合并同一个表的索引扫描,不能合并跨表扫描。

合并可能生成基础扫描结果的"并集","交集",或者"交集的并集"

示例:

这种方法适用于 WHERE 子句中的条件是通过 AND 结合的不同索引的范围条件时,其中的每个条件都需要满足下列条件之一:

示例:

索引合并交集算法在所有使用的索引上同时进行扫描,并从扫描结果中生成行的交集

如果查询中的所有列都被使用的索引覆盖,不需要检索所有表行( EXPLAIN 输出中的 Extra 列中包括 Using index )。例如这个语句:

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1

如果使用的索引没有覆盖查询中所有的行,只有当所有使用的索引的范围条件满足时才检索整个行。

如果合并条件中包括 Innodb 表主键索引条件,主键并不用来检索数据,而是用来筛选使用其他条件检索出的行。 # 就是先通过其他的范围条件筛选出一部分数据,在从这部分数据中,通过主键来筛选出最终的结果

这种方法适用于 WHERE 子句中的条件是通过 OR 结合的不同索引的范围条件时,其中的每个条件都需要满足下列条件之一:

示例:

这种方法适用于 WHERE 子句中的条件是通过 OR 结合的不同索引的范围条件,但是不能使用 Index Merge Union 算法的情景

示例:

sort_union 和 union 算法的区别是, sort_union 必须在返回行数据前先获取行ID并对行ID进行排序。

在 optimizer_swith 中有4个关于 Index Merge 的变量:

index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union

默认情况下都是启用的。要单独启用某个算法,设置 index_merge=off ,并将相应的标志设置为 on


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/6149577.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-16
下一篇 2023-03-16

发表评论

登录后才能评论

评论列表(0条)

保存