MySQL高级

本文主要是介绍MySQL高级，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

第 1 章 MySQL 的架构介绍

1、MySQL 简介

1.1、概述

MySQL基础

1.2、高级MySQL

mysql内核
sql优化工程师
mysql服务器的优化
查询语句优化
主重复制
软硬件升级
容灾备份
sql编程

2、MySQL逻辑架构简介

2.1、mysql的分层思想

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。
插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2.2、mysql 四层架构

在这里插入图片描述

mysql四层架构

连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过APl与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

解析四层架构部件

connectors：不同语言中与sql的交互
connection pool：线程连接池
1. 管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。
2. 每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信。接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
Management Services & Utilities：备份，容灾恢复，安全，复制，集群等。
SQL interface：SQL接口，接收用户的SQL命令，并返回用户需要查询的结果，比如select from就是调用SQL interface。存储过程，视图触发器等。
Parser：解析器，解析转换，sql语句加载从from开始，mysql将他重组过滤然后从from开始去解析。
1. 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
2. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
Optimizer：查询优化器，主人给我一个sql，是1,2，3,4顺序，但是我mysql会按照我自己认为最优的方式去执行，有可能我做了之后就是2,3,1,4执行顺序。但是这个并不是DBA程序员认为最优的sql方式去执行。如果极端业务的场景下，公司制定了优化方式让mysql按照他的方式执行，而不走mysql自己的优化步骤。所以后面如果出现索引失效可能就是这层优化器出现了问题。
Caches & Buffers：查询缓存，将返回结果cache到内存中，与该query的一个hash值做对比，数据发生变化后，自动让cache失效。读写多的系统中，性能提高显著，内存消耗也大。
引擎层：分层可拔插组件式的存储引擎，常用MyISAM和InnoDB；例子：去不同地理环境对跑车的路况，轮胎，底盘等不同，一种环境一种跑车成本太高；没办法一种环境一种车的情况下，一辆车去不同环境，就换掉车的引擎，这样更加优化经济。类似策略模式。==注意：存储引擎是基于表的，不是数据库。
存储层：file system：系统文件；files & logs：文件和日志。硬盘有关

2.3、查询语句的执行过程

查询语句的执行过程：

连接mysql服务器，发送查询语句
查询查询缓存，存在，则直接返回结果
语法解器和预处理，通过关键字对SQL语句解析并生成解析树，预处理器根据mysql规格进一步检查解析树是否合法。
解析树合法，由查询优化器将其转化为执行计划
查询执行计划
查询执行引擎
查询数据返回结果

3、存储引擎

3.1、查看 mysql 存储引擎

#查看mysql支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
#查看默认的存储引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.02 sec)

mysql>

3.2、MyISAM 和InnoDB的区别

在这里插入图片描述

innodb是聚集索引，myisam是非聚集索引。
事务支持 > MyISAM：强调的是性能，每次查询具有原子性,其执行数度比 InnoDB 类型更快，但是不提供事务支持。 > InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
InnoDB 支持行级锁，而 MyISAM 支持表级锁. >> 用户在操作myisam 表时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。
InnoDB 支持 MVCC, 而 MyISAM 不支持
InnoDB 支持外键，而 MyISAM 不支持
表主键 > MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 > InnoDB：如果没有设定主键或者非空唯一索引，就自动生成一个 6 字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
InnoDB 不支持全文索引，而 MyISAM 支持。
可移植性、备份及恢复 > MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 > InnoDB：免费的方案可以是拷贝数据文件、备份binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了
存储结构 > MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 > InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

4、索引

索引优化分析

4.1、性能下降、 SQL 慢、执行时间长、等待时间长的原因分析

查询语句写的烂
索引失效：
- 单值索引：在user表中给name属性建个索引，create index idx_user_name on user(name)
- 复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email)
关联查询太多join(设计缺陷或不得已的需求)
服务器调优及各个参数设置(缓冲、线程数等)

4.2、常见的Join查询

4.2.1、SQL 执行顺序

手写
在这里插入图片描述

机读
在这里插入图片描述
总结：mysql是从from开始执行的

4.2.2、Join查询

join图
在这里插入图片描述

select <select_list> from tableA A 
inner join tableB B 
on A.key=B.key;

select <select_list> from tableA A 
left join tableB B 
on A.key=B.key;

select <select_list> from tableA A 
right join tableB B
on A.key=B.key;

select <select_list> from tableA A 
left join tableB B
on A.key=B.key
where B.key is null;

