- 常用的索引数据结构
- 哈希表:只适用于等值查询,如果是区间查询效率很低
- 有序数组:等值查询和区间查询效率都很高,但是新增需要移动元素腾出位置,效率比较低;适用于静态索引数据,例如17年的身份证数据等
- 搜索树:等值查询和区间查询及新增效率都比较高,查询效率最高的是二叉搜索树,但是索引不只存在于内存中还存储在磁盘中,所以二叉搜索树作为索引会增加好多次io读写,效率比较低,目前innodb使用的是B+树
- 主键索引和普通索引的区别
- 主键索引的叶子节点挂载的是整行数据,又称为聚簇索引;
- 普通索引的叶子节点是主键值,又称为二级索引,根据普通索引查找数据,会先找到对应的叶子节点主键值,然后再去主键索引中找到该行数据,,该操作称为回表
- 数据库建索引的时候推荐使用自增主键作为主键索引,这样普通索引的叶子节点占用的存储空间会小,搜索的时候尽量使用主键查询,效率比较高,只需要查询一次即可
- 索引优化
- 覆盖索引:如果二级索引可以直接获取到要的数据,就不需要回表,这类索引称为覆盖索引。例如select id from T where k=3;其中id是主键,k是普通索引,查找数据的时候k索引中就可以获取到id值,此时就不用回表了,可以减少查找次数,达到优化查询的目的
- 前缀索引:有联合索引(name,age),如果根据名称查数据,是可以用到这个索引的,这样就不需要单独建立name的索引减少维护成本,但是如果有只根据age查询的sql,那么这个联合索引就不能用了,需要再建立一个age索引;
- 索引下推:例如select * from T where name like ‘张%’ and age=10 and isMal=1;这个表有一个(name,age)的索引,mysql5.6之前是没有索引下推的,查找过程是先找到name是张的找到对应的主键,回表找到行数据比对age和isMal字段;但是有索引下推优化就可以减少回表的次数了,当根据(name,age)索引找到对应的数据时,可以先感觉age判断一下,如果不满足情况就不需要回表操作了,可以减少查询的次数,达到优化sql的目的
- 索引优化删除多余索引分析
CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;
优化结果:ca这个索引可以删除,cb需要保留,原因是c这个索引生成的结果是cab,ca这个索引生成的结果也是cab,cb对应的是cba,所以ca和c的结果是一样的根据最左前缀的原则,为什么删除的不是c这个索引而是ca呢;ca这个索引按照理解叶子节点是主键,那么应该是caab,因为数据库索引会删除重复的字段,所以是cab
问题
- b+树的索引结构,比如地址这个字段作为索引列,多个相同的地址是什么样的结构?
- 我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:mysql> select * from tuser where name like ‘张%’ and age=10 and ismale=1;
你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 ID3。当然,这还不错,总比全表扫描要好。(为什么不走联合索引?是因为名字用了like?)