1 MySQL逻辑架构介绍

1.1 MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL是开源的，所以你不需要支付额外的费用。

MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL使用标准的SQL数据语言形式。

MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、.Ruby和Tcl等。

MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。

MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。

1.2 MySQL Linux版安装

1.2.1.环境安装

# 查看Linux服务器上是否安装过MySQL
rpm -qa | grep -i mysql # 查询出所有mysql依赖包

# 1、拉取镜像
docker pull mysql:5.7

# 2、创建实例并启动
docker run -p 3306:3306 --name mysql \
-v /root/mysql/log:/var/log/mysql \
-v /root/mysql/data:/var/lib/mysql \
-v /root/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7

# 3、mysql配置 /root/mysql/conf/my.conf
[client]
#mysqlde utf8字符集默认为3位的，不支持emoji表情及部分不常见的汉字，故推荐使用utf8mb4
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
#设置client连接mysql时的字符集,防止乱码
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'

#数据库默认字符集
character-set-server=utf8

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8_general_ci

# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

# 4、重启mysql容器
docker restart mysql

# 5、进入到mysql容器
docker exec -it mysql /bin/bash

# 6、查看修改的配置文件
cat /etc/mysql/my.conf

1.2.2 安装位置

Docker容器就是一个小型的Linux环境，进入到MySQL容器中。

docker exec -it mysql /bin/bash

Linux环境下MySQL的安装目录。

路径	解释
`/var/lib/mysql`	MySQL数据库文件存放位置
`/usr/share/mysql`	错误消息和字符集文件配置
`/usr/bin`	客户端程序和脚本
`/etc/init.d/mysql`	启停脚本相关

1.2.3 修改字符集

# 1、进入到mysql数据库并查看字符集
# show variables like 'character%';
# show variables like '%char%';

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

MySQL5.7配置文件位置是/etc/my.cnf或者/etc/mysql/my.cnf，如果字符集不是utf-8直接进入配置文件修改即可。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_general_ci'

# 数据库默认字符集
character-set-server=utf8

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8_general_ci

# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

注意：安装MySQL完毕之后，第一件事就是修改字符集编码。

1.2.4 配置文件

1、二进制日志log-bin：主从复制。

# my,cnf
# 开启mysql binlog功能
log-bin=mysql-bin

2、错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

# my,cnf
# 数据库错误日志文件
log-error = error.log

3、查询日志log：默认关闭，记录查询的sql语句，如果开启会降低MySQL整体的性能，因为记录日志需要消耗系统资源。

# my,cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

4、数据文件。

frm文件：存放表结构。
myd文件：存放表数据。
myi文件：存放表索引。

# mysql5.7 使用.frm文件来存储表结构
# 使用 .ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

MySQL5.7的Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为:ibdata1 初始化为10M。
独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。在配置文件my.cnf中设置： innodb_file_per_table。

1.3 MySQL逻辑架构

Connectors：指的是不同语言中与SQL的交互。
Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层。
Management Serveices & Utilities：系统管理和控制工具。备份、安全、复制、集群等等。。
SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
Parser：SQL语句解析器。
Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。For Example： select uid,name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
Caches & Buffers：查询缓存。
Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎(注意：存储引擎是基于表的，而不是数据库)。
File System：数据落地到磁盘上，就是文件的存储。

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

逻辑架构分层

连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。
引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

1.4 存储引擎

show engines;命令查看MySQL5.7支持的存储引擎。

mysql> show engines;

show variables like 'default_storage_engine%';查看当前数据库正在使用的存储引擎。

mysql> show variables like 'default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

InnoDB和MyISAM对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不适合高并发操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，対内存要求较高，而且内存大小対性能有决定性影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

2 索引优化分析

不加索引或加无效索引会导致性能下降SQL慢，执行时间长，等待时间长。有以下几种情况：

查询语句写得烂
索引失效
关联查询太多join（设计缺陷或者有不得已的需求。）
服务器调优以及各个参数设置（缓冲、线程数等。）

下面来一一讲解

2.1 常见通用的Join查询

2.1.1 SQL执行顺序

select              # 5
	... 
from                # 1
	... 
where               # 2
	.... 
group by            # 3
	... 
having              # 4
	... 
order by            # 6
	... 
limit               # 7
	[offset]

2.1.2 七种JOIN理论

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
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;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

2.2 索引简介

MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果。

从而可以获得索引的本质：索引是排好序的快速查找数据结构。