select <select_list> from tableA A 
right join tableB B
on A.key=B.key
where A.key is null;

select <select_list> from tableA A 
full outer join tableB B
on A.key=B.key

select <select_list> from tableA A 
full outer join tableB B 
on A.key=B.key
where A.key is null
or B.key is null;

建表语句

#建库
create database db0629;
#使用建好的库
use  db0629;
#建表
CREATE TABLE tbl_dept(
	id INT(11) NOT NULL AUTO_INCREMENT,
	deptName VARCHAR(30) DEFAULT NULL,
	locAdd VARCHAR(40) DEFAULT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#建表
CREATE TABLE tbl_emp (
	id INT(11) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT NULL,
	deptId INT(11) DEFAULT NULL,
	PRIMARY KEY (id),
	KEY fk_dept_Id (deptId)
	#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

tbl_emp表的数据

mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql>

tbl_dept表的数据

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)

inner join左表和右表共有部分
在这里插入图片描述

mysql> select * from tbl_emp a inner join tbl_dept b on a.deptid=b.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)

在这里插入图片描述
left join左表全有

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid=b.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

在这里插入图片描述
right join右表全有

mysql> select * from tbl_emp a right join tbl_dept b on a.deptid=b.id;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)

在这里插入图片描述
左表独有的

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid=b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.04 sec)

在这里插入图片描述
右表独有的

mysql> select * from tbl_emp a right join tbl_dept b on a.deptid=b.id where a.deptid is null;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)

在这里插入图片描述
左右表都有

在这里插入图片描述
mysql中没有full outer join；所以改变办法。

mysql> select * from tbl_emp a full outer join tbl_dept b on a.deptid=b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join tbl_dept b on a.deptid=b.id' at line 1
mysql> select * from tbl_emp a left join tbl_dept b on a.deptid=b.id
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptid=b.id;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.01 sec)

在这里插入图片描述
左表和B表的独有

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid=b.id where b.id is null
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptid=b.id where a.deptid is null;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

在这里插入图片描述

4.3、索引简介

4.3.1、索引是什么

索引类似于书的目录，可以快速查找到对应的内容

索引的本质：索引是数据结构
排好序的快速查找数据结构，即索引 = 排序 + 查找；（索引会影响where后面的查询条件和order by后面的排序）
索引往往以文件形式存储在硬盘上
我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

4.3.2、索引优势

提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

4.3.3、索引劣势

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
降低更新表的速度，更新索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
花时间研究建立优秀的索引，或优化查询语句

4.3.4、索引的使用场景

1.对于中大型表：索引非常有效，能较大程度的提升查询的效率
2. 对于较小的表，使用全表扫描更加有效，
3. 对于特大型表：建立索引代价也会增加，后期可以使用分区技术来解决
注意：实际开发中，一般不使用分区技术，因为有全局索引的解决方案，MyCat等

4.4、索引分类

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

#建索引
CREATE INDEX  indexName ON mytable(columnname(length));
#建索引另一种方式
ALTER mytable ADD INDEX [indexName] ON(columnname(length));
#删除索引
drop index [indexName] on mytable;
#查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）
HOW INDEX FROM table_name\G

注意：一个表最好建的索引不要超过5个
2. 唯一索引：索引列的值必须唯一，但允许有空值。

#建索引
CREATE UNIQUE indexName ON mytable(columnname(length));
#建索引另一种方式
ALTER mytable ADD UNIQUE [indexName] ON(columnname(length));

复合索引：即一个索引包含多个列

#建索引
CREATE [UNIQUE] INDEX  indexName ON mytable(columnname1(length),columnname2(length));
#建索引另一种方式
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname1(length),columnname2(length));

添加索引的具体类型
主要有六种：

普通索引：基本的索引不加任何的约束。
唯一索引：避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。（列的值不可重复，值可以为空，可以有多个列作为唯一索引）
主键索引：唯一的标识，主键不可重复，只能有一个列作为主键。（列的值不可重复，值不能为空，只能有一个列作为主键索引）
复合索引：多个列组合到一起创建的索引，可以覆盖多个例。只有使用复合索引第一个字段，索引才会生效。
外键索引：只有InnoDB类型的数据才能使用外键索引，保证了数据的一致性，完整性，保证了数据的完整性，实现了数据的级联操作。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

4.5、索引创建

创建索引

#两种创建方式
CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为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)

删除索引

DROP INDEX [indexName] ON mytable;

查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）

