InnoDB索引

薄荷街的香草先生
78 阅读9分钟

索引是什么

索引是什么.png

索引的类型

索引类型.png 数据结构维度

  • B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
  • 哈希索引:  适合等值查询，检索效率高，一次到位。
  • 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
  • R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

  • 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
  • 非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

逻辑维度

  • 主键索引：一种特殊的唯一索引，不允许有空值。
  • 普通索引：MySQL中基本索引类型，允许空值和重复值。
  • 联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
  • 空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

索引的作用

建立索引的目的：减少磁盘 IO的次数，加快查询效率。

查看索引

 select * from information_schema.TABLE_CONSTRAINTS 
 where TABLE_NAME = 'tb_company_department';
 ​
 show index from tb_company_department;

索引的结构

聚簇索引

InnoDB索引-数据结构原理-目录项记录页的数据页.png

  • 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。
  • InnoDB存储引擎会自动的创建聚簇索引。
  • 由于数据物理存储排序方式只能有一种，所以每个MysQ的表只能有一个聚簇索引。一殷情况下就是该表的主键。
  • 如果没有定义主键， innodb会选择非空的唯一索引代替。如果没有这样的索引， Innodb会隐式的定义一个主键来作为聚簇索引。
  • 为了充分利用聚簇索引的聚簇的特性，所以 innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUD、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

二级索引（非聚簇索引）

InnoDB索引-二级索引.png

  • 二级索引访问需要两次索引查找：第一次找到主键值，第二次根据主键值找到行数据。
  • 行记录只存放索引列 +主键列
  • 回表：通过二级索引查主键值，根据主键值在聚簇索引中获取行记录。
  • 一个表只能有一个聚簇索引。
  • 创建的二级索引不管是否包含主键索引，数据页中的记录都会包含主键记录，防止重复

联合索引

InnoDB索引-联合索引.png

  • 每条目录项记录都由c1、c2、页号这三个部分组成，各条记录先按照c1列的值进行排序，如果记录的c1列相同，则按照c2列的值进行排序。
  • B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

降序索引（8.0新特性）

 -- 支持版本：MySQL8.0，仅限InnoDB
 -- 作用：默认都是升序索引，反向扫描效率低，可能要扫面所有记录
 alter table tableA add index `idx_c1_c2`(c1 asc,c2 desc);

全文索引

 支持引擎: MyIsam-MySQL.6.4前, InnoDB-MySQL5.6.4后
 分词: 不支持中文分词
 建议: 分词检索使用 ElasticSearch

索引的区别

聚簇索引与非聚簇索引的区别

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。

在MySQL的InnoDB存储引擎中， 聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

  • 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
  • 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。

Hash索引和B+树区别

  • B+树可以进行范围查询，Hash 索引不能。
  • B+树支持联合索引的最左侧原则，Hash 索引不支持。
  • B+树支持 order by 排序，Hash 索引不支持。
  • Hash 索引在等值查询上比 B+树效率更高。（但是索引列的重复值很多的话，Hash冲突，效率降低）。
  • B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

索引设计

适合创建索引的情况

  • 字段的数值有唯一性的限制，即使是组合字段，也必须建立唯一索引（主键，唯一索引（含多字段组成））
  • 频繁作为where查询条件的字段，创建普通索引就可以大大提升查询效率
  • 经常order by的列（建立索引时已排序），如果待排序的列有多个，可以在这些列上建立组合索引。
  • 经常group by的列
  • update、delete 的 where 条件列
  • distinct 字段需要创建索引
  • 多表join，对于连接的字段创建索引，并且创建索引的字段在各表中类型必须一致
  • 使用列的类型小的创建索引：例如bigint, int, mediumint, tinyint
  • 使用字符串的前缀创建索引（前缀索引），但必须指明索引长度，如：地址信息等
    • alter table tableA add index idx_adress(adress(12))
    • select count(distinct left(adress,1)) / count(*) as qufendu from tb_company_department;
    • 越接近1越好，一般超过33%
  • 使用最频繁的列放到联合索引的左侧（最左前缀原则）
  • 在多个字段都要创建索引的情况下，联合索引优于单值索引
  • 单表索引数量不超过6个

