mysql

2021-03-17 13:34:00   72  举报





AI智能生成

MySQL是一个开源的关系型数据库管理系统，由瑞典的MySQL AB公司开发，现在属于Oracle公司。它使用结构化查询语言（SQL）作为访问和管理数据的主要方式。MySQL被广泛应用于各种类型的应用中，包括网站、网络应用程序、企业级软件等。 MySQL的主要特点包括：高性能、稳定性强、易于使用和扩展性强。它支持多用户并发访问，具有严格的数据安全性和完整性控制。此外，MySQL还提供了丰富的内置函数和存储过程，以及灵活的插件系统，可以根据需要添加额外的功能。总的来说，MySQL是一个功能强大、性能优秀的数据库管理系统，是构建各种类型应用的理想选择。

mysql

mysql原理

数据库

作者其他创作

大纲/内容

限制

任何标准表最多可以创建16个索引列

基本概念

SQL语句分类

DDL

数据定义语句（create drop alter）

DML

数据操作语句（insert update delete）

子查询操作符

all

any

合并查询

union

union all

intersect

minus

DQL

数据查询语句（select）

DCL

数据控制语句（grant revoke commit rollback）

基本操作语句

显示数据库创建语句：SHOW CREATE DATABASE db_name

辅助操作

约束

not null

unique

primarykey

foregin key

check

触发器

函数

存过程

数据备份为sql文件及数据恢复

备份

恢复

函数

时间日期函数

字符串函数

数字函数

流程相关函数

其他函数

mysql用到字符编码的地方

client：客户端字符集

connection：连接数据库的字符集，不指定则使用服务端默认字符集

database：指定数据库使用的字符集，若建库时不指定则使用服务器字符集

result：数据库给客户端返回的字符集，若不指定则使用服务器默认字符集

server：服务器安装时使用的默认字符集

system：数据库系统使用的字符集

事务和锁

事物

原子性
持久性
一致性

redo log

用户崩溃时恢复数据（WAL预写日志的方式，在数据提交前记录日志（日志文件顺序存储速度快））；
Force Log at Commit（1.设定参数每隔一段时间落盘；2.事物提交时落盘）保证该日志落盘（从redo log buffer 到 redo log），以此来达到奔溃时数据可能恢复;。
总的有两个日志空间交替使用，当空间满的时候出发数据文件落盘(Buffer Poll 到数据文件)，数据文件落盘之后及可清除redo log日志文件

force log at commit

保证事物提交之后redo log已经持久化

Double write

用于提高数据库的可靠性，用于解决脏页落盘时部分写失效问题

操作系统磁盘最小单位4KB；
mysql最小操作单位16KB；
duble write 保证mysql的数据页要么全部成功，要么全部失败

undo log

用于对数据回滚时进行撤销，也会用于隔离性的多版本控制

控制台操作

0) start transaction //开始一个事务
1) savepoint 保存点名 //设置保存点
2) rollback to 保存点名 //取消部分事务
3) rollback //取消全部事务
4) commit //提交事务.

隔离性

脏读

当一个事务读取另一个事务尚未提交的修改时，产生脏读

不可重复读

同一查询在同一事务中多次进行，由于其他提交事务所做的修改，每次返回不同的结果集，此时发生非重复读

幻读

同一查询在同一事务中多次进行，由于其他提交事务所做的插入或删除操作，每次返回不同的结果集，此时发生幻读。

导致一条数据查询不存在，但是插入也不能成功（因为别的事物插入了该数据，而当前session不可见）

相关操作指令

1.查看当前会话隔离级别： select @@tx_isolation;

2.查看系统当前隔离级别：select @@global.tx_isolation;

3.设置当前会话隔离级别：set session transaction isolation level repeatable read;

4.设置系统当前隔离级别：set global transaction isolation level repeatable read;

5. mysql 默认的事务隔离级别是 repeatable read

锁

insert/update/delete

for update

mysql使用行锁，首先得明确使用的是innoDB引擎

innoDB存储引擎加行锁，实际是对索引加锁

分类

表级锁

特征

开销小，加锁快，不会出现死锁；
粒度大，发生锁冲突概率大，并发低

适合以查询为主

读锁

local table table_name read

现象：
可以对表进行读操作；
当前session对表进行CUD操作（报错）；
其他session进行CUD操作（等待）

写锁

local table table_name write

现象：
当前session可以进行CRUD操作；
其他session CRUDE操作（等待）

释放锁

unlock tables

行级锁

读锁

不允许其他session 再来加排他锁，但可以加读锁

select * from table_name where ** lock in share mode

写锁

不允许其他事物写和加锁，但允许读

select * from table_name where ** for update

特征

开销大、加锁慢、会出现死锁；
粒度小，发生锁冲突低，并发高

注意

行锁必须要有索引才能实现，否则锁全表