SHOW INDEX FROM table_name\G

4.6、建立索引的原则

4.6.1、适合建立索引的情况

主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
Where 条件里用不到的字段不创建索引
单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段

4.6.2、不适合建立索引的情况

表记录太少
经常增删改的表
数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

5、性能分析

5.1、查询优化器的作用

MySQL Query Optimizer（MySQL查询优化器）的作用：

MySQL中有专门负责优化select语句的优化器模块，主要功能是：计算系统中收集的统计信息，为客户端请求的查询语句提供他认为最优的执行计划
客户端向MySQL请求一条查询语句，命令解析器模块完成请求分类，区别出select并转发给优化器，优化器会对整条语句进行优化。常量值替换常量表达式，简化条件语句。然后分析查询语句中的Hint信息，是否能确定执行计划，如果不能或者没有Hint信息，那么会读取所涉及对象的统计信息，根据查询语句进行写相应的就计算分析，然后得出最后的执行计划。

5.2、MySQL 常见瓶颈

CPU：大数据量的写入内存和大数据量从磁盘读取的时候会出现CPU饱和问题。
IO：装入数据远大于内存容量，写入的东西大于内存容量，肯定就写不进去了。
服务器硬件性能：top，free，iostat和vmstat来查看系统的性能状态。

5.3、Explain

5.3.1、explain概念

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

5.3.2、explain作用

在这里插入图片描述

表的读取顺序（id 字段）
1. id全相同，从上到下的顺序执行
2. id全不同，id大的先执行
3. id有相同和不同，相同的归为一组，顺序执行，然后跟不同的进行比较id，id越大的先执行，
数据读取操作的操作类型（select_type 字段）
1. simple：普通的，不包含子查询或者union
2. primary：如果复杂子部分，那么最外层的就是primary，即是最后加载的
3. subquery：select或者where包含子查询
4. derived：from列表中包含子查询，子查询标记为derived，递归执行这些子查询，结果存储到临时表。
5. union：在union后的和在form字句中的子查询有union的外层就是union；
6. UNION RESULT：从UNION表获取结果的SELECT
哪些索引可以使用（possible_keys 字段）
1. 显示可能应用在这张表中的索引，一个或多个
2. 若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
哪些索引被实际使用（keys 字段）
1. 实际使用的索引，如果为null，则没有使用索引
2. 若查询中使用了覆盖索引，则该索引仅出现在key列表中
表之间的引用（ref 字段）
1. 显示索引哪一列被用到了，属性是索引名或者常量const。
每张表有多少行被优化器查询（rows 字段）

type：访问类型排列，显示查询使用了何种类型

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
挑重要的来说：system>const>eq_ref>ref>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

const只有一行，而eq_ref中的t1.id是要去匹配很多行t2.id；const是单表，eq_ref是联表。
eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

eq_ref理解成主键等于主键，无论什么情况都只能查出一个记录来，而ref查找索引是一个范围，可能是一条也可能是多条。eq_ref是唯一索引，ref是普通索引。
单表：返回一条数据就是const;返回多条数据是ref；多表：使用了主键或者唯一的字段，返回一条数据是eq_ref；
rang：有范围查询
index：index只扫描索引，而索引只是一个key值而已。
All：没使用索引

select col1,col2 from t1;
col1,col2是复合索引，所以如果select后的字段跟我建的复合索引一一对应，那么直接从索引扫描不需要全表扫描。查询字段跟建的索引对应就是覆盖索引

key_len：
表示索引中使用的字节数。
这里的13=char（4）*（utf-8）（3）+允许为null（1）
在这里插入图片描述
Extra：（格外信息）

Using filesort（文件排序）：出现得优化
Using temporary（创建临时表）：也得优化
Using index（使用了覆盖索引，即是查询出来的字段刚好是索引列）：效率不错
Using where：表明使用了where过滤
Using join buffer：表明使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元组。说明sql写的有问题了，得优化
select tables optimized away
distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

6、索引优化

6.1、单表索引优化

explain查看语句之后，如果type，extra中都是比较坏的情况，就需要进行优化
在复合索引中，如果索引的字段有作为范围出现的，那么会使其后面的索引字段失效，因此需要将有范围的索引字段去掉。

6.2、双表索引优化

左连接的时候，左边全部扫描，对左表字段建立索引没有多大作用，应该对右表字段建立索引。
右连接的时候，右边全部扫描，对右表字段建立索引没有多大作用，应该对左表字段建立索引。