不适合创建索引的情况

  • 在where中使用不到的字段，包括order by 和 group by，不要设置索引
  • 数据量小的表不要设置索引，如小于1000
  • 有大量重复数据的列上不要创建索引（数据重复度高于10%的时候），如：性别（区分度低）
  • 避免对经常更新的表创建过多的索引
  • where、group by、order by等后面没有使用到的字段，不需要建立索引
  • 不建议用无序的值作为索引，如身份证号、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、无序长字符串等
  • 不要定义冗余或重复的索引
    • 冗余：单列索引和联合索引包含统一字段
    • 重复：如即是唯一索引，又是普通索引

索引使用最佳方案

  • 联合索引全值匹配（and情况下无需按序，查询优化器也会自动优化）
  • 最佳左前缀法则
    • 使用联合索引必须匹配最左字段列情况下，再考虑匹配其他字段
    • 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
    • 一次查询只能使用一个索引
  • 主键按序递增插入
  • 联合索引中的范围查询字段放最后，where查询条件放置最后

索引失效

数据库优化步骤-索引失效常见原因.png

  • 计算、函数、类型转换（自动或手动），会导致索引失效

     -- 使用函数使索引失效
 select * from tb_relation_student where left(STUDENT_NAME, 3) = 'Ken';
 ​
 -- 类型转换使索引失效：String 20 转 int 20
 EXPLAIN select * from tb_relation_student where AGE = '20'; 
 ​
 -- 不等于(!= 或 <>)使索引失效
 EXPLAIN select * from tb_relation_student where SID <> 20;

  • 范围条件右边的索引列失效

     create index idx_age_classid_name on student(age,classid,name);
 -- 联合索引中name不会被使用过滤
 select * from student where age = 10 and classid > 10 and name = 'abc'

  • 不等于(!= 或 <>)使索引失效

  • is null 可以使用索引，is not null 无法使用索引，not like 无法使用索引

  • like 以通配符%开头使索引失效

  • or 情况下前后存在非索引的列，索引失效

  • 数据库和表的字符集不统一会使索引失效（不同字符集比较前需类型转换）

大表如何添加索引

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？

我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：

  1. 先创建一张跟原表A数据结构相同的新表B。
  2. 在新表B添加需要加上的新索引。
  3. 把原表A数据导到新表B
  4. rename新表B为原表的表名A，原表A换别的表名；

覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

例如：查询SQLselect * 修改为 select id, age，因为id和age的值都在idx_age索引树的叶子节点上，其实是不需要回表的。

什么是回表

当查询的数据在索引树中找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。

例如：需要查询所有列的数据idx_age普通索引不能满足，需要拿到主键id的值后，再回到id主键索引查找获取，这个过程就是回表

SQL是否走索引查询

explain查看SQL的执行计划，这样就知道是否命中索引了。

当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。

一般来说，我们需要重点关注type、rows、filtered、extra、key。

数据库优化步骤-explain执行计划显示列.png

type

type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
  • const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
  • eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
  • ref : 常用于非主键和唯一索引扫描。
  • ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
  • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
  • unique_subquery：类似于eq_ref，条件用了in子查询
  • index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
  • range：常用于范围查询，比如：between ... and 或 In 等操作
  • index：全索引扫描
  • ALL：全表扫描

rows

该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。

filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

extra

该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

  • Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
  • Using index ：表示是否用了覆盖索引。
  • Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
  • Using where : 表示使用了where条件过滤.
  • Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

key

该列表示实际用到的索引。一般配合possible_keys列一起看。

索引下推

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

Mysql5.6之前版本.png

有些朋友可能觉得奇怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6版本之后，选出包含“小”字后，顺表过滤age=28

Mysql5.6之后版本.png

avatar
文章
阅读
粉丝