• 周二. 10 月 8th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL 索引的知识整理

admin

11 月 28, 2021
前言:
      很多面试者,在面试的时候,都会回答,”索引就相当于一本书的字典,有了他能够很快的找到数据”,
这种答案好像在读书的时候老师告诉这么说的吧。今天来全面的描述一下数据库索引的原理及优化。
 
MySQL 几种索引类型:
     1、Normal     普通索引
          
     2、Unique     唯一索引
          
     3、Full Text    全文索引
          目前只有 MyISAM 存储引擎才支持,只能在字符类型字段上改用 Full Text 索引,为了解决 name like ‘%%’ 这类 SQL 的写法,
          一般数据量小的系统、网站上可以使用使用,但是在数据库量比较大的系统中,不建议使用全文索引,会给你的数据库带来极大的压力,
          同时你的数据库硬盘也会增加很快,可以采用一些开源的全文索引,Solr、Lucene.NET 等。
          
          为什么在给某张表/字段增加全文索引的时候,比一般的索引创建都要久呢,因为 MySQL 内部在为全文索引做分词处理,然后才建立对应关系;
 
MySQL 索引的方式:
      1、BTREE       树结构
           默认索引方式,BTREE 树索引能够加快访问数据的速度,因为存储引擎不在需要进行全表扫描来获取需要的数据,取而代之是从索引的根节点开始
           进行搜索,根据点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入
           下一层子节点,这些指针实际上定义了子节点页中值的上限和下限,最终存储引擎要么是找到对应的值,要么该记录不存在。
          
           因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的order by操作,一般来说,如果btree可以按照某种方式查找的值,
           那么也可以按照这种方式用于排序,所以,如果order by子句满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求。
 
           关于 BTREE 索引的限制:
           A:如果不是按照索引的最左列开始查找的,则无法使用索引
               (注意,这里不是指的where条件的顺序,即where条件中,不管条件顺序,只要where中出现的列在多列索引中
               能够从最左开始连贯起来就能使用到多列索引)
          B:不能跳过索引中的列,如:查询条件为姓和出生日期,跳过了名字列,这样,多列索引就只能使用到姓这一列
          C:如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询,
               如:where last_name=xxx and first_name like ‘xxx%’ and dob=’xxx’;这样,first_name列可以使用索引,这列之后的dob列无法使用索引。
          
      2、HASH       键值对结构
           目前只有 Memory 引擎支持哈希索引。基于哈希表实现,同理索引存储方式就是键值对(Key/Value)形式存储,访问数据速度非常快。
           只有精准匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有的索引列的值计算一个哈希码,哈希码是一个较小的值,
           并且不同键值的行计算出来的哈希码不一样,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行指针。
           与众不同的是如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。
 
           弊端:
           A:哈希索引只包含哈希值和行指针,而不存储字段值,所以不能索引的值来避免读行(即不能使用哈希索引来做覆盖索引扫描);
           B:哈希索引数据不是按照索引列的值顺序存储的,所以也就无法用于排序;
           C:哈希索引不支持部分索引列匹配查找。因为哈希索引始终是使用索引的全部列值内容来计算哈希值。
           D:哈希索引只支持等值比较查询(a = 10 or a in (1,2)),不支持范围查询(a > 10 and a < 100)
           E:哈希索引如果出现冲突时,存储引擎必须遍历链表中所有的行指针,逐行比较,直到找到所有符合条件的行。
 

发表回复