1、MySQL数据库逻辑架构。

 MySQL数据库逻辑架构图

 （1）网络连接层。

客户端连接器（Client Connectors）。

提供与MySQL服务器建立连接的支持。最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端实现的类似tcp/ip的通信。

目前几乎支持所有主流的服务端编程技术，例如常见的 Java、.NET、PHP、Python、C等，它们通过各自API技术与MySQL建立连接。

同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

（2）服务层（MySQL Server）。

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存和缓冲池。

 1）连接池（Connection Pool）。

负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。

提供一些类似于连接处理、授权认证、及相关的安全方案，为通过认证安全接入的客户端提供线程。

 2）系统管理和控制工具（Management Services & Utilities）。

例如：备份恢复、安全管理、集群管理等

 3）SQL接口（SQL Interface）。

用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。

例如：DML、DDL、存储过程、视图、触发器等。

 4）解析器（Parser）。

解析器将请求的SQL解析生成一个“解析树”，然后根据MySQL的语法规则检查解析树是否合法。

 5）查询优化器（Optimizer）。

当“解析树”通过解析器语法检查后，将交给优化器将其转化成执行计划，然后与存储引擎交互。

 6）缓存和缓冲池（Cache&Buffer）。

缓存机制是由一系列小缓存组成的。例如：表缓存，记录缓存，权限缓存，引擎缓存等。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

第二层架构主要完成大多数的核心服务功能，例如SQL接口，并且完成缓存的查询，SQL的分析和优化及部分内置函数的执行。

所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并且创建相应的内部解析树，并且对其完成相应的优化。

例如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。

如果缓存空间足够大，这样在解决大量读操作的环境环境中能够很好的提升系统的性能。

（3）存储引擎层（Pluggable Storage Engines）。

存储引擎负责MySQL中数据的存储和提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过API接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异，不同的存储引擎具有的功能不同，可以根据需要进行选取。最常见的存储引擎是InnoDB和MyISAM。

（4）系统文件层（File System）。

系统文件层负责将数据、日志存储在文件系统上，并且完成与存储引擎的交互，是文件的物理存储层。

主要包含日志文件、数据文件、配置文件、pid 文件、socket 文件等。

 1）日志文件。

  [1]、错误日志（Error log）。

log-error错误日志记录严重的警告信息、错误信息、启动与关闭详细信息。

#查看错误日志存放路径等。

show variables like '%log_error%'

  [2]、通用查询日志（General query log）。

log查询日志记录一般的SQL查询语句。可以与慢查询日志结合使用，分析查询慢的SQL语句。

#查看是否开启记录日志，通用查询日志存放路径。

show variables like '%general%';

  [3]、二进制日志（binary log）。

记录对MySQL数据库执行更改操作的SQL语句，并且记录了SQL语句的发生时间、执行时长。

不记录select、show等不修改数据库记录的SQL语句。

log-bin二进制日志主要用于数据库恢复和主从复制。

#查看是否开启记录日志，binlog日志存放路径等。

show variables like '%log_bin%';

#查看binlog配置参数。

show variables like '%binlog%';

#查看有哪些binlog日志文件。

show binary logs;

  [4]、慢查询日志（Slow query log）

记录所有执行时间超时的查询SQL语句。

#查看是否开启，慢查询日志存放路径。

show variables like '%slow_query%';

查看配置的SQL语句执行超时的时间。默认设置是10秒。

show variables like '%long_query_time%';

 2）配置文件。

用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

window系统是my.ini配置文件，Linux系统是my.cnf配置文件。

 3）数据文件。

  [1]、MYD格式文件。

MyISAM存储引擎专用，存放表数据。存放MyISAM表的data数据，每一张表都会有一个.MYD文件。

  [2]、MYI格式文件。

MyISAM存储引擎专用，存放表索引。存放MyISAM表的索引相关信息，每一张 MyISAM表对应一个.MYI文件。

  [3]、ibd文件、IBDATA文件。

存放InnoDB 的数据文件，包括索引。

InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。

独享表空间使用.ibd文件来存放数据，且每一张InnoDB表对应一个.ibd文件。

共享表空间使用.ibdata文件，所有表共同使用一个或多个.ibdata文件。

  [4]、ibdata1文件。

系统表空间数据文件，存储表元数据、Undo日志等。

  [5]、ib_logfile0文件、ib_logfile1文件。

Redo log日志文件。

  [6]、frm格式文件。

存放表的结构。存储与表相关的meta元数据信息，包括表结构的定义信息等，每一张表都会有一个frm文件。

mysql5.x有frm格式文件，mysql8.x没有frm格式文件。

MySQL8.0删除了原来的frm文件，MySQL8.0重新设计数据词典后，引入了SDI（Serialized Dictionary Information），统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中，可以使用ibd2sdi工具查看表结构信息。

  [7]、db.opt文件。

记录这个库的默认使用的字符集、校验规则。

mysql5.x有db.opt文件，mysql8.x没有db.opt文件。

4）pid文件。

存放mysql的进程id。pid文件是mysqld应用程序在Linux环境下的一个进程文件。

5）socket文件。

socket文件是在Linux环境下的文件。在Linux环境下，用户客户端可以使用Unix Socket连接MySQL，不使用TCP/IP网络连接。

2、存储引擎介绍。

（1）查看存储引擎。

-- 查看mysql版本号。

SELECT VERSION();

-- 查看mysql支持的存储引擎。

show engines;

-- 查看当前默认的存储引擎。

show variables like '%storage_engine%';

（2）MyISAM与InnoDB的对比。

|
对比项
|
MyISAM
|
InnoDB |
|
主外键
|
不支持
|
支持 |
|
事务
|
不支持
|
支持 |
|
行表锁
|
表锁，操作一条记录会锁住整个表，不适合高并发操作。
|
行锁，操作一条记录锁住某一行，不对其它行有影响。

适合高并发的操作。 |
|
缓存
|
只缓存索引，不缓存真实数据。
|
缓存索引和真实数据。 |
|
表空间
|
小
|
大 |
|
关注点
|
性能
|
事务 |

（3）几种存储引擎的对比。

|
特点
|
InnoDB
|
MyISAM
|
M********emory
|
MERGE
|
NDB
|
Archive |
|
存储限制
|
64TB
|
有
|
有
|
没有
|
有
|
没有 |
|
事务安全
|
支持
|
|
|
|
支持
| |
|
锁机制
|
行锁（适合高并发）
|
表锁
|
表锁
|
表锁
|
行锁
|
行锁 |
|
B树索引
|
支持
|
支持
|
支持
|
支持
|
支持
| |
|
哈希索引
|
支持
|
|
支持
|
|
| |
|
全文索引
|
5.6版本之后支持
|
支持
|
|
|
| |
|
集群索引
|
支持
|
|
|
|
| |
|
数据索引
|
支持
|
|
支持
|
|
支持
| |
|
索引缓存
|
支持
|
支持
|
支持
|
支持
|
支持
| |
|
数据可压缩
|
|
支持
|
|
|
|
支持 |
|
磁盘空间使用
|
高
|
低
|
N/A
|
低
|
低
| |
|
内存使用
|
高
|
低
|
中等
|
低
|
高
|
低 |
|
批量插入速度
|
相对低
|
高
|
高
|
高
|
高
|
非常高 |
|
支持外键
|
支持
|
|
|
|
| |

（4）MySQL各种存储引擎介绍。

   1）InnoDB存储引擎。

[1]将数据存储在表空间中，表空间由一系列的数据文件组成，由InnoDB管理。

[2]支持每个表的数据和索引存放在单独文件中innodb_file_per_table。

[3]支持事务，采用MVCC来控制并发，并实现标准的4个事务隔离级别，支持外键。

[4]索引基于聚簇索引建立，对于主键查询有较高性能。

[5]数据文件的平台无关性，支持数据在不同的架构平台移植。

[6]能够通过一些工具支持真正的热备。如XtraBackup等。

[7]内部进行自身优化如采取可预测性预读，能够自动在内存中创建hash索引等。

2）MyISAM存储引擎。

[1]MySQL5.1中默认，不支持事务和行级锁。

[2]提供大量特性如全文索引、空间函数、压缩、延迟更新等。

[3]数据库故障后，安全恢复性差。

[4]对于只读数据可以忍受故障恢复，MyISAM依然非常适用。

[5]日志服务器的场景也比较适用，只需插入和数据读取操作。

[6]不支持单表一个文件，会将所有的数据和索引内容分别存在两个文件中。

[7]MyISAM对整张表加锁而不是对行，所以不适用写操作比较多的场景。

[8]支持索引缓存不支持数据缓存。

3）Archive存档、归档存储引擎。

