索引优化剖析

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.idunionselect * 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 nullunionselect * 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索引是没有序的，就不能通过二分查找找到。（范畴前面的生效，这个前面是看联结索引的程序的）