MYSQL优化详解

最新推荐文章于 2022-12-10 15:04:36 发布

yezi66308

最新推荐文章于 2022-12-10 15:04:36 发布

阅读量212

点赞数

分类专栏：数据库文章标签： mysql 数据库数据库开发

本文链接：https://blog.csdn.net/yezi66308/article/details/118603487

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MSQL优化详解

1.索引
- 1.1索引简介
- 1.2索引的优势和劣势
- 1.3索引数据结构
- 1.4索引分类
- 1.5索引语法
- 1.6索引设计原则
2.视图
- 2.1创建视图的优势
- 2.2创建或修改视图
- 2.3查看视图
- 2.4删除视图
3.存储过程和函数
- 3.1存储过程和函数简介
- 3.2创建存储过程
- 3.3调用存储过程
- 3.4查看存储过程
- 3.5删除存储过程
- 3.6存储过程语法
- 3.7存储函数
4.触发器
- 4.1介绍
- 4.2创建触发器
- 4.3删除触发器
- 4.4查看触发器
5.mysql的体系结构
6.mysql的存储引擎
- 6.1存储引擎介绍
- 6.2存储引擎类别介绍
- - 6.2.1介绍
  - 6.2.2存储引擎特性
7.优化SQL步骤
- 7.1查看SQL执行频率
- 7.2定位低效SQL语句
- 7.3explain分析执行计划
- 7.4show profiles分析SQL
- 7.5trace分析优化器执行计划
8索引的使用
- 8.1验证索引提升查询效率
- 8.2索引的使用
- 8.3查看索引的使用情况
9SQL优化
- 9.1大批量插入数据
- 9.2优化insert语句
- 9.3优化order by语句
- 9.4优化group by语句
- 9.5优化嵌套查询
- 9.6优化OR条件
- 9.7优化分页查询
- 9.8使用SQL提示
10应用优化
- 10.1使用连接池
- 10.2减少对mysql的访问
- 10.3负载均衡
11mysql查询缓存优化
- 11.1查询缓存流程：
- 11.2查询缓存配置：
- 11.3开启查询缓存
- 11.4查询缓存select选项
12mysql内存管理及优化
- 12.1内存优化原则
- 12.2MyISAM内存优化
- 12.3InnoDB内存优化
13mysql并发参数调整
14mysql锁
- 14.1锁的分类
- 14.2mysql锁
- 14.3MyISAM表锁
- 14.4InnoDB行锁
15常用SQL技巧
- 15.1SQL执行顺序
- 15.2使用正则表达式
- 15.3mysql常用函数
16mysql常用工具
- 16.1mysql
- 16.2mysqladmin
- 16.3mysqlbinlog
- 16.4mysqldump
- 16.5mysqlimport/source
- 16.6mysqlshow
17mysql日志
- 17.1错误日志
- 17.2二进制日志
- 17.3查询日志
- 17.4慢查询日志
18mysql复制
- 18.1复制原理
- 18.2复制优势

1.索引

1.1索引简介

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

1.2索引的优势和劣势

优势
（1）提高数据检索的效率，降低数据库的IO成本
（2）通过索引列对数据进行排序，降低数据排序成本和CPU消耗
劣势
（1）实际上索引也是一张表，该表中保存了主键与索引字段，所以索引列也要占用磁盘空间
（2）MySQL更新表数据的时候，也需要更新索引列的数据结构。

1.3索引数据结构

索引	InnoDB引擎	MyISAN引擎	Memory引擎
B-TREE索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-TREE索引	不支持	支持	不支持
Full-text索引	5.6版本之后支持	支持	不支持

*在MySQL中，默认使用的是B+TREE树索引*

1.3.1B-TREE结构

B-TREE又叫做多路平衡搜索树，一颗M叉的B-TREE特性如下所示：

树中每个节点最多包含M个子节点
除根节点和叶子节点之外，每个节点至少有M/2向上取整个子节点
若根节点不是叶子节点，则至少有两个子节点
所有的叶子节点都在同一层
每个非叶子节点由N个key和N+1个指针组成，(M/2)-1向上取整<=N<=M-1，当N>4时，中间节点分裂到父节点，两边节点分裂。

1.3.2B+TREE结构

B+TREE为B-TREE的变种，二者区别为：