[1]只支持insert和select操作。

[2]缓存所有的写数据并进行压缩存储，支持行级锁但不支持事务。

[3]适合高速插入和数据压缩，减少IO操作，适用于日志记录和归档服务器。

4）Blackhole黑洞存储引擎。

   [1]没有实现任何存储机制，会将插入的数据进行丢弃，但会存储二进制日志。

[2]会在一些特殊需要的复制架构的环境中使用。

5）CSV文件格式存储引擎。

   [1]可以打开CSV文件存储的数据，可以将存储的数据导出，并使用Excel打开。

[2]可以作为一种数据交换的机制，同样经常使用。

6）Memory内存存储引擎。

[1]将数据在内存中缓存，不消耗IO。

[2]存储数据速度较快但不会被保留，一般作为临时表的存储被使用。

7）Federated存储引擎。

[1]能够访问远程服务器上的数据的存储引擎。

  [2]能够建立一个连接连到远程服务器。

8）Mrg_MyISAM存储引擎，合并MyISAM表存储引擎。

[1]将多个MyISAM表合并为一个。

  [2]本身并不存储数据，数据存在MyISAM表中间。

9）NDB集群引擎。

MySQL Cluster，MySQL集群专用。

3、SQL性能下降原因。

（1）查询语句写的烂。

（2）索引失效。

（3）关联查询太多join连接，设计缺陷或者不得已不合理的需求。

（4）服务器调优及各个参数设置，缓冲、线程数设置不当。

导致mysql性能下降，sql执行时间长，等待时间长。

4、SQL语句执行顺序。

|
执行顺序
|
SQL
|
备注 |
|
7
|
SELECT
|
从虚拟表中选择出我们需要的内容。 |
|
8
|
DISTINCT <select_list>
|
对进行distinct操作的列增加一个唯一索引，用来移除相同的行。 |
|
1
|
FROM <left_table>
|
执行一个笛卡尔乘积。 |
|
3
|
<join_type> JOIN <right_table>
|
如果是outer join【left outer join、right outer join、full outer join】那么这一步就将添加外部行。 |
|
2
|
ON <join_condition>
|
on筛选器筛选出满足on逻辑表达式的行。 |
|
4
|
WHERE <where_condition>
|
对上一步产生的虚拟表使用用where筛选器，生成新的虚拟表。 |
|
5
|
GROUP BY <group_by_list>
|
开始使用select中的别名，后面的语句中都可以使用。 |
|
6
|
HAVING <having_condition>
|
group by与having之间可以执行avg,sum....等函数。  |
|
9
|
ORDER BY <order_by_condition>
|
对虚拟表中的内容按照指定的列进行排序，生成一个游标。 |
|
10
|
LIMIT <limit_number>
|
从上一步得到的虚拟表中选出从指定位置开始的指定几行数据。 |

笛卡尔乘积：包含两个集合中任意取出两个元素构成的组合的集合。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。 

MySQL多表查询的笛卡尔积原理。

（1）首先确定数据要用到哪些表。A、B表。

（2）其次将多个表先通过笛卡尔乘积变成一个表。AxB。

（3）然后根据两个表的关系去掉不符合逻辑的数据。on a.x= b.x。

（4）最后当做是一个虚拟表加上条件筛选出数据即可。where。

5、7种join连接查询。

（1）7种join连接查询。

|
名称
|
图示
|
查询sql |
|
内连接查询
|

|
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 |
|
查询a表独有的记录。
|

|
select <select_list> from tableA A

left join tableB B

on A.key = B.key

where B.key is null |
|
查询b表独有的记录。
|

|
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 |
|
查询a、b独有的记录。
|

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

（2）7种join连接查询示例。 

select * from tbl_dept;
select * from tbl_emp;
select * from tbl_dept, tbl_emp;

-- 内连接查询
select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

-- 左连接查询
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

-- 右连接查询
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

-- 左连接查询，查询a表独有的记录。
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

-- 右连接查询，查询b表独有的记录。
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

-- 全连接查询（mysql不支持full outer join全连接查询）
select * from tbl_emp a full outer join tbl_dept b on a.deptId = b.id;

-- 全连接查询1，查询a、b所有的记录。（union去重）
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;

-- 全连接查询2，查询a、b所有的记录。（union all不去重）
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union all
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

-- 全连接查询3，查询a、b独有的记录。
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;

6、索引。

（1）索引。

索引Index是帮助mysql高效获取数据的数据结构。

提高查询效率，类比字典。

排好序的快速查找数据结构，着重于查找和排序两种功能。

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

索引用于提高查询和排序效率。

为了加快Col2的查找，可以维护一个二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，可以运用二叉查找在一定的复杂度内获取到相应数据，快速检索出符合条件的记录。

平常所说的索引，如果没有特别指明，都是指B树结构组织的索引，多路搜索树，并不一定是二叉树。

其中聚焦索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称为索引。

除了B+树索引外，还有哈希索引hash index等。

（2）索引优势、劣势。

优势；提高数据检索的效率，降低数据库的IO成本。通过索引列队数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：索引是一张表，保存了主键与索引字段，指向实体表的记录，索引占用空间。索引降低更新表的速度，对表进行insert、update、delete时，mysql不仅需要保存数据，还要保存更新索引信息。

（3）索引分类。

   1）单值索引。

一个索引只包含单个列字段，一个表可以有多个单列索引。

建议一张表的索引数量不要超过5个。

   2）唯一索引。

索引列字段的值必须唯一，充许NULL空值。

   3）复合索引。

一个索引包含多个列字段。

   4）基本语法。 

-- 创建索引1
CREATE [UNIQUE] INDEX indexName ON tableName(columnName);
-- 创建索引2
ALTER TABLE tableName ADD [UNIQUE] INDEX [indexName](columnName);
-- 删除索引
DROP INDEX [indexName] ON tableName;
-- 查看表有哪些索引
SHOW INDEX FROM tableName;

-- 创建主键索引。索引值必须唯一，不能为NULL。
ALTER TABLE tableName ADD PRIMARY KEY(column_list);
-- 创建唯一索引。索引值必须唯一，索引的字段可以有NULL值，可以有多条记录有NULL值。
ALTER TABLE tableName ADD UNIQUE indexName(column_list);
-- 创建普通索引。索引的字段值可以出现多次。
ALTER TABLE tableName ADD INDEX indexName(column_list);
-- 创建全文索引。
ALTER TABLE tableName ADD FULLTEXT indexName(column_list);

（4）索引结构、检索原理。

   1）BTree索引。

[1]初始化介绍。 

一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），

如磁盘块1包含数据项17和35，包含指针P1、P2、P3，

P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

[2]查找过程。

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2）Hash索引

3）full-text全文索引

4）R-Tree索引

（5）适合创建索引的场景。

1）主键自动建立唯一索引。

2）频繁作为查询条件的字段应该创建索引。为最经常查询、最经常排序的字段建立索引。

3）查询中与其他表关联的字段，外键关系建立索引。

4）where条件里面用不到的字段不创建索引。

5）单键索引和组合索引，在高并发情况下倾向创建组合索引。

6）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

7）查询中统计或分组字段。

（6）不适合创建索引的场景。

1）表中记录数量太少，官方支持单表500~800万数据，数据量达到300万时就要注意。

2）经常增删改的表。

3）频繁更新的字段不适合建立索引。

索引提高了查询速度，在insert、update、delete时，mysql不仅需要保存数据，还要保存更新索引信息，降低了更新表的速度。

4）数据重复并且分布平均的表字段。如果某个数据列中包含许多重复的内容，建立索引没有太大的实际效果。例如：为sex性别字段建立索引。

索引的选择性：指建立索引的字段中不同值的记录数与表中总记录数量的比。

一个索引的选择性越接近1，索引的效率就越高。

7、explain。

（1）explain查看执行计划。

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

（2）explain的作用。

1）表的读取顺序。id。

2）数据读取操作的操作类型。select_type。

3）哪些索引可以使用。possible_keys。

4）哪些索引被实际使用。key。

5）表之间的引用。

6）每张表有多少行被优化器查询。rows。

（3）explain分析的各字段解释。

1）id。

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

[1] id都相同，都是一样的。执行顺序由上至下。

[2] id不同，id都不一样。子查询的id序号会递增，id值越大优先级越高，越先被执行。

[3] id有相同的，也有不相同的。id相同的是一组，从上往下顺序执行。在所有组中，id值越大，优先级越高，越先执行。

2）select_type。

查询的类型，主要用于区分普通查询、联合查询、子查询等的复杂查询。

[1]SIMPLE。

简单的select查询，查询中不包含子查询或者UNION。

[2]PRIMARY。

