索引详解与优化
2019-01-11 本文已影响0人
yellow_han
1、索引类型
MySQL中索引的存储类型有两种：BTREE和HASH。
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
2、索引优缺点
优点
- 加快数据的查询速度
- 唯一索引，可以保证数据库表中每一行数据的唯一性
- 在实现数据的参考完整性方面，可以加速表和表之间的连接
- 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间
缺点
- 占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸（合理运用，问题不大）
- 损耗性能（添加、修改、删除） 索引需要动态地维护
3、索引分类
-
主键索引
- primary key() 要求关键字不能重复，也不能为null,同时增加主键约束 主键索引定义时，不能命名
-
唯一索引
- unique index() 要求关键字不能重复，同时增加唯一约束
-
普通索引
- 单列索引： 即一个索引只包含单个列，一个表可以有多个单列索引
- 组合索引： 指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用
-
全文索引
- 类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引
4、索引设计原则
-
索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
-
避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段
-
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果
-
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
-
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
-
在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引
-
如果需要索引很长的字符串，此时需要考虑前缀索引，在保证其选择性为1。计算该列完整列的选择性，使得前缀选择性接近于完整列的选择性
-
尽量使用多列索引
-
选择合适的索引列顺序
5、索引使用原则
-
列独立
- 保证索引包含的字段独立在查询语句中，不能是在表达式中
-
左前缀
- like:匹配模式左边不能以通配符开始，才能使用索引 注意：前缀索引在排序 order by 和分组 group by 操作的时候无法使用。
-
复合索引由左到右生效
- 建立联合索引，要同时考虑列查询的频率和列的区分度。
-
不要滥用索引，多余的索引会降低读写性能
- 即使满足了上述原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大。
6、Btree和Hash的区别以及使用场景
- 1、Btree类型在我们查询数据时适合用于范围查找，在某一叶子的节点到另一节点的范围。例如<,>,orderby等场景都可使用
- 2、Hash索引，其检索效率非常高的一种精确定位索引。Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。无法被用来避免数据的排序操作。不能利用部分索引键（组合索引）查询。遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
7、性能分析
Explain：看一条 SQL 语句的性能，可以使用 explain 关键字查看语句性能
-
id : 表示SQL执行的顺序的标识,SQL从大到小的执行
-
select_type：表示查询中每个select子句的类型
-
table：显示这一行的数据是关于哪张表的，有时不是真实的表名字
-
type 表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
-
all，即全表扫描，说明这个 SQL 语句没有使用到索引，有可能是表本身没有创建索引，也可能是因为 SQL 语句导致没有使用索引
-
range，说明使用的是有范围的索引扫描，性能优于 index
-
index，这里说明使用了索引，这种情况下，如果 extra 列中的值为 Using index，这种情况是索引覆盖，索引覆盖的意思是，我们想要查询的数据，索引中已经都存在啦，这种情况下就不需要再回表取数据了
-
ref，说明条件列使用了索引，但是不是主键和 unique，所以这里即使使用了索引，索引值不唯一，有重复的情况
-
eq_ref，相对于 ref 来说就是使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录
-
const/system，单表中最多只有一条匹配行，查询起来非常迅速，所以这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行的查询
-
index_merge，说明使用了 MySQL 的索引合并的优化方法，当使用合并索引的时候，就需要检查一下我们所创建的索引是否为多个单列索引
-
-
possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
-
Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。
-
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
-
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
-
rows： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好
-
Extra：该列包含MySQL解决查询的详细信息
8、存在索引但不能使用索引的典型场景
1. 以%开头的 like 查询不能利用 B-Tree 索引，执行计划中 key 的值为 null 表示没有使用索引
-
B-Tree 索引的结构，所以以%开头的插叙很自然就没法利用索引了。一般推荐使用全文索引（Fulltext）来解决类似的全文检索的问题
2. 数据类型出现隐式转换的时候也不会使用索引
-
在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，mysql 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。
3. 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的。
4. 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
5. 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
9、聚集索引与非聚集索引
1. 聚集索引
InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，非叶子节点存放<key,address>，address就是下一层的地址。
image.png
2. 非聚集索引
非聚簇索引，叶子节点上的data是主键(即聚簇索引的主键，所以聚簇索引的key，不能过长)。
image.png
9、索引实战分析
1. 主键索引
explain SELECT * FROM store_goods WHERE id = '10002'
image.png
2、唯一索引
explain SELECT * FROM store_goods WHERE goods_sn = 'X14481662'
image.png
3、普通索引（单列）
EXPLAIN SELECT * FROM store_orders WHERE shop_id = '37';
image.png
4、普通索引（组合）
EXPLAIN SELECT * FROM d_part_record WHERE draw_id = '1082839908363476994' and mid = '2'
image.png
索引使用
参考链接
https://juejin.im/entry/5a448726f265da43062b10f1
https://juejin.im/entry/59a8fa9f5188251d445fb9fb
https://juejin.im/post/5a6873fbf265da3e393a97fa
https://juejin.im/post/5b6fd2b85188256128593eca
https://juejin.im/post/5ab34d21f265da23766b30ed