CUD默认都会加行级锁

页面锁

特征

开销介于表级别锁与行级别锁之间；
会出现死锁；
粒度介与表级锁与行级锁之间；并发一般。

BDB引擎使用

索引

分类

聚集索引

非聚集索引

基本操作

查看索引

show index from 表名

创建索引

CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;

操作命令

show processlist

查看连接池中建立好的连接

Time：表示这个连接多久没有动静了；
默认地，如果超过8个小时还没有动静，连接器就会自动断开连接，可以通过wait_timeout参数进行控制

执行流程

查询流程

整体架构

sql执行流程

1.先尝试从~~查询缓存~~(8.0以后不再有查询缓存)中查询该sql是否已经有缓存的结果了；
2.分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性；
3.优化器拿到分析器的sql之后，开始继续解析sql，判断需要走什么索引，根据实际情况重写sql，最终生成执行计划；
4.执行器根据执行计划执行sql，执行之前会先进行操作权限校验；
然后根据表存储引擎调用对应接口进行查询数据，这里的扫描行数就是指的接口返回的记录数，执行器拿到返回记录之后进一步加工；
5.执行器根据sql条件依次筛选数据；
从磁盘数据中读取数据到内存Buffer Pool（数据页，索引页）中；

CUD流程

1.数据写入Buffer Pool的同时，先写入数据到redo log buffer中；
2.redo log buffer 依据 Force log at Commit(按时或事物提交的时候)写入 redo log日志中；
3.Buffer pool 依据check point 择时脏页数据落盘；（redo log日志满了也会触发该操作，成功之后会清理redo log日志）;
4.Double write保证脏页数据不会出现部分落盘失败的情况。

in与exists效率

1. 如果查询的两个表大小相当，使用in和exists差别不大；
2. 如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询小的使用in；
3. not in和 not exists ：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而notexists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in快。

优化

一般操作步骤

1.通过show status了解各种SQL执行频率；

show [session|global] status [like '???']

session来表示当前的连接的统计结果(默认)，
global来表示自数据库上次启动至今的统计结果

show status like ‘Com_%’;

Connections：试图连接MySQL服务器的次数
Uptime：服务器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默认是慢查询时间10s)

2.定位执行效率较低的SQL；

默认不记录慢查询日志，需要在服务启动时手动开启；
通过慢查询日志定位执行效率较低的SQL语句。
慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句

慢查询

常用配置

slow_query_log 启动停止记录慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

记录语句

记录CRUD所有符合条件的语句

分析工具

mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

c:总次数
t:总时间
l:锁的时间
r:总数据行
at,al,ar :t,l,r平均数【例如：at = 总时间/总次数】

-t top 指定取前面几天作为结果输出

pt_query_digest

pt-query-digest --explain h=127.0.0.1, u=root,p=password slow-mysql.log

3.通过explain分析低效率SQL的执行情况；

Explain + SQL语句

表示查询中执行select子句或操作表的顺序

三种情况

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

select_type
（查询类型）

主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY：若查询中包含任何复杂的子查询，则外层标记为此类型
SUBQUERY:在select或where中使用的查询使用该类型
DERIVED/MATERIALIZED:在from列表中使用的子查询被标记为DERIVED（衍生），mysql会递归这些子查询，把结果放入临时表中
UNION:若第二个select出现在union之后，则被标记为UNNION；若UNION被包含在from子句的查询中，则外层被标记为DERIVED
UNION RESULT:从union表获取结果的select

table

显示这一行数据是关于哪个表

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了；const用于比较primary key或者unique索引
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询
index：使用了整个索引文件（全表扫描的一种）
all：Full Table Scan，将遍历全表以找到匹配的行

possible_key

key

key_len

表示索引使用的字节数
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

key_len计算总结

ref

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

rows

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

extra

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

4.确定问题，并做相应处理。

索引

查看索引的使用情况 show status like ‘Handler_read%’;
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。

常用优化方式

选择合适的存储引擎

合适的数据类型

对表进行水平划分

对表进行垂直划分

数据库参数配置

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

四个方面

1.数据库设计；

2.sql优化

大批量插入数据

MyISAM：
alter table table_name disable keys;
loading data//insert语句;
alter table table_name enable keys;

Innodb：
1，将要导入的数据按照主键排序
2，set unique_checks=0,关闭唯一性校验。
3，set autocommit=0,关闭自动提交。

优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。
如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

原则

优化需要优化的Query
定位优化对象性能瓶颈
从Explain入手
永远小结果集驱动大结果集#
尽可能在索引中完成排序
只取自己需要的Column
仅仅使用最有效的过滤条件
尽可能避免复杂的join和子查询

3.数据库参数设置；

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

4.恰当的硬件资源和操作系统

避免索引失效

尽量全值匹配

联合索引，尽量吧联合字段都使用起来

