MySQL高级

最新推荐文章于 2022-02-19 18:52:52 发布

Nancy_G

最新推荐文章于 2022-02-19 18:52:52 发布

阅读量1.3k

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/sinat_41653656/article/details/110196673

版权

数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL高级

1. MySQL简介
- 1.1 什么是MySQL
- 1.2 MySQL高手是如何炼成的
- 1.3 整体架构图
2. MySQL存储引擎
- 2.1 查看存储引擎
- 2.2 各个存储引擎的介绍
- 2.3 InnoDB 和 MyISAM 引擎
3. SQL预热之常见的 JOIN 查询图
4. 索引优化分析
- 4.1 索引简介
- - 4.1.1 索引是什么
  - 4.1.2 索引的优缺点
- 4.2 MySQL的索引结构
- 4.3 MySQL 索引分类
- 4.4 索引的创建时机
- - 4.4.1 适合创建索引的情况
  - 4.4.2 不适合创建索引的情况
5. Explain 性能分析
- 5.1 概念
- 5.2 怎么用
- 5.2 Explain分析准备工作
- - 5.2.1 Explain字段解释
  - 5.2.2 创建测试数据
- 5.3 Explain字段分析
6. 批量数据脚本
- 6.1 准备工作
- 6.2 编写随机函数
- 6.3 创建存储过程
- 6.5 批量删除某个表上的所有索引
7. 单表使用索引常见的索引失效
- 7.1 全值匹配我最爱
- 7.2 最佳左前缀法则
- 7.3 索引列上做计算
- 7.4 索引列上进行范围查询
- 7.5 使用覆盖索引
- 7.6 使用不等于(!= 或者<>)
- 7.7 is not null 和 is null
- 7.8 like的前后模糊匹配
- 7.9 使用or
- 7.10 总结练习
- 7.11 口诀
8. 查询优化
- 8.1 关联查询优化
- 8.2 子查询优化
- 8.3 排序分组优化
9. 慢查询
- 9.1 慢查询的定义
9.2 开启慢查询日志
- 9.3 查看慢查询日志
10. 视图
- 10.1 是什么
- 10.2 干什么
- 10.3 使用
11. 主从复制
- 11.1 主从复制基本原理
- 11.2 复制的基本原则
- 11.3 一主(hadoop100)一从(hadoop101)

1. MySQL简介

1.1 什么是MySQL

（1）MySQL是一个关系型数据库管理系统
（2）MySQL是开源的、可以定制的，采用了GPL协议，可以修改源码来开发自己的MySQL系统
（3）MySQL使用标准的SQL数据语言形式
（4）MySQL可以允许于多个系统上，并且支持多种语言。这些语言包括C、C++、Java、Python、PHP等
（5）MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

1.2 MySQL高手是如何炼成的

（1）数据库内部结构和原理
（2）数据库建模优化
（3）数据库索引建立
（4）SQL语句优化
（5）SQL编程
（6）MySQL服务器的安装配置
（7）主从复制
（8）数据迁移
（9）容灾备份和恢复
… …

1.3 整体架构图

在这里插入图片描述
（1）连接层
最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证及相关的安全方案。
（2）服务层
（3）引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选择
（4）存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2. MySQL存储引擎

2.1 查看存储引擎

（1）查看支持的存储引擎：

show engines

（2）查看当前MySQL默认的存储引擎

show variables like "%storage_engine%"

2.2 各个存储引擎的介绍

（1）InnoDB 存储引擎
InnoDB 是 MySQL 的默认事务型引擎，它被设计用来存储大量的短期（short-lived）事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

（2）MyISAM 存储引擎
MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM 不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

（3）Archive 引擎
Archive 档案存储引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引。
Archive 表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive 表比 MyISAM 表要小大约 75%，比支持事务处理的 InnoDB 表小大约 83%。

（4）Blackhole 引擎
Blackhole 引擎没有任何实现存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种方式会遇到很多问题，不建议使用。

（5）CSV引擎
①CSV引擎可以将普通的 CVS 文件作为 MySQL 的表来处理，但不支持索引。
②CSV 引擎可以作为一种数据交换的机制，非常有用
③CSV 存储的数据直接可以在操作系统里，用文本编辑器或者excel读取。

