第五章—创建高性能的索引

标签:获取   索引基础   放弃   分离   搜索   高性能   b-tree   www   是你   

索引基础

  • 索引,又叫key(键)
  • 在mysql中,存储引擎先在索引中找到检索的内容,然后根据索引结果找到对应的数据行
  • 索引可以包含一个或多个列的值,如果索引包含多个列,那么列的顺序十分重要,因为mysql只能高效的使用索引的最左前缀列
    • 最左前缀列就是KEY(id, name, sex)idid、name、sex里面是写在左边的,这就叫最左前缀

索引的类型

  • 索引是存储引擎层而不是服务器层实现的

B-Tree索引

技术图片

  • 使用B-Tree数据结构来存储数据
  • innoDB使用B+Tree
  • B-Tree意味着所有的数据都是按顺序存储,适合查找某一范围内的数据,对数据排序
  • 可加快访问数据的速度,存储引擎不再需要扫描全表,而是遍历索引搜索数据
  • 既然索引按顺序存储,也就意味着它对数据排序了,索引对数据排序的依据是按照定义索引的顺序来排序,例如KEY(id, name, sex),先对id排序,再是name,最后是sex

B+Tree索引

技术图片

 

 

 是B-Tree的改进版本,同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说,进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高

为什么索引结构默认使用B-Tree,而不是hash,二叉树,红黑树?

  • hash:虽然可以快速定位,但是没有顺序,IO复杂度高。
  • 二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
  • 红黑树:树的高度随着数据量增加而增加,IO代价高。

为什么官方建议使用自增长主键作为索引

  • 结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。
  • 并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率

可以用B-Tree索引的查询类型

当使用一下查询类型是可以使用B-Tree索引 假设有以下索引:KEY(id, name, sex)这个id不一定是唯一标识,例如班级编号等

  • 全值匹配:例如查找id为5,name为张三,sex为女的人,匹配全部的列
  • 匹配最左前缀:例如只匹配id=5的人
  • 匹配列前缀:匹配某一列的开头部分,例如查找name中姓为“李”的人
  • 匹配范围值:例如查找id在5到100之间的人
  • 精确匹配某一列并范围匹配另外一列:例如查找id=5,name匹配“李”开头的人,即第一列全部匹配,第二列部分匹配
  • 只访问索引的查询:只需要查询索引,不需要查询数据行

刚才说了索引中的节点都是有序的,所以索引除了用于按值查找,还可以用于order by等排序操作

  • 如果order by子句满足前面列出的查询类型,就可以用来排序

B-Tree索引的限制

  • 如果不是按照最左前缀原则来查询的话,则无法使用索引:例如无法查找name=张三的人,也无法查找sex=女的人,因为他们都不是key里面最左边的。同理如果第一个索引不是id是name,无法查找名字中以“三”结尾的人,因为以**结尾不是最左原则
  • 不能跳过中间索引的列:不可以跳过name来查找id=5,sex=女的人,如果你这样干了,那mysql只会使用第一个索引:id=5这个条件,放弃其他的。
  • 如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引优化查询:例如查询id=5 and name like ‘李%’ and sex=女的人,因为name列使用了范围查询(like),所以sex无法使用,只会使用前两列索引

因此索引的顺序很重要,一个经验是将索引选择性最高的放到前面

哈希索引

  • 哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效
  • 解释一下上面的话,假如有哈希索引:KEY USING HASH(name),哈希索引会计算所有name列的值(是值整体,不是值的某一部分),得到一个hash码存储在索引中,同时在哈希表中保存每个hash码指向的数据行的指针

hash码指针指向1235第一行5623第十行5984第五行

  • 当你查询name=张三的时候,哈希索引会计算张三这个值,然后得到一个hash码,再根据这个hash码哈希表中找到指定的行的指针,然后找到这行,取这行name的值计算hash看看是否等于“张三”,确保就是要查找的行。
  • 如果不是精确查找,也就是你要找张三,但是输入成了张三三,由于张三三计算出来的hash码不等于张三的hash码,于是查找失败。
  • 哈希索引的特点是速度快
  • 哈希索引存储的是hash码与指针,不是实际数据,无法用于排序
  • 不支持部分索引列匹配查找,因为哈希要计算全部值得hash码 只支持精确查找,无法用于部分查找和范围查找 无法用于排序与分组
  • 只支持等值比较: =、 IN
  • 不支持>一类的查询
  • innoDB有自适应哈希索引:他会把频繁使用的索引,自动创建哈希索引
  • 场景:对于URL,有时候URL会很大,不适合B-Tree索引,可以使用哈希索引,通过计算URL的hash码,是比较快的方式

全文索引

  • MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。
  • 查找条件使用 MATCH AGAINST,而不是普通的 WHERE。
  • 全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。
  • InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

索引的优点

  • 索引让服务器可以快速定位到表的指定位置
  • 大大减少了服务器需要扫描的数量
  • 帮助服务器避免排序和临时表
  • 将随机I/O变为顺序I/O

