【无标题】

一位不愿透露姓名的靓仔

已于 2023-03-20 22:01:42 修改

阅读量157

点赞数

分类专栏： mysql 文章标签： mysql Powered by 金山文档

于 2023-03-12 14:17:25 首次发布

本文链接：https://blog.csdn.net/qq_35744081/article/details/129475383

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章介绍了MySQL的三层架构，包括客户端层、服务层和存储引擎层，重点讨论了存储引擎如InnoDB的工作原理，如聚集索引和主键的重要性。此外，文章还涉及了查询优化，如索引、缓存、查询过程以及如何创建高效的存储过程。同时，提到了并发控制中的锁机制，以及数据库的优化策略，如读写分离、分库分表和使用缓存。最后，强调了SQL优化和业务优化在数据库性能提升中的重要性。

摘要由CSDN通过智能技术生成

mysql逻辑架构图

mysql整体服务分为三层，最上层为客户端层，并非mysql所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理

第二层包含了mysql的核心服务，mysql大多数核心服务均在中间这层，包括查询解析、分析、优化、缓存、内置函数（比如时间、数学、加密等函数）。所有的跨存储引擎的功能也在这一层实现：例如存储过程、触发器、视图等

最下层包含了存储引擎，存储引擎用于数据的存储和读取，和linux下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过API来访问存储引擎，这些API接口屏蔽了不同的存储引擎之间的差异

mysql查询过程

对于select语句，在解析查询之前，服务器会先查询缓存，如果能在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行整个过程，而是直接返回查询缓存中的结果集

mysql文件系统

cpu，内存，硬盘之间交换文件的最小单位叫页，一个页大小为4k（不同的操作系统可能不同），一次IO，取的数据必须是页的整数倍

mysql叶文件默认为16k

myinsam数据引擎

.frm：表结构文件，存储了一张表的表结构定义

.myd：表数据文件，存储了表里所有的数据

.myi：表索引文件，内部是一个B+树

通过索引可以快速查询，其原理就是先通过

.myi文件中的B+树快速查找到某一条记录，

根据提供的文件地址，直接从.myd文件中找出该条数据

（叶子结点上存放了一个值，该值就是这条记录在磁盘上存储的文件地址）

Innodb索引实现（聚集索引）

表数据文件本身就是按B+组织的一个索引结构文件

聚集索引：叶子结点包含了完整的数据记录

.frm文件：存储了表结构

.ibd文件：索引+数据

ibd文件本身就是依据B+数进行存储的一个文件，根据B+树找到记录后，可以直接找到数据，不需要访问另一文件

Mysql数据表默认使用了Innodb存储引擎

表数据文件本身就是按B+Tree组织的一个索引结构文件

使用了聚集索引，叶子结点包含了完整的数据记录

Innodb表必须要有主键，并且推荐使用整型的自增主键

因为表使用的是B+Tree结构，这个结构决定了它必须要有一个索引，而且要一个主键

如果创建数据表时没有指定主键，innodb默认会寻找一个唯一列作为主键（如果找不到它会自己额外加一列作为主键）

文件系统中最小的单位是块，一个块大小为4kb

这里创建了一个文件，虽然实际大小只有1kb，但是该文件在磁盘中占用了4kb的空间

索引

在mysql中，有以下两种方式访问数据表中的数据：

顺序访问：进行全表扫描，返回与之匹配的数据项

索引访问：在列上建立索引，根据索引找到记录的位置

目前大部分数据库及文件系统都使用的B-Tree作为索引结构

普通索引

唯一的作用是加快数据访问，没有任何限制，使用index或者key关键字

-- 创建索引，绑定的是name字段，长度为250

create index phone_index on phone(name(250));

-- 查看当前表下所有的索引

show index from phone;

-- 删除索引

drop index index_name on phone;

-- 添加索引，效果和前面的创建索引一样 ,不过没有指定长度

alter table phone add index index_name(name);

-- 在创建表时添加索引

create table stu( id int(255),

stu_name varchar(20),

age smallint unsigned,

index(id)

)

唯一性索引（unique index）

不允许具有相同索引值的索引（该列不能重复）

主键索引（primary key）

一个表只能有一个主键，在创建主键时自动添加主键索引

组合索引（普通索引上绑定多个字段）

一个索引对应多个列，根据最左原则进行查询（即左边的优先级高）

全文索引（fulltext index）

用于查找文本中的关键字，类似于搜索引擎

-- 创建唯一索引