M叉B+TREE最多含有M个key，而B-TREE最多含有M-1个key
B+TREE的叶子节点保存所有的key信息，按照key的大小顺序排序
所有的非叶子节点都可以看做是key的索引部分
由于B+TREE只有叶子节点保存key信息，查询任何key必需走到叶子节点，所以B+TREE的查询效率更加稳定

1.3.3MySQL中的B+TREE结构

MySQL索引数据结构对经典的B+TREE进行了优化，在原有的B+TREE基础上，增加一个指向相邻接点的链表指针，就形成了带有顺序指针的B+TREE，可以进行区间索引查询
在这里插入图片描述

1.4索引分类

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

1.5索引语法

索引在创建表时，可以同时创建，也可以随时增加新的索引

准备环境：

create database demo_1 default charset=utf8mb4;

use demo_1;

create table 'city'(
'city_id' int(11) not null auto_increment,
'city_name' vachar(50) not null,
'country' int(11) not null,
primary key ('city_id')
)engine=InnoDB default charset=utf8;

create table 'country'(
'country_id' int(11) not null auto_increment,
'country_name' varchar(100) not null,
primary key('country_id')
)engine=InnoDB default charset=utf8;

insert into 'city' ('city_id','city_name','country') values(1,'西安',1);
insert into 'city' ('city_id','city_name','country') values(2,'NewYork',2);
insert into 'city' ('city_id','city_name','country') values(3,'北京',1);
insert into 'city' ('city_id','city_name','country') values(4,'上海',1);

insert into 'country'('country_id','country_name') values(1,'China');
insert into 'country'('country_id','country_name') values(2,'America');
insert into 'country'('country_id','country_name') values(3,'Japan');
insert into 'country'('country_id','country_name') values(4,'UK');

1.5.1创建索引

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称
[USING index_type]
ON table_name(表中需要添加索引的字段名)

1.5.2查询索引

show index from 表名;

1.5.3删除索引

drop index 索引名称 on 表名;

1.5.4ALTER命令

alter table 表名 索引类型 索引名称(表中需要添加索引的字段名)

1.6索引设计原则

对查询频率较高，且数据量较大的表创建索引
索引字段的选择应当从当前where子句中提取，如果where子句中的组合较多，那么应当挑选最常用，过滤效果最好的列进行组合
使用唯一索引，区分度越高，使用索引的效率越高
索引可以提高查询数据的效率，但是索引数量不是越多越好，索引越多，维护索引的代价也会增加
尽可能的使用短索引。索引是用磁盘来存储的，因此使用短索引可以提升I0的访问效率
使用最左前缀，N个列组合索引，相当于创建了N个索引。如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提高查询效率

2.视图

2.1创建视图的优势

视图是一种虚拟存在的表，在数据库中不是实际存在的表数据，是在使用视图的时候动态生成的。视图就是一条SELECT语句执行后返回的结果集。

视图优势：

简单：使用视图用户完全不用关心查询SQL语句的表结构、关联条件和筛选条件，对于用户来说已经是过滤好的符合条件的结果集所组成的一张新数据表。
安全：通过视图限制可以简单实现一张数据表的某个行和列不可被用户访问。
数据独立：一担视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，可以通过修改视图来解决，不会对用户造成影响。

2.2创建或修改视图

创建视图语法：

create [or replace] [algorithm={undefined | merge | temptable}]
view 视图名 [列名]
as 查询语句/或者查询出来的数据集
[with [cascaded | local] check option]

修改视图语法：

alter [algorithm={undefined | merge | temptable}]
view 视图名 [列名]
as 查询语句/或者查询出来的数据集
[with [cascaded | local] check option]

选项：

#决定了是否允许更新数据，是记录不在满足视图的条件
[with [cascaded | local] check option] 
#local：只要满足本视图的条件就可以更新
#cascaded：必须满足所有针对该视图的所有视图的条件才能更新，默认值

2.3查看视图

查看视图语法：

show tables;

查看创建视图时使用的SQL语句：

show create view 视图名;

2.4删除视图

drop view 视图名;

3.存储过程和函数

3.1存储过程和函数简介

存储过程和函数：是事先经过编译并存储在数据库中的一段SQL语句的集合。
优势：调用存储过程和函数可以减少数据在数据库和应用服务器之间的传输，可以提高数据处理效率。
区别：
存储过程：没有返回值
存储函数：有返回值

3.2创建存储过程

创建存储过程语法：

