MySQL中的索引之分类,原理,作用,优缺点和执行计划

2023-12-30 13:46:45

索引

  • 索引的作用:加速查找
    • 例如: 300w条数据的表中查询,无索引需要700s, 利用索引可能只需要1s
    • 用索引的时机是,数据量巨大,并且搜索快速
  • 索引为什么能实现加速查找
    • 基于索引的内部存储结构
    • 索引底层基于 B+Tree 的数据结构存储的
    • 在创建一张表的时候,将数据按照先后顺序放在一个文件里
    • 如果你为表里的数据创建索引后,会将数据生成额外的数据结构
    • 在这个数据结构中,将索引的这一列按照另外的规则进行存储,即 B+Tree 的结构
  • 只要给一个 字段添加索引,就会为这个字段新增一个 B+Tree 的结构
  • 背后的缺点是额外维护数据结构,并且新增或删除数据时,速度比之前要慢
  • 只有查找会变快,新增,修改,删除都会变慢
  • MySQL中的索引都是 基于 B+Tree 实现的
  • 在MySQL中,如果要创建一张表,可以指定不同的引擎
    • myisam 引擎,用的是非聚簇索引 (数据和索引结构 分开存储)
      • 在这个表里创建索引,称为非聚簇索引
      • 表是表,索引结构式索引结构,拆开放的
    • innodb 引擎,用的是 聚簇索引 (数据和主键索引结构存在一起)
      • 创建表的时候,实际上是没有表的,而是将主键通过树形结构存储起来
      • 没有这个表的,节点不仅存储主键,而且把每行存储的信息存储在上面
  • 基于两种引擎创建的表,底层都用 B+Tree 来存储,但是存储中也是不太一样的
  • 有了索引结构的查询效率,比表中逐行查询的速度要快很多,且数据量越大越明显
  • https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

mysiam引擎

create table 表名(
  id int not null auto_increment primary key,
  name varchar(32) not null,
  age int
) engine=myisam default charset=utf8;
  • 在索引中的节点存储了表中数据行的内存地址, 这样就可以直接找到当前行的数据

聚簇引擎

create table 表名(
  id int not null auto_increment primary key,
  name varchar(32) not null,
  age int
) engine=innodb default charset=utf8;
  • 根据索引结构,通过主键拿到当前行的所有数据
  • 如果除了主键索引,还需要创建如 name 的索引
  • 这时候,就会创建一个辅助索引,生成另外一套数据结构
  • 按照name来存放,会存储主键id, 再根据主键根据主键索引文件中查找

两种索引的文件对比

  • 开发中,一般使用 innodb 引擎,支持事务,行级锁,外键等特点

  • 在mysql5.5之后,所有默认引擎也是 innodb

  • 可以找到 mysql的安装目录,比如:/usr/local/mysql/data/userdb

  • 常见不同引擎的表,生成的文件也不一样

  • 比如 big 这张表,是基于 innodb 引擎的

    • big.frm 表结构
    • big.ibd 数据和索引结构
  • 对于表 t2 是 myisam 引擎的话

    • t2.frm 表结构
    • t2.MYD 数据
    • t2.MYI 索引结构
    • 它的底层帮我们创建3个文件
  • 我们后续用的 innodb ,都是聚簇索引

索引的优缺点

  • 优点: 查找速度快,约束 (主键, 唯一, 联合唯一)
  • 缺点: 插入、删除、更新速度比较慢, 因为每次操作都需要调整整个B+Tree的数据结构关系
  • 所以,在表中不要无节制的创建索引,不使用索引反而会适得其反

查询要命中索引

  • 比如有一张300w数据量的用户表

表结构示例

create table `users` (
  `id` int(11) not null auto_increment,
  `name` varchar(32) default null,
  `email` varchar(64) default null,
  `password` varchar(64) default null,
  `age` int(11) default null,
  primary key (`id`),         --- 主键索引
  unique key `big_unique_email` (`email`),         --- 唯一索引
  index `ix_name_pwd` (`name`, `password`),         --- 联合索引
) engine=InnoDB default charset=utf8;
  • 以上表结构中有三个索引,2个 key, 1个 index(普通)
  • 一般,基于索引列搜索都可命中索引,加速查找
  • 注意,联合索引,查询其中之一也是快的

查询示例

select * from big where id=5;
select * from big where id>5;
select * from big where email='xxxx@qq.com';
select * from big where name='xxx';
select * from big where name='sss' and password='ssdd';

命中索引的场景

  • 以下几种情况是常用的是否命中索引的场景

1 )类型不一致场景

select * from users where name = 123;     -- 不会命中索引
select * from users where email = 123;    -- 不会命中索引

-- 下面用主键
select * from users where id='123';       -- 会命中索引

2 )使用不等于

select * from users where name != 'xxxx';            -- 不会命中索引
select * from users where email != 'xxxx@qq.com';    -- 不会命中索引

-- 主键
select * from users where id != 123;                 -- 不会命中索引

3 )使用 or

select * from users where id = 123 or password = 'x';     -- 不会命中 这里后面联合索引中只用了一个
select * from users where name = 'xx' or password = 'y';    -- 不会命中 用 or 将联合索引拆成了两个