查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY，最后面被加载的那个。

[3]SUBQUERY。

在SELECT或WHERE列表中包含了子查询。

[4]DERIVED。

在from列表中包含的子查询被标记为DERIVED衍生，mysql会递归执行这些子查询，把结果放在临时表里。

table列显示的derived是衍生出的临时表，derived2，从id为2那行记录衍生出的临时表。

[5]UNION。

如果第2个select查询语句出现在UNION的后面，则被标记为UNION。

如果UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

[6]UNION RESULT。

从UNION表获取结果的SELECT。两个UNION合并的结果集。

3）table。

显示这一行的数据与哪一张表有关。

4）type。

访问类型排列。显示查询使用了何种类型。

type从最好的到最差的简化排序依次是：

system > const > eq_ref > ref > range > index > ALL

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。

索引1次就找到了记录，只匹配1行数据。const用于比较primary key、unique索引。

例如：在where条件后面查询主键，mysql会将该查询转换成一个常量。

[3] eq_ref。

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

[4] ref。

非唯一性索引扫描，返回匹配某个单独值的所有行。可能会找到多个符合条件的行，属于查找和扫描的混合体。

[5] range。

只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。

一般是在where语句中使用了between、<、>、in等的查询。

范围扫描索引只需要开始于索引的某一点，结束于另一点，不用扫描全部索引。

[6] index。

Full Index Scan，index与ALL区别为index类型只遍历索引树，通常比ALL快，索引文件通常比数据文件小。虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的。

[7] all。

Full Table Scan，将遍历全表以找到匹配的行。

5）possible_keys。

理论上可能被用到的索引，显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

6）key。

实际使用的索引。如果显示为NULL，则没有使用索引或者没有建立索引。

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

覆盖索引：创建了复合索引，刚好查询时用的字段与复合索引的字段个数、顺序吻合。

7）key_len。

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

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

8）ref。

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

9）rows。

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

10）Extra。

包含不适合在其他列中显示但十分重要的额外信息。

[1] Using filesort。

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为文件排序。

无法利用索引中的排序，mysql自己创建了一个排序，是一个不好的现象。

[2] Using temporary。

产生了一个临时表，是一个不好的现象。

使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

[3] USING index。

表示相应的select操作中使用了覆盖索引Covering Index，避免访问了表的数据行，查询效率高。

如果出现using index时，同时出现using where，表明索引被用来执行索引键值的查找。

如果只出现using index，没有出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，查询列要被所建的索引覆盖。

索引时高效查找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了或覆盖了满足查询结果的数据就叫做覆盖索引。

使用覆盖索引时，select列表中只取出需要的列，不要写select * from。

查询的列要被所建的索引覆盖，查询时的字段个数、顺序要与所建索引一致。

[4] using where。

表明使用了where过滤。

[5] using join buffer。

使用了join连接缓存。

[6] impossible where。

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

[7] select tables optimized away。

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

[8] distinct。

优化distinct操作，在找到第一匹配的元组后即停止查找同样值的动作。

[9] using index condition。

搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

8、索引案例，数据库表优化。

（1）索引案例，单表优化。

# 索引案例，单表优化。
select * from article;

-- 1
EXPLAIN select id, author_id from article where category_id=1 and comments > 1 order by views desc limit 1;

show index from article;

create index idx_article_ccv on article(category_id, comments, views);

EXPLAIN select id, author_id from article where category_id=1 and comments > 1 order by views desc limit 1;

-- 2
drop index idx_article_ccv on article;
EXPLAIN select id, author_id from article where category_id=1 and comments = 1 order by views desc limit 1;

-- 3 索引建立在category_id，views两个字段上。
drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id, views);
EXPLAIN select id, author_id from article where category_id=1 and comments > 1 order by views desc limit 1;

（2）索引案例，两张表优化。

-- 结论：本例情况，左连接相反加，左连接时在右表创建索引。右连接相反加，右连接时在左表创建索引。

-- 由左表连接的特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，所以右表要建立索引。

# 索引案例，两张表优化。
select * from class;
select * from book;

-- 1、
EXPLAIN select * from class LEFT JOIN book ON class.card = book.card;
ALTER TABLE book ADD INDEX Y(card);
-- 2、
EXPLAIN select * from class LEFT JOIN book ON class.card = book.card;
-- 3、
DROP INDEX Y ON book;
-- 4、
ALTER TABLE class ADD INDEX Y(card);
-- 5、
EXPLAIN select * from class LEFT JOIN book ON class.card = book.card;

-- 结论：本例情况，左连接相反加，左连接时在右表创建索引。右连接相反加，右连接时在左表创建索引。
-- 由左表连接的特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，所以右表要建立索引。

-- 6、
DROP INDEX Y ON class;
show INDEX from class
show INDEX from book
-- 7、
ALTER TABLE book ADD INDEX Y(card);
EXPLAIN select * from class LEFT JOIN book ON class.card = book.card;
-- 8、
EXPLAIN select * from class RIGHT JOIN book ON class.card = book.card;
-- 9、
DROP INDEX Y ON book;
-- 10、
ALTER TABLE class ADD INDEX Y(card);
-- 11、
EXPLAIN select * from class RIGHT JOIN book ON class.card = book.card;

（3）索引案例，三张表优化。

-- 结论：索引设置在需要经常查询的字段中。

-- 尽可能减少join语句中的nestedloop的循环总次数，永远用小结果集驱动大的结果集。

-- 优先优化nestedloop的内层循环。

-- 保证join语句中被驱动表上join条件字段已经被索引。

-- 配置文件设置合理的JoinBuffer连接缓存。

# 索引案例，三张表优化。
-- 1、
show INDEX from class;
show INDEX from book;
DROP INDEX Y ON class;

-- 2、
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;
-- 3、
ALTER TABLE book ADD INDEX Y(card);
ALTER TABLE phone ADD INDEX Y(card);
-- 4、
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card;

-- 结论：索引设置在需要经常查询的字段中。
-- 尽可能减少join语句中的nestedloop的循环总次数，永远用小结果集驱动大的结果集。
-- 优先优化nestedloop的内层循环。
-- 保证join语句中被驱动表上join条件字段已经被索引。
-- 配置文件设置合理的JoinBuffer连接缓存。

9、索引优化。

（1）全值匹配我最爱。

建的索引字段刚好与需要查询的字段一致，字段个数、名称、顺序完全相同。

（2）最佳左前缀法则。

如果索引了多个字段列，需要遵守最左前缀法则，查询从索引的最左前列开始并且不跳过索引中间的列。

带头大哥不能挂，中间兄弟不能断。

（3）索引列上少计算。

不在索引列上做任何操作，包括计算、函数、自动或手动类型转换，会导致索引失效，从而变成全表扫描。

（4）范围之后全失效。

存储引擎不能使用索引中范围条件右边的列。rang范围之后全失效。

（5）覆盖索引不写*。

尽量使用覆盖索引，只访问索引的查询，索引列和查询列一致，减少使用select * from查询。

（6）不等空值还有or，索引失效要少用。

   1）在使用不等于“!=”、“<>”的时候，无法使用索引会导致全表扫描。

mysql8.0.12，type显示为range。mysql5.6.17，type显示为ALL。

8.x的mysql进行了优化。

   2）is null、is not null无法使用索引。

is not null可以通过搭配覆盖索引使用上索引。

is null不可以使用覆盖索引。

   3）少用or，使用or连接会导致索引失效。

（7）like百分写最右。

使用like以通配符“%abc...”开头，索引会失效，变成全表扫描。

“%abc...”索引会失效。

“%abc...%”索引会失效。

“abc...%”索引不会失效。

解决like “%abc...%”时索引不被使用的方法。

like查询的%写右边。如果要两边都要%，搭配覆盖索引使用上索引，使用覆盖索引解决like “%abc...%”时索引没有使用的情况。

（8）varchar单引号不可丢。

字符串不加单引号导致索引失效。

varchar类型要加单引号’字符串’。

（9）总结。

|
假设有一个复合索引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 and c=4
|
Y，使用到a,b,c。 |
|
where b=3 或者where b=3 and c=4 或者where c=4
|
N。？？？ |
|
where a=3 and c=5
|
使用到a，但是c不可以，断了中间b。 |
|
where a=3 and b>4 and c=5
|
使用到a和b，c不能用在范围后面，b断了。 |
|
where a=3 and b like ‘kk%’ and c=4
|
Y，使用到a,b,c。 |
|
where a=3 and b like ‘%kk’ and c=4
|
Y，只用到a。 |
|
where a=3 and b like ‘%kk%’ and c=4
|
Y，只用到a。 |
|
where a=3 and b like ‘k%kk%’ and c=4
|
Y，使用到a,b,c。 |

