首页  思维导图  详情

MySQL

2023-02-25 13:47:36   0  举报





仅支持查看

AI智能生成

Mysql

学习笔记

作者其他创作

大纲/内容

SQL 基础

卸载 / 安装 / 修改远程链接

https://dev.mysql.com/downloads/mysql/

卸载

停止当前服务mysql： systemctl stop mysqld

查看当前MySQL安装状况 rpm -qa|grep -i mysql

卸载上述命令查询出的所有已安装程序

mysql5

rpm -e --nodeps mysql57-community-release rpm -e --nodeps mysql-community-server rpm -e --nodeps mysql-community-common rpm -e --nodeps mysql-community-libs rpm -e --nodeps mysql-community-client

mysql8

rpm -e --nodeps mysql-community-common rpm -e --nodeps mysql-community-server rpm -e --nodeps mysql-community-libs rpm -e --nodeps mysql-community-icu-data-files rpm -e --nodeps mysql-community-client rpm -e --nodeps mysql-community-client-plugins

查找并删除残留文件

find / -name mysql

mysql5

rm -rf /var/lib/mysql rm -rf /usr/share/mysql rm -rf /etc/selinux/targeted/active/modules/100/mysql rm -rf /etc/selinux/targeted/tmp/modules/100/mysql ................ docker 镜像文件配置不需要删除

mysql8

rm -rf /etc/selinux/targeted/active/modules/100/mysql rm -rf /etc/selinux/targeted/tmp/modules/100/mysql rm -rf /usr/lib64/mysql rm -rf /var/lib/mysql ................ docker 镜像文件配置不需要删除

删除配置文件和日志

rm -f /etc/my.cnf* rm -f /var/log/mysqld.log

安装

查询是否有依赖没有需要域名yum install

rpm -qa|grep libaio rpm -qa|grep net-tools

检查/tmp临时目录权限，如果不是777则需要设置限权

ll -a /

chmod -R 777 /tmp

上传文件并解压

cd /opt tar xvf mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

安装

rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm # -i, --install 安装软件包 # -v, --verbose 提供更多的详细信息输出 # -h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

查验是否安装成功并查验版本

rpm -qa|grep -i mysql mysqladmin --version

初始化mysql

#初始化数据目录并生成初始密码 mysqld --initialize --user=mysql #查看数据目录 ls /var/lib/mysql/

查找初始密码

#mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。 grep 'temporary password' /var/log/mysqld.log 或者使用 more /var/log/mysqld.log

修改初始密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; -- 或者 SET password FOR 'root'@'localhost'= '123456';

远程链接

- `Host=localhost`，表示只能通过本机客户端去访问。 - `Host=%` ，表示所有IP都有连接权限。

UPDATE user SET Host = '%' WHERE User ='root'; FLUSH PRIVILEGES;

配置新连接报错：错误号码 2058，出现这个原因是MySQL 8 之前的版本中加密规则是mysql_native_password，而在MySQL 8之后，加密规则是caching_sha2_password。解决方案有两种，一种是升级SQLyog和Navicat（因此，新版SQLyog不会出现此问题），另一种是把MySQL用户登录密码加密规则还原成mysql_native_password。 **解决方法：**Linux下 mysql -uroot -p 登录你的 MySQL 数据库，然后执行这条SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

mysql5 ---> 8 区别 UTF-8

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。（无法存储emoji表情 utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

MySQL 8版本之前，默认字符集为 latin1 ，不支持中文，使用前必须设置字符集为utf8（utf8mb3）或utf8mb4。从MySQL 8开始，数据库的默认字符集为 utf8mb4 ，从而避免中文乱码的问题。

登录MySQL服务器与用户管理及权限管理

完整语句

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e 'SQL语句'

创建用户

语法：CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

-- 默认host是 % CREATE USER zhang3 IDENTIFIED BY '123456';

-- 进允许本地连接 CREATE USER 'li4'@'localhost' IDENTIFIED BY '123456';

删除用户

语法： DROP USER user[,user]…;

DROP USER zhang3; -- 默认删除host为%的用户

DROP USER 'wang5'@'localhost';

权限管理

查看有哪些权限：SHOW PRIVILEGES;

权限

