目录

索引 (Index)

一、索引分类、创建索引  ★★

索引的使用场景  ★

二、Explain 索引优化分析  ★★★

Explain 扩展 mysql8.0新特性

三、查询优化 | 失效情况 ★★

1、准备数据-保存50万条数据

2、常见索引失效 | 索引优化

四、关联查询(JOIN) 索引优化  ★★

1、驱动表 | 被驱动表

2、Explain 分析优化

3、排序、分组优化 

4、单路排序、双路排序 ；GROUP BY 关键字优化

五、慢查询日志

View 视图 (了解)

优化追踪器 

---

索引 (Index)

简历：熟悉mysql索引，有过线上调优经验，熟练使用多种调试工具；Profiles、Explain ..?

索引是帮助MySQL高效获取数据的数据结构。一个数据库表可以建立多条索引。(字典中的目录)

优势：

1、使用索引能够大大减少数据量的扫描。

2、因为索引结构是有序、连续的，因此使用索引可以使随机读取变成顺序读取。提高数据检索的效率，降低数据库的IO成本。

3、索引也是InnoDB中实现行锁的必要前提。

LMAP？

1、提高数据检索的效率，降低数据库的IO成本

2、保证数据库表中每一行`数据的唯一性

3、在实现数据的参考完整性方面，可以加速表和表之间的连接。对于有依赖关系的子表和父表联合查询时，`可以提高查询速度`。

4、在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。

缺点：

1、创建索引需要再构建一颗b+数，所以耗费时间 

2、索引需要占磁盘空间

3、会降低更新表的速度 。速度。

聚簇索引：将主键id作为索引。数据即使索引，索引即是数据。所以表中只有一个聚簇索引。(如分页查询，通过主键id作为唯一标识，数据存储是连续的，顺序读效率高)

非聚簇索引：反之，数据和索引不放在一起(自己建的)。需要二次查找，需要先找到主键值，再通过主键值找到数据行的数据页。

了解：分布式索引 (分布式id)：根据数据库建立字符串索引，可使用步进实现。

 UUID：无逻辑、无序没法排序。难以维护，效率低下。可能会出现id冲突。用于极高并发量

一、索引分类、创建索引  ★★

创建索引需谨慎，会导致全盘扫描；磁盘是我们的一个瓶颈，建立索引是在磁盘上存储数据，产生IO操作，引发效率问题。冗余存储。

每建立一个索引，都会形成一颗新的树，树之间相互独立，共享叶子结点的数据 (爹不一样儿子一样)。每次查询只会引用一个索引。Mysql优化器会选择一个最优的索引树。

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引 (UNI)：保证索引列、字段不重复，但允许有空值。注意：唯一索引效率较低，尤其在高并发

主键索引 (聚簇索引)(PRI)：主键及索引，InnoDB中的聚簇索引。

复合索引 (MUL)：由两个字段内容同时生成索引，比对时按照两个条件在一份数据内匹配。创建组合索引时，区分度最高的应在最左边。

如果两个字段，其中一个字段有索引，可以用到索引吗？
        可以，现根据索引缩小范围，在进行查询。

总结：

查看索引详细信息：

SHOW INDEX FROM table_name

查看表信息：

DESC table_name

创建索引：

CREATE  [UNIQUE] INDEX indexName ON mytable(column name);

为了组织成树形结构，一个节点至少要有两个索引，即每个索引8k；所以建长文本字段索引时(如varChar)，我们需要使用Length限制索引长度。

CREATE  [UNIQUE ] INDEX indexName ON mytable(column name(length));

删除索引：

DROP INDEX [indexName] ON mytable

• 随表一起创建索引：

CREATE TABLE customer (
    
  id INT UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
    
  PRIMARY KEY(id), -- 主键索引：列设定为主键后会自动建立索引，唯一且不能为空。
  UNIQUE INDEX uk_no (customer_no), -- 唯一索引：索引列值必须唯一，允许有NULL值，且NULL可能会出现多次。
  KEY idx_name (customer_name), -- 普通索引：既不是主键，列值也不需要唯一，单纯的为了提高查询速度而创建。
  KEY idx_no_name (customer_no,customer_name) -- 复合索引：即一个索引包含多个列。
);

• 单独建创索引：

CREATE TABLE customer1 (
  id INT UNSIGNED,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200)
);
 
CREATE INDEX idx_name ON customer1(customer_name);  -- 普通索引
ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主键索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

• 删除索引：

DROP INDEX idx_name ON customer; -- 删除单值、唯一、复合索引
 
ALTER TABLE customer MODIFY id INT, DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
ALTER TABLE customer DROP PRIMARY KEY;  -- 删除主键索引(没有主键自增)

索引的使用场景  ★

• 哪些情况需要创建索引？

需要查询的字段比较频繁，预期后期数据量会增大，就需要建立索引。

1、主键自动建立唯一索引，约定大于配置 (建表三要素：ID、创建时间、修改时间) 

2、频繁查询的字段应该创建索引

3、查询中与其它表关联的字段，外键关系建立索引 (a JOIN b，a表需要建立关联字段索引，b如果不是关联主键，也需要建立索引)

4、索引的选择问题：由Mysql执行优化器做最优选择。

5、查询中排序、分组的字段，通过索引访问将大大提高排序速度

思考：排序和分组哪个更伤性能？取决于排序、分组所使用的字段。

分组：先排序，后分组。ORDER BY 前面一定要有筛选条件！

阿里开发规范：创建索引时避免有如下极端误解：
1）宁滥勿缺。认为一个查询就需要建一个索引。(根据需求决定)
2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。 

• 哪些情况不建议创建索引

1、系统表，查询效率不高

2、表记录数不多(几千条)

3、经常增删改的表：可作为数据存储来用，不参与查询。建立集群，各司其职，读写分离。

4、只做冷数据存储

5、高并发的系统，效率依靠多级缓存，数据内容前置，数据库只做数据存储。

二、Explain 索引优化分析  ★★★

用于查看当前sql索引引用情况，便于进行索引优化。

回表：一个概念，去表中(磁盘中)查询数据，效率低；

覆盖索引：查询数据就是索引，无需回表，效率高。我们可以根据覆盖索引进行优化。
如果数据存在于索引中，如查询id (select id)，可以直接在叶子节点中取出数据，不需要回表，效率较高。

Mysql是基于IO的，磁盘IO是我们的一个瓶颈，因为IO设备不支持并发。

id：执行select子句或操作表的顺序

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

SIMPLE：从上到下去执行，效率最高。

SUBQUERY、PRIMARY：层层嵌套的查询(子查询)，效率低于SIMPLE。

DEPENDENT SUBQUERY：用了系统变量

UNION：被动表，效率较低。默认去重(每一行一样时)

table：查询的数据是关于哪张表的； 派生表

type：访问类型排列 ★

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

想要提高效率，查询至少达到range级别，最好能达到ref。

回表：一个概念，要不要去表中(磁盘中)查询数据；

• system：系统表，const类型，效率极高。  (mysql8为null)
• const：只通过一次索引就找到了，没有回表。
      explain select * from t1 where id = 1;
• eq_ref：唯一性索引扫描，表中只有一条记录与之匹配。
      explain select * from t1,t2 where t1.id = t2.id;
• ref ：非唯一性索引扫描，返回匹配某个单独值的所有行
      explain select * reom t1, t2 where t1.content = t2.content; (content：索引)
• range ：只检索给定范围的行，范围搜索比全局扫描好，如分页查询。
      explain select * from t2 where id >1 and id <5;
• Index：只遍历索引树，用到了覆盖索引。   explain select id from t1;
• All：全盘扫描。   explain select * from t2;

possible_keys：这张表中可能用的索引树，但不一定被查询实际使用。

key：实际使用的索引。

key_len：索引使用的字节数 ★；用于推断使用了索引中多少数据。那个2长度越长越精确，数据越少，筛选条件越多。

ref：显示将哪些列或常量与键列中命名的索引进行比较。

rows：影响的行数 ★

filtered：filtered：命中率，百分比 ★； rows/命中的行数 * 100%  

Extra 额外信息 ★

1、Using filesort：Order By没有用上索引，使用了文件排序，需要建立索引。
        SELECT * FROM table ORDER BY 排序前面没有筛选条件，灾难。

2、Using temporary：分组没有用上索引。产生临时表。注意：分组操作是需要先排序后分组的

3、Using index ：表示使用了覆盖索引，即直接访问索引就足够获取到所需要的数据，没有产生回表。

4、Using where：表明使用了where过滤

5、Using join buffer：关联字段没有使用索引。Mysql8新特性

6、impossible where：筛选条件有误

没有建立索引：

Explain 扩展 mysql8.0新特性

Format=tree：可以显示更详细性能消耗

Format=json：可以看到选择索引的过程

三、查询优化 | 失效情况 ★★

匹配原则：1、全值匹配，效率最高。2、最左前缀匹配，从左边开始去匹配。

1、准备数据-保存50万条数据

注意：禁止使用存储过程，存储过程难以调试和扩展，更没有一致性。此处只为演示

# 默认情况下，mysql不开启创建函数设置，如果使用函数来操作数据，会导致从机和主键操作时间不一致。
#查看mysql是否允许创建函数：
show variables like 'log_bin_trust_function_creators';

#命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; 
# 不加global只是当前窗口有效。mysqld重启，上述参数又会消失。

#永久方法： 
linux下：/etc/my.cnf下my.cnf[mysqld]加上：log_bin_trust_function_creators=1 

#13.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 $$
#假如要删除
DELIMITER ;
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$$ 
 
#假如要删除
DELIMITER ;
drop function rand_num;
 
 
#创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 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表添加随机数据
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;
 
 
 #执行存储过程，往dept表添加10万条数据
CALL insert_dept(100000); 
 
#执行存储过程，往emp表添加50万条数据
CALL insert_emp(100000,500000); 

查看索引：show index from emp;

删除对应索引： drop index idx_deptid_name on emp;

2、常见索引失效 | 索引优化

1. 全值匹配我最爱

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd';

如何建立索引：全值匹配 完全匹配效率最高

CREATE INDEX idx_age ON emp(age);  #不行

CREATE INDEX idx_age_deptid ON emp(age,deptid);  #不行

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

建立索引前：All 全盘扫描

建立索引后

2. 最左前缀法则：索引中，用第一个索引列去匹配。

查询从索引的最左前列开始，而不跳过索引中的列。

LIKE% 可以 %LIKE不行 

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
或者
EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';

3. like 以通配符%开头索引失效

创建索引：CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT * FROM emp WHERE emp.name LIKE 'abc%'; 可以使用索引

EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%abc'; 不能使用索引

索引在后面无法匹配，造成索引失效。

4. 范围条件右边的列 索引失效

5. 计算、函数 索引失效

6. 不等于(!=  或 <>) 索引失效

7. is not null无法使用索引，is null可使用索引

8. 类型转换导致索引失效

Mysql 8新增功能：

query_cost：预估的cpu计算所需的单位

四、关联查询(JOIN) 索引优化  ★★

mysql中表链接分为三种

1. 左连接 left join

左连接以左表为基础，查询出左表所有数据并且去匹配右表的数据，如果右表没有数据，则为空

2. 右连接 right join

右连接以右表为基础，查询出右表所有数据并且去匹配左表的数据，如果左表没有数据，则为空

3. 内连接 inner join

内连接会把左右表匹配的数据查询出来，不存在的数据直接忽略

UNION 在使用时，两张表的字段保证一致 (字段行数、字段名)，如果不一致，请在slect后面列选字段，不要使用*

UNION ALL 不会自动去重。

1、驱动表 | 被驱动表

驱动表与被驱动表的概念

驱动表是表链接的基础表，也就是通过驱动表的数据结果作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后合并。

驱动与被驱动

左连接中 左表是驱动表，右表是被驱动表

右链接中 右表是驱动表，左表是被驱动表

内连接中 表数据较小的表 会由Mysql自动选择为驱动表去驱动大表

如果where条件存在的话，Mysql会根据where实际条件进行驱动表的选择。

sql优化：要用小表 JOIN (驱动) 大表，效率较高。因为每一循环都会产生io操作。

原因：比如20w的大表和200条的小表，如果大表驱动，那么是20w条记录外循环，内循环200条去链接查找，需要20w次链接。反之，小表只需要200次链接，并且驱动表是不会使用索引的。

2、Explain 分析优化

能够直接多表关联，不用子查询

1、驱动表无法使用索引，被驱动表可以使用索引。所以驱动表最好加入where缩小范围。 

但是驱动表可以使用覆盖索引(type = index)，直接访问索引就足够获取到所需要的数据，不会产生回表。所以两表都建议建立索引。

2、内连接，MySQL会自动选择驱动表

其他：最好减少外键约束，尽量在代码中实现。

3、排序、分组优化 

limit 也属于筛选条件，在where条件之后，注意要符合最左匹配法则！

1. 无过滤，不索引；没有过滤条件无法使用索引

2. 顺序错，必排序；过滤条件要符合最左匹配法则

3. 方向错，必排序

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

所有的排序都是在条件过滤之后才执行的，所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。

4、单路排序、双路排序 ；GROUP BY 关键字优化

双路排序：两次扫描磁盘，效率低
1. 通过索引找到需要排序的字段 + 数组指针，order by排序
2. 通过指针按顺序读取完整单行数据

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

限制：1、单词排序整体内存用量  2、单行数据大小(字节长度)

优化策略：

1、增大排序缓冲区大小；sort_buffer_size 参数
2、增大单行数据大小；max_length_for_sort_data (数
3、减少select 后面的查询的字段。 禁止使用select *

GROUP BY关键字优化

1. group by 是排序再分组，所以order by 在 group 前面没有意义

2. where高于having，能写在where限定的条件就不要写在having中了
3. group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。

五、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time 的默认值为10，意思是运行10秒以上的语句。

1. 查看信息：SHOW VARIABLES LIKE '%slow_query_log%';

2. 开启记录：set global slow_query_log=1;
只对窗口生效，重启服务失效

3. 查看、设置阈值 long_query_time 时间：SHOW VARIABLES LIKE '%long_query_time%';

全局变量设置，对所有客户端有效。但，必须是设置后进行登录的客户端。
SET GLOBAL long_query_time=0.01;

对当前会话连接立即生效，对其他客户端无效。session可省略；
SET SESSION long_query_time=0.01;

永久生效：修改配置文件my.cnf，略（其它系统变量也是如此）

4. 记录慢SQL并后续分析：SELECT * FROM emp;

5. 询当前系统中有多少条慢查询记录：SHOW GLOBAL STATUS LIKE '%Slow_queries%';

6. 根据log位置 查看慢查询日志：cat /var/lib/mysql/plisetsky-slow.log

View 视图 (了解)

将一段查询sql封装为一个虚拟的表。 这个虚拟表只保存了sql逻辑，不会保存任何查询结果。

• 封装复杂sql语句，提高复用性
• 逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活

创建

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
查询
select * from view_name 
更新
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition
删除
drop view view_name;

 

优化追踪器 

可以查看sql执行，经历了什么；

开启：set session optimizer_trace="enabled=on",end_markers_in_json=on;

进行查询：SELECT age,name  FROM emp  where age >45 order BY NAME ;

后续学习：

网络、磁盘IO原理，计算机组成原理，Linux内核网络