什么时候使用索引

  • 当表数据量大的时候,要使用索引 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效 对于中到大型的表,索引就非常有效
  • 但是对于特大型的表,建立和维护索引的代价将会随之增长。这种情况下,需要用到一种技术可以直接区分出需要查询的一组数据,而不是一条记录一条记录地匹配,例如可以使用分区技术
  • 还有一点就是,对于超大型数据,B-Tree索引会失效!除非是索引覆盖查询

高性能的索引策略

先讲一下有哪些策略,再讲一下,一些索引的特点

  • 独立的列:索引列不能是表达式的一部分,也不能是函数的参数,例如:where id + 1 = 5 这个id + 1 = 5就不是独立的列,直接写成id=4多好 虽然你写一个表达式也可以查询出结果,但是无法使用索引
  • 前缀索引和索引选择性 对于特别长的字符列,一般只索引这段长字符的前面一部分(当然哈希索引也很好),从而提高效率 对与bolb和text或者很长的varchar,必须使用前缀索引,mysql不允许全部索引他们 
  • 索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。 对于数据量比较小的索引可以放到最左,例如dog在数据库中有100条记录,cat在数据库中有56231条记录,可以把dog放到最左。因为更小意味着速度更快(前提是你会查它)
  • key最好是有顺序的,例如int型自增的id
  • 多列索引: (把频繁查询的列都设置为索引) 在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。例如下面的语句中,最好把 actor_id 和 film_id 设置为多列索引。
  • SELECT film_id, actor_ id FROM sakila.film_actor
    WHERE actor_id = 1 AND film_id = 1;

     

  • 覆盖索引: 索引包含所有需要查询的字段的值。(就是你要查询的数据都是索引) 具有以下优点:
    • 索引通常远小于数据行的大小,只读取索引能大大减少数据访问量。
    • 一些存储引擎(例如 MyISAM)在内存中只缓存索引,而数据依赖于操作系统来缓存。因此, 只访问索引可以不使用系统调用(通常比较费时)。
    • 对于 InnoDB 引擎,若辅助索引能够覆盖查询,则无需访问主索引。

聚簇索引

  • 一种数据存储方式;在同一个结构中保存了B-Tree索引数据行;数据行放在索引的叶子页中
  • 一个表只能有一个聚簇索引
  • 可以理解聚簇索引“就是”表
  • mysql中InnoDB引擎的主键索引为聚簇索引,MyISAM存储引擎采用非聚集索引
  • InnoDB通过主键聚集数据,如果没有定义主键,InnoDB将选择一个没有空值的列创建聚簇索引。

优点

  • 相关数据保存在一起,不必每次都导致磁盘I/O
  • 数据访问速度更快:因为数据和索引都在一个B-Tree中

缺点

  • 导致全表扫描变慢
  • 二级索引(非聚簇索引)可能会很大,或者访问需要两次索引查找:因为二级索引中保存的行指针,指向的是主键值,还需要再根据主键值去聚簇索引中查找数据行
  • 更新聚簇索引列的代价很高

非聚簇索引

  • 索引数据和存储数据是分离的

二级索引

  • 二级索引存储的是记录的主键,而不是数据存储的地址
  • 需要两次查询,效率不高

覆盖索引

  • 指一个索引覆盖了所有需要查询的字段的值(也就是扫描索引就可获取数据,无需再回去查表)
  • 是非常有用的工具,可以极大地提高性能
  • MySQL只能使用B-Tree来做覆盖索引
  • 因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多

聚簇索引与覆盖索引的区别

  • 聚簇索引不是索引,是一种数据存储方式;而覆盖索引是索引
  • 聚簇索引会将数据存储在索引的叶子节点中,例如 InnoDB 的主索引(primary key)就是聚簇索引,而辅助索引(key)不是聚簇的,这是因为只能将数据存储在一个索引上。MyISAM 的主索引不是聚簇的。
  • 而覆盖索引是指,使用索引来覆盖查询到的所有字段比如查询涉及到的字段为 a,b,c,那么就在这三个字段上设置索引,因为索引的本身包含了这些字段的值,因此也不需要进行磁盘操作。
  • mysql会同时使用到这两种索引

压缩前缀索引

  • 对较长的字符串压缩,从而让更多的索引可以放入内存中

索引和锁

  • 索引可以让查询锁定更少的行
  • innoDB在访问行的时候会对其加锁,索引可以减少innoDB访问的行数,从而减少锁的数量
  • 如果做一个查询:select id from user where id < 5;返回如下
|------|
|  id  |
|------|
|   2  | 
|   3  |
|   4  |
--------
?

虽然只返回了2、3、4行的数据,但是实际上是获得了1~4行的排它锁(X锁),只有在查询结束后X锁才会被释放

  • 把需要做范围查询的列放到索引的最后

第五章—创建高性能的索引

标签:获取   索引基础   放弃   分离   搜索   高性能   b-tree   www   是你   

原文地址:https://www.cnblogs.com/tracydzf/p/14229869.html

版权声明:完美者 发表于 2021-01-07 11:49:38。
转载请注明:第五章—创建高性能的索引 | 完美导航

暂无评论

暂无评论...