索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a---z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？

重点：索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

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

# Linux下查看磁盘空间命令 df -h 
[root@Ringo ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   16G   23G  41% /
devtmpfs        911M     0  911M   0% /dev
tmpfs           920M     0  920M   0% /dev/shm
tmpfs           920M  480K  920M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
overlay          40G   16G   23G  41%

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

索引的优势和劣势

优势：

查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

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

2.2.1 MySQL索引分类

索引分类：

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但是允许空值。
复合索引：一个索引包含多个字段。

建议：一张表建的索引最好不要超过5个！

/* 基本语法 */

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

使用ALTER命令来为数据表添加索引

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

2.2.2 MySQL索引数据结构

索引数据结构：

BTree索引。
Hash索引。
Full-text全文索引。
R-Tree索引。

BTree索引检索原理：

2.2.3 哪些情况需要建索引

主键自动建立主键索引（唯一 + 非空）。
频繁作为查询条件的字段应该创建索引。
查询中与其他表关联的字段，外键关系建立索引。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段（group by也和索引有关）。

2.2.4 哪些情况不要建索引

记录太少的表。
经常增删改的表。
频繁更新的字段不适合创建索引。
Where条件里用不到的字段不创建索引。
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

2.3 性能分析

2.3.1 MySQL Query Optimizer

MySq中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Quey提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
当客户端向MySQL请求一条Quey,命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Queryi进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Quy中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2.3.2 MySQL常见瓶颈

CPU：CPU在饱和的时候，一般发生在数据装入内存，或从磁盘上读取数据的时候。
IO：磁盘I/O瓶颈发生在装入的数据远大于内存容量的时候。

服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态。

2.3.3 Explain

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

语法：explain + SQL。

执行计划包含以下：

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
- id相同，执行顺序由上而下
- id不同，如果是子查询，id序列号会递增，序号越大，执行的优先级越高。
- id有相同也有不同，先执行序号大的，相同序号的由上而下依次执行。
select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
- SIMPLE：简单的SELECT查询，不包含子查询或UNION。
- PRIMARY：查询中包含了所有复杂的子部分，属于最外层的查询。
- SUBQUERY：在SELECT或WHERE列表中包含了子查询。
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION：若第二个SELECT出现在UNION之后，会被标记为UNION; 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
- UNION RESULT：从UNION表中获取结果的SELECT。
table：显示这行数据是关于哪张表的。
type：显示出查询所使用的的类型，最好到最差依次排列：
- system：表只有一行记录（等于系统表），相当于const的特例，平时不会出现，可以忽略不计。
- const：表示通过索引一次性找到结果，const用于比较primary key或unique索引。因为只需匹配一行数据，所以查询速度极快。若将主键置于where条件中， mysql就能将该查询转换为一个常量。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref：非唯一性扫描，返回匹配的某个单独值的所有行。本质上也是一种索引扫描，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以属于查找和扫描的混合体。
- range：只检索给定某个范围的行，使用一个索引来选择行，key列显示了使用哪个索引，一般在where语句中出现between、>、<、in等查询。这种范围扫描比全表扫描要好，因为只需要开始于索引的某一点，结束于另一点即可，缩小了扫描范围。
- index：Full Index Scan，index与ALL的区别为：index类型只遍历索引树，这通常比ALL类型快。因为索引文件比数据文件小（也就是说index与ALL都是扫描全表，但index类型从索引中扫描，而ALL类型从磁盘中扫描。）
- ALL：遍历整张表去找匹配的行，效率极低。

SYSTEM>CONST>EQ_REF>REF>RANGE>INDEX>ALL一般来说，至少保证达到range级别，最好能达到ref。

possible_keys:显示理论上应用在这张表的索引，一个或多个。查询涉及到的字段上若存在索引，则列出。但不一定被实际查询所使用。
key:实际使用的索引，如果为null，则没有使用索引。

查询中若使用了覆盖索引(Covering Index)，则该索引仅出现在key列表中。

key_len:表示索引中使用的字节数，可通过该列计算出查询中所使用的索引长度。在不损失精度的情况下，索引越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际长度。因为key_len是根据表定义计算而得，而不是通过表内检索出的。

ref:显示索引的那一列被使用了，如果可能的话是一个常数。表示哪些列或常数被用于查找索引列上的值。
rows:根据表统计信息以及索引的选用情况，大致估算出找到所需记录一共需要读取的行数。
extra:包含不适合在其他列中显示，但又十分重要的额外信息。
- Using filesort:说明mysql会对数据使用外部的索引排序，而不是按照表内的索引顺序读取。mysql中无法利用索引完成的排序操作，称为"文件排序"。
- Using temporary:表示使用了临时表保存中间结果，mysql在对查询结果排序时，使用了临时表。常见于排序order by和分组查询group by。
- Using index:表示select操作中，使用了覆盖索引（Covering Index），避免了访问表的数据行，效率很高。如果同时出现Using where，表明索引被用来执行索引键值的查找;如果没有同时出现Using where，则表示索引用来读取数据，而非执行索引查找动作。
- Using where:表示使用了where过滤条件。
- Using join buffer:表示使用了连接缓存。
- impossible where:where子句的值总是false，条件永远不成立，不能用来获取任何元组。
- select tables optimized away:在没有group by子句的情况下，基于索引优化min/max操作或对于MyISAM存储引擎优化的count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。
- distinct:优化distinct操作，在找到第一次匹配的元组后，立即停止找相同值的动作。

覆盖索引(Covering Index),一说为索引覆盖。
理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竞索引叶子节点存储了它们索引的数据：当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意：如果要使用覆盖索引，任定要注意select列表中只取出需要的列，不可select*,因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

2.4 索引优化

2.4.1 索引分析

2.4.1.1 单表索引分析

DROP TABLE IF EXISTS `article`;

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`author_id` INT(10) UNSIGNED NOT NULL COMMENT '作者id',
`category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
`views` INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
`comments` INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
`title` VARCHAR(255) NOT NULL COMMENT '标题',
`content` VARCHAR(255) NOT NULL COMMENT '正文内容'
) COMMENT '文章';

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,1,1,'1','1');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(2,2,2,2,'2','2');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(3,3,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,4,4,'4','4');

查询category_id为1且comments大于1的情况下，views最多的article_id。

# 1、sql语句
SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

# 2、sql执行计划
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using filesort  # 产生了文件内排序，需要优化SQL
1 row in set, 1 warning (0.00 sec)

创建索引idx_article_ccv。

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是在order by排序的时候没有用到索引，MySQL居然还是用的Using filesort

范围之后的索引会失效。

/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);

2.4.1.2 两表索引分析

DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '商品类别';

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

不创建索引的情况下，SQL的执行计划。

book和class两张表都是没有使用索引，全表扫描，那么如果进行优化，索引是创建在book表还是创建在class表呢？

创建索引idx_book_card

/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);

在book表中有idx_book_card索引的情况下，查看SQL执行计划

创建索引idx_class_card

/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);

由此可见，左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适。

2.4.1.3 三张表索引分析

DROP TABLE IF EXISTS `phone`;

CREATE TABLE IF NOT EXISTS `phone`(
`phone_id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '手机';

根据两表查询优化的经验，左连接需要在右表上添加索引，所以尝试在book表和phone表上添加索引。

/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);

