关于mysql:二MySQL索引优化分析

41次阅读

共计 8015 个字符，预计需要花费 21 分钟才能阅读完成。

索引优化剖析

join 查问

SQL 执行程序

mysql 从 FROM 开始执行~

join 连贯查问

7 种 JOIN 示例

建表
  • tbl_dept 表构造（部门表）
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)
  • tbl_emp 表构造（员工表）
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)
笛卡尔积
  1. tbl_emp 表和 tbl_dept 表的笛卡尔乘积：select * from tbl_emp, tbl_dept;
  2. 其后果集的个数为：5 * 8 = 40
inner join

tbl_emp 表和 tbl_dept 的交加局部（公共局部）

select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;

员工表的部门编号 和 部门表的 id 编号，就是他们的公共局部。

left join

tbl_emp 与 tbl_dept 的公共局部 + tbl_emp 表的独有局部

select * from tbl_emp e left join tbl_dept d on e.deptId = d.id

right join

tbl_emp 与 tbl_dept 的公共局部 + tbl_dept 表的独有局部

select * from tbl_emp e right join tbl_dept d on e.deptId = d.id

left join without common part

tbl_emp 表的独有局部。

员工表要独占，有部门表什么事吗？所以用 where d.id is null 去掉部门表的内容即可。

select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null

right join without common part

tbl_dept 表的独有局部。

部门表要独占，有员工表什么事吗？所以用 where e.deptId is null 去掉员工表的内容即可。

select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.deptId is null

full join
  • MySQL 不反对 full join，然而咱们能够用 union 关键字连贯后果集，并且主动去重。
  • 将 left join 的后果集和 right join 的后果集应用 union 合并即可
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;

full join without common part

tbl_emp 表的独有局部 + tbl_dept 表的独有局部。

select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null
union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.deptId is null

索引简介

索引是什么

  1. MySQL 官网对索引的定义：索引是帮忙 MySQL 高效获取数据的 数据结构。
  2. 简略了解为“排好序的疾速查找的数据结构”，索引 = 排序 + 查找
  3. 一般来说索引自身占用内存空间很大，因而索引往往 以文件模式存储在硬盘上
  • 咱们平时所说的索引，如果没有特地指明，都是指 B 树（多路搜寻树）构造组织的索引。
  • 汇集索引，主要索引，笼罩索引，复合索引，前缀索引，惟一索引默认都是应用 B + 树索引，统称索引。除了 B + 树这种类型的索引之外，还有哈希索引（hash index）等。

索引原理

  • 索引 以某种形式指向数据，这样就能够在这些数据结构上实现高级查找算法。

下图就是一种可能的索引形式：

为了放慢 col2 的查找，能够保护左边所示的二叉查找树，每个节点别离蕴含索引键值和一个指向对应数据物理地址的指针。

索引优劣势

  • 劣势

    1. 疾速找到数据，升高数据库的 IO 老本
    2. 通过索引列对数据进行排序，升高数据排序老本，升高了 CPU 的耗费

  • 劣势

    1. 实际上索引也是一张表，该表保留了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
    2. 以表中的数据进行增、删、改的时候，索引也要动静的保护，这就 升高了更新表的速度。

MySQL 索引分类

  • 单值索引：最根本的索引，即一个索引只蕴含单个列，一个表能够有多个单列索引。（倡议一张表索引不超过 5 个，优先思考复合索引）
  • 惟一索引：索引列的值必须惟一，但容许有空值。（例如：身份证号不能反复）
  • 复合索引：即一个索引蕴含多个列。
  • 主键索引：一个表只能有一个主键，不容许有空值。个别是在建表的时候同时创立主键。
  • 全文索引：次要用来查找文本中的关键字，而不是间接与索引中的值相比拟。fulltext 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简略的 where 语句的参数匹配。

MySQL 索引构造

  • BTree 索引
  • Hash 索引
  • full-text 索引（全文索引）
  • R-Tree 索引（空间索引）

