索引优化分析

一.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.索引的优势和劣势

优势：

1.类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本 2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。 2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。 3.索引只是提高效率的一个因素，如果你的MySQL有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询条件。

3.mysql索引分类

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) );

— 单独建唯一索引： CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

— 删除索引： DROP INDEX idx_customer_no on customer ;

3.主键索引

设定为主键后数据库会自动建立索引，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)索引

4.复合索引

即一个索引包含多个列 — 随表一起建索引： 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 ;

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);

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

4.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，显然成本非常非常高。

4.2 关于时间复杂度

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

4.3 原理图

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

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))); image-20210519140356072 image-20210519140356072

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

显示查询使用了何种类型

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 primary key unique index 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于 主键或唯一索引扫描

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

— range where 后面条件 只检索给定范围的行,使用一个索引来选择行。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 keykey

实际使用的索引。如果为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个字节

第一组 keylen=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的情况：

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

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)

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 * 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 * 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 * 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 from emp where age=45 order by deptid; 4、explain select from emp where age=45 order by deptid,name; 5、explain select from emp where age=45 order by deptid,empno; 6、explain select from emp where age=45 order by name,deptid; 7、 explain select * from emp where deptid=45 order by age;

顺序错，必排序

8、explain select from emp where age=45 order by deptid desc, name desc ; 9、explain select 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的速度

1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是： 1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。 1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
3. 尝试提高 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 * from emp where name like ‘%abc’;

使用覆盖索引后

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