MySQL-索引及优化

最新推荐文章于 2024-09-05 14:17:33 发布

little_Punk

最新推荐文章于 2024-09-05 14:17:33 发布

阅读量83

点赞数

文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/weixin_54848834/article/details/134106406

版权

索引及优化

一.SQL性能下降原因

性能下降，导致SQL慢、执行时间长、等待时间长

1.数据过多

分库分表

2.关联了太多的表，太多join

SQL优化

3.没有充分利用到索引

索引建立

4.服务器调优及各个参数设置

调整my.cnf

二.SQL预热 - 常见通用的join查询

1.SQL执行顺序

1.1 编写顺序

在这里插入图片描述

1.2 执行顺序

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
下面是经常出现的查询顺序：

在这里插入图片描述

2. join图

在这里插入图片描述

2. 建表SQL

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 empno int  not null,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);

3. 7种JOIN

-- 1.所有有门派的人员信息 （A、B两表共有） 
select * from t_emp a inner join t_dept b on a.deptId = b.id; 

-- 2.列出所有用户，并显示其机构信息 （A的全集）
select * from t_emp a left join t_dept b on a.deptId = b.id; 

-- 3.列出所有门派 （B的全集）
select * from  t_dept  b  

-- 4.所有不入门派的人员 （A的独有）
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 

-- 5.所有没人入的门派 （B的独有）
 select * from t_dept b left join  t_emp a on a.deptId = b.id where a.deptId is null;  

-- 6.列出所有人员和机构的对照关系(AB全有)
MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

-- 7.列出所有没入派的人员和没人入的门派（A的独有+B的独有）
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.id IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.deptId IS NULL;

练习：增加掌门字段

ALTER TABLE `t_dept` add  CEO  INT(11)  ;
 
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;


-- 求各个门派对应的掌门人名称
 
-- 求所有当上掌门人的平均年龄
 
-- 求所有人物对应的掌门名称

三.索引简介

1.索引的概念

1.MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？

是不是觉得如果没有索引，这个事情根本无法完成？

2.你可以简单理解为==“排好序的快速查找的数据结构”==。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

下图就是一种可能的索引方式示例：

在这里插入图片描述

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

3.一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

2.MySQL索引分类

2.1 按数据结构分类

B+tree索引、Hash索引

B+tree与B-tree的对比

B+tree 非叶子节点只存储键值信息，数据记录都存放在叶子节点中。而B-tree的非叶子节点也存储数据。所以B+tree单个节点的数据量更小，在相同的磁盘I/O次数下，能查询更多的节点。B+tree 所有叶子节点之间都采用单链表连接。适合MySQL中常见的基于范围的顺序检索场景，而B-tree无法做到这一点。

B+tree与红黑树的对比

红黑树是二叉树，节点子节点个数为两个，意味着其搜索复杂度为 O(logN)，树的高度也会比 B+tree 高出不少，因此红黑树检索到目标数据所需经历的磁盘I/O次数更多。

B+tree与Hash的对比

1.由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

2.由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

3.对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4.Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

2.2 按物理存储分类

MySQL索引按叶子节点存储的是否为完整表数据分为：聚簇索引、非聚簇索引（二级索引、辅助索引）。全表数据存储在聚簇索引中，聚簇索引以外的其他索引叫做二级索引，也叫辅助索引。

总结：聚簇索引的特点是叶子节点包含了完整的记录行，而非聚簇索引的叶子节点只有索引字段和主键ID 。

1). 聚簇索引

聚簇索引也叫聚集索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行，叶子节点间按id列递增连接，可以方便地进行顺序检索。

InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。

以MyISAM为存储引擎的表不存在聚簇索引。MyISAM表中的主键索引和非主键索引的结构是一样的，索引的叶子节点不存储表数据，存放的是表数据的地址。所以，MyISAM表可以没有主键。

2). 非聚簇索引（二级索引）

非聚簇索引也叫辅助索引、普通索引，它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

回表查询

由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到聚簇索引列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据。回表查询需要额外的 B+tree 搜索过程，必然增大查询耗时。

注意，通过二级索引查询时，回表不是必须的过程，当SELECT的所有字段在单个二级索引中都能够找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。可以用Explain命令查看SQL语句的执行计划，执行计划的Extra字段中若出现Using index，表示查询触发了索引覆盖。

InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适的列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引。

2.3 按字段特性分类

1). 主键索引

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。设定为主键后数据库会自动建立索引，innodb为聚簇索引。