（10）索引优化记忆口诀。

全值匹配我最爱，最左前缀要遵守。

带头大哥不能挂，中间兄弟不能断。

索引列上少计算，范围之后全失效。

like百分写最右，覆盖索引不写*。

不等空值还有or，索引失效要少用。

varchar单引号不可丢，sql高级也不难。

-- 索引优化1
show index from staffs;
-- 1、部分匹配索引，用到了1个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July";
-- 2、部分匹配索引，用到了两个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age=23;
-- 3、匹配索引，用到了3个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age=23 AND pos="dev";
-- 4、部分匹配索引，用到了1个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July" AND pos="dev";
-- 5、没有匹配到索引，用到了0个索引。
EXPLAIN SELECT * FROM staffs WHERE age=23 AND pos="dev";
-- 6、没有匹配到索引，用到了0个索引。
EXPLAIN SELECT * FROM staffs WHERE pos="dev";



-- 索引优化2
-- 1、部分匹配索引，用到了1个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July";
-- 2、
EXPLAIN SELECT * FROM staffs WHERE left(name,4)="July";
-- 3、


-- 索引优化3
-- 1、匹配索引，用到了3个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age=23 AND pos="dev";
-- 2、
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age>23 AND pos="dev";


-- 索引优化4
desc staffs;
-- 1、匹配索引，用到了3个索引。
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age=23 AND pos="dev";
-- 2、
EXPLAIN SELECT name,age,pos FROM staffs WHERE name="July" AND age=23 AND pos="dev";
-- 3、mysql8.0.12，type显示为range。mysql5.6.17，type显示为ref。
EXPLAIN SELECT name,age,pos FROM staffs WHERE name="July" AND age>23 AND pos="dev";
-- 4、mysql8.0.12，type显示为range。mysql5.6.17，type显示为ref。
EXPLAIN SELECT name,age,pos FROM staffs WHERE name="July" AND age>23;
-- 5、
EXPLAIN SELECT name,age,pos FROM staffs WHERE name="July";


-- 索引优化5
-- 1、
EXPLAIN SELECT * FROM staffs WHERE name="July";
-- 2、mysql8.0.12，type显示为range。mysql5.6.17，type显示为ALL。Extra显示为Using index condition。
EXPLAIN SELECT * FROM staffs WHERE name!="July";
-- 3、mysql8.0.12，type显示为range。mysql5.6.17，type显示为ALL。Extra显示为Using index condition。
EXPLAIN SELECT * FROM staffs WHERE name<>"July";
-- 4、
EXPLAIN SELECT name,age,pos FROM staffs WHERE name="July";
-- 5、mysql8.0.12，type显示为range。mysql5.6.17，type显示为index。
EXPLAIN SELECT name,age,pos FROM staffs WHERE name!="July";
-- 6、mysql8.0.12，type显示为range。mysql5.6.17，type显示为index。
EXPLAIN SELECT name,age,pos FROM staffs WHERE name<>"July";


-- 索引优化6
-- 1、没有使用索引。
EXPLAIN SELECT * FROM staffs WHERE name is null;
-- 2、没有使用索引。
EXPLAIN SELECT * FROM staffs WHERE name is not null;
-- 3、is not null可以通过搭配覆盖索引使用上索引。
EXPLAIN SELECT name,age,pos FROM staffs WHERE name is not null;


-- 索引优化7
-- 1、type显示为ALL，全表扫描。
EXPLAIN SELECT * FROM staffs WHERE name like "%July%";
-- 2、type显示为ALL，全表扫描。
EXPLAIN SELECT * FROM staffs WHERE name like "%July";
-- 3、type显示为range。
EXPLAIN SELECT * FROM staffs WHERE name like "July%";
EXPLAIN SELECT * FROM staffs WHERE name like "J%uly%";

-- 4、
SELECT * FROM tbl_user WHERE name like "%aa%";
SELECT * FROM tbl_user WHERE name like "aa%";
SELECT * FROM tbl_user WHERE name like "%aa";
-- 5、
EXPLAIN SELECT name,age FROM tbl_user WHERE name like "%aa%";
show index from tbl_user;
-- 6、查看建索引前情况。
EXPLAIN SELECT id FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT name FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT age FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name,age FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT name,age FROM tbl_user WHERE name like "%aa%";

EXPLAIN SELECT * FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name,age,email FROM tbl_user WHERE name like "%aa%";

-- 7、创建索引
CREATE INDEX idx_user_nameAge ON tbl_user(name,age);

-- 8、查看建索引后情况。
-- 使用了索引，使用覆盖索引解决like “%abc...%”时索引不被使用的方法。
EXPLAIN SELECT id FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT name FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT age FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name,age FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT name,age FROM tbl_user WHERE name like "%aa%";

-- 9、没有使用索引，索引失效。
EXPLAIN SELECT * FROM tbl_user WHERE name like "%aa%";
EXPLAIN SELECT id,name,age,email FROM tbl_user WHERE name like "%aa%";


-- 索引优化8，索引优化9
-- 1、name是varchar类型，不要在索引列上做任何操作，自动或手动转换等。
select * from staffs;
select * from staffs where name='2000';
select * from staffs where name=2000;
-- type显示为ref。
EXPLAIN select * from staffs where name='2000';
-- type显示为ALL。
EXPLAIN select * from staffs where name=2000;

-- 少使用or连接。
-- mysql8.0.12，type显示为range，Extra显示为Using index condition。
-- mysql5.6.17，type显示为ALL，Extra显示为Using where。
EXPLAIN select * from staffs where name='July' or name='z3';

10、小表驱动大表。

（1）优化流程。

1）开启慢查询，捕获sql。

2）explain + 慢sql分析。

3）show profile查询sql在mysql服务器里面的执行细节和生命周期情况。

4）调优mysql数据库服务器的参数。

（2）小表驱动大表。

小表驱动大表，即小的数据集驱动大的数据集，数据量小的表驱动数据量大的表。

1）当b表的数据量小于a表的数据量时，用in优于exists。 

select * from employee a where id in(select id from department b);
等价于：
for select id from b
for select * from a where a.id=b.id

2）当a表的数据量小于b表的数据量时，用exists优于in。 

select * from employee a where exists (select 1 from department b where b.id=a.id);
等价于：
for select * from a
for select * from b where b.id=a.id

a表与b表的id字段建立索引。

select * from tbl_emp e where e.deptId in (select id from tbl_dept d);
select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id=e.deptId);
select * from tbl_emp e where exists (select 'X' from tbl_dept d where d.id=e.deptId);
select * from tbl_emp e where exists (select 'Y' from tbl_dept d where d.id=e.deptId);

3）exists。

select ... from table where exists(subquery);

将主查询的数据，放到子查询中做条件验证，根据验证结果true或false来决定主查询的数据结果是否得以保留。

exists(subquery)只返回true或false，因此子查询中的select *也可以是select 1或select ‘X’或select 'Y'，实际执行时会忽略select清单。

exists子查询实际执行过程可能经过了优化。

11、使用order by排序时用上索引，order by排序关键字优化。

（1）order by子句尽量使用Using index方式排序，避免使用Using filesort方式排序。

MySQL支持两种方式的排序，FileSort、Index，Index效率高。

指mysql扫描索引本身完成排序，FileSort方式效率较低。

order by满足以下两种情况，才会使用Index方式排序。

order by语句使用索引最左前列。

使用where子句与order by子句条件列组合满足索引最左前列。

（2）filesort的两种算法：双路排序、单路排序。

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

如果不在索引列上，filesort有两种算法：双路排序、单路排序。

双路排序：mysql4.1之前是使用双路排序，取一批数据，扫描两次磁盘，最终得到数据。

读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。

并且把随机IO变成了顺序IO，但是它会使用更多的空间，把每一行都保存在内存中。

总体而言单路好于双路。

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序，创建tmp文件，多路合并，排完再取。

sort_buffer容量大小，再排....导致多次I/O，大量I/O操作。

（3）优化策略。

增大sort_buffer_size参数的设置。

增大max_length_for_sort_data参数的设置。

提高order by的速度。

1）使用order by时，不写select *，写查询需要的具体字段。

当查询的字段大小总和小于max_length_for_sort_data，排序的字段不是text、blob类型时，会使用改进后的算法单路排序，否则使用多路排序。

两种算法的数据可能超出sort_buffer的容量大小，超出之后，会创建tmp文件进行合并排序，导致多次I/O。

2）提高sort_buffer_size大小。

这个参数配置大会提高效率，根据系统服务器情况配置。

3）提高max_length_for_sort_data的大小。

提高这个参数，会增加用改进算法的概念。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