（1） `CREATE和DROP权限` 可以创建新的数据库和表，或删除已有的数据库和表。（2） `SELECT、INSERT、UPDATE和DELETE权限` 允许在一个数据库现有的表上实施操作。（3） `INDEX权限` 允许创建或删除索引。（4） `ALTER权限` 可以使用ALTER TABLE来更改表的结构和重新命名表。（5） `GRANT权限` 允许授权给其他用户，可用于数据库、表和保存的程序。

设置权限

语法： GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

GRANT ALL PRIVILEGES ON *.* TO 'tian7'@'%'; --授权所有限权，除给其他用户授权外

放到授权最后，给其他用户授予授权的权限 WITH GRANT OPTION

GRANT SELECT, INSERT, UPDATE ON atguigudb.* TO 'zhao6'@'%'; --授权

查看当前用户或其他用户限权

当前用户：SHOW GRANTS;

其他用户：SHOW GRANTS FOR '用户名'@'主机地址';

回收限权

REVOKE 权限1, 权限2, …权限n ON 数据库名称.表名称 FROM '用户名'@'主机地址';

收回某库限权：REVOKE SELECT ON atguigudb.* FROM 'zhao6'@'%';

收回所有限权：REVOKE ALL PRIVILEGES ON *.* FROM 'zhao6'@'%';

SQL语句

sql_mode

宽松模式 vs 严格模式

宽松模式： 执行错误的SQL或插入不规范的数据，也会被接受，并且不报错。 严格模式： 执行错误的SQL或插入不规范的数据，会报错。MySQL5.7版本开始就将sql_mode默认值设置为了严格模式。

查看和设置sql_mode

查询当前sql_mode模式： SELECT @@session.sql_mode; SELECT @@global.sql_mode; -- 或者 SHOW VARIABLES LIKE 'sql_mode'; --session级别

设置sql_mode模式： SET GLOBAL sql_mode = '模式值'; --全局，要重新启动客户端生效，重启MySQL服务后失效 SET SESSION sql_mode = '模式值'; --当前会话生效效，关闭当前会话就不生效了。可以省略SESSION关键字

在 /etc/my.cnf 中配置，永久生效 [mysqld] sql-mode = '模式值'

常用模式

设置sql_mode 模式： SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION';

sql_mode说明

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。

STRICT_TRANS_TABLES

- 对于支持事务的表，如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。 - 对于不支持事务的表，不做限制，提高性能。

NO_ZERO_IN_DATE

不允许日期和月份为零。

NO_ZERO_DATE

MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO

在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL。

NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代。

SQL 高级-逻辑架构

逻辑架构剖析图

SQL语句执行流程图(一)

SQL语句执行流程图(二)

SQL流程话术

MySQL服务器之外的客户端程序，与具体的语言相关，例如Java中的JDBC，图形用户界面SQLyog等。`本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。`

SQL语句通过拿取链接和数据库建立TCP链接

1.经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做`身份认证、权限获取`。 2. TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

SQL语句通过缓存和缓冲器；查询缓存是否有该语句为Key值的查询结果，如果有，直接返回，没有继续查询

mysql8舍弃这一部分，太鸡肋；要求完全一直，包括空格等一系列信息，太苛刻

通过SQL Interface 判断是DDL DML，并拿到相应接口

通过SQL解析器 Parser，解析SQL语句并生成一棵解析树 ; 并且判断语句是否有错误，包括语法错误和逻辑错误

通过 Optimizer 语句优化器对SQL语句优化

通过调用 Storage Engines 的各种引擎来查询结果

Sql执行流程开启查看

SHOW VARIABLES LIKE '%profiling%';

SET profiling = 1; -- profiling = ON profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：

显示最近几次查询：SHOW PROFILES;

步骤

- 主要步骤： - checking permissions：检查权限 - Opening tables：打开表 - init ：初始化 - System lock ：系统锁 - optimizing ：优化sql - statistics ：统计 - preparing ：准备执行 - executing ：执行sql - Sending data ：发送数据 - Sorting result ：排序 - end ：结束 - query end ：查询结束 - closing tables ：关闭表／去除TMP 表 - freeing items ：释放 - cleaning up ：清理

执行引擎:

查看MySQL提供什么存储引擎: SHOW ENGINES;

也可以通过以下语句查看默认的存储引擎：SHOW VARIABLES LIKE '%default_storage_engine%';

设置默认引擎：SET DEFAULT_STORAGE_ENGINE=MyISAM;

各类引擎说明

分支主题

