锐客网

  1. 首页 > 睿知 > it技术 > >

mysql|mysql 索引优化 2_mysql调优二-索引优化

mysql2索引优化

mysql调优-索引优化
一、前置：索引相关知识
1、索引用途/优点/分类
索引优点：
1>、大大减少了服务器需要扫描的数据量。
2>、帮助服务器避免排序和临时表。
3>、将随机io变成顺序io。
索引用途：
1>、快速查找匹配WHERE子句的行。
2>、如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引。
3>、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。
4>、当有表连接的时候，从其他表检索行数据。
5>、查找特定索引列的min或max值。
6>、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组。
7>、在某些情况下，可以优化查询以检索值而无需查询数据行。
索引分类：
主键索引、唯一索引、普通索引、全文索引、组合索引
2、索引过程现象
回表：当前使用的索引为普通索引，查询的列为非索引列，定位数据需要两步，首先通过普通索引定位到主键值，再根据主键值通过主键索引(聚集索引)来定位对应行记录。出现回表现象会降低查询效率，因为进行了两次索引扫描。
·
覆盖索引：查询的字段为 主键 + 已建立索引的字段 时触发(where条件中通过建立索引列的字段查询)。因为在普通索引的date中存的是 主键id 和 该字段值。举例说明，表user表中id列为主键索引，name列为普通索引，那么select id,name from user where name=‘zhangsan’; 查询会触发覆盖索引。
·
最左匹配：前置条件 使用索引为 组合索引。在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合，在建立组合索引的时候查询最频繁的条件要放在左边。
·
索引下推：在5.6后出的索引下推，之前使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器判断数据是否符合条件；
推出索引下推后，存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引下推可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
3、索引数据结构
索引具体的数据结构依据存储引擎的不同而不同
Memory存储引擎：
该存储引擎是基于内存的，采用的是hash表索引格式。利用hash存储需要将所有的数据文件添加到内存中，比较耗费内存空间。如果查询为等值查询，hash方式是极快的。
Mysam/Innodb存储引擎：
这两类存储引擎的索引数据结构都是B+树结构，B+树是在B树的基础上做的一种优化，主要变化：1>、B+树每个节点可以包含更多的节点，可以降低树的高度，将数据范围变为多个区间，区间越多，数据检索越快。2>、非叶子节点存储key，叶子节点存储key和数据。3>、叶子节点两两指针相互连接，顺序查询性能高。
mysql|mysql 索引优化 2_mysql调优二-索引优化
文章图片

Mysam存储引擎的索引是非聚簇索引，其索引文件和数据文件是分离的。data值存储的是该主键所对应的列的物理地址，再通过该地址查到对应的行数据。
Innodb存储引擎的索引是一种聚簇索引，索引文件与数据文件不分离，数据文件本身就是索引文件，data域中完整的保存了所有的数据信息。
4、索引匹配方式
举例说明：user表建立组合索引 name,phone,age
全值匹配：和索引中的所有列进行匹配 --explain select * from user where name = ‘zhangsan’ and phone = ‘13333333333’ and age = ‘22’;
最左前缀匹配：只匹配前面的几列 --explain select * from user where name = ‘zhangsan’ and phone = ‘13333333333’; explain select * from user where name = ‘zhangsan’;
匹配列前缀：匹配某一列的值的开头部分 --explain select * from user where name like ‘zhang%’;
匹配范围值：查找某一个范围的数据 --explain select * from user where name > ‘zhangsan’;
精确匹配某一列并范围匹配另外一列：查询第一列的全部和第二列的部分 --explain select * from user where name = ‘zhansgan’ and phone > ‘13333333333’;
只访问索引的查询：相当于覆盖索引。
二、hash/(非)聚簇/覆盖 索引说明
1.哈希索引
特点：
1>、基于哈希表的实现，只有精确匹配索引所有列的查询才有效；也不支持任何范围查询；
2>、在mysql中，只有memory的存储引擎显式支持哈希索引；
3>、哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，所以哈希索引查找的速度非常快；
4>、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行；
5>、哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序；
6>、哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值；
7>、访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行；
应用场景：
存储较多URL，需要根据URL的查找比较多，可以将url使用CRC32做哈希，可以使用使用体积很小的索引来完成查找；
2.(非)聚簇索引
聚簇索引：
是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。
优点：把相关数据保存在一起；数据访问更快，因为索引和数据保存在同一个树中；使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：1>、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势。
2>、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式。
3>、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置。
4>、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。
5>、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
非聚簇索引：数据文件跟索引文件分开存放。
3.覆盖索引
在上面已经介绍过，需要注意memory是不支持覆盖索引的。
特点：
1>、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量。
2>、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。
3>、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题。
4>、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用。
三、索引优化
1、具体优化小细节
1>、当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。
2>、尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询。
3>、使用前缀索引。如果某字段较长那么使用该列开始的部分字符串来作为索引 --alter table 表名 add key(字段名(6));
4>、使用索引扫描来排序。尽可能使用同一个索引既满足排序，又用于查找行。当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序(需要满足最左前缀的要求)。
5>、union all,in,or都能够使用索引，推荐使用in。
6>、范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。(、>=、between)
7>、强制类型转换会全表扫描
8>、更新十分频繁，数据区分度不高的字段上不宜建立索引。原因是更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。
9>、创建索引的列，不允许为null，可能会得到不符合预期的结果。
10>、当需要进行表连接的时候，最好不要超过三张表，需要join的字段，数据类型必须一致。
11>、能使用limit的时候尽量使用limit
12>、单表索引建议控制在5个以内，对于组合索引的索引字段数不允许超过5个
总结
索引的优化时针对表及相关查询操作的，不需要过早优化。在索引优化时要结合执行计划来看，这点尤为重要。
文章来源: blog.csdn.net，作者：酷儿Jay，版权归原作者所有，如需转载，请联系作者。
【mysql|mysql 索引优化 2_mysql调优二-索引优化】原文链接：blog.csdn.net/weixin_49442658/article/details/112369023

    推荐阅读


    上一篇：mysql优化varchar索引_Mysql调优-4通过索引进行优化

    下一篇：java提高|第07章 InnoDB数据存储结构【2.索引及调优篇】【MySQL高级】