最左匹配原则

如果索引了多列，查询从索引的最左列开始并且不能跳过部分索引

不在索引上做任何操作

不要在索引字段上做操（函数，类型转换，计算）作，会失效

范围条件放最后

范围条件之后的索引字段会索引失效

尽量使用覆盖索引(查询列是索引列)，减少使用select *

少用不等于

使用不等于的时候会索引失效导致全表扫描

Null、Not null可能有影响

字段not null(所以字段都有值，此时进行null值判断没有任何意义，如一定要用可使用覆盖索引方式)

条件为is null 索引失效

条件is not null 索引失效

字段为is null

条件为is null 使用索引

条件为is not null 索引失效

like查询使用‘%具体字段’的方式会索引失效，全表扫描

字符串不加单引号会索引失效

类型不匹配会调用函数转类型，从而导致失效

union比or的效率更高

磁盘文件

日志文件

错误日志文件（error log）

二进制日志文件(bin log)
【mysql服务】

需要手动开启

通用查询日志(general query log)

慢查询日志(show query log)

中继日志(relay log)

主从复制时，从库从主库获取过来的bin log日志在本地保存为中继日志（relay log）

重做日志文件(redo log)

用于奔溃时回复数据，循环使用；
根据checkpoint脏页落盘成功之后就可以清除该记录（当redolog空间不足也会触发脏页落盘）。

回滚日志文件(undo log)
【innodb存储引擎】

事物回滚

隔离性，多版本控制

ibdata

*.fmt

MVCC多版本控制

存储引擎

事务安全型

innoDB

特征

5.5 后默认引擎,系统表空间；
5.6后默认使用独立表空间

表空间相关参数：innodb_file_per_table

系统表空间无法简单收缩文件大小

独立表空间可以使用optinized table 收缩文件大小

系统表空间会产生IO瓶颈

独立表空间可以同时向多个文件刷新数据

支持事物

redo.log, undo.log

行级锁（默认），表级锁

行锁为索引加锁，如果where字段不是索引，将会锁全表

5.6之后支持全文索引，对中文支持需要特殊配置处理

InnoBD引擎架构

buffer pool
(缓冲池)

主内存中的一个区域，在InnoDB访问表数据和索引数据的时候，会顺便把对应的数据页缓存到缓冲池中；
专用服务器上，通常将80%左右的物理内存分配给缓冲池;
提高缓存管理效率，缓冲池把页面链接为列表，使用改进版的LRU算法将很少使用的数据从缓存中老化淘汰掉.

LRU

当需要把新页面存储到缓冲池中的时候，将淘汰最近最少使用的页面，并将新页面添加到旧子列表的头部。

默认 3/8缓冲池用于旧子列表；
当新页面如缓冲池时，首先将其插入旧子列表头部；
重复访问旧子列表的页面，将使其移动至新子列表的头部；
随着数据库的运行，页面逐步移至列表尾部，缓冲池中未被方位的页面最终将被老化淘汰。

`innodb_old_blocks_pct`：控制LRU列表中旧子列表的百分比，默认是37，也就是3/8，可选范围为5~95；
`innodb_old_blocks_time` ：指定第一次访问页面后的时间窗口，该时间窗口内访问页面不会使其移动到LRU列表的最前面。默认是1000，也就是1秒。

innodb_old_blocks_time很重要，有了这1秒，对于全表扫描，
由于是顺序扫描的，一般同一个数据页的数据都是在一秒内访问完成的，
不会升级到新子列表中，一直在旧子列表淘汰数据，所以不会影响到新子列表的缓存。

文件IO

innodb_flush_method

将数据刷新到InnoDB数据文件和日志文件的方法，这可能会影响I/O吞吐量

change bugger

当二级索引页(非唯一索引)不在缓冲池中时，它们会缓存这些更改。当页面通过其他读取操作加载到缓冲池中时，再将由INSERT，UPDATE或DELETE操作（DML）产生的change buffer合并到buffer pool的数据页中。

索引不可以使用chage buffer

自适应哈希索引

自适应哈希索引功能由innodb_adaptive_hash_index变量启用，或在服务器启动时由--skip-innodb-adaptive-hash-index禁用。

Log Buffer

og buffer(日志缓冲区)用于保存要写入磁盘上的log file(日志文件)的数据。日志缓存区的内容会定期刷新到磁盘。
日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为16MB。较大的日志缓冲区可以让大型事务在提交之前无需将redo log写入磁盘。
如果您有更新，插入或者删除多行的事务，尝试增大日志缓冲区的大小可以节省磁盘I/O。

参数配置

innodb_flush_log_at_trx_commit

控制如何将日志缓冲区的内容写入并刷新到磁盘

通过该参数获取更好的性能，但是会导致在系统崩溃的过程中导致数据丢失。

可选参数：