-- 即使没有添加唯一约束的列也可以使用唯一索引，添加以后该列也不允许有重复值

create unique index index_id on stu(id);

-- 创建组合索引

alter table stu add index name_country_city(stu_name,country,city);

-- 全文索引

alter table stu add fulltext index text_index(stu_name);

create fulltext index text_index on stu(stu_name)

一条mysql查询语句的执行过程

客户端的数据库驱动与mysql建立连接，连接完成以后向mysql发送一条查询请求

服务器首先查询缓存，如果命中缓存，则立即返回存储在缓存中的结果，否则执行下一阶段

服务器在sql解析、预处理、再由优化器生成对应的执行计划

mysql根据执行计划，调用存储引擎API来执行查询

将结果返回给客户端，同时缓存查询结果

Mysql架构的Service层

缓存

MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的：

先说下缓存中数据存储格式：key（sql语句）- value（数据值），所以如果SQL语句（key）只要存在一点不同之处就会直接进行数据库查询了；

由于表中的数据不是一成不变的，大多数是经常变化的，而当数据库中的数据变化了，那么相应的与此表相关的缓存数据就需要移除掉；

解析器/分析器：

分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等

数据库三大范式

第一范式：

数据的不可分割性（原子性），字段只能是一个值，不能被拆分成多个字段，否则的话它就是可以分割的，不满足第一范式

例如以下表，歌曲编号为3的这条记录中，歌手和经纪公司都有两个值，不符合数据表的原子性

这里通过拆列，将原本的一条记录拆分成两条，满足了第一范式，现在每一个字段都不可再分割

第二范式：

表必须有一个主键

没有包含在主键中的列，必须完全依赖于主键，而不是只依赖于主键的一部分

假设这里有一张订单表（OderDetail），其中有以下字段：

OrderId（订单编号）、ProductId（产品编号），ProductName产品名，

DisCount（折扣）、Price（单价）、Quantity（数量）、Total（总价）

因为我们知道在一个订单中可以订购多种产品（比如表中1号订单就购买了两种商品），所以单单一个OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）

以上表，显而易见，只有DisCount和Quantity完全依赖于主键（OrderId，ProductId）

产品名和单价只与产品id相关（ProductName、Price，只依赖于ProductId）

所以这张表并不满足第二范式，不符合第二范式的表容易产生数据冗余

（假如现在要将iphone14的价格改为5000元，那么这张表里所有ProductId=10的记录都会被修改，系统开销就会非常大）

所以要将以上数据表进行拆分

订单表	产品表（Product）

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

案例2：

假设这里有一张雇员表emp，里面的数据如下

这里会出现一个问题，如果需要修改数据表，假设新入职了一名员工马云，他将替代张三成为销售部的领导，这样的话雇员表就会需要修改四条数据（所有部门领导为张三的记录都会更改为马云），这样明显是不合理的，所以这张表并不满足第二范式

如果想要满足第二范式的话需要将数据表进行拆分，拆分后如下：

雇员表（emp）	部门表（dept）

这时，如果马云再想入职，只需要在雇员表中新增一条记录，然后在部门表中将部门编号为1的这条记录的部门领导改为马云即可

第三范式

任何非主属性不依赖于其它非主属性（比如直接依赖主属性，目的是在第二范式的基础上消除值传递）

考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。

其中OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

Order	Customer

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

mysql简单查询语句

select语句进行查询

-- 使用 * 代表查询全部内容，from，表名代表从哪一张表中查询数据

select * from 表名

-- 查询单个列

select name from user;

-- 查询多个列（查询指定字段）

select name , age from user;

-- 使用distinct去重

select distinct address from company;

-- 使用as设置别名

select name , address as "地址" from company;

-- 使用limit限制查询结果的记录条数

select * from company limit 4; -- 查询前四行数据

select * from company limit 6,3; -- 从第6行开始，显示3条数据

order by 关键字进行排序，默认是升序，如果要降序的话，在后面加上desc

select * from phone order by price desc;

查询所有手机信息，按照价格降序排列

按多个列排序，只需要在后面继续添加排序条件，之间用逗号隔开即可

select * from phone order by size desc,price

先按照尺寸降序排列，然后按照价格升序排列

条件查询

使用 where 进行单一条件查询

select * from company where address ="深圳";

使用between and 查询某个区间的值

select * from phone where price between 2000 and 4000;

使用is null进行空值检查

select * from phone where price is null;

不匹配查询

select * from phone where brand <>"小米"