（4）为排序使用索引。

mysql使用两种排序方式：Using filesort文件排序，Using index扫描有序索引排序。

mysql能为排序与查询使用相同的索引。

复合索引key a_b_c(a,b,c)

order by能使用索引最左前缀。

order by a

order by a,b

order by a,b,c

order by a desc, b desc, c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引。

where a=const order by b,c

where a=const and b=const order by c

where a=const and b>const order by b,c

不能使用索引进行排序。

-- 排序不一致

order by a asc,b desc,c desc

-- 丢失a索引

where g=const order by b,c

-- 丢失b索引

where a=const order by c

-- d不是索引的一部分

where a=const order by a,d

-- 对于排序来说，多个相等条件也是范围查询

where a in(...) order by b,c

12、group by关键字优化。

group by先排序后进行分组，遵照索引建的最佳左前缀。

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

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

其他优化与order by优化相同。

13、慢查询日志。

（1）mysql的慢查询日志。

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

long_query_time的默认值为10秒。

（2）查看是否开启慢查询。

show variables like '%slow_query_log%';

（3）开启慢查询日志。

-- 临时开启慢查询日志，mysql重启后失效。

set global slow_query_log=1

永久开启慢查询，mysql重启后不会失效。

修改my.cnf文件，[mysqld]标签下增加以下参数内容。

# 开启慢查询日志记录。

slow_query_log=1

# 设置慢查询日志记录文件名称。

slow_query_log_file=/var/lib/mysql/mysql-slow.log

# 超过3秒记录慢查询日志。

long_query_time=3;

# 日志记录到文件中。

log_output=FILE

不设置slow_query_log_file文件名，默认文件名为主机名称-slow.log。

（4）慢查询配置的时间。

-- 查看慢查询配置的时间，执行sql超过这个时间才会记录。

show variables like 'long_query_time%';

-- 设置慢查询配置的时间。

set global long_query_time=3;

设置之后，需要断开mysql连接，重新开一个窗口会话，才能用【show variables like 'long_query_time%';】看到修改的值。

-- 模拟慢查询sql

select sleep(4);

-- 查询当前系统中有多少条慢查询记录。

show global status like '%Slow_queries%';

（5）mysqldumpslow日志分析工具。

window下需要安装Perl。

Perl下载地址：Strawberry Perl for Windows

The Perl Programming Language - www.perl.org

日志分析工具mysqldumpslow。

查看mysqldumpslow帮助信息。

s：是表示按照何种方式排序。

c：访问次数。

l：锁定时间。

r：返回记录。

t：查询时间。

al：平均锁定时间。

ar：平均返回记录数。

at：平均查询时间。

t：即为返回前面多少条的数据。

g：后边搭配一个正则匹配模式，大小写不敏感。

-- 得到返回记录集最多的10个sql。

mysqldumpslow -s r -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

-- 得到访问次数最多的10个sql。

mysqldumpslow -s c -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

--得到按照时间排序的前10条里面包含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g "left join" 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

-- 结合| more使用，防止爆屏，防止大量日志刷屏。

mysqldumpslow -s r -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log' | more

14、批量插入数据脚本。

# 创建数据插入函数
# $$：更改结束符为$，当然不用$用别的也可以
# 用于随机产生字符串
delimiter $$
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT ' ';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = concat( return_str, substring( chars_str, floor( 1 + rand( ) * 52 ), 1 ) );
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END $$