create procedure 存储过程名称([创建存储过程所需要的参数])
begin
	--多条SQL语句的集合
end;

注意：delimiter关键字用来声明SQL语句的结束符，告诉mysql解释器，该命令已经结束，mysql可以执行了，在创建存储过程时，可以利用该关键字将结束符修改为：“$”。创建完成存储过程之后，再将结束符修改为：“;”
例如：

delimiter $
delimiter ;

3.3调用存储过程

语法：

call 存储过程名称([创建存储过程所需要的参数]);

3.4查看存储过程

#查询指定数据库中所有的存储过程
select name from mysql.proc where db='数据库名称';

#查询存储过程的状态信息
show procedure status;

#查询某个存储过程的定义
show create procedure 存储过程名称;

3.5删除存储过程

语法：

drop procedure [if exists] 存储过程名称;

3.6存储过程语法

存储过程是可以编程的，意味着可以使用变量、表达式、控制结构、来完成比较复杂的功能。

3.6.1变量

declare
通过declare变量可以定义一个局部变量，该变量的作用范围只能在begin…end块中。

declare 变量名 变量类型 [default 5];

set
直接使用set，可以对变量进行赋值

set 变量名 = 变量值 [,变量名=变量值];

select…into
通过select…into 进行赋值

select 查询变量 into 赋值变量 from 表名;

3.6.2if条件判断

语法结构：

if 判断条件1 then 满足条件1执行的SQL语句
	[elseif 判断条件2 then 满足条件2执行的SQL语句]
	[else 条件1和2都不满足执行的SQL语句]
end if;

3.6.3传递参数

语法格式：

create procedure 存储过程名称([in/out/inout] 参数名 参数类型)
#in：该参数可以作为输入，需要调用方法时传入值，默认
#out：该参数可以作为输出，相当于方法的返回值
#inout：既可以作为输入参数也可以作为输出参数

调用：

#调用存储过程
call 存储过程名称(输入的参数名);
call 存储过程名称(输入的参数名，@输出的参数名);
#查询输出的参数值
select @输出的参数名;

小知识

@变量名：这种变量要在变量名前加上@符号，叫做用户会话变量，在当前用户会话中有效，代表整个会话过程都是有作用的，类似于全局变量。
@@变量名：在变量名前加上@@符号，叫做系统变量。

3.6.4case结构

语法：

#方式一：
case 判断条件
	when 条件1 then 满足条件1执行的SQL语句
	[when 条件2 then 满足条件2执行的SQL语句]
	[else 条件1和2都不满足执行的SQL语句]
end case;
#方式二：
case
	when 条件表达式1 then 如果条件表达式1为真则执行该SQL语句
	[when 条件表达式2 then 如果条件表达式2为真则执行该SQL语句]
	[else 若都为假则执行该SQL语句]
end case;

3.6.5while循环

语法结构：

while 循环条件(条件成立，执行do后面的语句) do 
	循环语句;
end while;

当满足条件的时候，执行循环语句，不满足则退出循环

3.6.6repeat循环

满足条件的时候退出循环

语法结构：

repeat
	循环语句;
 	entil 循环条件
 end repeat;

3.6.7loop循环

loop实现简单的循环，退出循环的条件需要使用其他语句定义，通常使用leave语句实现
语法结构:

[循环别名:]loop
	循环语句;
end loop [循环别名]

如果不在循环语句中增加退出循环的语句，那么loop语句可以用来实现死循环

3.6.8leave语句

用来标注流程构造中的退出，通常和begin…end或者循环一起使用，用于退出循环。

3.6.9游标/光标

光标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理(遍历)。光标的使用包括光标的生命、open、fetch和close。
声明光标：

declare 光标名称 cursor for 查询语句;

open光标：

open 光标名称;

fetch光标：

fecch 光标名称 into 变量名[,变量名];

close光标：

close 光标名称;

3.7存储函数

语法结构：

cerate function 函数名称([变量名称 变量类型，...])
returns type(返回值类型)
begin
	函数体;
end;

调用：

select 存储函数名称(变量名);

4.触发器

4.1介绍

触发器是与表有关的数据库对象，指的是在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
mysql目前的触发器支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD的使用
INSERT型触发器	NEW表示将要或者已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据

4.2创建触发器

语法结构：

create trigger 触发器名称
before/after insert/update/delete
on 表名
[for each row]#行级触发器
begin
	触发器执行语句;