（6）Memory 引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表是非常有用的。Memory 表至少比 MyISAM 表要快一个数量级。

（7）Federated 引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

2.3 InnoDB 和 MyISAM 引擎

对比项	InnoDB	MyISAM
外键	支持	不支持
事务	支持	不支持
聚簇索引	支持	不支持
行表锁	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作
缓存	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响	只缓存索引，不缓存真实数据
关注点	并发写、事务、资源	读性能
默认安装	Y	Y
默认使用	Y	N
自带系统表使用	N	Y

3. SQL预热之常见的 JOIN 查询图

在这里插入图片描述

4. 索引优化分析

4.1 索引简介

4.1.1 索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
在这里插入图片描述
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

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

4.1.2 索引的优缺点

优点：
（1）提高数据检索的效率，降低数据库的IO成本。
（2）通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

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

4.2 MySQL的索引结构

4.2.1 B-tree索引

在这里插入图片描述

一棵B树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

4.2.2 B+tree索引

在这里插入图片描述

B+树的非叶子结点仅仅存储着关键字信息和儿子的指针，B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据.因此每个磁盘块包含的关键字信息会更多。这样也就决定了加载一个磁盘块可以获取到更多的关键字，可以减少IO操作，
一次IO操作相当于成百上千次的内存比较.
在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构

4.2.3 B-Tree与B+Tree 的区别

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

4.2.4 为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

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

4.2.5 聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。
非聚集索引是一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

在这里插入图片描述

聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 IO 操作。

聚簇索引的限制：
（1）对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引。
（2）由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

4.3 MySQL 索引分类

4.3.1 单值索引

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

（1) 随表一起创建：

CREATE TABLE customer (
  id INT(10) AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)	-- 给 customer_name 列创建索引
);

（2) 单独建单值索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

（3) 查看某个表的索引

show index from 表名   
show keys from  表名

4.3.2 唯一索引

概念：索引列的值必须唯一，但允许有空值

（1) 随表一起创建：

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

（2) 单独建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

4.3.3 主键索引

概念：设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引

（1) 随表一起建索引

CREATE TABLE customer (
 id INT(10) AUTO_INCREMENT ,
 customer_no VARCHAR(200),
 customer_name VARCHAR(200),
 PRIMARY KEY(id) 
);

（2) 单独建主键索引：

ALTER TABLE customer add PRIMARY KEY (customer_no);

（3) 删除主键索引：

ALTER TABLE customer drop PRIMARY KEY ;

（4) 需要注意的问题：

设置为自增的主键上的索引不能删除.

4.3.4 复合索引

概念：即一个索引包含多个列

（1) 随表一起建索引：

CREATE TABLE customer (
  id INT(10)  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);

（2) 单独建索引：

CREATE  INDEX idx_no_name ON customer(customer_no,customer_name);

4.3.5 基本语法

功能	语法
创建	CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除	DROP INDEX [indexName] ON mytable;
查看	SHOW INDEX FROM table_name\G
使用Alter命令添加主键索引	ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
使用Alter命令添加普通索引	ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
使用Alter命令指定全文索引	ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

4.4 索引的创建时机

4.4.1 适合创建索引的情况

（1) 主键自动建立唯一索引
（2) 频繁作为查询条件的字段应该创建索引
（3) 查询中与其它表关联的字段，外键关系建立索引
（4) 单键/组合索引的选择问题，组合索引性价比更高
（5) 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
（6) 查询中统计或者分组字段

总结： 查询中过滤、统计、分组、排序、关联所能用到的字段应该建立索引，建索引优先考虑复合索引，其次考虑单值索引。

4.4.2 不适合创建索引的情况

（1) 表记录太少
（2) 经常增删改的表或者字段
（3) WHERE 条件里用不到的字段不创建索引
（4) 过滤性不好的不适合建索引

5. Explain 性能分析

5.1 概念

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

5.2 怎么用

Explain+SQL语句。
Explain执行后返回的信息：
在这里插入图片描述

5.2 Explain分析准备工作

5.2.1 Explain字段解释