# 用于随机产生部门编号
delimiter $$
CREATE FUNCTION rand_num ( ) RETURNS INT ( 5 ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET i = floor( 100 + rand( ) * 10 );
	RETURN i;
	
END $$

# 创建存储过程
# 插入员工表
delimiter $$
CREATE PROCEDURE insert_emp ( IN START INT ( 10 ), IN max_num INT ( 10 ) ) BEGIN
	DECLARE
		i INT DEFAULT 0;# 将其设为0，防止将insert语句自动提交
	
	SET autocommit = 0;
	REPEAT
			
			SET i = i + 1;
		INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
		VALUES
			(
				( START + i ),
				rand_string ( 6 ),
				'SALESMAN',
				0001,
				curdate( ),
				2000,
				400,
				rand_num ( ) 
			);
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;
	
END $$

# 插入部门表
delimiter $$
CREATE PROCEDURE insert_dept ( IN START INT ( 10 ), IN max_num INT ( 10 ) ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;# 将其设为0，防止将insert语句自动提交
	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 $$

delimiter ; # 将$$结束符改回;

-- 
CALL insert_dept(100, 10);
CALL insert_dept(100001, 500000);
CALL insert_emp(100001, 500000);
select * from emp;
select * from dept;

15、用Show Profile进行sql分析。

（1）Show Profile介绍。

用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量。

（2）sql分析。

-- 查看当前是否支持profile。
show variables like 'profiling%';
show variables like 'profiling';
-- 开启profile。
set profiling=on;

select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d  on e.deptId=d.id;
select * from tbl_emp e left join tbl_dept d  on e.deptId=d.id;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

-- 查看结果。
show profiles;
# 诊断sql，查看sql的执行的完整生命周期，分步分解的细粒度步骤。show profile cpu,block io for query ID。
show profile cpu,block io for query 138;

（3）日常开发需要注意的结论。

converting HEAP to MyISAM。

查询结果太大，内存不够用，往磁盘上放数据了。

Creating tmp table。

创建临时表。拷贝数据到临时表，用完再删除。

Copying to tmp table on disk。

把内存中临时表复制到磁盘，很危险。

locked。

锁住了。

16、全局查询日志。

开启全局查询日志，不要在生产环境使用。

修改my.cnf配置文件，永久生效。

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

临时开启全局查询日志，mysql重启后失效。

set global general_log=1;
set global log_output='TABLE';

select * from dept;

编写的sql语句记录到mysql库中的general_log表。使用如下命令查看。

select * from mysql.general_log;

17、数据库锁。

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

（1）锁的分类。

从对数据操作的类型区分：读锁、写锁。

读锁（共享锁）：对于同一份数据，多个读操作可以同时进行，不会相互影响。

写锁（排它锁）：当前写操作没有完成之前，阻断其他写锁和读锁。

从对数据操作的粒度区分：表锁、行锁。

表锁。

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

手动增加表锁。

lock table 表名字1 read(write), 表名字2 read(write)，其它。

查看表上加过的锁，In_use字段显示1为已加锁，0位没有加锁。

show open tables;

18、数据库表锁。

（1）表加read锁。

-- 查看表上加过的锁。
show open tables;
-- mylock表加读锁，book表加写锁。
lock table mylock read, book write;
-- 释放锁，释放被当前会话持有的任何锁。
unlock tables;

-- 1、mylock表加读锁。
lock table mylock read;
-- 2、当前会话1可以查询mylock表。其他会话2也可以查询mylock表。
select * from mylock;
-- 3、不能修改，Table 'mylock' was locked with a READ lock and can't be updated
update mylock set name='a2' where id=1;
-- 4、不能读其他表，Table 'book' was not locked with LOCK TABLES
select * from book;
unlock tables;

unlock tables;
-- 2、会话2也可以查询mylock表。
select * from mylock;
-- 3、会话2也可以查询其他表book。
select * from book;
-- 会话2出现阻塞，不能立刻修改。当会话1unlock tables解锁表后，会话2可以修改。
update mylock set name='a3' where id=1;

|
session1
|
session2 |
|
获得mylock表的read锁。

lock table mylock read;
|
连接上mysql终端。 |
|
当前session1可以查询mylock表的记录。

select * from mylock;
|
session2可以查询mylock表的记录。

select * from mylock; |
|
当前session1不能查询其他没有锁定的表。

select * from book;
|
session2可以查询、更新未锁定的表。

select * from book;

update book set card=1 where bookid=1; |
|
当前session1不能插入、更新锁定的表。

update mylock set name='a2' where id=1;

insert into mylock(name) values('e');
|
session2插入、更新锁定的表时，会出现阻塞现象，会一直等待获得锁。

update mylock set name='a3' where id=1; |
|
当前会话session1释放锁。

unlock tables;
|
session1释放锁后，session2获得锁，阻塞消失，完成更新或插入操作。

update mylock set name='a3' where id=1; |

（2）表加write锁。

-- mylock表加写锁。
lock table mylock write;
-- 当前会话1可以读自己锁住的mylock表。
select * from mylock;
-- 当前会话1可以修改自己锁住的mylock表。
update mylock set name='a4' where id=1;
-- 当前会话1不能读取其他表。
select * from book;

-- mylock表加写锁。会话2查询出现阻塞，更加不可能修改、插入。
select * from mylock where id=1;

|
session1
|
session2 |
|
获得mylock表的write写锁定。

lock table mylock write;
|
待session1开启写锁后，session2再连接终端。 |
|
当前session1对锁定的mylock表，可以执行查询、更新、插入操作。

select * from mylock;

update mylock set name='a4' where id=1;

insert into mylock(name) values('f');
|
session2对已经被session1锁定的表作查询操作，查询被阻塞，需要等待session1释放锁。

select * from mylock where id=1; |
|
session1释放锁。

unlock tables;
|
session1释放锁，session2获得锁，查询返回数据。

select * from mylock where id=1; |

（3）表读锁和写锁对比。

MyISAM在执行select查询语句之前，会自动给涉及的所有表加锁，在执行增删改操作前，会自动给涉及的表加写锁。

mysql的表级锁模式：

表共享读锁Table Read Lock。

表独占写锁Table Write Lock。

|
锁类型
|
可否兼容
|
其他session读锁
|
其他session写锁 |
|
读锁
|
是
|
是
|
否 |
|
写锁
|
是
|
否
|
否 |

1）对MyISAM表的读操作，加读锁，不会堵塞其他进程对同一表的读请求，会堵塞其他线程对同一表的写请求。

只有当读锁释放后，才会执行其它进程的写操作。

2）对MyISAM表的写操作，加写锁，会阻塞其它进程对同一表的读和写操作。

只有当写释放后，才会执行其它进程的读写操作。

总结：读锁会堵塞写，不会堵塞读。写锁会把读和写都堵塞。

（4）分析表锁定。

-- 分析表锁定。
show status like 'table%';

Table_locks_immediate

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

Table_locks_waited

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

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

-- 查看表上加过的锁。
show open tables;
-- mylock表加读锁，book表加写锁。
lock table mylock read, book write;
-- 释放锁，释放被当前会话持有的任何锁。
unlock tables;

-- 1、mylock表加读锁。
lock table mylock read;
-- 2、当前会话1可以查询mylock表。其他会话2也可以查询mylock表。
select * from mylock;
-- 3、不能修改，Table 'mylock' was locked with a READ lock and can't be updated
update mylock set name='a2' where id=1;
insert into mylock(name) values('e');
-- 4、不能读其他表，Table 'book' was not locked with LOCK TABLES
select * from show ;
unlock tables;

-- 手动kill掉锁住的表
SHOW PROCESSLIST;
kill 22


-- mylock表加写锁。
lock table mylock write;
-- 当前会话1可以读自己锁住的mylock表。
select * from mylock;
-- 当前会话1可以修改自己锁住的mylock表。
update mylock set name='a4' where id=1;
insert into mylock(name) values('f');
-- 当前会话1不能读取其他表。
select * from book;
unlock tables;

-- 分析表锁定。
show status like 'table%';

-- 在此处执行不能释放session1的锁。
unlock tables;
-- 2、会话2也可以查询mylock表。
select * from mylock;
-- 3、会话2也可以查询其他表book。
select * from book;
-- 会话2出现阻塞，不能立刻修改。当会话1unlock tables解锁表后，会话2可以修改。
update mylock set name='a3' where id=1;

-------------
-- mylock表加写锁。会话2查询出现阻塞，更加不可能修改、插入。
select * from mylock where id=1;

19、数据库行锁。

（1）行锁特点。

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

InnodbDB与MyISAM最大不同有两点：支持事务TRANSACTION。采用了行级锁。

（2）行锁支持事务。

事务Transaction及其ACID属性。

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常称为事务的ACID属性。

原子性Atomicity：事务是一个原子操作单元，对数据的修改，要么全都执行成功，要么全都不执行。

一致性Consistent：在事务开始和完成时，数据都必须保持一致状态。保持数据的完整性。

隔离性Isolation：通过数据库系统的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。事务处理过程的中间状态对外部是不可见的。

持久性Durable：事务完成之后，对于数据的修改时持久性的。

（3）并发事务处理带来的问题。

1）更新丢失lost update。

当多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发送丢失更新问题，最后的更新覆盖了由其他事务所做的更新。

例如：两个程序员修改同一个java文件，每个程序员独立更新其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前面一个程序员所做的更改。

如果再一个程序员完成并且提交事务之前，另一个程序员不能访问同一个文件，则可避免此问题。

2）脏读dirty reads。

一个事务正在对一条记录做修改，在这个事务完成并且提交之前，这条记录的数据就处于不一致的状态。此时，另一个事务也来读取同一条记录，

如果不加控制，第二个事务读取了这些脏数据，并且根据此数据做进一步的处理，就会产生未提交的数据依赖关系。

这种现象就是脏读。

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

3）不可重复读non-repeatable reads。

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，发现读取的数据已经发生了改变，或者某些记录已经被删除了，这种现象就叫做不可以重复度。

事务A读取到了事务B已经提交的修改数据，不符合隔离性。

4）幻读phantom reads。

一个事务按相同的查询条件重新读取以前检索过的数据，发现其他事务插入了满足其查询条件的新数据，这种现象就是幻读。

事务A读取到了事务B提交的新增加的数据，不符合隔离性。

幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

（4）事务的隔离级别。

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

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行，显然与并发是矛盾的。

同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对不可重复读和幻读并不敏感，可能更关心数据并发访问的能力。

查看当前的数据库事务隔离级别。

-- mysql5.6.17
show variables like 'tx_isolation';
-- mysql8.0.12
show variables like 'transaction_isolation';

（5）行锁演示。

|
session1
|
session2 |
|
操作同一条记录。 |
|
-- 1、关闭自动提交。当前session禁用自动提交事物，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

set autocommit=0;
|
-- 1、关闭自动提交。当前session禁用自动提交事物，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

set autocommit=0; |
|
session1修改更新记录，暂时不提交记录，没有手动commit。

update test_innodb_lock set b='4001' where a=4;
|
session2修改被阻塞，只能等待。

update test_innodb_lock set b='4002' where a=4; |
|
session1手动commit提交更新。

commit;
|
session2解除阻塞，更新记录完成。

update test_innodb_lock set b='4001' where a=4; |
|
操作不同的记录互不影响。 |
|
session1更新id为4的记录。

-- 1、

update test_innodb_lock set b='4005' where a=4;

-- 2、

commit;

-- 3、

select * from test_innodb_lock;
|
session2更新id为9的记录。

-- 1、

update test_innodb_lock set b='9001' where a=9;

-- 2、

commit;

-- 3、

select * from test_innodb_lock; |

（6）索引失效行锁变表锁。

|
session1
|
session2 |
|
恢复数据

-- 1、

update test_innodb_lock set b='4000' where a=4;

-- 2、

commit;

-- 3、

select * from test_innodb_lock;
|
恢复数据

-- 1、

update test_innodb_lock set b='9000' where a=9;

-- 2、

commit;

-- 3、

select * from test_innodb_lock; |
|
查看表建立的索引。

-- 4、

show index from test_innodb_lock;
|
查看表建立的索引。

-- 4、

show index from test_innodb_lock; |
|
正常更新。

-- 5、

update test_innodb_lock set a=41 where b='4000';
|
正常更新。

-- 5、

update test_innodb_lock set b='9001' where a=9; |
|
-- 6、

commit;
|
-- 6、

commit; |
|
已建立的索引失效，导致从行锁变成表锁。 |
|
-- 7、b字段建立了索引，b字段没有用单引号括起来'4000'，索引失效，从行锁变成了表锁。

update test_innodb_lock set a=41 where b=4000;
|
-- 7、

update test_innodb_lock set b='9002' where a=9; |
|
-- 8、

commit;

-- 9、

select * from test_innodb_lock;
|
-- 8、

commit;

-- 9、

select * from test_innodb_lock; |

（7）间隙锁危害。

|
session1
|
session2 |
|
更新某个键值范围内的记录，锁住了这个范围内的记录，包括存在和不存在的记录。

update test_innodb_lock set b='0629' where a>1 and a<6;
|
产生阻塞，暂时不能插入，只能等待。

insert into test_innodb_lock values(2, '2000'); |
|
commit;
|
接触阻塞，执行插入记录完成。

insert into test_innodb_lock values(2, '2000'); |

当用范围条件而不是相等条件查询数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁。

对于键值在条件范围内，但是不存在的记录（符合查询范围条件，但是记录不存在，ID序号不连续。），叫做间隙GAP。

InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。

查询执行过程中通过范围查找，将锁定整个范围内所有的索引键值，即使这个键值不存在。

间隙锁锁定一个范围键值之后，即使某些不存在的键值也会被锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据。

在某些场景下可能会对性能造成很大的危害。

（8）使用for update锁定某一行记录。

|
session1
|
session2 |
|
select * from test_innodb_lock;
|
select * from test_innodb_lock; |
|
begin;

select * from test_innodb_lock where a=8 for update;
|
update test_innodb_lock set b='8001' where a=8; |
|
commit;
|
commit; |
|
commit;
| |
|
select * from test_innodb_lock;
|
select * from test_innodb_lock; |

（9）行锁总结。

  1）行锁总结。

InnoDB存储存储引擎由于实现了行级锁定，在锁定机制的实现方面的性能损耗可能比表级锁高。

在并发量较高时，InnoDB的整体性能比MyISAM高，处理并发的能力强。

如果InnoDB使用不当，可能造成行锁变成表锁。

  2）行锁分析。

-- 行锁分析
show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits

当前正在等待锁定的数量。

Innodb_row_lock_time

等待总时长。从系统启动到现在锁定总时间长度。

Innodb_row_lock_time_avg

等待平均时长。每次等待所花平均时间。

Innodb_row_lock_time_max

从系统启动到现在等待最长的一次所花的时间。

Innodb_row_lock_waits

等待总次数。系统启动后到现在总共等待的次数。

当Innodb_row_lock_waits等待次数很高，每次等待时长很大时，需要分析系统情况，根据分析结果进行优化。

3）行锁优化。

行锁分析。

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

合理设计索引，尽量缩小锁的范围。

尽可能较少检索条件，避免间隙锁。

尽量控制事务大小，减少锁定资源量和时间长度。

尽可能低级别事务隔离。

-- 0、关闭自动提交。当前session禁用自动提交事物，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。
set autocommit=0;

-- 操作同一条记录。
-- 1、
select * from test_innodb_lock;
-- 3、
update test_innodb_lock set b='4001' where a=4;
-- 4、
select * from test_innodb_lock;
-- 5、
commit;


-- 1、
update test_innodb_lock set b='4002' where a=4;
-- 2、
select * from test_innodb_lock;
-- 3、
commit;
-- 4、
select * from test_innodb_lock;

-- 操作不同记录。
-- 1、
update test_innodb_lock set b='4005' where a=4;
-- 2、
commit;
-- 3、
select * from test_innodb_lock;


-- 索引失效行锁变表锁。
-- 1、
update test_innodb_lock set b='4000' where a=4;
-- 2、
commit;
-- 3、
select * from test_innodb_lock;
-- 4、
show index from test_innodb_lock;
-- 5、
update test_innodb_lock set a=41 where b='4000';
-- 6、
commit;
-- 7、b字段建立了索引，b字段没有用单引号括起来'4000'，索引失效，从行锁变成了表锁。
update test_innodb_lock set a=41 where b=4000;
-- 8、
commit;
-- 9、
select * from test_innodb_lock;



-- 间隙锁危害。
select * from test_innodb_lock;
-- 1、
update test_innodb_lock set b='0629' where a>1 and a<6;
-- 2、
commit;



-- 如何锁定某一行记录。
select * from test_innodb_lock;

begin;
select * from test_innodb_lock where a=8 for update;
commit;
commit;



-- 行锁分析
show status like 'innodb_row_lock%';

-- 0、关闭自动提交。
set autocommit=0;

-- 操作同一条记录。
-- 1、
select * from test_innodb_lock;
-- 2、
update test_innodb_lock set b='4001' where a=4;
-- 3、
select * from test_innodb_lock;
-- 4、
commit;



-- 1、
update test_innodb_lock set b='4003' where a=4;
-- 2、
commit;
-- 3、
select * from test_innodb_lock;


-- 操作不同记录。
-- 1、
update test_innodb_lock set b='9001' where a=9;
-- 2、
commit;
-- 3、
select * from test_innodb_lock;



-- 索引失效行锁变表锁。
-- 1、
update test_innodb_lock set b='9000' where a=9;
-- 2、
commit;
-- 3、
select * from test_innodb_lock;
-- 4、
show index from test_innodb_lock;
-- 5、
update test_innodb_lock set b='9001' where a=9;
-- 6、
commit;
-- 7、
update test_innodb_lock set b='9002' where a=9;
-- 8、
commit;
-- 9、
select * from test_innodb_lock;



-- 间隙锁危害。
select * from test_innodb_lock;
-- 1、
insert into test_innodb_lock values(2, '2000');
-- 2、
commit;



-- 如何锁定某一行记录。
update test_innodb_lock set b='8001' where a=8;
select * from test_innodb_lock;
commit;

-- 1、
select * from test_innodb_lock;

20、页锁。

开销和加锁时间介于表锁和行锁之间，会出现死锁。

锁定粒度介于表锁和行锁之间，处理高并发能力一般。

21、mysql主从复制。

（1）复制的基本原理。

slave会从master读取binlog二进制日志来进行数据同步。

（2）mysql复制过程的三个步骤。

1）master将改变记录到二进制日志binary log，这个过程叫做二进制日志事件binary log events。

2）slave将master的binary log events拷贝到他的中继日志relay log。

3）slave重做中继日志中的事件，将改变应用到自己的数据库中。

mysql复制是异步串行化的。复制可能有延迟。

（3）复制的基本原则。

每个slave只有一个master。

每个slave只能有一个唯一的服务器ID。

每个master可以有多个salve。

（4）配置mysql一主一从复制。

环境：

master主机服务器：window系统，mysql版本：mysql8.0.12。

slave从机服务器：centos7，Linux系统，mysql版本：8.0.27。

在以上环境试验成功。

步骤：

1）mysql版本需要一致，以后台服务的方式运行。

2）在同一个网段，相互可以ping的通。

3）以小写的方式，在[mysqld]节点下配置。

4）修改master主机上的my.ini文件。

注意：以下配置信息需要配置在[mysqld]节点下。 

# master主服务器唯一ID。
server_id = 1
# 启用二进制日志。
log-bin = mysql-bin
# 根目录，basedir=mysql的本地路径，安装目录。
basedir = D:\Program Files\MySQL8\mysql
# 数据目录，存放数据库的目录，datadir=mysql的本地路径/data。
datadir = D:\Program Files\MySQL8\db\data
# 主机master，读写都可以。
read-only = 0
#选择row模式
binlog-format=ROW
# 设置不用复制数据的数据库，主从同步复制数据时，忽略该数据库。
binlog-ignore-db = mysql
# 设置需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
#binlog-do-db = db0629

5）修改slave从机上的my.cnf文件。

注意：以下配置信息需要配置在[mysqld]节点下。

# salve从服务器唯一ID。
server_id=2
# 启用二进制日志。
log-bin=mysql-bin
#选择row模式
binlog-format=ROW

6）主机服务器、从机服务器关闭防火墙。

关闭主机服务器的防火墙，关闭从机服务器的防火墙，一定要关闭，就算IP地址可以ping同，也要关闭，否则会出现连接不上的现象。

7）在master主机上建立账户，并且授权给slave从机。

在主机服务器mysql上操作。

mysql8.x语法：

CREATE USER '用户名'@'slave从机数据库服务器IP地址' IDENTIFIED WITH mysql_native_password BY '用户密码';

GRANT REPLICATION SLAVE ON . TO '用户名'@'slave从机数据库服务器IP地址' WITH GRANT OPTION;

flush privileges;

示例：

use mysql;

select * from user;

创建用户名xinxin，设置密码为123456，充许192.168.3.101访问。

CREATE USER 'xinxin'@'192.168.3.101' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'xinxin'@'192.168.3.101' WITH GRANT OPTION;

flush privileges;

#查看master主机的状态。

show master status;

查看master主机的状态显示解读：

File：mysqlbin.00000x。

二进制日志文件。

Position。

从日志文件的那个位置开始复制数据。

Binlog_Do_DB。

需要复制的数据库，为空代表所有数据库，包括新建时产生的。

Binlog_Ignore_DB。

忽略的数据库，不复制的数据库。

8）在salve从机上配置需要复制的主机。

在从机服务器mysql上操作。

语法：

CHANGE MASTER TO MASTER_HOST='master主机服务器IP地址',

MASTER_USER='主机数据库用户名',

MASTER_PASSWORD='主机数据库用户密码',

MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体Position位置值;

示例：

CHANGE MASTER TO MASTER_HOST='192.168.3.3',
MASTER_USER='xinxin',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=155;

9）启动slave从机服务器的复制功能。

在从机服务器mysql上操作。 

#启动从机服务器的复制功能。
start slave;
#查看slave从机的状态。
show slave status;

显示如图所示才可以数据同步，以下为正常显示。

字段Slave_IO_State显示为Waiting for source to send event，等待master主机发送事件。

字段Slave_IO_Running显示为Yes。

字段Slave_SQL_Running显示为Yes。

注意：如果出现不正常显示，数据同步不了，需要查看这Last_IO_Error、Last_SQL_Error两个字段，错误信息会显示在这两个字段里面，根据这两个字段就可以排查原因了。

显示不正常，并且这两个字段显示为空，需要等待一段时间，slave从会重试60次去连接同步数据，重试完之后，还是不可以才会有错误显示。

不需要主从不同时，在从机上停止复制功能。

#停止slave从服务复制功能。
stop slave;

10）测试主从复制。

在主机服务器上操作。

create database mydb58;
use mydb58;
create table dog(id int not null,name varchar(20));
insert into dog values(1,'小黑');
insert into dog values(2,'小白');
insert into dog values(3,'大白');
select * from dog;

（5）配置mysql主从复制常见问题。

如果出现不正常，数据同步不了，需要查看这Last_IO_Error、Last_SQL_Error两个字段，错误信息会显示在这两个字段里面，根据这两个字段就可以排查原因了。

#查看slave从机的状态。

show slave status;

1）错误1：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

没有配置server_id，或者server_id配置错误。

server_id配置在[mysqld]节点下，master主机和slave从机都需要配置，配置成不一样的唯一标识数字。

-- 查看mysql服务ID，server_id命令。

show variables like 'server_id';

在my.ini或my.cnf配置文件中配置。

例如：

[mysqld]

master主服务器唯一ID。

server_id = 1

[mysqld]

salve从服务器唯一ID。

server_id=2

2）错误2：

error connecting to master 'xinxin@192.168.3.3:3306' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

master主机创建用户时使用的caching_sha2_password加密方式，slave从机使用mysql_native_password加密方式连接不上，出现权限认证不通过。

删掉master主机的用户，重新创建用户，创建用户指定使用mysql_native_password加密方式，WITH mysql_native_password。

例如：

use mysql;

select * from user;

delete from user where host='192.168.3.101' and user='xinxin';

CREATE USER 'xinxin'@'192.168.3.101' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON . TO 'xinxin'@'192.168.3.101' WITH GRANT OPTION;

flush privileges;

3）错误3：

error connecting to master 'xinxin@192.168.3.3:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.3.3:3306' (110)

没有关闭防火墙，连接不上mysql，关闭master主机、slave从机防火墙。

（6）my.ini和my.cnf配置文件。

window上的my.ini配置文件内容。 

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

 

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

 

# These are commonly set, remove the # and set as required.


port = 3306

# master主服务器唯一ID。
server_id = 1
# 启用二进制日志。
log-bin = mysql-bin
# 根目录，basedir=mysql的本地路径，安装目录。
basedir = D:\Program Files\MySQL8\mysql
# 数据目录，存放数据库的目录，datadir=mysql的本地路径/data。
datadir = D:\Program Files\MySQL8\db\data
# 主机master，读写都可以。
read-only = 0
#选择row模式
binlog-format=ROW
# 设置不用复制数据的数据库，主从同步复制数据时，忽略该数据库。
binlog-ignore-db = mysql
# 设置需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
#binlog-do-db = db0629

# server_id = .....

 

 

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M 

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

 

character-set-server = utf8mb4

 

performance_schema_max_table_instances = 600

table_definition_cache = 400

table_open_cache = 256


[mysql]

default-character-set = utf8mb4


[client]

default-character-set = utf8mb4

Linux上的my.cnf配置文件内容。

[mysqld]
basedir=/app/runtime/mysql
datadir=/app/data/mysql
socket=/app/data/mysql/mysql.sock
character-set-server=utf8mb4
# salve从服务器唯一ID。
server_id=2
# 启用二进制日志。
log-bin=mysql-bin
#选择row模式 
binlog-format=ROW

[client]
socket=/app/data/mysql/mysql.sock
default-character-set=utf8mb4

22、使用的数据库相关脚本。 

#初始tbl_dept,tdl_emp表

drop database if exists db0629;
create database db0629;
use db0629;



drop table if exists `tbl_dept`;

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;



drop table if exists `tbl_emp`;

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



#=====================================================================
#索引案例  article表

drop table if exists `article`;

create table if not exists `article`(
`id` int(10) unsigned not null primary key auto_increment,
`author_id` int(10) unsigned not null,
`category_id` int(10) unsigned not null,
`views` int(10) unsigned not null,
`comments` int(10) unsigned not null,
`title` varbinary(255) not null,
`content` text not null
) ;

insert into `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`)values (1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3'); 

select * from article;



#=====================================================================

drop table if exists `class`;

create table if not exists `class`(
`id` int(10) unsigned not null auto_increment,
`card` int(10) unsigned not null,
primary key(`id`)
);



drop table if exists `book`;

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




#=====================================================================

drop table if exists `phone`;

create table if not exists `phone`(
`phoneid` int(10) unsigned not null auto_increment,
`card` int(10) unsigned not null,
primary key (`phoneid`)
)engine=innodb;

insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));
insert into phone(card) values(floor(1+(rand() * 20)));



