mysql sparse索引_数据库索引(笔记)

引言

最简单的数据查询方式是全表扫描，找出符合条件的数据。

索引的设计灵感来源于字典，根据关键信息可以快速定位。

为什么要使用索引

索引能够避免全表扫描，提升检索效率。

什么样的信息能成为索引

主键，唯一键等能够让数据具备一定区分度的字段。

索引的数据结构

主流使用B+树，也有一部分数据库索引使用Hash索引、BitMap。

Hash索引的缺点

Hash索引仅能满足=和IN查询，无法实现范围查询。

Hash索引值的大小关系并不一定和Hash运算前的大小关系完全一样，数据库无法利用索引的数据来避免任何排序运算。

对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值的，不是单独计算Hash值，所以不能利用部分索引键查询。

不同数据可能存在相同的Hash值，所以不能避免表扫描。

遇到大量Hash值相等的情况后性能会降低。

密集索引和稀疏索引的区别

密集索引：叶子节点保存了整条数据记录。密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引：叶子节点只保存了键值以及该行数据记录的地址/主键。

InnoDB

如果一个主键被定义，则该主键作为密集索引。

如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引。

若不满足以上条件，InnoDB内部会生成一个隐藏主键(密集索引)。

非主键索引存储相关键位和其对应的主键值，包含两次查找。

MySQL

InnoDB中，主键使用密集索引，辅助键使用稀疏索引。稀疏索引中存储的是数据的主键。

MyISAM中，主键与辅助键都是用稀疏索引，存储的是数据的地址。

如何定位并优化慢查询SQL

整体思路：

根据慢日志定位慢查询SQL

使用explain等工具分析SQL

修改SQL或尽量让SQL走索引

慢日志：MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句。

设置：

键

配置

描述

long_query_time

1

超过1s被记录为慢查询

slow_query_log

ON

开启慢查询日志

slow_query_log_file

xxx.log

慢查询日志文件

联合索引的最左匹配原则的成因

建立联合索引：index_area_title。

SELECT * FROM person_info WHERE area = 'TIANJIN' AND title = 'YUNZHI'，走索引。可以乱序。

SELECT * FROM person_info WHERE area = 'TIANJIN'，走索引。

但是，SELECT * FROM person_info WHERE title = 'YUNZHI'，不走索引。

MySQL会一直向右匹配直到遇到范围查询(>/ 5 and d = 6，如果建立(a, b, c, d)顺序的索引，d就是用不到索引的；如果建立(a, b, d, c)的索引则都可以用到。

=和IN可以乱序，比如a = 1 and b = 2 and c = 3建立(a, b, c)索引可以任意顺序，MySQL的查询优化器会优化成索引可以识别的形式。

原因

MySQL创建复合索引的规则是首先对复合索引的最左边，也就是索引的第一个字段进行排序，再第一个字段的基础上，再对索引上第二个字段进行排序。

第一个字段是绝对有序的，第二个字段就是无序的了，因此直接使用第二个字段判断是用不到索引的。

索引是建立得越多越好吗

数据量小的表不需要建立索引，建立会增加额外的开销。

数据变更需要维护索引，因此更多的索引意味着更多的维护成本。

更多的索引也意味着更多的空间。