索引实现是用 B+Tree，MySQL 中看到的索引index_type=BTree 也就是个索引类型的名词；而真正的底层实现是用的 B +tree

  • 为什么不必 hash 表构造？

    • hash 的值是无序的值，不能进行范畴查找。如果要排序操作，也不能用哈希值去排序。

  • 为什么不必均衡二叉树构造？

    • 随着树的高度减少，查找速度会越来越慢。而且范畴查问是在二叉树间 盘旋查找，速度也不快

  • 为什么不必 B 树结构？

    • 高度相比于均衡二叉树矮，查找速度会更快。然而范畴查问会 盘旋查找 的问题仍然存在。

BTree 构造

BTree 又叫多路均衡搜寻树，以 5 叉 BTree 为例，当 k 元素大于 4 时，两头节点决裂到父结点，两边节点决裂。

插入 C N G A H E K Q M 数据为例：

  • 因为 key 最大为 4，咱们先插入 4 个

  • 而后插入 H，H 依据大小放在 G 和 N 两头，这时 n >4，两头元素 G 向上决裂为父节点，两边节点决裂。

  • 插入 E，K，Q 不须要决裂

  • 插入 M，两头元素 M 向上决裂到父结点 G

BTREE 树和二叉树相比，查问数据的效率更高，因为对于雷同的数据量来说，BTREE 的层级构造比二叉树小，因而搜寻速度快。（二分查找）

B+TREE 构造

B+Tree 与 BTree 的区别
  1. B+Tree 只有叶子节点上存放数据，非叶子节点寄存索引就行
  2. B+Tree 的叶子节点间还有一个 链表 将所有的叶子节点连接起来，不便遍历
  3. BTree 中所有节点都有索引和数据，这样带来的问题是，如果数据过大，会影响索引的存储，从而可能深度会更高，影响 I / O 的读取效率。

  • B+ 树的查问效率更加稳固

    • 在 BTree 中，越凑近根节点的记录查找时间越快。

      而 B +Tree 中每个记录的查找时间根本是统一的，都须要从根节点走到叶子节点。

  • B+ 树的磁盘读写代价更低

    • 然而在理论利用中却是 B +Tree 性能比拟好，因为 B +Tree 的非叶子节点不存放数据，这样每个节点可包容的元素就比 BTree 多，树高就比 BTree 小，这样的益处是缩小磁盘拜访次数（一次磁盘拜访的工夫相当于成千盈百次内存比拟的工夫）。
    • 而且 B + 树的叶子节点应用指针连贯在一起，不便程序遍历（范畴搜寻）

聚簇索引和非聚簇索引

聚簇索引（InnoDB）

  • 将数据存储与索引放到一块，索引构造的叶子节点保留了行数据。表数据装置索引的程序来存储，也就是说索引的程序和表中记录的程序统一。
  • InnoDB 中，在聚簇索引之上创立的索引被称为辅助索引，像复合索引，前缀索引，惟一索引等等。

聚簇索引默认是主键 ，如果表中没有主键，InnoDB 会抉择一个 惟一的非空索引 代替，如果没有，InnoDB 会在外部生成一个 隐式 的聚簇索引。

非聚簇索引（MyISAM）

将数据与索引离开，表数据记录程序与索引程序无关。

MyISAM 索引查问数据过程

  • 非聚簇索引的索引和数据是离开的，有两个文件。

  • B+ 树的每一个节点就是咱们的主键 id，最初一层是叶子节点，叶子节点下面存储的是 数据的物理地址。通过地址在数据文件外面查问相应的数据。

InnoDB 索引查问数据过程

  • 聚簇索引的索引和数据是存储到同一个文件外面的。

  • 右边的是一个主键索引，也叫聚簇索引。左边的是一个辅助索引。
  • 聚簇索引：B+ 树的每一个节点就是咱们的主键 id，最初一层是叶子节点，叶子节点上存储的是 实在的数据。例如我查找 id 等于 15，我找到这个叶子节点后，能把 id=15 这一行的数据全副拿进去。
  • 辅助索引：以 name 字段为索引，B+ 树的每一个节点，存储的是相应的名字，然而叶子节点上存储的是 主键 id 值，这就是聚簇索引和辅助索引的区别。而后通过主键的 id 值，去聚簇索引上查找相应的数据。
  • 如果查问语句是 select * from table where ID = 21，即主键查问的形式，则只须要搜寻 ID 这棵 B+ 树。
  • 如果查问语句是 select * from table where name=’hce’，即非主键的查问形式，则先搜寻 name 索引树，失去 ID，再到 ID 索引树搜寻一次，这个过程也被称为回表。