id: select 查询的序列号`,表示查询中执行select子句或操作表的顺序。
select_type: 主要用于区别普通查询、联合查询、子查询等的复杂查询。
table: 这个数据是基于哪张表的。
partitions: 查询数据匹配的分区
type: 是查询的访问类型，是较为重要的一个指标
possible_keys: 显示可能应用在这张表中的索引，一个或多个。
key: 实际使用的索引。如果为NULL，则没有使用索引。
key_len: 表示索引中使用的字节数
ref: 显示索引的哪一列被使用了
rows: 显示MySQL认为它执行查询时必须检查的行数，不精确。
filtered: 返回结果的行占需要读到的行(rows列的值)的百分比
Extra: 其他的额外重要的信息。

5.2.2 创建测试数据

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

5.3 Explain字段分析

5.3.1 Explain 之id

id: select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
（1) id相同：执行顺序由上至下

mysql>EXPLAIN select * from t1,t2,t3 where t1.id = t2.id and t2.id = t3.id ;

在这里插入图片描述
（2) id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
mysql> 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 = ‘’)
);

MySQL5.5结果:

（3) 有相同也有不同
mysql> EXPLAIN select t2.* from t2 ,(select * from t3 where t3.content = ‘’) s3 where
s3.id = t2.id ;
在这里插入图片描述

MySQL5.5的结果:
在这里插入图片描述

（4) 总结：
id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行。
id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

5.3.2 Explain 之select_type

select_type : 主要用于区别普通查询、联合查询、子查询等的复杂查询。
（1) SIMPLE：代表单表查询

mysql>EXPLAIN select * from t1 ;

在这里插入图片描述

（2) PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。
在这里插入图片描述
MySQL5.5结果:

（3) DERIVED：在FROM查询中的子查询，结果存放在临时表中

（4) SUBQUERY：在WHERE列表中包含了子查询。

mysql> EXPLAIN select t2.id from t2 where t2.id = 
        ( select t3.id from t3 where t3.id = 1);

在这里插入图片描述

（5) DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外层。

mysql> EXPLAIN select t2.id from t2 where t2.id in 
       (select t3.id from t3 where t3.content = 't3_522');

在这里插入图片描述

MySQL5.5的结果:
在这里插入图片描述

注意：SUBQUERY和DEPENDENT SUBQUERY都是where后面的条件，subquery是单个值，dependent subquery是一组值。
（6) UNCACHEABLE SUBQUREY：当使用了@@来引用系统变量的时候，不会使用缓存。

mysql> EXPLAIN select * from t3 where id = 
        (select id from t2 where t2.id = @@sort_buffer_size);

在这里插入图片描述

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

mysql> EXPLAIN select t2.id ,t2.content from t2
        union all
        select t3.id ,t3.content from t3 ;

在这里插入图片描述

mysql> EXPLAIN select t2.id ,t2.content from 
    (select t3.id, t3.content from t3  union all select t1.id ,t1.content from t1  ) t2;

在这里插入图片描述

（8) UNION RESULT：从UNION表获取结果的SELECT。

mysql> EXPLAIN select t2.id ,t2.content from t2
        union all
        select t3.id ,t3.content from t3 ;

MySQL5.5结果:
在这里插入图片描述

5.3.3 Explain 之type

type是查询的访问类型，是较为重要的一个指标结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，
一般来说，得保证查询至少达到range级别，最好能达到ref。
（1) system
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
（2) const
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

mysql> EXPLAIN  select * from t1 where t1.id  = 1;

在这里插入图片描述

（3) eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
mysql> EXPLAIN select * from t1 ,t2 where t1.id = t2.id ;
在这里插入图片描述

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

mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content;

在这里插入图片描述

建立索引后：

mysql> create index idx_t2_content on t2(content);
mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content;

在这里插入图片描述

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

mysql> EXPLAIN select * from t1 where t1.id >10 ;

在这里插入图片描述

mysql> EXPLAIN select * from t1 where t1.id between 1 and 3 ;

在这里插入图片描述

mysql> EXPLAIN select * from t1 where t1.id in (1,2);

在这里插入图片描述

（6) index
出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

mysql> EXPLAIN select * from t1;
mysql> EXPLAIN select id from t1;

在这里插入图片描述

（7) all
Full Table Scan，将遍历全表以找匹配的行。

mysql> EXPLAIN select * from t1 ,t2 where t1.content = t2.content ;

在这里插入图片描述

（8) index_merge
在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。
MySQL5.5的结果:
在这里插入图片描述

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

mysql> EXPLAIN select * from t2 where t2.content is null or t2.content = 'abc';

在这里插入图片描述

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

mysql> create index idx_t3_content on t3(content);
mysql> EXPLAIN select * from t2 where t2.content in (select t3.content from t3) ;

在这里插入图片描述

MySQL5.5的结果:
在这里插入图片描述
（11) unique_subquery
该联接类型类似于index_subquery。子查询中的唯一索引。

mysql> EXPLAIN select * from t2 where t2.id in (select t3.id from t3 );

在这里插入图片描述

MySQL5.5的结果
在这里插入图片描述

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

5.3.4 Explain 之possible_keys

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

5.3.5 Explain 之 key

实际使用的索引。如果为NULL，则没有使用索引。
5.3.6 Explain 之 key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用的越充分。
示例如下
在这里插入图片描述

如何计算：
（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
在这里插入图片描述

5.3.7 Explain 之 ref

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

mysql> create index idx_name_t_emp on t_emp(name);
mysql> EXPLAIN select * from t_emp emp ,t_dept dept  where emp.name = 'aaa' 
        and emp.deptId = dept.id ;

在这里插入图片描述

5.3.8 Explain 之 rows

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

mysql> create index idx_name_t_emp on t_emp(name);
mysql> EXPLAIN select * from t_emp emp ,t_dept dept  where emp.name = 'aaa' 
        and emp.deptId = dept.id ;

在这里插入图片描述

5.3.9 Explain 之 Extra

（1) Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
注意: 测试表数据不能太小。
优化前：

mysql> explain select id ,empno ,name  from t_emp 
          where deptid = 100 order by name limit 10 ;

在这里插入图片描述

优化后：

mysql> create index idx_name_emp_1 on emp(name);
mysql> explain select id ,empno ,name from emp where deptId = 100  order by name limit 10 ;

在这里插入图片描述

（2) Using temporary
使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
优化前：

mysql> explain select  name, count(*) from emp where deptid = 100 group by name limit 10 ;

在这里插入图片描述

优化后：

mysql> create index idx_name_emp_1 on emp(name);
mysql> explain select  name, count(*) from emp where deptid = 100 group by name limit 10 ;

在这里插入图片描述

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

mysql> explain select id from emp where id >1000 order by  id ;

在这里插入图片描述

（4) Using where：表明使用了where过滤。
（5) Using join buffer：使用了连接缓存。

mysql> explain select * from emp ,dept where emp.empno = dept.ceo ;

在这里插入图片描述

（6) impossible where：
where子句的值总是false，不能用来获取任何元组。

mysql> explain select empno, name from emp where empno >200000 and empno < 100000;

在这里插入图片描述

MySQL5.5的结果:
在这里插入图片描述

（7) select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询Explian生成的阶段即完成优化。
在innodb中：

mysql> explain select max(id) from emp ;

在这里插入图片描述

在MyISAM中：
在这里插入图片描述

6. 批量数据脚本

6.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;

（2) 开启log_bin_trust_function_creators

它控制是否可以信任存储函数创建者

mysql> show variables like 'log_bin_trust_function_creators'; //查询
mysql> set global log_bin_trust_function_creators=1;  //开启

设置永久生效方式：修改/etc/my.cnf 文件

[mysqld]
log_bin_trust_function_creators=1

6.2 编写随机函数

（1) 生成随机字符串

DELIMITER $$  -- 修改SQL的结束符号（默认就是;）
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 $$

（2) 如果要删除函数，则执行：drop function rand_string

（3) 生成随机数字

#用于随机产生多少到多少的编号
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$$

（4) 如果要删除函数：drop function rand_num

6.3 创建存储过程

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

DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
#set 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;

（2) 创建往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;

6.4 调用存储过程

（1) 添加数据到部门表

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

（2) 添加数据到员工表

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

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

（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$$

（2) 执行存储过程

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

7. 单表使用索引常见的索引失效

7.1 全值匹配我最爱

（1) 建立索引

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

（2) 查看下列sql的Explian

 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'

在这里插入图片描述

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到！

(3) 更换条件顺序查看索引能否被应用

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

在这里插入图片描述

结论：SQL中查询字段的顺序，与使用索引中字段的顺序没有关系。

7.2 最佳左前缀法则

使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
在这里插入图片描述

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

7.3 索引列上做计算

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

（1) 在查询列上使用了函数

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

在这里插入图片描述

结论：等号左边无计算！

（2) 在查询列上做了转换

create index idx_name on emp(name);
explain select sql_no_cache * from emp where name = '30000';
explain select sql_no_cache * from emp where name = 30000;

在这里插入图片描述

结论：等号右边无转换！

7.4 索引列上进行范围查询

（1) 在索引列上使用范围查询

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<5 AND emp.name = 'abcd';

在这里插入图片描述

建议：将可能做范围查询的字段的索引顺序放在最后

7.5 使用覆盖索引

（1) 查询列和索引列时，不要写 select *

explain  SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';
explain  SELECT SQL_NO_CACHE age,deptId,name  FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';

在这里插入图片描述

7.6 使用不等于(!= 或者<>)

（1) 使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。

mysql> explain select SQL_NO_CACHE *  from emp where emp.age = 30 ;
mysql> explain select SQL_NO_CACHE *  from emp where emp.age != 30 ;

在这里插入图片描述

7.7 is not null 和 is null

（1) 查看索引字段是否允许为空

mysql> desc emp;

（2) 当字段允许为 null 的条件下

explain select * from emp where age is null 
explain select * from emp where age is not null

在这里插入图片描述

结论：is not null 用不到索引，is null 可以用到索引。

7.8 like的前后模糊匹配

(1) 前缀模糊查询会导致索引失效

mysql> create index idx_name_emp on emp (name);
mysql> explain select * from emp where name like '%a' ;
mysql> explain select * from emp where name like '%a%' ;
mysql> explain select * from emp where name like 'a%' ;

在这里插入图片描述

7.9 使用or

(1) OR查询会导致索引失效

mysql> explain select * from emp where age = 30 or age = 40 ;

在这里插入图片描述

(2) 使用 union all 或者 union 来替代

mysql> explain select * from emp where age = 30 union all select * from emp where age = 40 ;

在这里插入图片描述

7.10 总结练习

建立索引index(a,b,c)，判断下列索引引用情况。
在这里插入图片描述

7.11 口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；

8. 查询优化

8.1 关联查询优化

（1) 建表语句

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

8.1.1 left join优化

（1) 查看Explian

mysql> explain select * from class left join book on class.card = book.card ;

在这里插入图片描述

（2) 在book表上建立索引

mysql> ALTER TABLE `book` ADD INDEX  idx_card(`card`);

在这里插入图片描述

在class表上建立索引

mysql> drop index idx_card on book;
mysql> alter table class add index idx_card(cad);

在这里插入图片描述

结论：在优化关联查询时，只有在匹配表上建立索引才有效。
left join时，左侧的为驱动表，右侧为匹配表！

8.1.2 inner join优化

（1) 查看Explian

mysql> explain select * from book inner join class on class.card = book.card;

在这里插入图片描述

（2) 在book表上建索引

mysql>ALTER TABLE `book` ADD INDEX  idx_card(`card`);
mysql> explain select * from book inner join class on class.card = book.card;

在这里插入图片描述

调换两个表的顺序

mysql> explain select * from class inner join book on class.card = book.card;

在这里插入图片描述

（3) 在book表中，删除9条记录

mysql> delete from book where bookid <10 ;
mysql> select count(*) from book ;
mysql> select count(*) from class ;

在这里插入图片描述

mysql> CALL proc_drop_index("mydb","book");
mysql> explain select * from class inner join book on class.card = book.card;

在这里插入图片描述

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

（4) straight_join: 效果和inner join一样，但是会强制将左侧作为驱动表！

mysql> explain select * from class straight_join book  on class.card= book.card;

在这里插入图片描述

8.1.3 四个关联查询案例分析

（1) 案例一

EXPLAIN SELECT ed.name '人物',c.name '掌门' FROM 
(SELECT e.name,d.ceo from emp e LEFT JOIN dept d on e.deptid=d.id) ed
 LEFT JOIN emp c on ed.ceo= c.id;

在这里插入图片描述

MySQL5.5结果:
在这里插入图片描述

（2) 案例二

EXPLAIN SELECT e.name '人物',tmp.name '掌门'
FROM emp e LEFT JOIN (SELECT d.id did,e.name FROM dept d LEFT JOIN emp e ON d.ceo=e.id)tmp 
ON e.deptId=tmp.did;

在这里插入图片描述

MySQL5.5结果:
在这里插入图片描述

（3) 案例三

EXPLAIN SELECT e1.name '人物',e2.name '掌门' 
 FROM emp e1 
 LEFT JOIN dept d on e1.deptid = d.id
 LEFT JOIN emp e2 on d.ceo = e2.id ;

在这里插入图片描述

MySQL5.5结果:
在这里插入图片描述

（4) 案例四

Explain SELECT e2.name '人物',
(SELECT e1.name FROM emp e1 where e1.id= d.ceo) '掌门'
 from emp e2 LEFT JOIN dept d on e2.deptid=d.id;

在这里插入图片描述

MySQL5.5结果：
在这里插入图片描述

8.1.4 建议

（1) 保证被驱动表的 join 字段已经被索引
（2) left join 或者 right join时，选择小表作为驱动表，大表作为被驱动表
（3) inner join 时，mysql会自己把小表选为驱动表
（4) 子查询尽量不要放在被驱动表，有可能使用不到索引
（5) 能直接多表关联就尽量直接关联，不用子查询

8.2 子查询优化

（1) 取所有不为掌门人的员工，按年龄分组！

mysql> CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
mysql> explain select age as '年龄', count(*) as '人数' from emp where id  not in 
(select ceo from dept where ceo is not null) group by age;

在这里插入图片描述

（2) 解决dept表的全表扫描，建立ceo字段的索引
在这里插入图片描述

（3) 进一步优化，替换not in。

 mysql>explain select age as '年龄',count(*) as '人数' from emp e left join dept d on e.id=d.ceo
 where d.id is null group by age;

在这里插入图片描述

结论：在范围判断时，尽量不要使用not in和not exists，使用 left join on xxx is null代替。

8.3 排序分组优化

where 条件和 on 的判断这些过滤条件，作为优先优化的部分，是要被先考虑的！其次，如果有分组和排序，那么也要考虑group by 和order by。

8.3.1 无过滤，不索引

（1) 创建索引，查看Explian

create index idx_age_deptid_name on emp (age,deptid,name);
explain select * from emp where age=40 order by deptid;
explain  select * from emp order by age,deptid;
explain  select * from emp order by age,deptid limit 10;

在这里插入图片描述

结论：using filesort说明进行了手工排序。原因在于没有where作为过滤条件。where，limt都相当于一种过滤条件，所以才能使用上索引。

8.3.2 顺序错，必排序

（2) 查看Explian

explain  select * from emp where age=45 order by deptid,name;
explain select * from emp where age=45 order by deptid,empno;
explain  select * from emp where age=45 order by  name, deptid;
explain select * from emp where deptid=45 order by age;

在这里插入图片描述

结论:正常使用索引
在这里插入图片描述

结论：empno字段并没有建立索引，因此也无法用到索引，此字段需要排序！
在这里插入图片描述

结论：where 两侧列的顺序可以变换，但是order by列的顺序不能随便变换！
在这里插入图片描述

结论：deptid作为过滤条件的字段，无法使用索引，因此排序没法用上索引

8.3.3. 方向反，必排序

（1) 查看Explian

explain select * from emp where age=45 order by  deptid desc, name desc ;

在这里插入图片描述

结论：如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。

explain select * from emp where age=45 order by  deptid asc, name desc ;

在这里插入图片描述

结论：如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的！

8.3.4. 索引的选择

（1) 清除emp上面的所有索引，只保留主键索引！

CALL proc_drop_index("mydb","emp");

（2) 查询年龄为30岁的，且员工编号小于101000的用户，按用户名称排序

explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

在这里插入图片描述

（3) 优化：创建一个此三个字段的复合索引

create index idx_age_empno_name on emp(age,empno,name);

在这里插入图片描述

再次查询，发现using filesort依然存在。empno是范围查询，因此导致了索引失效，所以name字段无法使用索引排序。所以，三个字段的复合索引，没有意义，因为empno和name字段只能选择其一！

（4) 解决：要么选择empno,要么选择name

CALL proc_drop_index("mydb","emp");
create index idx_age_name on emp(age,name);
create index idx_age_empno on emp(age,empno);
explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

在这里插入图片描述

原因：所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，使用empno字段的范围查询，过滤性更好（empno从100000开始）！
结论：当范围条件和 group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

8.3.5 group by

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

在这里插入图片描述

8.3.6 使用覆盖索引

简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键
SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

在这里插入图片描述

9. 慢查询

9.1 慢查询的定义

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析

9.2 开启慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
（1) 查看慢查询日志是否开启

mysql> show variables like "%slow_query_log%";

在这里插入图片描述

（2) 开启慢查询日志

mysql> set global slow_query_log =1 ;

在这里插入图片描述
因为slow_query_log是一个全局变量，因此需要加上 global关键字.
如果要永久生效,需要修改/etc/my.cnf配置文件

[mysqld]
slow_query_log =1   #开启慢查询
slow_query_log_file=/var/lib/mysql/atguigu-slow.log  #慢查询日志位置

（3) 多久算慢查询
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，MySQL会将执行时间大于 long_query_time的SQL记录到日志中。

mysql> show variables like "%long_query_time%";

在这里插入图片描述

9.3 查看慢查询日志

（1) 跟随查询

[root@hadoop205 ~]# tail -f /var/lib/mysql/xxxx.log

（2) 通过Mysql方式查看

mysql> show status like '%slow_queries%';

在这里插入图片描述
（3) 日志分析工具mysqldumpslow

10. 视图

10.1 是什么

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

10.2 干什么

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

10.3 使用

（1) 创建视图

CREATE  OR  REPLACE  VIEW  视图名  AS  SQL语句

（2) 使用视图

select  *  from  视图名

11. 主从复制

11.1 主从复制基本原理

在这里插入图片描述

（1) master将改变记录到二进制日志（binary log）。
这些记录过程叫做二进制日志事件，binary log events；
不管使用何种存储引擎，在server层都可以开启binlog日志功能。binlog会记录所有的逻辑操作，并且是采取追加写的形式，将写操作命令，记录在一个二进制文件中。因此binlog日志通常用于恢复数据，或者是主从复制。
（2) slave将master的binary log events拷贝到它的中继日志（relay log）；
（3) slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

11.2 复制的基本原则

（1) 每个slave只有一个master
（2) 每个slave只能有一个唯一的服务器ID
（3) 每个master可以有多个salve

11.3 一主(hadoop100)一从(hadoop101)

（1) 修改主机hadoop100的 my.cnf配置文件

[mysqld]
server-id=1    #server实例的id
log-bin=/var/lib/mysql/mysql-bin   #log-bin文件存储位置
binlog_format=ROW  # 设置log-bin格式 STATEMENT   ROW  MIXED  

#可选的配置
binlog-ignore-db=mysql  # 设置不要复制的数据库
binlog-do-db=xxx  # 设置需要复制的主数据库名字

注意:修改完后重启MySQL服务

（2) 修改从机hadoop101的my.cnf 配置文件

[mysqld]
server-id=2    #server实例的id
relay-log=mysql-relay   #中继日志

注意:修改完后重启MySQL服务

（3) 在主机hadoop100上建立账户并授权从机slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'从机器数据库IP' IDENTIFIED BY '密码';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.202.101' IDENTIFIED BY '123456';

（4) 查询注解hadoop100 的状态

mysql> show master status ;

注意:记录下File 和Position的值，切记不要再操作主机MySQL，防止主机的 File 和 Position 值发生变化

（5) 在从机hadoop101上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='主机IP',
        MASTER_USER='主机创建好的用户',
        MASTER_PASSWORD='密码',
        MASTER_LOG_FILE='File名1字',
        MASTER_LOG_POS=Position数字;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.202.100',
       MASTER_USER='slave',
       MASTER_PASSWORD='123456',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=154;

（6) 在从机hadoop101上启动主从复制功能

mysql> start slave;

（7) 在从机hadoop101查看主从复制状态

mysql> show slave status\G;

主要查看两个参数:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

（8) 测试主从
在主机hadoop100上新建库、新建表、插入数据，查看从机是否复制

（9) 在从机hadoop101停止主从

mysql> stop slave ;

Nancy_G

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录