-- 随表一起建索引：
CREATE TABLE customer (
    id INT(10)  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (
    id INT(10) UNSIGNED,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id) 
);
 
-- 单独建主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
-- 删除建主键索引
ALTER TABLE customer drop PRIMARY KEY ;  
 
-- 修改建主键索引
必须先删除掉(drop)原索引，再新建(add)索引

2). 唯一索引

建立在UNIQUE字段上的索引被称为唯一索引，索引列的值必须唯一，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

-- 随表一起建索引：
CREATE TABLE customer (
    id INT(10)  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
  	KEY (customer_name),
  	UNIQUE (customer_no)
);
  
-- 单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
-- 删除索引：
DROP INDEX idx_customer_no on customer ;

3). 普通索引

建立在普通字段上的索引被称为普通索引。

-- 随表一起建索引
CREATE TABLE customer (
    id INT(10) AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
  	KEY (customer_name)
);
  
-- 单独建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name); 
 
-- 删除索引
DROP INDEX idx_customer_name on customer;

2.4 按字段个数分类

1).单值索引

又称为单列索引，即一个索引只包含单个列，一个表可以有多个单列索引

-- 随表一起建索引
CREATE TABLE customer (
    id INT(10) AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
  	KEY (customer_name)
);
  
-- 单独建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name); 
 
-- 删除索引
DROP INDEX idx_customer_name on customer;

2).复合索引

又称为联合索引（组合索引），即一个索引包含多个列

-- 随表一起建索引：
CREATE TABLE customer (
    id INT(10)  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  
    PRIMARY KEY(id),
  	KEY (customer_name),
  	UNIQUE (customer_no),
  	KEY (customer_no,customer_name)
);
 
-- 单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
 
-- 删除索引：
DROP INDEX idx_no_name  on customer ;

2.5 基本语法

-- 创建
CREATE  [UNIQUE]  INDEX  indexName  ON table_name(column_list);

-- 删除
DROP INDEX indexName ON table_name;

-- 查看
SHOW INDEX FROM table_name;

-- 使用ALTER命令，有四种方式来添加数据表的索引：
-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
 
-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

-- 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
 
-- 该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

3.MySQL索引结构 B+Tree索引

3.1 案例

在这里插入图片描述

【初始化介绍】
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

3.2 关于时间复杂度

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

在这里插入图片描述

3.3 原理图

在这里插入图片描述

3.4 面试题-B树和B+树的区别

B+Tree与B-Tree 的区别

1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
　2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
　
思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的磁盘读写代价更低
　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
B+树的查询效率更加稳定
　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

4.索引的优势和劣势

优势

1.类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

3.索引只是提高效率的一个因素，如果你的MySQL有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询条件。

5.哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其它表关联的字段，外键关系建立索引

4.单键/组合索引的选择问题，组合索引性价比更高 - 高并发下倾向创建组合索引

5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6.查询中统计或者分组字段

6.哪些情况不要创建索引

1.表记录太少

2.经常增删改的表或者字段

3.Where条件里用不到的字段不创建索引

4.过滤性不好的不适合建索引（数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）。

四.性能分析

1.MySQL Query Optimizer

1.1 MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划。（他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

1.2 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是select并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2.MySQL常见瓶颈

2.1 CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

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

2.3 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

3.Explain

