MySQL体系结构、优化与锁

一 MySQL体系结构

1.1 体系结构图

image-20201224184601732.png

1.2 Server的各组成部分

• Connection Pool：连接池组件
• Management Services & Utilities：管理服务和工具组件
• SQL Interface：SQL接口组件
• Parser：查询分析器组件
• Optimizer：优化器组件
• Caches & Buffers：缓冲池组件
• Pluggable Storage Engines：存储引擎
• File System：文件系统

二 存储引擎

2.1 各种存储引擎的特性

image-20201224185826128.png

2.2 查看存储引擎的相关命令

# 查看支持的存储引擎
show engines;
# 查看mysql默认的存储引擎
show variables like '%storage_engine%';

2.3 InnoDB

• 支持事务控制
• 支持外键约束
• 支持行锁
• 存储方式

.frm文件：保存表结构
.ibd文件：保存索引和数据

2.4 MyISAM

• 不支持事务
• 存储方式

.frm文件：保存表结构
.MYD文件：存储数据
.MYI文件：存储索引

2.5 存储引擎选择建议

• InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作， 那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统， InnoDB是最合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。 MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善 MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

三 数据库的优化措施

3.0 配置测试环境的库表

CREATE DATABASE demo01;
USE demo01;

create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('alibaba','阿里巴巴','阿里小
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('baidu','百度科技有限公司','百度小
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('huawei','华为科技有限公司','华为小
店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播
客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('itheima','黑马程序员','黑马程序
员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('luoji','罗技科技有限公司','罗技小
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('qiandu','千度科技','千度小
店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

3.1 分析SQL的工具

1. 查看SQL执行频率

# 查看每个xxx语句执行的次数
show status like 'Com_______'; # 例如：Com_select 执行select的操作次数
# 查看InnoDB存储引擎的相关操作执行次数
show status like 'Innodb_rows_%'; # 例如：Inoodb_rows_read 执行select返回的行数

2. 定位低效SQL

# 查看当前MySQL在进行的线程
show processlist;

3. explain分析执行计划

explain select * from tb_seller where id = 1;

image-20201224191715049.png

4. profiling分析语句执行时间

# 查看profiling的开启情况
select @@profiling;
# 开启
set profiling=1;

# 执行一些操作
show databases;
show tables;
select count(*) from tb_seller;

# 查看sql执行耗时
show profiles;

3.2 索引

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

1. 全值匹配，对索引所有列指定具体值

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

image-20201224205659065.png

2. 最左前缀法则——查询从索引的最左前列开始，并且不跳过索引中的列

# 跳过了中间的一个索引列，导致只使用了name的索引
EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' AND  address='北京市';

image-20201224210053884.png

# 缺失最左侧索引，整个sql不走索引
EXPLAIN SELECT * FROM tb_seller WHERE  STATUS='1' AND address='北京市';

image-20201224210153068.png

3. 范围查询右边的列，不走索引

# address没走索引
EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' AND STATUS>'1' AND address='北京市';

image-20201224210351598.png

4. 不要在索引列上进行运算操作， 索引将失效

# 在name索引列做运算操作，索引失效
EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(NAME,3,2)='科技';

image-20201224210551035.png

5. 字符串不加单引号，造成索引失效

# status是字符串类型，但没加单引号
EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' AND STATUS=1;

image-20201224210731488.png

6. 尽量使用覆盖索引，避免select *

# 只查询有索引的列效率更高，会在extra中显示使用索引，若多加password没有索引的这个字段则效率降低
EXPLAIN SELECT NAME,STATUS,address FROM tb_seller WHERE NAME='小米科技' AND STATUS='1' AND address='北京市';

image-20201224211017450.png

7. 用or分割开的条件，不走索引

# 用OR连接两个索引条件，也不会走索引
EXPLAIN SELECT * FROM tb_seller WHERE NAME='黑马程序员' OR STATUS = '1';

image-20201224211459020.png

8. 如果MySQL评估使用索引比全表更慢，则不使用索引

CREATE INDEX idx_seller_addr ON tb_seller(address);
EXPLAIN SELECT * FROM tb_seller WHERE address='西安市'; # 走索引（数据稀缺）
EXPLAIN SELECT * FROM tb_seller WHERE address='北京市'; # 不走索引（数据大量存在）

9. 单列索引和复合索引——尽量使用复合索引，而少使用单列索引

# 创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 ：
name
name + status
name + status + address

# 创建单列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。而复合索引可能会使用到多个字段的索引故效率更高。

3.3 SQL优化

1. 大批量插入数据

• 主键顺序插入——尽量保证待插入的数据按主键顺序存放
• 关闭唯一性校验

set unique_check=0;
# 插入数据
set unique_check=1;

• 手动提交事务

set autocommit=0;
# 插入数据
set autocommit=1;

2. 优化insert语句

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户 端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

# 坏
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
# 优
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

• 在事务中进行数据插入

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

• 主键顺序插入

3. 优化order by语句

• 通过查找存在索引的字段，使得extra使用 using index，不需要额外排序，操作效率高。

EXPLAIN SELECT * FROM tb_seller ORDER BY NAME DESC; # using filesort 效率低
EXPLAIN SELECT NAME FROM tb_seller ORDER BY NAME DESC; # using index 效率高

• Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是 降序。否则肯定需要额外的操作，这样就会出现FileSort。

4. 优化group by语句

# 默认group by 之后会进行 order by的操作 故性能较低
EXPLAIN SELECT COUNT(*) FROM tb_seller GROUP BY address;

image-20201224223025841.png

# 去除order by的性能影响
EXPLAIN SELECT COUNT(*) FROM tb_seller GROUP BY address ORDER BY NULL;

image-20201224223109126.png

# 创建索引后，效率提升 变为 using index
CREATE INDEX idx_seller_addr ON tb_seller(address);
EXPLAIN SELECT COUNT(*) FROM tb_seller GROUP BY address ORDER BY NULL;

image-20201224223338319.png

5. 优化嵌套查询——连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作
6. 优化分页查询—— 一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ， 此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

• 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
• 用于主键自增的表，可以把Limit 查询转换成某个位置的查询

3.4 查询缓存优化

1. 请求流程

1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端。

2. 查询缓存的配置

# 若未开启或者缓存大小为0，则需要在my.cnf中配置相关参数，并重启mysql服务
# 查看当前的MySQL数据库是否支持查询缓存
SHOW VARIABLES LIKE 'have_query_cache';
# 查看当前MySQL是否开启了查询缓存
SHOW VARIABLES LIKE 'query_cache_type';
# 查看查询缓存的占用大小
SHOW VARIABLES LIKE 'query_cache_size';
# 查看查询缓存的状态变量：
SHOW STATUS LIKE 'Qcache%';

3. 查询缓存失效的情况

• SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须一致

SQL1 : select count(*) from tb_seller;
SQL2 : Select count(*) from tb_seller;

• 当查询语句中有一些不确定的时，则不会缓存

SELECT user();
SELECT database();

• 不使用任何表的查询语句，不会缓存

select 'A';

• 查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存

 select * from information_schema.engines;

• 在存储的函数，触发器或事件的主体内执行的查询，不会走缓存
• 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除

3.5 内存管理与优化

1. 内存优化原则

• 将尽可能多的内存分配给MySQl做缓存，但要为操作系统等其他程序预留足够的内存空间
• MyISAM存储引擎的数据读取依赖于操作系统本身的IO缓存，故使用MyISAM表时，要预留更多的内存给操作系统做IO缓存
• 排序区、连接区等缓存是分配给每个数据库会话专用。需要合理配置，否则会在并发连接较高的时候导致内存耗尽

四 应用层面优化

4.1 使用连接池

建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，需要建立数据库连接池，以提高访问的性能。

4.2 减少对MySQL的访问

1. 业务层上减少对数据进行重复检索

编写业务时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。

2. 增加cache层

可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式， 只要能达到降低数据库的负担又能满足应用需求就可以。可用选择使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据。

4.3 负载均衡

1. 利用MySQL复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

2. 采用分布式数据库架构

分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

五 MySQL锁机制

5.1 并发参数调整

1.max_connections：控制连接到MySQL数据库的最大数量，默认为151
2.back_log：控制积压的请求栈大小，若连接数达到max_connections则新请求会被存入栈中。若等待的连接数量超过back_log则不被授予连接。默认50+(max_connections/5) = 80
3.table_open_cache：控制所有SQL语句执行线程可以打开表缓存的数量，需要根据最大连接数和涉及到的表的最大数量来设定 max_connections*N
4.thread_cache_size：控制缓存客户服务线程的数量
5.innodb_lock_wait_timeout：设置Innodb事务等待行锁的时间，默认为50ms

5.2 锁分类

从对数据操作的颗粒度分：

• 表锁：锁定整个表（MyISAM，Innodb）。开销小，加锁快；不会死锁；锁的粒度大，发生锁冲突的概率高，并发低。
• 行锁：锁定当前操作行（Innodb）。开销大，加锁慢；会死锁；锁的粒度小，发生所冲突的概率低，并发高。

从对数据操作的类型分：

• 读锁（共享锁）：对同一份数据，多个读操作互不影响
• 写锁（排他锁）：当前操作结束前，其他读锁和写锁阻塞

5.3 MyISAM表锁

1. 环境准备

CREATE TABLE `tb_book` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
`publish_time` DATE DEFAULT NULL,
`status` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');
CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

2. 读锁案例

客户端1：

# 获取tb_book 表的读锁
lock table tb_book read;
# 执行查询操作
select * from tb_book; # 正常执行

客户端2：

# 执行查询操作
select * from tb_book; # 正常执行

客户端1：

# 查询未锁定的表
select * from tb_user; # 无法操作

ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

客户端2：

# 查询未锁定的表
select * from tb_user; # 正常执行

客户端1：

# 执行插入操作
insert into tb_book values(null,'mysql','2020-12-28','1');

ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated

客户端2：

# 执行插入操作
insert into tb_book values(null,'mysql','2020-12-28','1'); # 阻塞，等待客户端1释放读锁

客户端1：

# 释放读锁
unlock tables; # 客户端1释放读锁后，客户端2成功插入数据

3. 写锁案例

客户端1：

# 获取tb_book表的写锁
lock table tb_book write;
# 执行查询操作
select * from tb_book; # 正常执行
# 执行更新操作
update tb_book set name='java' where id=1; # 正常执行

客户端2：

# 执行查询操作
select * from tb_book; # 阻塞

客户端1：

# 释放写锁
unlock tables; # 客户端1释放写锁后，客户端2成功查询数据

总结：读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写

5.4 InnoDB行锁

1. 环境准备

create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

2. 行锁案例

• 有索引的情况

客户端1，2：

# 关闭自动提交
set autocommit=0;
# 查询数据
select * from test_innodb_lock; # 正常查询全部数据
# 查询id=3的数据
select * from test_inoodb_lock where id=3; # 正常执行
# 更新id=3的数据，相同行的数据
update test_innodb_lock set name='test' where id=3; # 客户端1执行成功，客户端2阻塞
# 客户端1提交事务
commit; # 客户端2的更新也执行成功

# 若更新不同行，由于是行锁不会发生阻塞现象

• 无索引或索引失效的情况

客户端1，2：

# 删除索引
drop index idx_test_innodb_lock_name on test_innodb_lock;
# 查看是否删除成功
show index from test_innodb_lock\G
# 关闭自动提交
set autocommit=0;
# 更新name=400的数据
# 客户端1
update test_innodb_lock set sex='2' where name='400'; # 由于已经删除name的索引，降为表锁
# 客户端2
update test_innodb_lock set sex='2' where id=9; # 操作其他行被阻塞，降级为表锁
# 客户端1提交事务
commit; # 客户端2的更新也执行成功

3. 间隙锁

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

六 MySQL常用工具及日志

6.1 mysql

mysql [options] [database]
参数 ：
-u, 指定用户名
-p, 指定密码
-h, 指定服务器IP或
-P, 指定连接端口
-e, 执行SQL语句并退出

# 示例
mysql -u root demo01 -e "select * from tb_book" -p

6.2 错误日志

它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息

# 查看日志位置
show variables like 'log_error%';
# 查看日志内容
tail -f 位置

6.3 二进制日志

记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过其实现的。

1. 开启二进制日志并重新启动mysql服务

#配置开启binlog日志
server-id=1
log_bin=mysqlbin
log-bin-index=mysqlbin.index

2. 插入一条数据

insert into tb_book values(null,'Lucene','2088-05-01','0');

3. 进入日志存储的目录，并查看日志文件

cd /var/lib/mysql
mysqlbinlog --no-defaults  mysqlbin

4. 删除日志

Reset Master