本专题讲到索引查询优化, 恭喜你, 已经达到 MySQL 优化的中级水平. 这篇我们要讲的是 MySQL 优化中重点中的重点 -- 索引优化. 面试官百分百必问
目录
多关于索引, 分为以下几点来讲解:
一, 索引的概述(什么是索引, 索引的优缺点)
二, 索引的基本使用(创建索引)
三, 索引的基本原理(面试重点)
四, 索引的数据结构(B 树, hash)
五, 创建索引的原则(重中之重, 面试必问! 敬请收藏!)
六, 百万级别或以上的数据如何删除
一, 索引的概述
1)什么是索引?
索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分), 它们包含着对数据表里所有记录的引用指针. 更通俗的说, 索引就相当于目录. 当你在用新华字典时, 帮你把目录撕掉了, 你查询某个字开头的成语只能从第一页翻到第一千页. 累! 把目录还给你, 则能快速定位!
2)索引的优缺点:
可以大大加快数据的检索速度, 这也是创建索引的最主要的原因., 且通过使用索引, 可以在查询的过程中, 使用优化隐藏器, 提高系统的性能. 但是, 索引也是有缺点的: 索引需要额外的维护成本; 因为索引文件是单独存在的文件, 对数据的增加, 修改, 删除, 都会产生额外的对索引文件的操作, 这些操作需要消耗额外的 IO, 会降低增 / 改 / 删的执行效率.
二, 索引的基本使用
1)创建索引:(三种方式)
第一种方式:
第二种方式: 使用 ALTER TABLE 命令去增加索引:
ALTER TABLE 用来创建普通索引, UNIQUE 索引或 PRIMARY KEY 索引.
其中 table_name 是要增加索引的表名, column_list 指出对哪些列进行索引, 多列时各列之间用逗号分隔.
索引名 index_name 可自己命名, 缺省时, MySQL 将根据第一个索引列赋一个名称. 另外, ALTER TABLE 允许在单个语句中更改多个表, 因此可以在同时创建多个索引.
第三种方式: 使用 CREATE INDEX 命令创建
CREATE INDEX 可对表增加普通索引或 UNIQUE 索引.(但是, 不能创建 PRIMARY KEY 索引)
三, 索引的基本原理(不想像别的文章那样一大堆篇幅废话)
索引用来快速地寻找那些具有特定值的记录. 如果没有索引, 一般来说执行查询时遍历整张表.
索引的原理很简单, 就是把无序的数据变成有序的查询
1, 把创建了索引的列的内容进行排序
2, 对排序结果生成倒排表
3, 在倒排表内容上拼上数据地址链
4, 在查询的时候, 先拿到倒排表内容, 再取出数据地址链, 从而拿到具体数据
四, 索引的数据结构(b 树, hash)
1)B 树索引
MySQL 通过存储引擎取数据, 基本上 90% 的人用的就是 InnoDB 了, 按照实现方式分, InnoDB 的索引类型目前只有两种: BTREE(B 树)索引和 HASH 索引. B 树索引是 MySQL 数据库中使用最频繁的索引类型, 基本所有存储引擎都支持 BTree 索引. 通常我们说的索引不出意外指的就是 (B 树) 索引(实际是用 B + 树实现的, 因为在查看表索引时, MySQL 一律打印 BTREE, 所以简称为 B 树索引)
查询方式:
主键索引区: PI(关联保存的时数据的地址)按主键查询,
普通索引区: si(关联的 id 的地址, 然后再到达上面的地址). 所以按主键查询, 速度最快
B+tree 性质:
1.)n 棵子 tree 的节点包含 n 个关键字, 不用来保存数据而是保存数据的索引.
2.)所有的叶子结点中包含了全部关键字的信息, 及指向含这些关键字记录的指针, 且叶子结点本身依关键字的大小自小而大顺序链接.
3.)所有的非终端结点可以看成是索引部分, 结点中仅含其子树中的最大 (或最小) 关键字.
4.)B+ 树中, 数据对象的插入和删除仅在叶节点上进行.
5.)B + 树有 2 个头指针, 一个是树的根节点, 一个是最小关键码的叶节点.
2)哈希索引
简要说下, 类似于数据结构中简单实现的 HASH 表 (散列表) 一样, 当我们在 MySQL 中用哈希索引时, 主要就是通过 Hash 算法(常见的 Hash 算法有直接定址法, 平方取中法, 折叠法, 除数取余法, 随机数法), 将数据库字段数据转换成定长的 Hash 值, 与这条数据的行指针一并存入 Hash 表的对应位置; 如果发生 Hash 碰撞(两个不同关键字的 Hash 值相同), 则在对应 Hash 键下以链表形式存储. 当然这只是简略模拟图.
ps: 关于数据结构, 有兴趣深入的朋友可以关注我后查看[数据结构] 专题, 这里不做详细讲解.
五, 创建索引的原则(重中之重)
索引虽好, 但也不是无限制的使用, 最好符合一下几个原则
1) 最左前缀匹配原则, 组合索引非常重要的原则, MySQL 会一直向右匹配直到遇到范围查询 (>,<,between,like) 就停止匹配, 比如 a = 1 and b = 2 and c> 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引, d 是用不到索引的, 如果建立 (a,b,d,c) 的索引则都可以用到, a,b,d 的顺序可以任意调整.
2)较频繁作为查询条件的字段才去创建索引
3)更新频繁字段不适合创建索引
4)若是不能有效区分数据的列不适合做索引列(如性别, 男女未知, 最多也就三种, 区分度实在太低)
5)尽量的扩展索引, 不要新建索引. 比如表中已经有 a 的索引, 现在要加 (a,b) 的索引, 那么只需要修改原来的索引即可.
6)定义有外键的数据列一定要建立索引.
7)对于那些查询中很少涉及的列, 重复值比较多的列不要建立索引.
8)对于定义为 text,image 和 bit 的数据类型的列不要建立索引.
百万级别或以上的数据如何删除
关于索引: 由于索引需要额外的维护成本, 因为索引文件是单独存在的文件, 所以当我们对数据的增加, 修改, 删除, 都会产生额外的对索引文件的操作, 这些操作需要消耗额外的 IO, 会降低增 / 改 / 删的执行效率. 所以, 在我们删除数据库百万级别数据的时候, 查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的.
所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)
然后删除其中无用数据(此过程需要不到两分钟)
删除完成后重新创建索引 (此时数据较少了) 创建索引也非常快, 约十分钟左右.
与之前的直接删除绝对是要快速很多, 更别说万一删除中断, 一切删除会回滚. 那更是坑了.
今天, 索引的讲解就到这里, 重点提一下, 索引基本原理和创建索引的原则是重点, 面试基本必问! 大家可以收藏好多理解理解. 本号内有多个专题, 如[数据结构] ,[netty 专题] ,[dubbo 专题] ,[MySQL 优化专题] ,[Redis 专题] ,[高并发专题] 等优质好文. 觉得有收获的同学来个关注.
来源: https://juejin.im/post/5ba8f9906fb9a05cdc498acd