mysql数据库索引

sdjzping

已于 2022-07-01 13:06:54 修改

阅读量352

点赞数

分类专栏： mysql 文章标签：数据库 sql 性能优化

于 2022-07-01 13:06:13 首次发布

本文链接：https://blog.csdn.net/sdjzping/article/details/125556723

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

常用的索引数据结构
- 哈希表：只适用于等值查询，如果是区间查询效率很低
- 有序数组：等值查询和区间查询效率都很高，但是新增需要移动元素腾出位置，效率比较低；适用于静态索引数据，例如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?)