1.是什么(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

官网介绍：

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

在这里插入图片描述

2.能干嘛

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

哪些索引被实际使用

表之间的引用

每张表有多少行被物理查询

3.怎么操作？Explain + SQL语句

执行计划包含的信息

建表脚本

CREATE TABLE t1(
    id INT(10) AUTO_INCREMENT,
    content  VARCHAR(100) NULL ,  
    PRIMARY KEY (id)
);

CREATE TABLE t2(
    id INT(10) AUTO_INCREMENT,
    content  VARCHAR(100) NULL , 
    PRIMARY KEY (id)
);

CREATE TABLE t3(
    id INT(10) AUTO_INCREMENT,
    content  VARCHAR(100) NULL ,  
    PRIMARY KEY (id)
);

CREATE TABLE t4(
    id INT(10) AUTO_INCREMENT,
    content  VARCHAR(100) NULL , 
    PRIMARY KEY (id)
);
 
 
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
 
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
  
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
    
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

4.各字段解释

4.1 id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

1.id相同，执行顺序由上至下

explain select * from t1 , t2 , t3 where t1.id = t2.id and t2.id = t3.id;

在这里插入图片描述

2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

explain select t1.id from t1 where t1.id in
	(select t2.id from t2 where t2.id in
		(select t3.id from t3 where t3.content='t3_257'));

在这里插入图片描述

3.id相同不同，同时存在

explain select t2.* from t2 , 
				(select * from t3 where t3.content='t3_257') s3 
				where t2.id=s3.id;

在这里插入图片描述

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行。

备注：衍生 = DERIVED，此处s3是衍生虚表。

注意：id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

4.2 select_type

有哪些

在这里插入图片描述

查询的类型，主要是用于区别：普通查询、联合查询、子查询等的复杂查询

SIMPLE

简单的 select 查询,查询中不包含子查询或者UNION

在这里插入图片描述

PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

在这里插入图片描述

DERIVED

在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里。

在这里插入图片描述

SUBQUERY

在SELECT或WHERE列表中包含了子查询

在这里插入图片描述

DEPENDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询,子查询基于外层

在这里插入图片描述

UNCACHEABLE SUBQUREY

在这里插入图片描述

SHOW VARIABLES LIKE '%lower_case_table_names%';
 
SELECT @@lower_case_table_names FROM DUAL;

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

在这里插入图片描述

UNION RESULT

从UNION表获取结果的SELECT

在这里插入图片描述

4.3 table

显示这一行的数据是关于哪张表的

4.4 type

从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

显示查询使用了何种类型

在这里插入图片描述

4.4.1 访问类型排列

-- type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 

-- 从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
 
 
-- 一般来说，得保证查询至少达到range级别，最好能达到ref。

4.4.2 显示查询使用了何种类型，从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

-- system
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

-- const
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量

在这里插入图片描述

-- eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

在这里插入图片描述

-- ref
非唯一性索引扫描，返回匹配某个单独值的所有行.
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

在这里插入图片描述

-- range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

在这里插入图片描述

-- index
Full Index Scan ， index和ALL区别为index类型只遍历索引树。者通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。
出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

在这里插入图片描述

-- all
Full Table Scan，将遍历全表以找到匹配的行

在这里插入图片描述

-- index_merge
在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

在这里插入图片描述

-- ref_or_null
对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

在这里插入图片描述

-- index_subquery
利用索引来关联子查询，不再全表扫描。

在这里插入图片描述

-- unique_subquery 
该联接类型类似于index_subquery。 子查询中的唯一索引

在这里插入图片描述

注意：一般来说，得保证查询至少达到range级别，最好能达到ref。

从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL

4.5 possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

4.6 key

实际使用的索引。如果为NULL，则没有使用索引。

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

在这里插入图片描述

4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'ab%';

在这里插入图片描述

如何计算
1 、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK 要乘2；
3 、varchar这种动态字符串要加2个字节
4、允许为空的字段要加1个字节

在这里插入图片描述

第一组
key_len=age的字节长度+name的字节长度=4+1 + ( 20*3+2)=5+62=67
第二组
key_len=age的字节长度=4+1=5

在这里插入图片描述

注意：key_len字段能够帮你检查是否充分的利用上了索引，若索引命中率越大（key_len值越大），执行效率越高

4.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

在这里插入图片描述

4.9 rows

rows列显示MySQL认为它执行查询时必须检查的行数。

在这里插入图片描述

注意：越少越好

4.10 filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

4.11 Extra

包含不适合在其他列中显示但十分重要的额外信息

1.Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”

出现filesort的情况：

在这里插入图片描述

优化后，不再出现filesort的情况：

-- 创建复合索引
create index idx_deptno_ename on emp(deptno , ename);

在这里插入图片描述

注意：查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

2.Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

优化前存在 using temporary 和 using filesort

在这里插入图片描述

优化前存在 using temporary 和 using filesort 不在，性能发生明显变化：

在这里插入图片描述

3.USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组

4.Using where

表明使用了where过滤

5.using join buffer

使用了连接缓存：

在这里插入图片描述

6.impossible where

where子句的值总是false，不能用来获取任何元素

在这里插入图片描述

7.select tables optimized away

在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
查询执行计划生成的阶段即完成优化。

在innodb中：

在这里插入图片描述

在Myisam中：

在这里插入图片描述

五.查询优化

1.批量数据脚本

1.1 往表里插入50W数据

1.1.1 建表

CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.1.2 设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC…

由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

show VARIABLES like '%log_bin_trust_function_creators%';

set global log_bin_trust_function_creators=1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1

linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

1.1.3 创建函数,保证每条数据都不同

随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
 
 
#假如要删除
#drop function rand_string;

随机产生部门编号

#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1))   ;
RETURN i;  
 END$$ 
 
#假如要删除
#drop function rand_num;

1.1.4 创建存储过程

创建往emp表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
 DECLARE i INT DEFAULT 0;   
 #set autocommit =0 把autocommit设置成0  
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   ,    rand_num(30,50),rand_num(1,10000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

1.1.5 调用存储过程

dept

#执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);

emp

#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);

1.2 批量删除某个表上的所有索引

1.2.1 存储过程

DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
        OPEN _cur;
        FETCH   _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index ",_index," on ",tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH   _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
   END$$

1.2.2 执行存储过程

CALL proc_drop_index("dbname","tablename");

2.单表使用索引及常见索引失效

2.1 案例(索引失效)

2.1.1 全值匹配我最爱

系统中经常出现的sql语句如下：  

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'  
 
索引应该如何建立 ？

建立索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)
 
#建立索引前

在这里插入图片描述

#索引后

在这里插入图片描述

2.1.2 最佳左前缀法则

如果系统经常出现的sql如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   

那原来的idx_age_deptid_name 还能否正常使用？

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'

在这里插入图片描述

虽然可以正常使用，但是只有部分被使用到了。

在这里插入图片描述

完全没有使用上索引。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

2.1.3 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

这两条sql哪种写法更好
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE 'abc%' 
 
EXPLAIN   SELECT SQL_NO_CACHE * FROM emp WHERE   LEFT(emp.name,3)  = 'abc'

第一种

在这里插入图片描述

第二种

在这里插入图片描述

2.1.4 存储引擎不能使用索引中范围条件右边的列

如果系统经常出现的sql如下：
 
EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 

那么索引 idx_age_deptid_name这个索引还能正常使用么？

在这里插入图片描述

如果这种sql 出现较多
应该建立：

create index idx_age_name_deptid on emp(age,name,deptid)

效果

在这里插入图片描述

# drop index idx_age_name_deptid on emp

2.1.5 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

CREATE INDEX idx_name ON emp(NAME)
  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc'

在这里插入图片描述

2.1.6 is not null 也无法使用索引,但是is null是可以使用索引的

UPDATE emp SET age =NULL WHERE id=123456;
 
下列哪个sql语句可以用到索引
EXPLAIN SELECT * FROM emp WHERE age IS NULL
  
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

在这里插入图片描述

2.1.7 like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

在这里插入图片描述

2.1.8 字符串不加单引号索引失效

在这里插入图片描述

2.1.9 总结

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

2.2 一般性建议

1.对于单键索引，尽量选择针对当前query过滤性更好的索引

2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

4.在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

5.书写sql语句时，尽量避免造成索引失效的情况

3.关联查询优化

3.1 建表SQL

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2 案例

# 下面开始explain分析
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;
#结论：type 有All
 
# 添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
换成inner join
 
delete from class where id<5;
 
# 第2次explain
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

3.3 建议

1、保证被驱动表的join字段已经被索引

2、left join 时，选择小表作为驱动表，大表作为被驱动表。

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询。

4.子查询优化

4.1 尽量不要使用not in 或者 not exists

-- 取所有不为掌门人的员工，按年龄分组 ，每个年龄段多少人
SELECT SQL_NO_CACHE age,count(*)  
FROM emp a 
WHERE  id  NOT  IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL)
group by age 
having count(*)<10000;

