mysql索引简介
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
1、InnoDB 的存储模型
在InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表,使用了B+树索引模型,所以数据都是存储在B+树中。
主键索引的叶子节点存的是整行数据。在InnoDB 里,主键索引页被称为聚簇索引(clustered index )。非主键索引的叶子节点内是主键的值。在InnoDB 里,非主键索引也被称为二级索引(secondary index
)。
假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应用用身份证号做主键,还是用自增字段做主键呢?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约20个字节,而如果用整形做主键,则只要4个字节,如果是长整形,也才8个字节。
显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。
只有一个索引
•该索引必须是唯一索引
•有没有什么场景适合用业务字段直接做主键的呢?还是有的,比如,有些业务的场景需求是这样的:
由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。
最左前缀原则
可以是联合索引的最左N 个字段,也可以是字符串索引的最左M 个字符。
2、索引下推
在MYSQL5.6引入的索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
如下图所示,无索引下推流程
索引
2019年5月14日
17:45
有索引下推流程
3、MYSQL 为什么会有时候选错索引?
(1)、MYSQL 是怎样得到索引的基数的呢?
一个索引上不同的值越多,这个索引的区分度就越大。而一个索引上不同的值的个数,我们称之为“基数(cardinality )”,基数越大,区分度越好。
采用统计的时候,InnoDB 默认会选择N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息页不会固定不变。所以,当变更的数据行数超过1/M 的时候,会自动触发重新做一次索引统计。
设置为on 的时候,表示统计信息会持久化存储。这时,默认的N 是20,M 是10。
•设置为off 的时候,表示统计信息只存储在内存中。这时,默认的N 是8,M 是16。
•在Mysql 中,有两种存储索引统计的方式。可以通过设置参数innodb_stats_persistent 的值来选择:
由于是采样统计,所以不管N 是20还是8,这个基数都是很容易不准的。
采用force index 强行选择一个索引。
•可以考虑修改语句,引导MYSQL 使用我们期望的索引。
•在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。
•(2)、索引选择异常和处理
4、如何给字符串自动加索引
(1)、前缀索引,例如取EMAIL 地址的前6个字节做为索引。
alter table suser add index index2 (email(6));
使用前缀索引后,可能会导致查询语句读取数据的次数变多。如果定义好长度,就可以即节省空间,又不用额外增加太多的查询成本。
使用前缀索引时,如何确定我应该使用多长的前缀呢?
可以使用下面的语句,计算出这个列上有多少个不同的值:
mysql> select count(distinct email) as L from SUser;
mysql> select count(distinct email) as L from SUser;
然后,依次选取不同长度的前缀来看这个值,比如我们要看一下4~7个字节的前缀索引,可以用这个语句:
mysql> select
count(distinct left(email,4))as L4,
count(distinct left(email,5))as L5,
count(distinct left(email,6))as L6,
count(distinct left(email,7))as L7,
from SUser;
使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如5%。
使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是选择是否使用前缀索引时需要考虑的一个因素。
(2)、倒序存储
如果你存储身份证号的时候把它倒过来存,每次查询的时候,可以这样写
mysql> select field_list from t where id_card = reverse('input_id_card_string');
由于身份证号最后6位没有地址码这样的重复逻辑,所以最后这6位很可能提供了足够的区分度。
(3)、使用hash 字段
在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
然后每次插入新记录的时候,都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突,也就是说两个不同的身份证号码通过crc32()函数得到的结果可能是相同的,所以你的查询语句where 部分要判断id_card 的值是否精确相同。
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
(4)、倒序存储和HASH 字段两种方式的异同点
从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而HASH 字段方法需要增加一个字段。当然,倒序存储方式使用4个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个HASH 字段也差不多抵消了。
•在CPU 消耗方面,倒序方式每次写和读的时候,都需要调用一次reverse 函数,而HASH 字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的CPU 资源会更小些。
•从查询效率上看,使用Hash 字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近1.而倒序存储方式毕竟还是用的前缀索引的方式。也就是说还是会增加扫描行数。•不同点
相同点:都不支持范围查询,只支持等值查询。