【Mysql篇】了解Mysql(二)

·  阅读 370
索引

索引是一种数据结构,帮助我们快速的检索数据库中的数据。大概分为以下几类:

  • 普通索引normal:仅仅加快查询;
  • 唯一索引unique:加索查询,列值唯一,可以有NULL。
  • 主键索引primary:加速查询,列值唯一,不可以为NULL,表中只有一个。
  • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。
  • 全文索引full text:对文本的内容进行分词,进行搜索。
索引具体底层(数据结构)
  • 索引的数据结构和具体存储引擎的实现有关
  • MySQL主要有两种结构:Hash索引和B+Tree索引,我们使用的是InnoDB引擎,默认的是B+树。
B+树索引和Hash索引比较
  • Hash索引底层是哈希表(不了解点击这里等待开通),是以Key-Value存储数据的结构,所以数据在存储关系上是没有顺序的,没办法进行范围查询,必须要全表扫描。Hash索引只适用于等值查询的场景。
  • B+树索引适合外部存储。由于内结点不存放真正的数据(只存放其子树的最大或最小的关键字,作为索引),一个结点可以存储更多的关键字,每个结点能索引的范围更大更精确,也意味着B+树单次磁盘IO的信息量大于B树,I/O的次数相对减少。B+树更容易进行区间访问,因为叶子节点维护了一个链表。
B+树的叶子节点,可以存放哪些东西
  • B+树索引中,叶子节点可能存储当前的key值,也可能存储当前的key值以及整行数据,这就是聚簇索引和非聚簇索引。
  • InnoDB引擎中,只有主键是聚簇索引,如果没有主键则挑选一个唯一键作为聚簇索引,如果没有唯一键,则隐式生成一个键来建立聚簇索引
聚簇索引和非聚簇索引特点
  • 聚簇索引更快,因为主键索引树的叶子节点直接就是要查询的整行数据
  • 非主键索引的叶子节点是主键的值,查到主键的值以后,还需再通过主键的值再进行一次查询
  • 当查询使用聚簇索引的时候,在对应的叶子结点上可以获取到整行的数据,不需要回表查询,而非聚簇索引需要回表查询
非聚簇索引一定要回表搜索吗
  • 不一定,这涉及到查询语句所要求的字段是否全部命中索引,如果全部命中,就不必再进行回表查询,即覆盖索引

覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖
当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免查到索引后再返回表操作,减少I/O提高效率

//表test中有一个普通索引 idx_key1_key2(key1,key2)。
select key2 from test where key1 = ‘keytest’;
//当通过上列SQL语句,就可以通过覆盖索引查询,无需回表。
复制代码
建立索引时,考虑哪些因素
  • 考虑字段的使用频率,经常作为查询条件的字段比较适合索引。索引占据内存,且修改表会导致索引更新,因此建立索引时要考虑表结构
  • 在一些场合使用联合索引是比较好的,如我们建立一个(学校-班级-ID)的联合索引,这样会比建立三个索引效果好,但如果只使用其中一个索引ID不会走联合索引会导致全表扫描。使用联合索引要注意顺序,尽量把区分度大的索引放在前面
为什么建立联合索引(区分度大的索引放在前面)
  • 联合索引使用中,想要命中索引需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。联合索引中有个最左匹配原则,当我们建立联合索引(A,B,C),实际上已经建立了(A)、(A,B)、(A,B,C)三个联合索引。
  • 比如上面说的(学校-班级-ID)联合索引,b+树是按照从左到右的顺序来建立搜索树的,b+树优先比较学校来确定下一步的搜索方向,如果还未达到条件则继续执行搜索。如果只有学校字段,班级字段缺失,只能找到这个学校的所有字段,然后再匹配相应ID的学生,此种情况无法用到联合索引。
如何判断创建的索引是否使用到(如何分析Sql语句)
  • 一般通过explain命令查看语句的执行计划,EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句。分析查询语句或是表结构的性能瓶颈。
Explain的字段
  • id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
  • select_type:查询的类型,主要是用于区别普通查询联合查询子查询等的复杂查询。
  • table:当前执行的表。
  • type:查询使用了哪种类型。查询性能从最好到最差依次是:system > const > eq_ref > ref > range > index > all,一般来说,得保证查询至少达到range级别,最好达到ref
  • possible_keyskey:(1)possible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。(2)key表示实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)。
  • key_len:索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。
  • ref:显示索引的那一列被使用,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
  • rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好
  • Extra:包含不适合在其他列中显式但十分重要的额外信息。
什么情况下针对列创建了索引,查询的时候却没有使用
  • 参与了数学运算或者函数
  • 字符串like时的左边是通配符,"%a"。
  • 当mysql分析全表扫描比使用索引快时。
MySQL 5.6,对索引做了哪些优化

索引下推,默认是开启的。

官方文档中给的例子和解释如下: people表中(zipcode,lastname,firstname)构成一个索引, SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%MainStreet%’;如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%MainStreet%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。

分类:
阅读
标签:
收藏成功!
已添加到「」, 点击更改