/* 在phone表上创建索引 */
CREATE INDEX idx_phone_card ON phone(card);

2.4.1.4 结论

JOIN语句的优化：

尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。
优先优化NestedLoop的内层循环。
保证JOIN语句中被驱动表上JOIN条件字段已经被索引。
当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。

2.4.2 索引失效情况

全值匹配我最爱：建多少索引就用多少索引，而且顺序一致
最佳左前缀法则：如果索引了多个列，要遵循左前缀法则，指查询需要从索引的最左前列开始，并且按照索引列中的顺序去查询，若跳过则索引失效。
不在索引列上做任何操作（计算、函数、类型转换（手动or自动）），会导致索引失效，转向全表扫描。
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引列（查询列和索引列一致）），减少select *
MySQL在使用不等于运算符（!=或<>）时，无法使用索引，会导致全表扫描。
is null和is not null，无法使用索引。
like若以通配符开头（'%aa'），则索引失效，全表扫描。

解决%aa%索引失效问题：
1.百分号写右边
2.采用覆盖索引

字符串不加引号导致索引失效。
少用or，用它来连接条件时，索引失效。

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a，b
where a = 3 and b = 5	Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N，没有用到a字段
where a = 3 and c = 5	使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5	使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like 'kk%' and c = 4	Y，a，b，c都用到
where a = 3 and b like '%kk' and c = 4	只用到a
where a = 3 and b like '%kk%' and c = 4	只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y，a，b，c都用到

