福建省建设工程职业注册网站,用花生壳怎么做网站的服务器,云南建设学校网站,成都住建局官网租房交易中心索引
索引是为了提高数据查询效率#xff0c;就像书的目录一样。如下图#xff0c;索引和数据就是位于存储引擎中#xff1a;
索引常见模型
哈希表
以键值对存储的数据结构。适用于只有等值查询的场景。
有序数组
在等值查询和范围查询场景中性能都特别优秀。但是有…索引
索引是为了提高数据查询效率就像书的目录一样。如下图索引和数据就是位于存储引擎中
索引常见模型
哈希表
以键值对存储的数据结构。适用于只有等值查询的场景。
有序数组
在等值查询和范围查询场景中性能都特别优秀。但是有序数组索引只适用于静态存储引擎如果需要更新数据的话就成本太高了。
二叉树
每个节点的左儿子小于父节点父节点又小于右儿子。二叉树的搜索效率是最高的但是实际上大多数的数据库存储并不使用二叉树因为索引不止存在内存中还要写道磁盘上。一般使用N叉树这个N取决于数据块的大小。 数据库发展到今天跳表、LSM树等数据结构也被应用于引擎设计中但是数据库底层存储的核心就是基于这些数据模型的。 InnoDB的索引模型
表都是根据主键顺序以索引的形式存放的这种存储方式称为索引组织表。InnoDB使用了B树的索引模型所以数据都是存储在B树中的。 索引类型分为主键索引和非主键索引
主键索引叶子节点存的是整行数据也被称为聚簇索引非主键索引叶子节点内容是主键的值也被称为二级索引 基于主键索引和普通索引的查询有什么区别 如果语句是select *fromTwhere ID500即主键查询方式则只需要搜索ID这棵B树如果语句是select *fromTwhere k5即普通索引查询方式则需要先搜索k索引树得到ID 的值为500再到ID索引树搜索一次。这个过程称为回表。 也就是说基于非主键索引的查询需要多扫描一棵索引树所以尽量使用主键查询。
为什么InnoDB选择B树作为数据结构
B树
B树在非叶子节点也要存储数据** B 树的每个节点都包含数据索引记录**而用户的记录数据的大小很有可能远远超过了索引数据这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。而B树叶子节点才会存放索引记录非叶子节点只会存放索引。非叶子节点的索引也会同时存在于子结点中。
相同磁盘I/O次数下B可以查到更多节点。而且B树叶子采用的是双链表适合于MySQL中常见的基于范围的顺序查找而B树无法做到这一点。
二叉树
太高了。
hash
hash只适合等值查询不适合范围查询。
索引维护
自增主键是指自增列上定义的主键在建表语句中一般是这么定义的 NOTNULL PRIMARY KEY AUTO_INCREMENT。 插入新纪录的时候可以不指定ID的值系统会获取当前ID最大值1作为下一条记录的ID值也就是说自增主键的插入数据模式符合递增插入的场景每次插入一条新纪录都是追加操作都不涉及到挪动其他记录也不会触发叶子节点的分裂。 有没有什么场合适合使用业务字段直接做主键呢有的比如有些业务场景需求是 1、 只有一个索引 2、 该索引必须是唯一索引 由于没有其他索引所以也就不用考虑其他索引的叶子节点大小问题。这个时候就要优先将这个索引设置为主键尽量使用主键查询避免每次查询需要搜索两棵树。 覆盖索引
如果执行的语句是select ID fromTwhere k between 3 and 5这时只需要查ID的值而ID的值已经在K索引树上了因此可以直接提供查询结果不需要回表。也就是说在这个查询里面索引k已经覆盖了我们的查询需求。 覆盖索引是指 SQL 中 query 的所有字段在索引 BTree 的叶子节点上都能找得到的那些索引从二级索引中查询得到记录而不需要通过聚簇索引查询获得可以避免回表的操作。
联合索引
建立在多列上的索引称为联合索引。
最左前缀 可以看到索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时可以快速定位到ID4然后向后遍历得到所有 需要的结果。如果你要查的是所有名字第一个字是“张”的人你的SQL语句的条件是where name like ‘张%’。这时你也能够用上这个索引查找到第一个符合条件的记录是ID3然后向后遍历 直到不满足条件为止。
可以看到不只是索引的全部定义只要满足最左前缀就可以利用索引来加速检索。这个最左 前缀可以是联合索引的最左N个字段也可以是字符串索引的最左M个字符。
如何索引内字段顺序
考虑索引的复用能力第一原则是如果通过调整顺序可以少维护一个索引那么这个顺序往往就是需要优先考虑采用的。
索引下推
MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
对于这条语句
mysql select * from tuser where name like 张% and age10 and ismale1没有下推 这个过程InnoDB并不会去看age的值只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此需要回表4次。
有下推 InnoDB在(name,age)索引内部就判断了age是否等于10对于不等于10的记录直接判断并跳过。在这个例子中只需要对ID4、ID5这两条记录回表取数据判断就只需要回表2次。
什么时候需要索引什么时候不需要
需要索引
字段有唯一性限制的比如商品编码经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。
不需要索引
WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引比如性别字段只有男女如果数据库表中男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为 MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引经常更新的字段不用创建索引比如不要对电商项目的用户余额建立索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引这个过程是会影响数据库性能的。
索引优化
前缀索引优化
使用前缀索引是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小。 不过前缀索引有一定的局限性例如
order by 就无法使用前缀索引无法把前缀索引用作覆盖索引
覆盖索引优化
可以建立一个联合索引即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据查询将不会再次检索主键索引从而避免回表。所以使用覆盖索引的好处就是不需要查询出包含整行记录的所有信息也就减少了大量的 I/O 操作。
主键索引自增
建表的时候默认将主键索引设置为自增的。
索引最好设置为NOT NULL约束
索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化因为可为 NULL 的列会使索引、索引统计和值比较都更复杂比如进行索引统计时count 会省略值为NULL 的行。NULL 值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题。
防止索引失效
避免写出索引失效的查询语句否则这样的查询效率是很低的。
索引失效的情况
当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。