end;

4.3删除触发器

语法结构：

drop trigger [数据库名称]触发器名称

如果没有指定数据库，默认为当前数据库

4.4查看触发器

语法结构：

show triggers;

可通过该语句查看触发器的状态、语法等信息

5.mysql的体系结构

在这里插入图片描述
Mysql Server由以下部分组成：

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

6.mysql的存储引擎

6.1存储引擎介绍

存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式。存储引擎是基于表的，而不是基于数据库的。
mysql提供了插件式的存储引擎架构，所以mysql存在多种存储引擎
可通过show engines，查询当前数据库支持的存储引擎。mysql支持的存储引擎包含：innoDB，MyISAM等。
创建表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，mysql5.5之前的默认存储引擎的MyISAM，5.5版本之后就改为InnoDB。

6.2存储引擎类别介绍

6.2.1介绍

特点	InnoDB	MyISAM
存储限制	64TB	有
事务安全	支持
锁机制	行锁	表锁
B树索引	支持	支持
hsah索引
全文索引	支持（5.6版本之后）	支持
数据索引	支持
索引缓存	支持	支持
数据可压缩		支持
空间使用	高	低
内存使用	高	低
批量插入速度	低	高
支持外键	支持

6.2.2存储引擎特性

InnoDB存储引擎：
InnoDB是mysql默认的存储引擎，InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，但是对比MyISAM的存储引擎，InnoDB写的处理效率更差一些，并且会占用更多的磁盘空间以保留数据和索引。
特点：

事务控制
外键约束
Mysql支持外键的存储引擎只有InnoDB
on delete restrict：删除主表数据时，如果有关联记录，不删除
on update cascade：更新主表时，如果子表有关联记录，更新子表记录
存储方式：
使用共享表空间存储，这种方式创建的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以使多个文件。
使用多表空间存储，这种方式创建的表结构仍在.frm文件中，但是在每个表的数据和索引单独保存在.ibd中
MyISAM存储引擎：
MyISAM存储引擎不支持事务，也不支持外键，但是访问速度较快。
存储方式：
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但是拓展名分别是：
.frm（存储表定义）
.MYD（存储数据）
.MYI(存储索引)

7.优化SQL步骤

7.1查看SQL执行频率

查看服务器状态信息：

show [session|global] status
#session:当前连接
#global：自数据库上次启动至今

show status like '	Com_______';
show status like 'Innodb_rows_%';

7.2定位低效SQL语句

可通过两种方式定位执行效率：

慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句，用log slow queries[=file_name]选择启动项时，mysql写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
show processlist：慢查询日志在查询结束以后才记录，所以使用慢查询日志并不能定位执行效率低的问题。可以使用show processlist命令查看当前mysql在进行的进程，包括线程的状态、是否锁表等，可以实时查看SQL的执行情况，同时对一些锁表操作进行优化。

7.3explain分析执行计划

定位效率低的SQL语句后，可以通过explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
查询SQL语句执行计划：

explain select * from 表名 where 限制条件;

7.3.1explain的id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种：

id相同，表示加载表的顺序是从上到下
id不同，表示id值越大，优先级越高，越先被执行。
id有相同的，也有不同的，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id值越大，优先级越高，越先被执行。

7.3.2explain的select_type

select_type	含义
SIMPLE	简单查询，不包含子查询或者union
PRIMARY	查询中包含任何复杂的子查询，最外层查询标记位该标识
SUBQUERY	在select或where列表中包含了子查询
DERIVED	在from列表中包含的子查询，被标记为DERIVED mysql会递归执行这些子查询，把结果放在临时表中
UNION	若第二个select出现在union之后，则标记为union，若union包含在from子句的子查询中，外层select将被标记为：DERIVED
UNION RESULT	从union表中获取结果的select

表格从上到下查询效率越来越低。

7.3.3explain的table

表示这一行数据是关于那个表的

7.3.4explain的type

type显示的是访问类型，是较为重要的一个指标

type	含义
NULL	mysql不访问任何表和索引，直接返回结果
system	表中只有一行记录
const	表示通过一次索引就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条
ref	非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行
range	只检索给定返回的行，使用一个索引来选择行。where之后出现between，<,>,in等操作
index	index与all之间的区别为，index类型只是便利了所引树，通常比all快，all是遍历数据文件
all	遍历全部表，找到匹配的行