MyISAM 和 InnoDB 引擎的区别

InnoDB 和 MyISAM 对比图(一)

SQL高级-索引与树

索引

概念

索引(index) 帮助MySQL 高效获取数据的数据结构；

优点：

（1）.快速找到内容，降低数据库IO成本(次数) （2）.通过创建唯一索引，保证数据的唯一性 （3）.加速表和表之间的连接。对于有依赖关系的子表和父表联合查询时，可以提高查询速度。 （4）.在使用分组和排序子句进行数据查询时，减少查询时间，降低CPU的消耗。

缺点:

索引维护成本高、占用储存空间、在增删改情况多的时候，调整索引成本高

分类

### 索引分类 - 从功能逻辑上划分：普通索引、唯一索引、主键索引、全文索引。 - 按照作用字段个数划分：单列索引和联合索引。 - 按照物理实现方式划分：聚簇索引和非聚簇索引。

普通索引指在数据表中非唯一和主键值的列

唯一索引是给唯一键建立索引

主键索引InnoDB执行引擎自动创建，手动创建表格时创建索引也可以

全文索引不建议创建，会导致表与表关系错综复杂且难以管理

单例索引是指一个字段建立索引；

联合索引指多字段建立索引;使用多字段查找时会速度更快

聚簇索引是指主键索

聚簇索引储存：索引值（主键值）数据页（指向对应数据行的物理指针 / 磁盘块）

InnoDB引擎的表中必须要有一个聚簇索引；没有聚簇索引也会自动选择一个唯一键称为聚簇索引，如果没有唯一键那么就会创立一个隐藏的聚簇索引

（1）索引和数据保存在同一个B+树中 （2）页内的记录是按照主键的大小顺序排成一个单向链表 。 （3）页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。 （4）非叶子节点存储的是记录的主键+页号。 （5）叶子节点存储的是完整的用户记录。