查询除了小米以外所有的手机，也可以使用!=

使用 and 进行多条件查询

select * from company where uid = 2 and address = "成都";

使用 or 进行判断查询

select * from phone where price <4000 or size <6.0

使用 like 进行模糊查询

select * from phone where name like "%米%"

查询所有名字包含”米”的手机

其中，% 表示匹配一个或多个字符，_ 表示匹配一个字符

使用in操作符来判断在某一个指定范围内

select * from phone where brand in("苹果","小米","redmi");

-- 查询所有手机品牌为苹果、小米、redmi的手机

not in

select * from phone where brand not in("苹果","小米","redmi");

分组查询

根据某个字段对数据表进行分组，将这些字段相同的数据归类在一起，并通过聚合函数来对数据进行处理

select size ,group_concat(name) as"型号",max(price),min(price),avg(price)

from phone group by size;

单表分组查询，emp雇员表，查询每个部门的平均工资以及部门人数

-- 查询每个部门的平均工资,以及部门人数

select

deptno as "部门编号",COUNT(*) as "部门人数",AVG(sal) as "平均工资"

FROM EMP

GROUP BY EMP.deptno

子查询

子查询是指将一个查询语句嵌套在另一个查询语句中。子查询可以在select、update和delete语句中使用，也可以进行多层嵌套，在实际开发中，子查询经常出现在where子句中

多表连接查询

组合查询

事务

存储过程

MySQL 存储过程是一组一起完成特定任务的预先编译的 SQL 语句集。MySQL 支持存储过程，允许用户将 SQL 语句捆绑成封装的程序单元，使得在执行这些 SQL 语句之前可以根据不同的参数传递实现动态地更改逻辑。

创建存储过程：需要使用create procedure 语句：

CREATE PROCEDURE procedure_name(arguments)

BEGIN

-- 存储过程执行体

END;

其中，procedure_name 为创建的存储过程的名称，arguments 是可选的参数列表，BEGIN 和 END 之间的是存储过程的执行体，是存储过程实际执行的业务逻辑代码。

例子：

CREATE PROCEDURE get_user(IN id INT)

BEGIN

SELECT * FROM users WHERE id = id;

END;

此存储过程定义了一个名为get_user 的存储过程，它接受一个名为 id 的参数，然后使用 SELECT 语句从 users 表中检索具有指定 ID 的用户信息。

调用存储过程：

调用存储过程，您需要使用CALL 语句，如下所示：

CALL procedure_name(arguments);

例子：

CALL get_user(1);

这里我们调用的是get_user 存储过程，并传入 id 参数的值为 1，执行后会将 id 为 1 的用户信息输出。

存储过程的主要优点：

提高性能

存储过程可以避免网络传输过程中出现的延迟，并且可以减少多次执行相同的SQL 查询所带来的开销。存储过程还可以缓存查询计划，使其下次执行更快。此外，存储过程还可以利用索引、视图和其他各种数据库技术，提高数据库的性能。

提高可维护性

存储过程将数据库的逻辑与应用程序分离，使得更改数据库逻辑不会影响到应用程序。存储过程还可以将复杂的SQL 逻辑封装在一个简单易懂的语句中，使得数据库开发更加容易，也方便了维护人员的维护工作。

提高安全性

存储过程还可以提高数据库的安全性，通过存储过程可以对敏感信息进行访问控制，从而保证数据的安全。存储过程还提供了代码审查的机会，可以防止SQL 注入等攻击。

实现复杂业务逻辑的封装

存储过程可以封装复杂的业务逻辑，例如事务处理、错误处理等等。这些都可以使数据库开发更加容易，也方便了维护人员的维护工作。

解析：

存储过程能够避免网络传输过程中出现的延迟，是因为存储过程是一段预先定义好的 SQL 代码块，它是在服务器上执行的，而不是在客户端上执行的。因此，当应用程序需要执行存储过程时，只需要将存储过程的参数传递给服务器，服务器就可以利用 CPU 等计算资源来执行存储过程，而不需要在每次执行该 SQL 语句时从客户端向服务器发送 SQL 查询和结果数据。

与客户端和服务器之间的每次请求和响应相比，存储过程只需要一次请求和响应过程，因此存储过程可以避免网络传输过程中出现的延迟，从而提高数据库应用程序的性能和效率。

此外，存储过程还可以缓存查询计划，使其下次执行更快。存储过程会将查询计划保留在内存中，当存储过程再次执行时，可以直接使用缓存中的查询计划，而不需要重新编译和优化查询计划，从而提高查询的执行效率。