7.3.5explain的key

possible_keys：显示可能应用这张表的索引，一个或者多个
key：实际使用的索引，如果为null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的前提下，长度越短越好。

7.3.6explain的rows

扫描行的数量

7.3.7explain的extra

其他额外的执行计划信息，在该列展示。

extra	含义
using filesort	文件排序，效率较低
using temporary	mysql在对查询结果排序时使用临时表，效率较低
using index	表示相对应select操作使用了覆盖索引，避免访问表的数据行，效率较高

7.4show profiles分析SQL

show profiles;

能够在做SQL优化时帮助我们了解时间都耗费到哪里。

select @@have_profiling;
select @@profiling;

能够看到当前mysql是否支持profile，默认profiling是关闭的，通过set语句在session级别开启profiling;

set profiling=1;

执行完上述命令，在执行show profiles;来查看SQL语句耗时。

show profile for query query_id;

可以查看该SQL执行过程中每个线程的状态和消耗时间。

7.5trace分析优化器执行计划

mysql5.6版本之后提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizeer_trace_max_men_size=1000000;

执行SQL语句
最后，检查information_schema.optimizer_trace就知道mysql是如何执行SQL的

select* from information_schema.optimizer_trace\G;

8索引的使用

索引是数据库优化最常用的手段之一，通过索引可以帮助用户解决大多数的mysql性能优化问题。

8.1验证索引提升查询效率

表的主键会自动创建一个主键索引，因此利用主键进行查询，就是利用索引进行查询。

利用索引进行查询，是优化查询效率最有效的方式之一。

8.2索引的使用

避免索引失效

全值匹配。对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。
最左前缀法则。如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。违反最左前缀法则，索引失效。
范围查询右边的列，不能使用索引
不能在索引列上进行运算操作，否则索引失效。
字符串不加单引号，索引失效
尽量使用覆盖索引，避免使用select * 进行查询，如果查询列超出索引列，也会降低性能。
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到。
以%开头的like模糊查询，索引失效，如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。
如果mysql评估使用索引比全表更慢，则不使用索引。
is null，is not null 有时索引失效
in走索引，not in 索引失效
尽量使用复合索引，而少使用单列索引

8.3查看索引的使用情况

show status like 'Handler_read%';#当前会话索引使用情况
show global status like 'Handler_read%';#全局索引使用情况

9SQL优化

9.1大批量插入数据

当使用load命令导入数据时，适当的设置可以提高导入的效率
对于InnoDB引擎类型的表，有以下几种方式可以提高导入效率：

主键顺序插入
关闭唯一性校验
手动提交事务

9.2优化insert语句

如果需要同时对一张表插入很多行数据时，应尽量使用多个值表的insert语句，这种方式大大缩减客户端与数据库之间的连接、关闭等消耗，使得执行效率比单个insert的语句块。
在事务中进行数据插入，改为手动提交事务。
数据有序插入

9.3优化order by语句

两种排序方式：

filesort排序，通过对返回数据进行排序。
using index 通过有序索引顺序扫描直接返回有序数据，不需要额外排序，效率较高。
尽量减少使用额外的排序，通过索引直接返回有序数据。多字段排序，要么都是升序或者要么都是降序，否则就会出现filesort
filesort优化：
两次扫描算法
一次扫描算法，该算法排序时内存开销较大，但是排序效率比两次扫描算法要高。
max_length_for_sort_data的大小比查询语句中字段总大小大，则使用一次扫描算法，否则使用两次扫描算法

9.4优化group by语句

在group by过程中，可以利用索引进行优化，并且若查询不需要排序，可以在group by后面加上order by null。

9.5优化嵌套查询

尽量将嵌套查询转换为链接查询

9.6优化OR条件

对于or的查询子句，如果要利用索引，则or之间的每个条件列都必须用到索引，而且不能使用到复合索引，如果没有索引，则应该考虑增加索引。
建议使用union替换or

9.7优化分页查询

优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
优化思路二：该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询

9.8使用SQL提示

SQL提示：是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index：在查询语句中表名的后面，添加use index(索引名称)来提供希望mysql去参考的索引列表，就可以让mysql不再考虑其他可用的索引
ignore index：如果用户只是想让mysql忽略一个或者多个索引，则可以使用ignore index(索引名称)
force index：强制MySQL使用一个指定的索引，在查询语句的表名后面添加force index(索引名称)。