优势： （1）数据访问更快 ，索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。 （2）聚簇索引对于主键的排序查找和范围查找速度非常快。  （3）按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作`。

劣势： （1）插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。 （2）更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

非聚簇索引是指非主键的普通索引

InnoDB：使用非聚簇索引查询时，需要根据非聚簇索引进行查询到相应的主键所在指针（物理地址）后，回表根据主键地址查询数据库；保证主键

MyISAM：使用非聚簇索引查询时，通过非聚簇索引进行查询相应主键物理地址（指针）后，去数据文件中查找数据(根据主键地址查找到值后根据主键查找而非根据聚簇索引查找)；

介绍： （1）页内的记录是按照大小顺序排成一个单向链表。 （2）页和页之间也是根据页中记录的大小顺序排成一个双向链表 。 （3）非叶子节点存储的是记录的查询条件的值+页号 （4）叶子节点存储的并不是完整的用户记录，而只是查询条件+主键这两个列的值。

InnoDB非聚簇索引回表图(一)

索引操作

创建索引方式：

ALTER TABLE ADD ( 索引值1，索引值2，........ ) ALTER TABLE customer1 ADD PRIMARY KEY (id);-- 主键索引 ALTER TABLE customer1 ADD UNIQUE INDEX uk_no (customer_no);-- 唯一索引 ALTER TABLE customer1 ADD INDEX idx_name (customer_name);-- 普通索引 ALTER TABLE customer1 ADD INDEX idx_no_name (customer_no,customer_name); -- 复合索引

CREATE 索引名 ON 表名 ( 索引值1，索引值2，....... ) CREATE PRIMARY KEY (id); --主键索引 CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引 CREATE INDEX idx_name ON customer1(customer_name);-- 普通索引 CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

对于Varchar类型的数据，如果数据过长，可以通过创建部分长度索引； 需要注意的是，前缀索引和函数索引都会导致一些查询时索引失效，因此需要根据实际情况进行权衡和选择。 CREATE INDEX  索引名  ON  表名(字段名(截取长度)); CREATE INDEX idx_address_prefix ON table_name(address(12));--前缀索引 CREATE INDEX  索引名  ON  表名(字段名(截取长度)); CREATE INDEX idx_address_func ON table_name(LEFT(address, 12));--函数索引

查看索引方式

SHOW INDEX FROM customer;

删除索引

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

索引的创建与否的判断依据

**哪些情况适合创建索引：** （1）主键自动建立唯一索引 （2）字段的值有唯一性的限制 （3）频繁作为WHERE查询条件的字段 （4）UPDATE、DELETE的WHERE条件列 （5） 经常GROUP BY 和 ORDER BY的列 （6）DISTINCT字段需要创建索引- 多表JOIN时，对连接字段创建索引 （7） 使用字符串前缀创建索引 （8） 区分度高的列（重复的数据少）适合作为索引- 使用频繁的列，放到联合索引的左侧- 多个字段都需要创建索引时，联合索引优于单值索引

**哪些情况不要创建索引：** （1）WHERE里用不到的字段不创建索引 （2）表的数据记录太少 （3）有大量重复数据的列上 （4）避免对经常增删改的表创建索引 （5）要定义冗余或重复的索引 （6）删除不再使用或很少使用的索引

树

概念：树有很多种，每个节点最多只能有两个子节点的一种形式称为二叉树。二叉树的子节点分为左节点和右节点。

二叉树

二叉搜索树BST

二叉树基础上演示出左节点比父节点小，右节点比父节点大；特别说明：如果有相同的值，可以将该节点放在左子节点或右子节点。

BST的生成演示：https://www.cs.usfca.edu/~galles/visualization/BST.html

问题：当节点都是小于父节点时，那么就会都在左边，形成类似于链表形式的树，不利于查找

平衡二叉树AVL

在搜索树基础上，添加了随时调整节点功能，使得树两边高度差值的绝对值小于等于1左右两边子树子树也是一颗平衡二叉树

AVL的生成演示：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

问题：当海量数据存储，查询时无法将数据一下子加载到内存中，只能逐点加载（一个节点一次IO）；磁盘IO次数和树的高度有关；海量数据导致树过高IO过于频繁，效率低下

多叉树

在平衡二叉树基础上，非叶子节点上可以放多个数据；重新组织节点，减少节点数量，增加分叉，减少树的高度

问题：多叉树降低了高度，但是没有规定树中数据排放方式；

多叉树（一）

B树 B-Tree

经过发展，多叉树已经把书高度降得很低了，没有规定数据按照什么规则进行排放；所以出现了B树；

B 树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间可以忽略不计。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。B 树相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能。

B树数据是由一个个的磁盘块组成，一个磁盘块默认128KB；磁盘块内部存储：主键值、指针、data：主键外的其他数据值；

非叶子节点内部：主键值、指针、键值的data数据 叶子节点内部：主键值和data数据

问题：①在大量数据情况下（千万）：树存储信息都是完整数据，导致树的高度较高；           ②同高度下磁盘块之间没有关联性：对于特殊需求（查询范围内的值）查询不方便，在同一高度也需要多次IO查询

示意图

B树简单示意图(一)

B树完整示意图(一)

B+树 B+ Tree

介绍：B+树节点由数据页来组成的 数据页就是磁盘块的另一种说法 ；默认也是128KB ; 数据页：目录项、主键值、数据值、指针（下一条记录next_record 或下一个数据页和上一个数据页的双向链表）； 数据分布：从小到大或从大到小，由数据页的record_type决定

- record_type：表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录（目录项记录）。 - next_record：表示下一条记录的相对位置，我们用箭头来表明下一条记录。 - 各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。（示意图中显示） - 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

在B+树的叶子节点层，双向链表形成了整个索引的顺序，这就可以实现范围查询和排序操作。同时，在非叶子节点上也可以设置同层次的节点之间的指针(非叶子节点之间由双向链表连接)，这样可以提高非叶子节点的遍历效率。

B+树数据结构：非叶子节点不存储数据，只存储对应的数据页和主键和主键值叶子节点存储数据值和双向链表；

特点：

（1）B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。 （2）B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。 （3）B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。 （4）补充：IO的次数和树的高度有关，减少IO次数就需要降低树的高度；

B+树容纳数据量： 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 16 条记录。 如果B+树有2层，最多能存放 `1600×16=25600` 条记录。 如果B+树有3层，最多能存放 `1600×1600×16=40960000` 条记录。- 如果存储千万级别的数据，只需要三层就够了

示意图

B+树单数据页横示意图(一)

B+树单数据页竖示意图(二)

B+树数据页示意图(三)

B+树完整模块示意图(一)

完整示意图(二)

B和B+树区别

相同条件下，数据量在千万之上，B+树的非叶子节点能存放更多的主键信息和数据页信息；而B树存放了指针和主键和完整数据情况下，数据会占用存储空间，导致B树的高度会更高；而高度越高，发生IO操作次数会更多；导致数据优势降低；所以在大量数据情况下，B+树优势更大，B树在小数据量情况下优势大；

B树存储的是指针和数据值和主键值，B+树非叶子节点存储的是数据页值和主键值；所以B+树的非叶子节点上能存储的数据会更多，树的高度会更低；在数据量非常庞大（千万级别）时，B+树比B树会更好，但是如果数据量不大那么B树会更有优势

补充：阿里规范：单表在2GB数据量以上或500w条数据以上才会推荐分库分表

SQL高级-索引优化

优化索引概念：MySQL中`提高性能`的一个最有效的方式是对数据表`设计合理的索引`。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。 我们创建索引后，用不用索引，最终是优化器说了算。`优化器会基于开销选择索引`，怎么开销小就怎么来。不是基于规则，也不是基于语义。 另外`SQL语句是否使用索引，和数据库的版本、数据量、数据选择度（查询中选择的列数）都有关系`。

性能分析器：EXPLAIN

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

演示

多表关联：t1为驱动表，t2为被驱动表。 （在INNER(内连接)中，表数据少的为驱动表，LEFT(左连接) t1是驱动表和RIGHT(右连接)中 t2是驱动表） EXPLAIN SELECT * FROM t1 INNER JOIN t2;

单表查询： EXPLAIN SELECT * FROM t1;

解析查询概念：

id：查询次数(IO次数) ；一般情况下都是1，子查询时会发生变化；值相同时是从上到下顺序执行，值不同时是从上到下顺序执行且执行IO次数是值

select_type：查询语句评级

**SIMPLE：**简单查询。查询中不包含子查询或者UNION。

EXPLAIN SELECT * FROM t1;

**PRIMARY：**主查询。查询中若包含子查询，则最外层查询被标记为PRIMARY。 **SUBQUERY：**子查询。在SELECT或WHERE列表中包含了子查询。

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content= 'a');

**DEPENDENT SUBQUREY：**如果包含了子查询，并且查询语句不能被优化器转换为连接查询，并且子查询是`相关子查询（子查询基于外部数据列）`，则子查询就是DEPENDENT SUBQUREY。

EXPLAIN SELECT * FROM t3  WHERE id = ( SELECT id FROM t2 WHERE content = t3.content );

**UNCACHEABLE SUBQUREY：**表示这个subquery的查询要受到外部系统变量的影响

EXPLAIN SELECT * FROM t3  WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server );

**UNION：**对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。 **UNION RESULT：**UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是"UNION RESULT"。

**DEPENDENT UNION：**子查询中的UNION或者UNION ALL，除了最左边的查询是DEPENDENT SUBQUREY，其余的查询都是DEPENDENT UNION。

**DERIVED：**在包含`派生表（子查询在from子句中）`的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。

**MATERIALIZED：**优化器对于包含子查询的语句，`如果选择将子查询物化后再与外层查询连接查询`，该子查询的类型就是MATERIALIZED。如下的例子中，查询优化器先将子查询转换成物化表，然后将t1和物化表进行连接查询。

table : 使用的表

partitions：分区命中情况，非分区表，为NULL

type：

结果值从最好到最坏依次是：  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

阿里巴巴开发手册要求：SQL 性能优化的目标：至少要达到 `range` 级别，要求是 `ref` 级别，最好是 `consts`级别。

possiblie_keys：可能用到的索引

key：实际用到的索引

key_len：使用的字节数；（值越大越好）

计算公式：

ref：显示与key中的索引进行比较的列或常量

rows：查询所扫描的行数。越小越好

filtered：最后查询出来的数据占所有服务器端检查行数（rows）的`百分比`。值越大越好。

Extra：包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来`理解MySQL到底将如何执行当前的查询语句`。

**Impossible WHERE**：where子句的值总是false

**Using where：**使用了where，但在where上有字段没有创建索引

**Using temporary：**使了用临时表保存中间结果

**Using filesort：**排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort）

**Using index：**使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

**Using index condition：**叫作  `Index Condition Pushdown Optimization （索引下推优化ICP）：找到满足条件的数据后进行判断是否满足Index中剩余条件是否符合，符合返回给server层，不满足就再找下一条