从图中不难看出，主键索引和非主键索引的区别是：非主键索引的叶子节点寄存的是 主键的值 ，而主键索引的叶子节点寄存的是 整行数据 ，其中非主键索引也被称为 辅助（二级）索引 ，而主键索引也被称为 聚簇索引。

索引的创立与否

哪些状况下适宜建设索引

  1. 主键主动建设惟一索引
  2. 频繁作为查问的条件的字段应该创立索引
  3. 查问中与其余表关联的字段，外键关系建设索引
哪些状况不适宜建设索引
  1. 表记录太少
  2. 更新太频繁地字段不适宜创立索引，除了更新数据，还会主动的更新索引。
  3. Where 条件里用不到的字段不创立索引

  4. 唯一性太差的字段不适宜建设索引（例如登录状态，性别字段）

    例如性别字段，因为只有两个值，比拟很简略，排序也不便，加索引所耗费的系统资源比不加更多。

    意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

性能剖析

MySQL 常见瓶颈

CPU：CPU 饱和的时候，个别产生在数据装入在内存或从磁盘上读取数据的时候

IO：磁盘 I / O 瓶颈产生在 装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top、free、iostat和 vmstat 来查看零碎的性能状态

Explain 详解

实用 Explain 能够模仿优化器执行 SQL 语句，从而晓得 MySQL 是如何解决这条 SQL 语句的。

explain select * from tbl_emp;

字段解释：

  • id（示意查问中执行 select 子句或操作表的程序）

    • id 雷同，执行程序由上至下
    • id 不同，如果是子查问，id 序号会递增。id 值越大优先级越高，越先被执行。

  • select_type（查问的类型）

    1. simple：简略的 select 查问
    2. primary：如果蕴含任何简单的子局部，最外层查问被标记为 primary
    3. subquery：在 select 或 where 列表中蕴含了子查问
    4. derived：在 FROM 列表中蕴含的子查问被标记为 derived（衍生）
    5. union：若第二个 select 呈现在 union 之后，则被标记为 UNION；若 UNION 蕴含在 FROM 子句的子查问中，外层 select 将被标记为：DERIVED
    6. union result：从 UNION 表获取后果的 select
  • table（显示这一行的数据是哪张表的）

  • type（拜访类型排列）

    • 从最好到最差顺次是：system > const > eq_ref > ref > range> index > ALL
    • system：表只有一行记录，这是 const 类型的特例，平时不会呈现
    • const：示意通过索引一次就找到了
    • eq_ref：唯一性索引，常见于主键或惟一索引扫描
    • ref：非惟一索引扫描
    • range：只检索给定范畴的行，应用一个索引来抉择行，个别是 where 中呈现了 between,<,>,in 等
    • index：从索引中读取全表
    • all：从硬盘数据库文件中读取全表
  • possible_keys（显示 可能 利用在这张表中的索引，一个或多个）
  • key（理论 应用的索引，如果为 null，则没有应用索引）
  • key_len（key_len 显示的值为 索引最大可能长度，并非理论应用长度）
  • ref（显示索引哪一列被应用了）
  • rows（估算出找到所需的记录所须要读取的行数）

  • Extra（蕴含不适宜在其余列中显示但非常重要的额定信息）

    • Using filesort 文件排序：MySQL 无奈利用索引实现排序，须要尽快优化！
    • Using temporary 创立长期表：应用了长期表保留两头后果，须要尽快优化！！
    • Using index 笼罩索引：示意相应的 select 操作中应用了笼罩索引，防止拜访了表的数据行，效率不错
    • Using where：表明应用了 WHERE 过滤
    • Using join buffer：表明应用了连贯缓存
    • impossible where：where 子句的值总是 false，不能用来获取任何元组

索引生效

建表 SQL

CREATE TABLE staffs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(24)NOT NULL DEFAULT''COMMENT' 姓名 ',
    `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
    `pos` VARCHAR(20) NOT NULL DEFAULT''COMMENT' 职位 ',
    `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职工夫'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

# 创立索引
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