在这里插入图片描述

4.2 用left outer join on xxx is null 替代

EXPLAIN SELECT SQL_NO_CACHE age,count(*) 
FROM  emp a LEFT OUTER JOIN dept b ON a.id =b.ceo
WHERE    b.ceo IS   NULL
group by age 
having count(*)<10000;

在这里插入图片描述

5.排序分组优化

5.1 case

create index idx_age_deptid_name on emp (age,deptid,name)
 
 
以下是否能使用到索引，能否去掉using filesort
 
 
1、explain  select SQL_NO_CACHE * from emp order by age,deptid; 
2、explain  select SQL_NO_CACHE * from emp order by age,deptid limit 10; 
#无过滤 不索引
 

3、explain  select SQL_NO_CACHE * from emp where age=45 order by deptid;
4、explain  select SQL_NO_CACHE * from emp where age=45 order by deptid,name; 
5、explain  select SQL_NO_CACHE * from emp where age=45 order by deptid,empno;
6、explain  select SQL_NO_CACHE * from emp where age=45 order by name,deptid;
7、explain  select SQL_NO_CACHE * from emp where deptid=45 order by age; 
#顺序错，必排序
 

8、explain select SQL_NO_CACHE * from emp where age=45 order by  deptid desc, name desc ; 
9、explain select SQL_NO_CACHE * from emp where age=45 order by  deptid asc, name desc ;
#方向反 必排序