**Using join buffer：**在连接查询时，当`被驱动表`不能有效的利用索引时，MySQL会提前申请一块内存空间（join buffer）来加快查询速度

`课外阅读：`在没有索引的情况下，为了优化多表连接，减少磁盘IO读取次数和数据遍历次数，MySQL为我们提供了很多不同的连接缓存的优化算法；  可参考https://blog.csdn.net/qq_35423190/article/details/120504960 - `Using join buffer (hash join)`**8.0新增：**连接缓存（hash连接） `速度更快` - `Using join buffer (Block Nested Loop)`**5.7**：连接缓存（块嵌套循环）

-- 显示sql语句执行时间 SET profiling = 1; SHOW VARIABLES  LIKE '%profiling%'; SHOW PROFILES;

索引失效

单表索引失效：

计算、函数导致索引失效

模糊查询 LIKE 中%在前面(全表扫描)，索引失效

不等于 ( !=  或者 <> )导致索引失效

IS NOT NULL 导致索引失效

数据库中的数据的索引列的NULL值达到比较高的比例的时候`，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）

类型转换导致索引失效

EXPLAIN SELECT * FROM emp WHERE name= '123' ;  EXPLAIN SELECT * FROM emp WHERE name=  123 ; --索引失效 name 是 varchar 类型数据输入查询条件是 int 类型，类型转换

全值匹配我最爱（查询语句效率最高）

左前缀法则：联合索引需要遵循，查询从最左边的索引开始，不能跨越索引； 如：索引index_test（A,B,C）中，三个列组成一个联合索引，查询条件如果只有A和C，那么B,C都不会被索引使用；       使用B或者C或者B,C；不使用A，那么这个索引就不会被使用；

索引中范围条件右边的列失效，如果范围条件对于优化器来说不合适，那么就会直接全表扫描；索引失效

关联查询优化：

左外连接：左表是驱动表（会进行全表扫描），右表是被驱动表；被驱动表会根据有用的索引进行查找，如果被驱动表没有合适的索引，那么就被驱动表也会全表扫描

被驱动表没有索引：驱动表全部数据 * 被驱动表所有数据（ 16 * 20 次） 被驱动表有索引：驱动表数据 * 被驱动表数据（ 16 * 1 次）

内连接：             ①如果都有（都没有）索引：SQL优化器会自动选择数据少的表作为驱动表进行全表扫描，另一个是被驱动表；             ②如果一个有表索引，一个表没有索引情况下，那么就会选择没有索引的作为驱动表；

总结： ①保证被驱动表的JOIN字段已经创建了索引 ②需要JOIN 的字段，数据类型保持绝对一致。 ③LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数。 ④INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表 。选择相信MySQL优化策略。 ⑤能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数) ⑦不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。 ⑥ 衍生表建不了索引

子查询优化（不建议使用子查询）

**子查询的执行效率不高。**原因： ① 执行子查询时，MySQL需要为内层查询语句的查询结果`建立一个临时表` ，然后外层查询语句从临时表 中查询记录。查询完毕后，`再撤销这些临时表` 。这样会`消耗过多的CPU和IO资源`，产生大量的慢查询。 ② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘`临时表都不会存在索引` ，所以查询性能会 受到一定的影响。 ③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询  要快 ，如果查询中使用索引的话，性能就会更好。 结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化：

-- 创建索引 CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

1. 无过滤，不索引

-- 没有使用索引： EXPLAIN SELECT * FROM emp ORDER BY age,deptid; -- 使用了索引：order by想使用索引，必须有过滤条件，索引才能生效，limit也可以看作是过滤条件 EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

2. 顺序错，不索引

-- 使用了索引： -- 注意：key_len = 5是where语句使用age索引的标记，order by语句使用索引不在key_len中体现。 EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid; -- 使用了索引： EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`;  -- 没有使用索引：因为索引列中不存在empno EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno; -- 没有使用索引：order by 后的排序条件的顺序，与索引顺序不一致 EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid; -- 没有使用索引：出现的顺序要和复合索引中的列的顺序一致！ EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

3. 方向反，不索引

-- 使用了索引：排序条件和索引一致，并方向相同，可以使用索引 SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC , `name` DESC; -- 没有使用索引：两个排序条件方向相反 SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC , `name` DESC;

SQL高级-慢查询日志于View视图

SQL高级-集群与分库分表