#=====================================================================

drop table if exists `staffs`;

create table `staffs`(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default  '' comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
)charset utf8 comment '员工记录表';

insert into staffs(name,age,pos,add_time)values('z3',22,'manager',now());
insert into staffs(name,age,pos,add_time)values('July',23,'dev',now());
insert into staffs(name,age,pos,add_time)values('2000',23,'dev',now());

select * from staffs;

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);



#=====================================================================

drop table if  exists `tbl_user`;

create table `tbl_user`(
`id` int(11) not null auto_increment,
`name` varchar(20) default null,
`age` int(11) default null,
email varchar(20) default null,
primary key(`id`)
) engine=innodb auto_increment=1 default charset=utf8;

insert into tbl_user(name,age,email)values('1aa1',21,'b@163.com');
insert into tbl_user(name,age,email)values('2aa2',222,'a@163.com');
insert into tbl_user(name,age,email)values('3aa3',265,'c@163.com');
insert into tbl_user(name,age,email)values('4aa4',21,'d@163.com');




#insert into tbl_user(name,age,email)values('aa',121,'e@163.com');



#=====================================================================

drop table if exists `test03`;

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

select * from test03;


#=====================================================================
drop table if exists tblA;

create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);

insert into tblA(age,birth)values(22,now());
insert into tblA(age,birth)values(23,now());
insert into tblA(age,birth)values(24,now());