存储过程的缺点：

开发需要花费更多的时间；

需要更高的技术水平；

可能会影响程序的可移植性。

尽管存储过程是提高数据库应用程序性能和安全性的重要手段，但也有以下一些缺点：

存储过程的开发和维护难度较大。

由于存储过程是一种复杂的数据库对象，需要深入了解SQL 语言的开发人员才能有效开发和维护存储过程。此外，存储过程的调试和测试需要专门的工具和技术，增加了开发和维护的难度和成本。

存储过程可能会降低数据库的可移植性和互操作性。

不同的数据库管理系统（DBMS）实现存储过程的方式不同，这可能导致存储过程不可移植和在不同的 DBMS 之间不兼容。此外，不同的 DBMS 对存储过程的支持程度不同，可能会导致存储过程在某些 DBMS 中无法正确执行。

存储过程可能会占用过多的资源。

存储过程需要CPU、内存和磁盘等资源来执行，如果存储过程执行频繁或查询很大，可能会占用过多的资源，导致数据库性能下降。

存储过程可能会使应用程序逻辑复杂化。

将业务逻辑和SQL 代码耦合到存储过程中可能会使应用程序逻辑复杂化，增加维护和调试的难度。

存储过程可能会导致安全问题。

存储过程可能会成为黑客攻击的一个目标，如果存储过程的权限设置不正确，则可能会导致安全问题。

总之，存储过程是一种尽管有诸多优点，但也存在一些缺点的技术。在使用存储过程时，应该考虑其优缺点，权衡其利弊，决定如何使用存储过程以提高数据库应用程序的性能和安全性。

除了上述提到的存储过程的缺点，还有以下一些：

存储过程可能会导致脏数据和死锁。

存储过程使用了大量的事务和锁，一个不良编写的存储过程可能会导致脏数据和死锁等问题。事务和锁的管理是非常复杂和难以调试的，需要深入了解数据库的开发人员才能写出高质量的存储过程。

存储过程需要进行版本控制和文档管理。

存储过程是一种软件源代码，需要进行版本控制和文档管理。存储过程的版本控制可以确保数据库的存储过程在版本升级时能够正确升级，避免出现数据丢失的问题。文档管理可以确保存储过程的设计和实现过程得到记录，并为存储过程的维护和更新提供支持。

存储过程可能会使数据库的生命周期变得复杂。

将业务逻辑和SQL 代码耦合到存储过程中可能会使数据库的生命周期变得复杂。由于存储过程包含业务逻辑和 SQL 代码，同一个存储过程可能会在不同的场景下执行不同的操作，可能会导致业务过程变得复杂。此外，存储过程的维护和更新需要深入了解业务逻辑和 SQL 语言的开发人员，增加了数据库的开发和维护成本。

总之，存储过程作为数据库应用程序中重要的组成部分，除了上述缺点，还包含其他缺点，如脏数据和死锁、版本控制和文档管理等。为了避免这些缺点，开发人员需要深入了解数据库和SQL 语言，写出合适的存储过程，并对存储过程进行版本控制和文档管理。

如何创建高效的存储过程

以下是如何创建高效的存储过程的几个建议：

消除性能瓶颈

在创建存储过程之前，需要先评估数据库的性能瓶颈，并确保使用存储过程可以有效解决这些瓶颈。例如，在多次执行相同的 SQL 查询时，使用存储过程可以避免重复编译每个查询，从而提高查询的执行效率。

最小化查询次数

存储过程应该尽可能少地执行查询，这可以通过使用 JOIN 和子查询等技术来实现。尽可能地将查询合并到一个存储过程中，可以减少数据访问次数，提高存储过程的执行效率。

注意存储过程的参数传递

存储过程的参数传递应该尽可能地简单和高效。应该通过参数和变量来传递数据，而不是将数据存储到临时表中。此外，应该注意避免在存储过程中使用全局变量，因为全局变量可能会导致存储过程的执行效率降低。

缓存查询计划

存储过程应该尽可能地缓存查询计划，以避免每次执行存储过程时重新编译查询计划所带来的开销。为了缓存查询计划，可以使用 WITH RECOMPILE 来动态编译存储过程，也可以使用 sp_recompile 来标记存储过程需要重新编译。

使用分区表

分区表可以将数据分散到多个物理分区中，从而将单个表的查询性能分散到多个物理分区中。如果存储过程使用分区表，则可以通过跨分区查询来处理大量数据，以提高存储过程的执行效率。