10应用优化

10.1使用连接池

因为频繁的创建和关闭连接，是比较耗费资源，因此有必要建立数据库连接池

10.2减少对mysql的访问

避免数据的重复检索
增加cache层：在应用中，可以增加缓存层来减轻数据库的负担，例如：使用redis

10.3负载均衡

1.通过mysql的主从复制，实现读写分离。
2.采用 分布式数据库架构，通过多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

11mysql查询缓存优化

11.1查询缓存流程：

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

11.2查询缓存配置：

查看当前的mysql数据库是否支持插叙缓存：

show variables like 'hava_query_cache';

查看当前mysql是否开启了查询缓存

show vartables like 'query_cache_type';

查看缓存占用大小

show variables like 'query_cache_size';

查看缓存状态信息

show status like 'Qcache%';

11.3开启查询缓存

mysql查询缓存默认是关闭的，需要手动配置参数开启。

query cache type=1;

在/usr/my.cnf文件中配置query cache type=1;
0:关闭
1：开启
2：查询缓存按功能进行，显示指定sql_cache的select的语句才会缓存，其余不缓存
开启后，重启服务器生效

11.4查询缓存select选项

sql_cache：如果查询结果是可以缓存的，并且缓存状态已经开启，则缓存查询结果
sql_no_cache：服务器不适用查询缓存

12mysql内存管理及优化

12.1内存优化原则

尽量多的内存分配给mysql做缓存。
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存
排序区、链接区缓存分配进行合理分配

12.2MyISAM内存优化

MyISAM没有特别的缓存机制，完全依赖于操作系统的IO缓存，利用key_buffer缓存索引块

key_buffer_size：
key_buffer_size决定MyISAM缓存区的大小，直接影响MyISAM的缓存效率，可以设置在/usr/my.cnf文件中配置
key_buffer_size=512M
read_buffer_size：
如果经常顺序扫描MyISAM表，可以增大read_buffer_size的默认值，但是是每个session独占的，如果默认值设置太大，就会造成内存浪费。
read_rnd_buffer_size：
如果带有order by子句的SQL查询，适当增加read_rnd_buffer_size的值，可以改善性能，但是不能设置的太大，否则造成内存资源浪费

12.3InnoDB内存优化

InnoDB用一块内存区域作为IO缓存池，不仅用来缓存InnoDB的索引块，也用来缓存InnoDB的数据块

innodb_buffer_pool_size：
该变量决定了innodb存储引擎表数据和索引数据的最大缓存区的大小。值越大，性能越高。
innodb_buffer_pool_size=512M
innodb_log_buffer_size：
决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加该字段的大小，可避免在事务提交前就执行不必要的日志写入操作。

13mysql并发参数调整

max_connections：
max_connections控制允许连接到mysql数据库的最大数量，默认值是151。如果connection_errors_max_connections不为零，并且一直增加，则需要增加max_connections的属性值。
back_log：
控制mysql监听TCP端口时设置的积压请求栈的大小。连接数达到max_connections时，新来的请求将会被存储在堆栈中，该堆栈的数量就是back_log。
table_open_cache：
控制所有SQL语句执行线程可打开表缓存的数量。每个链接关联查询中涉及的表的最大数量。
thread_cache_size：
控制mysql缓存客户服务线程的数量。相当于在mysql维护一个线程池，该字段就是线程池的大小。
innodb_lock_wait_timeout：
用来设置innodb事务等待行锁的时间，默认为50s。

14mysql锁

14.1锁的分类

数据操作的粒度分类：

表锁：操作时，锁定整个表
行锁：操作时，锁定当前操作行。
数据操作的类型分类：
读锁：针对同一份数据，多个读操作可以同时进行而不会相互影响。
写锁：当前操作没有完成之前，会阻断其他写锁和读锁。

14.2mysql锁

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持

表级锁：偏向myisam存储引擎，开销小，加锁快，不会出现死锁，锁的粒度大
行级锁：偏向InnoDB，开销大，加锁慢，会出现死锁，锁粒度小

14.3MyISAM表锁

MyISAM存储引擎只支持表锁。
在执行查询select语句前，会自动给涉及的所有表加读锁，在执行更新操作时，会自动给涉及的表加上写锁。

#加锁
lock table 表名 read;
lock table 表名 write;
#解锁 
unlock tables;