create index idx_A_ageBirth on tblA(age,birth);

select * from tblA;


#=====================================================================
#此处教程未新建库，但是笔者决定新建一个
drop if exists database bigData;
create database bigData;
use bigData;

drop table if exists  dept;

create table dept(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default "",
loc varchar(13) not null default ""
)engine=innodb default charset=gbk;

create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,/*编号*/
ename varchar(20) not null default "",/*名字*/
job varchar(9) not null default "",/*工作*/
mgr mediumint unsigned not null default 0,/*上级编号*/
hiredate date not null,/*入职时间*/
sal decimal(7,2) not null,/*薪水*/
comm decimal(7,2) not null,/*红利*/
deptno mediumint unsigned not null default 0/*部门编号*/
)engine=innodb default charset=gbk;

#=====================================================================
#创建数据插入函数
#$$ 更改结束符为$，当然不用$用别的也可以
#用于随机产生字符串
delimiter  $$
create function rand_string(n int) returns varchar(255)
begin 
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default ' ';
 declare i int default 0;
 while i < n do
 set return_str =concat(return_str,substring(chars_str,floor(1 + rand()*52),1));
 set i=i+1;
 end while;
 return return_str;
end  $$

#用于随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
 declare i int default 0;
 set i=floor(100+rand()*10);
 return i;
end $$

#创建存储过程
#插入员工表
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 set autocommit=0;#将其设为0，防止将insert语句自动提交
 repeat
 set i=i+1;
 insert into emp (empno , ename , job , mgr , hiredate , sal , comm , deptno )values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
 until i=max_num
 end repeat;
 commit;
 end $$

#插入部门表

delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 set autocommit=0;#将其设为0，防止将insert语句自动提交
 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 $$

delimiter ; #将结束符改回；

#=====================================================================
use db0629;
drop table if exists mylock;
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine=myisam;

insert into mylock(name)values('a');
insert into mylock(name)values('b');
insert into mylock(name)values('c');
insert into mylock(name)values('d');
insert into mylock(name)values('e');

select * from mylock;
unlock tables;
#=====================================================================
drop  table if exists  test_innodb_lock;

create table test_innodb_lock (
a int(11),
b varchar(16)
)engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');	
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');

create index test_innodb_a_ind on test_innodb_lock(a);

create index test_innodb_lock_b_ind on test_innodb_lock(b);	

select * from test_innodb_lock;