6.3、三表索引优化

左连接的时候，左边全部扫描，对左表字段建立索引没有多大作用，应该对全部右表字段建立索引。
右连接的时候，右边全部扫描，对右表字段建立索引没有多大作用，应该对全部左表字段建立索引。
使用小表驱动大表（大表建索引）

7、索引失效

详细例子请看这里
【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
varchar引号不可丢，SQL高级也不难！

【具体描述】

全值匹配我最爱（使用索引全部字段作为查询条件字段，即是where后面的条件）
最佳前缀法则（使用索引字段要按照顺序出现在查询条件字段，即是where后面的条件）
不在索引做任何操作（做了操作的字段自己和后面的索引失效）
范围之后索引全失效（使用到范围的索引字段的后面的字段的索引失效了，不包含自己，自己是使用了索引进行排序，并没有进行查找）
虽然ref为null，但是其实是使用了索引，因为是使用了范围，所以用作索引进行排序了，并没有进行查找，看key可以知道用到了索引。

在这里插入图片描述
5. 尽量使用覆盖索引（索引与查询字段一致），减少select *;
6. 使用不等于会使索引失效（不管不等于使用在哪个索引字段，他都会使索引全部失效）

is null ，is not null也无法使用索引（使用了is null is not null的字段的索引和后面的索引会失效，前面的不会失效）
like开头也会失效（使用了like%开头的，自己和后面的索引失效，前面的不会失效）

使用覆盖索引可以解决%在前面导致索引失效的问题
字符串不加单引号也会失效（自己和后面的失效，前面的不会失效）
用or也会失效。（只要用到or索引就会全部失效）

【自己总结的口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中件兄弟不能断；
索引列上少计算，自己后面都失效；
范围之后都失效，索引排序不查找；
少用or不等于，不管前后都失效；
空值还有like百分，自己后面都失效；
哇所不加单引号，自己后面都失效；
覆盖索引不写星，SQL高级也不难；

7.1、面试题

建表、添加数据，创建索引：

create table test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

全值匹配：
注意：如果索引全用到了，但是使用的顺序跟索引建的顺序不一样，那么也是全部索引都用到了。比如建索引c1，c2，c3，c4的顺序建立了复合索引；查询的时候是按照c1，c2，c4，c3的顺序查找的，这里是全部使用了索引。因为有优化器的存在，优化器会按照他自己认为最优的方式进行索引
在这里插入图片描述

在这里插入图片描述
这个c3是范围，那么c3后面的c4失效，所以只用到三个索引

c4是范围，所以c4后面全失效，所以只用到了四个
在这里插入图片描述

c1，c2，c3都用到了，c3用于排序。c3的作用是用于排序而不是查找，所以这里没有显示c3用了索引，但是其实是用了的。c4没有用到索引
在这里插入图片描述
跟上面一样，c4列没有用到索引

8、查询截取分析

数据库调优步骤

慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优

8.1、查询优化

永远小表驱动大表，类似嵌套循环 Nested Loop

8.2、慢查询日志

8.3、批量数据脚本

8.4、Show profile

8.5、全局查询日志

这篇关于MySQL高级的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySQL高级

第 1 章 MySQL 的架构介绍

1、MySQL 简介

1.1、概述

1.2、高级MySQL

2、MySQL逻辑架构简介

2.1、mysql的分层思想

2.2、mysql 四层架构

2.3、查询语句的执行过程

3、存储引擎

3.1、查看 mysql 存储引擎

3.2、MyISAM 和InnoDB的区别

4、索引

4.1、性能下降、 SQL 慢、执行时间长、等待时间长的原因分析

4.2、常见的Join查询

4.2.1、SQL 执行顺序

4.2.2、Join查询

4.3、索引简介

4.3.1、索引是什么

4.3.2、索引优势

4.3.3、索引劣势

4.3.4、索引的使用场景

4.4、索引分类

4.5、索引创建

4.6、建立索引的原则

4.6.1、适合建立索引的情况

4.6.2、不适合建立索引的情况

5、性能分析

5.1、查询优化器的作用

5.2、MySQL 常见瓶颈

5.3、Explain

5.3.1、explain概念

5.3.2、explain作用

6、索引优化

6.1、单表索引优化

6.2、双表索引优化

6.3、三表索引优化

7、索引失效

7.1、面试题

8、查询截取分析

8.1、查询优化

8.2、慢查询日志

8.3、批量数据脚本

8.4、Show profile

8.5、全局查询日志

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