#查看锁的征用情况
show open tables;
#查看表的锁定情况
show status like 'Table_locks%';

读锁会阻塞写，不会阻塞读，写锁即会阻塞读又会阻塞写

14.4InnoDB行锁

InnoDB与MyISAM两个不同点：一是支持事务，二是采用了行级锁
事务的属性：

原子性
一致性
隔离性
持久性
并发事务的问题：

问题	定义
丢失更新	当两个事务选择同一行进行更新操作，后一个事务的更新操作会覆盖前一个事务的更新
脏读	一个事务访问到了另一个事务还没有提交的数据
不可重复读	一个事务前后读取到一个字段值的数据不一致
幻读	一个事务在相同条件下查询时，前一次查询到的数据和后一次查询到的数据不一致

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	1	1	1
read committed	0	1	1
repratable read	0	0	1
serializable	0	0	0

0带表可以解决该问题
1代表不可解决该问题
mysql数据库默认的隔离级别是repeatable read 查看方式：

show variables like 'tx_isolation';

InnoDB行锁模式：

读锁：多个事务对同一个数据可以共享一把锁，都能访问到数据，但是只能读不能写
写锁：不能与其他锁共存。一个事务获取了一个写锁，其他事物不能获取该行的锁。但是获取到锁的事务可以对数据进行写操作和读操作。

对于数据更新操作，InnoDB会自动给涉及到的数据集加上写锁
对于select，InnoDB不会加任何锁
加锁方式：

#读锁
select 查询字段 from 表名 where 条件 lock in share mode;
#写锁
select 查询字段 from 表名 where 条件 for update;

如果不通过索引条件查询数据，InnoDB会对表中的所有数据加锁，等同于表锁

间隙锁：
使用范围条件而不是相等条件检索数据，并请求读锁时， InnoDB会对符合条件的数据进行加锁，如果键值对在条件范围内，但是在表内并不存在的记录，InnoDB也会对这个间隙进行加锁。
应该最大程度的避免间隙锁

#查看InnoDB行锁
show status like 'innodb_row_lock%';

优化SQL建议：

尽可能让所有检索数据都能通过索引完成，避免无索引升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，及索引范围，避免间隙锁
尽量控制事务的大小，减少锁定资源量和时间长度
尽可能使用低级别的事务隔离

15常用SQL技巧

15.1SQL执行顺序

1form 表名
2on join 连接
3where 条件
4group by 分组条件
5having过滤条件
6select查询字段
7order by 排序
8limit分页

15.2使用正则表达式

在这里插入图片描述

15.3mysql常用函数

数字函数
字符串函数
日期函数
聚合函数

16mysql常用工具

16.1mysql

连接选项：

-u：指定用户名
-p：指定密码
-h：指定服务器IP或域名
-P：指定连接端口号

例如：

mysql -h127.0.0.1 -P3306 -uroot -p123456

执行选项：

-e ：执行SQL语句并退出，在mysql外部执行SQL，后面跟SQL语句

16.2mysqladmin

mysqladmin是一个执行管理操作的客户端程序
查看帮助文档：

mysqladmin --help

16.3mysqlbinlog

检查二进制格式保存的文本
在这里插入图片描述

16.4mysqldump

用来备份数据库或在不同数据库之间进行数据迁移
在这里插入图片描述

16.5mysqlimport/source

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后到处的文本文件
在这里插入图片描述

16.6mysqlshow

mysqlshow客户端对象查找工具，用来很快查找存在哪些数据库、数据库中的表、表中的列或者索引。
在这里插入图片描述

17mysql日志

17.1错误日志

错误日志记录了mysql启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
设置日志存放位置指令：

show variables like 'log_error%';

17.2二进制日志

二进制日志记录了所有的DDL语句和DML语句，但是不包括数据查询语句，mysql的主从复制，就是通过binlog实现的。
二进制日志默认情况是没有开启的。
在这里插入图片描述

17.3查询日志

查询日志中包含了客户端所有的操作语句，而二进制日志不包含查询数据的SQL语句。
在这里插入图片描述

17.4慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志。long_query_time默认值为10s，最小为0。
在这里插入图片描述
在linux系统中可以通过cat命令查看慢查询日志
如果慢查询日志太多，可以借助mysql自带的mysqldumpslow工具，来对慢查询日志进行分类汇总。