5.2 ORDER BY子句

尽量使用Index方式排序,避免使用FileSort方式排序

5.3 索引的选择

执行案例前先清除emp上的索引，只留主键

索引的选择

#查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

在这里插入图片描述

结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

开始优化：

思路：尽量让where的过滤条件和排序使用上索引
但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引
1、我们建一个三个字段的组合索引可否？

在这里插入图片描述

CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
我们发现using filesort 依然存在，所以name 并没有用到索引。
原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。

所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引，DROP INDEX idx_age_empno_name ON emp

为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON emp(age,NAME);

在这里插入图片描述

也就是说empno 和name这个两个字段我只能二选其一。
这样我们优化掉了 using filesort。
执行一下sql

在这里插入图片描述

速度果然提高了4倍。

…

但是
如果我们选择那个范围过滤，而放弃排序上的索引呢
建立
DROP INDEX idx_age_name ON emp
create index idx_age_eno on emp(age,empno);

在这里插入图片描述

在这里插入图片描述
果然出现了filesort，而且type还是range光看字面其实并不美好。
我们来执行以下sql

在这里插入图片描述

结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故？

原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

5.4 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

5.4.1 双路排序

MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

5.4.2 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

5.4.3 单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

5.4.4 结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题？

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

5.4.5 优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

减少select 后面的查询的字段。

Why？

提高Order By的速度

Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是：
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
尝试提高 max_length_for_sort_data
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

5.5 GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致，唯一区别是group by 即使没有过滤条件用到索引，也可以直接使用索引。

6.最后使用索引的手段：覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

在这里插入图片描述

explain select SQL_NO_CACHE * from emp where name like '%abc';

在这里插入图片描述

使用覆盖索引后

在这里插入图片描述

总结：尽量不要使用select * ,使用查询索引字段

7. JSON 数组类型和多值索引的特点进行高效查询

需求：某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。

比如：

在电商行业中，根据用户的穿搭喜好，推荐相应的商品；
在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；
在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。

强烈推荐用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。

#创建用户画像定义表
CREATE TABLE `t_tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标签名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据
insert into t_tag values (null, '70后');
insert into t_tag values (null, '80后');
insert into t_tag values (null, '90后');
insert into t_tag values (null, '00后');
insert into t_tag values (null, '10后');
insert into t_tag values (null, '爱运动');
insert into t_tag values (null, '爱听歌');
insert into t_tag values (null, '爱看电影');
insert into t_tag values (null, '高学历');
insert into t_tag values (null, '小资');
insert into t_tag values (null, '有车');
insert into t_tag values (null, '有小孩');
insert into t_tag values (null, '喜欢网购');
insert into t_tag values (null, '喜欢点外卖');
insert into t_tag values (null, '萝莉');

#创建用户标签中间表
CREATE TABLE `t_user_tag` (
  `user_id` int NOT NULL COMMENT '用户id',
  `tag_id` json NOT NULL COMMENT '用户标签id',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据，使用数组的形式存储
insert into t_user_tag values (1,'[2,4,6]');
insert into t_user_tag values (2,'[1,3,7]');
insert into t_user_tag values (3,'[8,10,12]');

MySQL 8.0.17 版本开始支持 Multi-Valued Indexes多值索引，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。

#在表 t_user_tag 上创建 Multi-Valued Indexes：
ALTER TABLE t_user_tag ADD INDEX idx_user_tags ((cast((tag_id->"$") as unsigned array)));

#优化解析
EXPLAIN
select * from t_user_tag where 10 MEMBER OF(tag_id -> '$');

在这里插入图片描述

#1.查询爱看电影的
select * from t_user_tag where 8 MEMBER OF(tag_id -> '$');

#2.查询爱看电影，且有小孩的
select * from t_user_tag where JSON_CONTAINS(tag_id -> '$', '[8,12]');

#3.查询爱看电影，或有小孩的
select * from t_user_tag where JSON_OVERLAPS(tag_id -> '$', '[8,12]');

little_Punk

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-索引及优化

1.MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。索引是数据结构。索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？2.你可以简单理解为==“排好序的快速查找的数据结构”==。
复制链接

扫一扫