-- 下面会命中
select * from users where id = 10 or password='xx' and name='yy';  -- 命中 这里 or 前后都是索引

4 )使用排序

  • 根据索引排序时,选择的映射列不是索引,则不走索引
select * from users order by name asc;       -- 未命中
select * from users order by name desc;      -- 未命中

-- 主键会命中
select * from users order by id desc;        -- 会命中

5 )like 模糊匹配时

  • 通配符在最后面可以命中
select * from users where name like '%xxx'       -- 不会命中
select * from users where name like '_xxx'       -- 不会命中
select * from users where name like 'xx%xx'      -- 不会命中

-- 通配符在最后,会命中
select * from users where name like "xxxx%"      -- 命中
select * from users where name like "xxxx_"      -- 命中

6 )使用函数

select * from users where reverse(name) = 'xxxx'; --- 不会命中

-- 特别的
select * from users where name = reverse('abc')  -- 会命中

7 )联合索引

  • 如果是联合索引,最遵循最左前缀原则

  • 如果联合索引为 (name, password)

    • name and passsword 命中
    • name 命中
    • password 不会命中
    • name or password 不会命中
  • 最左边用可以命中,用or连接则不能

关于执行计划 explain

  • mysql中提供了执行计划, 用于预判sql的执行效率
  • 不能准确预判,只作为参考
  • 语法:explain sql语句

1 )使用

  • explain select * from users
  • 这会输出当前sql的分析表格

2 )解析 type

  • 基于输出表格字段中的 type 来看,它是一个重要的性能指标

    • 其值的性能依次排序为:
    • all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
  • 详解如下

    • ALL,全表扫描,数据从头到尾找一遍,(一般没有命中索引,都会执行全部扫描)
      • select * from users 全部扫描
      • select * from users limit 1; 这里特别,遇到 limit 结束后不再扫描
    • INDEX, 全索引扫描,对索引从头到尾扫描一遍
      • explain select id from users;
      • explain select name from users;
    • RANGE, 对索引列进行范围查找
      • explain select * from users where id > 10;
      • explain select * from users where id in (1,2,3);
    • INDEX_MERGE 合并索引,即使用了多个单列索引
      • explain select * from users where id = 10 or name='xxx'
    • REF, 根据索引直接去查找 (非键)
      • select * from users where name = 'xxx'
    • EQ_REF, 连表操作时常见, 也是根据索引查询
      • explain select article.title, users.id from article left join users on user.id = article.uid
    • CONST, 常量,表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快
      • explain select * from users where id = 123; 这里是主键
      • explain select * from users email = 'xxxx@qq.com 唯一索引
    • SYSTEM, 系统,表仅有一行(=系统表) 这里是 const连接类型的一个特例
      • explain select * from (select * from users where id=1 limit 1) as A;
  • 综合以上,一般来说,性能在 RANGE 及其以上,性能算是 OK的

  • 当然,这不是最终结果,只是初步的评价,和最终效率一定会有差异

3 )其他字段

  • id 执行顺序
  • select_type 查询类型
    • SIMPLE 简单查询
    • PRIMARY 最外层查询
    • SUBQUERY 映射为子查询
    • DERIVED 子查询
    • UNION 联合
    • UNION RESULT 使用联合的结果
  • table 正在访问的表名
  • partitions, 涉及的分区,不常用,mysql将数据划分到不同的 idb文件中,箱单与数据的拆分
    • 分区是指,一个特别大的文件拆分成多个小文件
  • possible_keys, 查询涉及到的字段上若存在索引,则该索引将被列出
    • 即:可能使用的索引
  • key, 在查询中实际使用的索引,若没有使用索引,显示为 null
    • 比如,有索引,但未命中,则 possible_keys显示,key则显示为 null
  • key_len, 表示索引字段最大可能的长度
    • 类型字节长度 + 变长2 + 可空1
    • 例如,key_ken = 195, 类型 varchar(64)
    • 195 = 64 * 3 + 2 + 1
  • ref, 连表时显示的关联信息
    • 例如,A和B连表,显示连表的字段信息
  • rows, 估计读取的数据行数 (只是预估值)
  • filtered, 返回结果的行栈需要读到行的百分比
    • explain select * from users where id = 1; 100, 这里只读了一行,返回结果也是1行
    • explain select * from big where password = 'xxx' 10, 读取了10行,返回了1行,注意,这里 xxx的 password在第10行
  • extra, 该列包含mysql解决查询的详细信息
    • Using index 表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index的访问类型弄混了
    • Using where
      • 表示mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里设计索引中的列
      • 当(并且如果)它读取索引时,就能被存储引擎检验
      • 因此,不是所有带 where 子句的查询都会显示 Using where
      • 有时,Using where的出现就是一个暗示,查询可受益于不同的索引
    • Using temporary
      • mysql在对查询结果排序时会使用一个临时表
    • Using filesort
      • mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行
      • mysql有两种文件排序算法,这两种排序方式都可以在内存或磁盘上完成
      • explain 不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成
    • Range checked foreachrecord(index map: N)
      • 这意味着没有好用的索引,新的索引将在连接的每一行上重新估算
      • N是显示在possible_keys列中索引的位图,并且是冗余的

文章来源:https://blog.csdn.net/Tyro_java/article/details/135303948
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。