全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效； Like百分写最右，覆盖索引不写星；不等空值还有or，索引失效要少用； VAR引号不可丢，SQL高级也不难！

GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。

3 查询截取分析

分析:

观察，至少跑一天，看看生产的慢SQL情况
开启慢擦汗寻日志，设置阈值，比如超过5s就是慢SQL，并抓取出来
explain+慢SQL分析
show profile查询SQL在MySQL服务器内执行细节和生命周期情况
SQL数据库服务器的参数调优

3.1 查询优化

3.1.1 永远小表驱动大表

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

IN和EXISTS

/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */

/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)

/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

EXISTS：

语法：SELECT....FROM tab WHERE EXISTS(subquery);该语法可以理解为：
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。

提示：

EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

3.1.2 ORDER BY关键字优化

order by子句，尽量使用index方式排序，避免使用filesort方式排序。
尽可能的在索引列上完成排序操作，遵照索引键的最佳左前缀原则。
如果不在索引列上，filesort有两种算法。双路排序、单路排序。

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

ORDER BY语句使用索引最左前列。
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

1、双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

2、单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

由于单路排序算法是后出的，总体而言效率好过双路排序算法。

但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

增大sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
- 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
- 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

3.1.3 GORUP BY优化

group by是先排序后分组，遵照索引键的最佳左前缀。因此其他跟ORDER BY一致

若无法使用索引列，则增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

where高于having，能写在where限定的条件，就最好不好去写having限定了。

3.2 慢查询日志

慢查询日志

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

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

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/1dcb5644392c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

# 2、开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  

# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log

开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

修改long_query_time的时间，需要在my.cnf修改配置文件

[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.00 sec)

3.3 日志分析工具

日志分析工具mysqldumpslow：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  # 按照何种方式排序
                al: average lock time # 平均锁定时间
                ar: average rows sent # 平均返回记录数
                at: average query time # 平均查询时间
                 c: count  # 访问次数
                 l: lock time  # 锁定时间
                 r: rows sent  # 返回记录
                 t: query time  # 查询时间 
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries  # 返回前面多少条记录
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
  
# 2、 案例
# 2.1、得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

3.4 批量插入数据脚本