索引生效判断准则

  • 要遵循最左前缀法令

    • 查问从索引的最左前列开始，并且不跳过索引中的列。例如咱们创立了复合索引(name,age,pos)，WHERE 查问时，要从 name 开始；如果只查问 pos 是不走索引的。
    • 和 WHERE 前面的程序无关
    • 跳过两头的 age 也会走索引，然而因为违反了最左前缀法令，理论只走了 name 索引。

  • 在索引列上进行任何操作，会导致索引生效

    • 蕴含 计算、应用函数、类型转换等
    • 例如应用 substring函数 截取子串。
  • 应用 is null，is not null 可能索引生效

  • % 或者 _ 结尾的 Like 含糊查问，索引生效

    • 只有不是放在头部，放在其余中央索引不会生效
    • 解决办法：应用笼罩索引
  • 应用 or，如果有局部条件没有索引，索引都生效
  • 应用不等于!=、<>，以后的索引会生效
  • 如果是 var 类型的数字，查问时要用单引号括起来，不然会主动类型转换，使得索引生效。

尽量应用 笼罩索引（只拜访索引的查问（索引列和查问列统一）），缩小 select*

益处：如果一个索引蕴含所有须要的查问的字段的值，间接依据索引的查问后果返回数据，而无需比照，可能极大的进步性能。

索引的案例剖析

  • test03 表中的测试数据
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1   | c2   | c3   | c4   | c5   |
+----+------+------+------+------+------+
|  1 | a1   | a2   | a3   | a4   | a5   |
|  2 | b1   | b2   | b3   | b4   | b5   |
|  3 | c1   | c2   | c3   | c4   | c5   |
|  4 | d1   | d2   | d3   | d4   | d5   |
|  5 | e1   | e2   | e3   | e4   | e5   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
# 给 test03 表创立索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);

咱们创立了复合索引 idx_test03_c1234，依据以下 SQL 剖析下索引应用状况？

  • SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
    • 全值匹配，索引全失效
  • SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
    • 尽管程序不同，然而 mysql 的优化器会进行优化，索引全失效
  • SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
    • c3 列字段应用了索引进行排序，没有进行查找，导致 c4 无奈用索引进行查找。应用到了 3 个索引，其中一个用于排序。（范畴之后全生效）
  • SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
    • 和下面类似，c3 字段将索引用于排序，c4 列没用到索引。
  • SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
    • 索引在 c3 处断了，c4 的排序没用到索引，显示了 Using filesort（文件排序），必须优化
  • SELECT * FROM test03 WHERE c1='a1' ORDER BY c2, c3;
    • 只用到了 c1 索引，然而 c2、c3 用于排序且程序正确。无 filesort
  • SELECT * FROM test03 WHERE c1='a1' ORDER BY c3, c2;
    • 呈现了 filesort，咱们建的索引是 1234，它没有依照程序来，32 颠倒了
  • SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3, c2;
    • 为什么 c3, c2 颠倒了然而没有呈现 filesort？
    • 因为查问条件c2='a2' ，我都把 c2 查问进去了，就不必排序了。应用了 3 个索引，1 个用于给 c3 排序。
  • group by 外表上叫分组，分组之前必排序，group by 和 order by 在索引上的问题根本是一样的。
  • SELECT * FROM test03 WHERE c1='a1' and c2 like '%kk';
    • 只应用到了 c1 索引，c2 索引生效了
  • SELECT * FROM test03 WHERE c1='a1' and c2 like 'k%k%';
    • 应用到了 c1 和 c2 索引，因为不是以 % 结尾的，能够了解为常量。

索引生效的底层原理

  • 索引生效的状况次要是针对复合索引。
  • 右边的值是有程序的：112233

    左边的值是没有程序的：121412

  • 因为排序是先排右边的 a 索引，只有在 a 相等的状况下，b 索引才是有序的。

    • 如果不遵循最左前缀准则，在没有 a 的状况下，b 必定是无序的。就不能在一个无序的 B + 树下面找到须要的值。
    • 如果应用范畴查找，a>1 and b=1。当咱们找到 a >1 的索引后，b 索引是没有序的，就不能通过二分查找找到。（范畴前面的生效，这个前面是看联结索引的程序的）

正文完
mysql
发表至： mysql
2021-02-22
 0
关于mysql:如何学习MySQL这几本书初学者必看
关于mysql:Mysql-实战笔记-二-实践1
关于mysql:MysqlMVCC篇
关于mysql:MySQL索引原理及实战