定期清理无用存储过程

定期清理无用存储过程可以帮助减少数据库的复杂性，提高数据库的性能。无用的存储过程可能会占用过多的资源，并干扰数据库的其他操作。为了避免这种情况发生，需要定期检查和删除无用的存储过程。

总之，创建高效的存储过程需要注意避免性能瓶颈，在查询次数和参数传递上进行优化，缓存查询计划，使用分区表等技术来提高数据库的性能。同时，需要定期清理无用的存储过程，以避免干扰数据库的其他

锁

无论何时，只要有多个查询在同一时刻修改数据，都会产生并发控制问题，mysql在两个层面处理并发控制，服务器层与存储引擎层

在存储引擎层，通常使用锁来解决并发问题

什么是锁？

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

锁用来解决什么问题

锁是用来解决并发事务的访问问题，我们已经知道事务并发执行时可能带来的各种问题，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下。

一个事务进行读取操作，另一个进行改动操作,我们前边说过，这种情况下可能发生脏读、不可重复读、幻读的问题。

锁的分类

MySQL大致可归纳为以下3种锁：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

根据存储引擎的不同，支持的锁类型也会不同

InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）

BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁

也会不同

表级锁

它会锁定整张表，一个用户在对表进行写操作时，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁的时候，其他用户才能获取读锁，读锁之间是不相互阻塞的

索引

mysql优化策略

不能使用null字段

不能使用select * + limit 语句

为字段选择合适的数据类型

合理创建索引

连接配置优化

处理连接是MySQL客户端和MySQL服务端亲热的第一步，第一步都迈不好，也就别谈后来的故事了。

既然连接是双方的事情，我们自然从服务端和客户端两个方面来进行优化喽。

服务端配置

服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过error 1040: Too many connections的错误？就是服务端的胸怀不够宽广导致的，格局太小！

我们可以从两个方面解决连接数不够的问题：

增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个

使用命令：show variables like 'max_connections';

及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点

mysql> show variables like 'wait_timeout';

MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。

客户端优化

客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。

解决的方案就是使用连接池来复用连接。

常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。

但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？

对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。

Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。

架构优化

使用缓存

系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。

如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。

缓存的使用

读写分离（集群、主从复制）

项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。

针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。

这就是集群

注：很多开发者不满master-slave这种具有侵犯性的词汇（因为他们认为会联想到种族歧视、黑人奴隶等），所以发起了一项更名运动。

受此影响MySQL也会逐渐停用master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。

使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢？

binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

主从复制

这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。

这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。

分库分表

分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。

大家都知道分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。

水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。

垂直分库

垂直分库以后

在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库啦。

垂直分表

垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查询速度。

垂直分表

拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情（一般都是点击详情按钮才会进行显示），而是会将商品更重要的信息（价格等）展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。

水平分表

把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是水平分表了。

水平分表以后

水平分库

水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。

水平分库以后

消息队列削峰

通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。

这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

队列削峰

优化器——SQL分析与优化

处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。

只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

慢查询

慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。

因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态

mysql> show variables like 'slow_query%';

slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。

除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';

打开慢日志

有两种打开慢日志的方式

修改配置文件my.cnf

此种修改方式系统重启后依然有效

# 是否开启慢查询日志

slow_query_log=ON

long_query_time=2

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

动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;

Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;

Query OK, 0 rows affected (0.00 sec)

慢日志分析

MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);

然后我们查询用时最多的1条慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log

Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost

SELECT sleep(N)

其中，

Count：表示这个SQL执行的次数

Time：表示执行的时间，括号中的是累积时间

Locks：表示锁定的时间，括号中的是累积时间

Rows：表示返回的记录数，括号中的是累积数

更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。

查看运行中的线程

我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。

其中，

Id：线程的唯一标志，可以使用Id杀死指定线程

User：启动这个线程的用户，普通账户只能查看自己的线程

Host：哪个ip和端口发起的连接

db：线程操作的数据库

Command：线程的命令

Time：操作持续时间，单位秒

State：线程的状态

Info：SQL语句的前100个字符

查看服务器运行状态

使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

-- 查看select的次数

mysql> SHOW GLOBAL STATUS LIKE 'com_select';

查看存储引擎运行信息

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。

例如：

SHOW ENGINE INNODB STATUS;

上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

EXPLAIN执行计划

通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？

MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。

EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。

这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。