/* 1.dept表 */
CREATE TABLE `dept` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
  `loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'

/* 2.emp表 */
CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',
  `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',
  `job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',
  `mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredata` date NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '分红',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'

由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。

# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。

[mysqld]
log_bin_trust_function_creators=ON

创建函数

# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

创建存储过程

# 1、函数：向dept表批量插入
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

# 2、函数：向emp表批量插入
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

3.5 Show Profile

Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

开启Show Profile功能，默认是关闭的，使用前需要开启。

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

运行SQL

SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;

SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

查看结果，执行SHOW PROFILES;

Duration：持续时间。

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling'                   |
|        2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;

# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000097 | 0.000090 |   0.000002 |            0 |             0 |
| checking permissions | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000058 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000046 |   0.000000 |            0 |             0 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000023 | 0.000037 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000041 | 0.000053 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.520620 | 0.516267 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000060 | 0.000051 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000064 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

Show Profile查询参数备注：

ALL：显示所有的开销信息。
BLOCK IO：显示块IO相关开销（通用）。
CONTEXT SWITCHES：上下文切换相关开销。
CPU：显示CPU相关开销信息（通用）。
IPC：显示发送和接收相关开销信息。
MEMORY：显示内存相关开销信息。
PAGE FAULTS：显示页面错误相关开销信息。
SOURCE：显示和Source_function。
SWAPS：显示交换次数相关开销的信息。

6、Show Profile查询列表，日常开发需要注意的结论：

converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
locked：死锁。

3.6 全局查询日志

配置启用：

在mysql的my.cnf中，设置如下：

#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用

set global general log=1;
set global log_output='TABLE';

永远不要在生产环境中开启此功能

4 MySQL锁机制

4.1 MySQL锁定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要，也更加复杂。

4.2 锁的分类

按照数据操作的类型(读/写)分类

读锁(共享锁)：针对同一份数据，多个读的操作可以同时进行，而不会互相影响。
写锁(排它锁)：当前写的操作没有完成之前，会阻塞其它读锁和写锁。

按照数据操作的粒度分类

表锁
行锁

4.3 三锁

4.3.1 案例

写锁案例

偏向MyISAM存储引擎，开销小，加锁快;无死锁;锁粒度大，发生锁冲突的概率最高，并发度最低。

# 【手动增加表锁】
lock table 表名字 read(write),表名字2 read(write),其它;
# 【查看表上加过的锁】
show open tables;
#【释放表锁】
unlock tables;

# SESSION1

# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# SESSION2

# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

行锁（偏写）案例

# SESSION1

# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# SESSION2

# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）。
表独占写锁（Table Write Lock）。

対MyISAM表进行操作，会有以下情况：

対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。

4.3.2 表锁

mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 173   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

4.3.3 行锁

4.3.3.1 行锁特点

行锁特点：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁。

4.3.3.2 复习

并发事务处理带来的问题

更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
脏读(Dirty Reads)：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
不可重复读(Non-Repeatable-Reads)：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
幻读(Phantom Reads)：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读

事务的ACID：

Atomicity [ˌætəˈmɪsəti]。
Consistency [kənˈsɪstənsi]。
Isolation [ˌaɪsəˈleɪʃn]。
Durability [ˌdjʊərəˈbɪlɪti]。

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

读数据一致性及允许的并发副作用	读数据一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交度(Read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

常看当前数据库的事务隔离级别：show variables like'tx isolation';

4.3.3.3 常见错误

Iodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAMi的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比yISAMi高甚至可能会更差。

行锁升级表锁

无索引或索引失效，行锁将升级为表锁

间隙锁

【什么是间隙锁】

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

【危害】

因为Quey执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

4.3.3.4 如何锁定一行

SELECT .....FOR UPDATE在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

4.3.3.5 行锁分析

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 124150 |
| Innodb_row_lock_time_avg      | 31037  |
| Innodb_row_lock_time_max      | 51004  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

対各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。

4.3.4 页锁

开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间，并发度一般。

了解一下即可

5 MySQL主从复制

5.1 复制的基本原理

slave会从master读取binlog来进行数据同步

MySQL复制过程分为三步：

Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件，Binary Log Events；
Slave将Master的Binary Log Events拷贝到它的中继日志(Replay Log);
Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。

5.2 复制基本原则

每个Slave只有一个Master。
每个Slave只能有一个唯一的服务器ID。
每个Master可以有多个Salve。

5.3 复制的最大问题

延时

5.4 一主一从常见配置

1、基本要求：Master和Slave的MySQL服务器版本一致且后台以服务运行。

# 创建mysql-slave1实例
docker run -p 3307:3306 --name mysql-slave1 \
-v /root/mysql-slave1/log:/var/log/mysql \
-v /root/mysql-slave1/data:/var/lib/mysql \
-v /root/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7

2、主从配置都是配在[mysqld]节点下，都是小写

# Master配置
[mysqld]
server-id=1 # 必须
log-bin=/var/lib/mysql/mysql-bin # 必须
read-only=0
binlog-ignore-db=mysql
# Slave配置
[mysqld]
server-id=2 # 必须
log-bin=/var/lib/mysql/mysql-bin

3、Master配置

# 1、GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机IP地址' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'username'@'ip' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 2、刷新命令
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 3、记录下File和Position
# 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      602 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、Slave从机配置

CHANGE MASTER TO MASTER_HOST='ip',
MASTER_USER='username',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.File的编号',
MASTER_LOG_POS=Position的最新值;
# 1、使用用户名密码登录进Master
mysql> CHANGE MASTER TO MASTER_HOST='ip',
    -> MASTER_USER='username',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

# 2、开启Slave从机的复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

# 3、查看Slave状态
# Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event # Slave待命状态
                  Master_Host: ip
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 602
               Relay_Log_File: b030ad25d5fe-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 602
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

5、测试主从复制

# Master创建数据库
mysql> create database test_replication;
Query OK, 1 row affected (0.01 sec)

# Slave查询数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_replication   |
+--------------------+
5 rows in set (0.00 sec)

6、停止主从复制功能

# 1、停止Slave
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

# 2、重新配置主从
# MASTER_LOG_FILE 和 MASTER_LOG_POS一定要根据最新的数据来配
mysql> CHANGE MASTER TO MASTER_HOST='ip',
    -> MASTER_USER='username',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=797;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ip
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 797
               Relay_Log_File: b030ad25d5fe-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 797
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

如果觉得文章对你有用，请随意赞赏

MySQL - 下

https://www.adrainty.online/archives/1712153298475

作者

AdRainty

发布于

2024-04-03

更新于

2024-04-03

许可协议

CC BY 4.0