【知识梳理】Mysql知识梳理

无影风Victorz

已于 2023-10-20 22:58:48 修改

阅读量99

点赞数

分类专栏：数据库知识梳理文章标签： mysql 数据库

于 2020-02-20 09:16:15 首次发布

本文链接：https://blog.csdn.net/victorzzzz/article/details/95204988

版权

知识梳理同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

数据库连接池：c3p0 dbcp

数据仓库的星型架构，雪花模型：

上卷：从当前数据往上回归上层数据
下钻：从当前数据展开下一层数据

数据库范式：

第一范式：无重复列
第二范式：1 表必须有主键 2 没有包含在主键的列必须完全依赖主键，而不能只依赖一部分
第三范式：2NF + 不能有传递依赖

范式越多，表越多

反范式：用空间换取时间，把数据冗余在多个表，避免表之间的关联

Mysql服务器逻辑框架：

上层服务：连接处理、授权认证、安全等等
第二层：查询解析、分析、优化、缓存以及所有内置函数。在这一层实现的有：存储过程、触发器、视图
第三层：包含了存储引擎。存储引擎负责mysql中的数据的存储和提取

数据引擎：

事务型存储引擎：InnoDB、NDB Cluster

第三方事务引擎：XtraDB、PBXT

其他引擎：Archive、Blackhole、CSV、Federated、Memory、Merge、NDB、OLTP

面向列的引擎：Infobright

社区存储引擎：Aria、Groonga、OQGraph、Q4M、SphinxSE、Spider、VPForMySQL

切换引擎：alter table mytable engine = xxx;

如果转换表的存储引擎，将会失去和原引擎相关的所有特性

Mysql执行引擎

MyISAM: 选择密集型、插入密集型，索引和数据分开

索引顺序访问方法，支持全文索引，非事务安全，不支持外键，会加表级锁

MyISAM利用unique key作为索引值，data域保存着地址（非聚簇索引）

MyISAM会把自增最大ID放到数据文件里

适用于小项目，读性能比InnoDB高

InnoDB: 更新密集型，索引和数据捆绑

事务型（ACID）存储引擎，加行锁，支持回滚，崩溃恢复

InnoDB利用key作为索引值，data域为数据（聚簇索引）

InnoDB把自增主键最大ID记录放在内存里

适用于存储，处理和保护数据的核心业务

每个数据库保存为数据目录下的一个子目录，子目录下创建一个与表同名的.frm文件保存表的定义

可以用show table status 显示表的相关信息

InnoDB四大特性：插入缓冲、二次写、自适应哈希索引、预读

（1）插入缓冲：

一般情况下，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。

如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。（这是因为B+树的特性决定了非聚集索引插入的离散性。）

插入缓冲对于非聚集索引的插入和更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓存池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插入到叶子结点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

插入缓冲的使用要满足两个条件：

索引是辅助索引
索引不是唯一的（辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况，插入缓冲就失去了意义。）
存在的问题：

在写密集的情况下，插入缓冲会过多的占用缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存。
（2）二次写

当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分的情况，我们称之为部分写失效。当写入失效发生时，先通过页的副本来还原该页，再重做日志，这就是两次写。

doublewrite步骤：

当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。
然后马上调用fsync函数，同步脏页进磁盘。在这个过程中，doublewrite页的存储是连续的，因此写入磁盘为顺序写，性能很高在完成doublewrite页的写入后，再将doublewrite buffer中的页写入到各个表空间文件中，此时的写入则是离散的。
如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其拷贝到表空间文件，再应用重做日志，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

（3）自适应哈希索引

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称为自适应的。

自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快，而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

（4）预读

InnoDB 提供了两种预读的方式：

一种是 Linear read ahead，由参数innodb_read_ahead_threshold控制，当你连续读取一个extent 的 threshold 个 page 的时候，会触发下一个 extent 64个page的预读。
另外一种是 Random read-ahead，由参数innodb_random_read_ahead控制，当你连续读取设定的数量的page后，会触发读取这个extent的剩余page。
InnoDB 的预读功能是使用后台线程异步完成的。

原文链接：https://blog.csdn.net/weixin_44052055/article/details/122958972

数据类型：

整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

可选UNSIGNED属性，表示不准许负值，可以使正数上限提高一倍

INT(数字)只是展示了显示字符的个数，并不能限制值的合法范围

Decimal：decimal只是一种存储格式，在计算中decimal会转换为double类型

字符串类型：varchar 变长字符串，比定长更省空间。innodb可以把过长的varhcar存储为blob

char 定长，末尾会用空格进行填充，对于经常变更的数据，char比varchar要好，因为不会产生碎片

BLOB 和 TEXT 类型：当作独立对象处理

BLOB：二进制存储，没有排序

TEXT：字符方式存储，存在排序，只针对max_sort_length字节而不是整个字符串做排序

枚举：底层存储的实际是数字，必要的时候转换为字符串

create table enum_test(e ENUM('fish’,'apple','dog') NOT NULL);

日期和时间类型：
- DATETIME：保存大范围的值
- TIMESTAMP：效率高。只用4个字节存储空间，范围比DATETIME小很多，只能表示1970年到2038年

提供了UNIX_TIMESTAMP函数把日期转换为Unix时间戳

位数类型：
- BIT：可以使用BIT在一列中存储一个或多个true、false值。尽量避免使用这个类型
- SET：改变列的定义代价较高，一般不推荐

注意点：

列：mysql存储引擎工作时需要在服务器层和存储引擎层之间通过缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据的操作代价非常高。

关联：mysql限制每个关联操作最多只能有61张表，如果希望查询得快且并发性能好，单个查询最好控制在12个表以内

alter table：大多数alter table都会导致mysql服务中断

慢： alter table mytable modify column ... 所有的modify column操作都会导致表的重建

快： alter table mytable alter column .... alter column会直接修改.frm文件而不涉及表数据

不需要重建表结构修改表的方法：

创建一张具有相同结构的空表
执行FLUSH tables with read lock。会关闭所有正在使用的表，并且禁止任何表被打开
交换.frm文件
执行UNLOCK TABLES 来释放第二步的读锁

快速的创建myisam索引：只对非唯一索引有效

禁止索引：alter table xxx disable keys;
载入数据
重启索引：alter table xxx enable keys;

p139

高级特性：

分区表：底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象封装（索引也是按分区的子表定义的，无全局索引）

分区表注意点：1. 一个表最多只能有1024个分区 2. 分区表无法使用外键约束

每个操作都会“先打开并锁住所有底层表”，但并不是说分区表在处理过程中是锁住全表的

为了保证大数据量的可扩展性，一般有两个策略：

1. 全量扫描数据，不要任何索引

2. 索引数据，并分离热点

分区无效的情况：

1. NULL值会使过滤无效

2. 分区列和索引列不匹配，会导致查询无法进行分区过滤

3. 随着分区的增长，选择分区的成本可能会很高

4. 打开锁住所有底层表的成本可能很高

5. 维护分区的成本很高

分表--分区---分库

分表：垂直分表（涉及全局主键问题、一致性Hash问题）、水平分表

分区：（类似Hive分区）减少了分表难度，独立的逻辑表。底层由多个物理表实现。应用层无需改变原有的SQL语句

分区的类型：

RANGE分区、List分区（partition by list values in 关键字）、Hash分区（确保平均分布）、Key分区

分区对原始系统改动最小，分区只涉及数据库层面，应用层面不需要改动。只起到水平切分的作用。

传统分表后，count、sum统计只对切分表进行操作，之后在应用层再次计算得出。而分区表不受影响，可以直接统计

分库：当单个数据库服务器无法支撑当前数据量时，需根据业务逻辑紧密程度把表分成几个小撮，分表放到不同数据库服务器降低单台服务器负载（跨数据库无法联表查询）

MySQL 分库分表方案，总结的非常好！ - 掘金

表空间：表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间。

约束：非空约束、主键约束、唯一性约束

视图：视图和表是在同一个命名空间，mysql在很多地方对于视图和表是同样对待的。不能对视图创建触发器

暂时不支持：1. 物化视图 2. 视图中创建索引 3. 不会保留创建视图的原始SQL语句

处理视图的两种算法：

1. 合并算法（推荐）

2. 临时表算法：如果视图中包含group by、distinct、聚合函数、union、子查询等，只要无法在原表记录和视图记录中建立一一对应的场景，mysql都会使用临时表算法

create algorithm=temptable view v1 as select * from xxx;

p278

可更新视图：可以通过更新这个视图来更新视图涉及的相关表（所有使用临时表算法实现的视图都无法被更新）

check option：任何通过视图更新的行，都必须符合视图本身where条件的定义

物化视图：将视图结果存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中

获取视图中创建语句的方式：

UDF：UDF Repository for MySQL

插件

索引

存储过程：对sql进行封装实现功能，是编译过的代码块，执行效率高，消耗时间较短

create Procedure G1
as
select * from MTable(MTable([password]) values('...'))
go
/*!99999 ..../
exec G1

触发器：触发器是自动的，是一种特殊的存储过程

create or replace trigger x before insert or update on tablename ...

触发器只支持“基于行的触发”，触发器始终针对一条记录，而不是针对整个SQL语句的

触发器并不能保证更新的原子性，在myisam中，无法回滚

在innodb表上的触发器是在同一个事务中完成的

事件：可以在INFORMATION_SHCEMA.EVENTS中看到各个事件的状态，mysql总有一个事件调度线程，在事件结束后会被销毁

事件包括定期地维护任务、重建缓存、构建汇总表来模拟物化视图，或存储用于监控和诊断的状态值

create event xxx on yyy every 1 week do call optimize_tables('somedb'）

为了防止一个事件未执行完成，另一个事件已经开始的情况，可以使用GET_LOCK()确保总有一个事件在被执行：

create event optimize_somedb on yyy every 1 week do
begin
declare continue hanlder for sqlexeption
begin end;

if get_lock('somedb',o) then
do call optimize_tables('somedb');
end if;
do release_lock('somedb');
end

游标：在服务器端提供只读的、单向的游标。只能在存储过程中或更底层的客户端API中使用

在一个存储过程中，可以有多个游标

create procedure bad_cursor()
begin
declare film_id INT;
declare f CURSOR for select id from film;
open f;
fetch f into id;
close f;
end

数据字典：存放数据库内部信息的地方，描述内部运行管理情况

储存过程和函数的区别：

存储过程实现功能复杂一点，而函数功能性，针对性比较强
存储过程可以返回参数，函数只能返回或表对象
存储过程一般作为一个独立的部分执行，而函数可以作为查询语句的一个部分调用

绑定变量（存储过程中使用）

当创建一个绑定变量SQL时，客户端向服务端发送一个SQL语句原型，服务端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句的处理句柄，以后每次执行这类查询，客户端都指定使用这个句柄。

例：insert into tbl(col1,col2,col3) values(?,?,?);

优点：在服务端只需要解析一次SQL语句

优化器的工作只需要执行一次

以二进制的方式只发送参数和句柄，比每次发送ASCII码文本效率更高

仅仅是参数，而不是整个查询语句，无须转义大大减少了SQL注入和攻击的风险

绑定变量的优化：

1）准备阶段：移除不可能的条件，重写子查询

2）第一次执行：简化嵌套循环关联，并将外关联转化为内关联

3）每次执行：

过滤分区
尽量移除COUNT(),MIN(),MAX()
移除常数表达式
检测常量表
做必要的等值传播
优化关联顺序

例：set @sql := 'select * from actor where name=?'

        prepare stmt_fetch_actor  from @sql;

        set @name := 'victorz'

        execute stmt_fetch_actor  using  @name;

绑定变量的限制：

1. 绑定变量是会话级的，连接之间不能共用绑定变量句柄

2. 绑定变量在5.1版本前不能使用查询缓存

3. 并不是所有绑定变量都能获得更好的性能

4. 如果忘记释放变量资源，可能会发生泄漏

绑定变量的区别：客户端模拟绑定变量、服务器端绑定变量、SQL接口绑定变量

分布式XA事务：两阶段提交

XA事务中需要有一个事务协调者来保证所有的事务参与者都完成了准备工作（第一阶段）

如果协调器收到所有的参与者都准备好信息，就会告诉所有的事务可以提交了

Mysql两种XA事务：

（1）mysql可以参与到外部的分布式事务中

（2）通过XA事务来协调存储引擎和二进制日志

查询缓存：缓存select查询结果，当查询命中该缓存，mysql会立刻返回结果，调过解析，优化和执行阶段

查询缓存有可能成为整个服务器的资源竞争单点，在多核服务器上还可能导致服务僵死，一般默认关闭

当查询结果中有一些不确定的数据时，则不会被缓存：例如NOW() 或 CURRENT_DATE()

内存池初始化：内存池初始化是一个完整的空闲块

为查询结果分配空间的过程：p312 (初始状态->结果存储->存储完成->合并完成)

只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升，对于复杂select语句都可以使用查询缓存（join，排序，分页等）

查询配置参数：

query_cache_type：是否打开查询缓存
query_cache_size：查询缓存使用的总内存空间
query_cache_min_res_unit：在查询缓存中分配内存块时的最小单位
query_cache_limit：mysql能够缓存的最大查询结果
query_cache_wlock_invalidate：如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果

cache_free_blocks 观察碎片

Flush Query Cache 完成碎片整理，将所有的查询缓存重新排序，并将所有的空闲空间都聚集到查询缓存的一块区域上

Reset Query Cache 清空缓存

分析和配置查询缓存

Innodb事务的查询缓存：让所有加锁操作的事务都不适用任何查询缓存

用户可以将query_cache_type设置为demand，通过SQL_CACHE自主选择哪些SQL被缓存，如果希望缓存多数查询，则少数SQL加上SQL_NO_CACHE

mysql基本数据类型：char(255)、varchar(64k)、text(4G)、blob存图片

sql查询优先级：from > on > outer(join) > where > group by > having > select > distinct > order by > top

查看字符数 & 字节数：length(String) & lengthb(String)

可以使用length（String）=lengthb（String）判断是否含有中文

事务ACID特性：

原子性
一致性
持久性
隔离性

mysql事务隔离级别：

Read Uncommiteed	刚刚的数据无效，被删除读到未提交的内容，导致“脏读”	任何操作都不会加锁
Read Committed	刚刚的数据被修改，被修改。“不可重复读”	读取不加锁、但是增，删，改加锁	（1）有一个交叉的事务有新的commit，导致了数据的改变（2）一个数据库被多个实例操作，同一事务的其他实例在该实例处理期间可能有新的commit
Read Repeatable	刚刚的数据有新符合条件的插入，被添加会导致“幻读”	在可重复读中，第一次读取到数据后，就将这些数据加锁，其他事务无法修改这些数据	当用户读取某一范围的数据行时，另一个事务又在该范围内插入新行
Serializable	读用读锁，写用写锁，读写互斥	强制事务排序，在每个读的数据上加共享锁

√: 可能出现 ×: 不会出现

	脏读（读到被删除内容）	不可重复读（读到被修改内容）	幻读（读到新插入内容）
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

1.脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

2.不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

3.幻读:是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

事务隔离级别：https://draveness.me/mysql-innodb

mysql事务的实现：https://draveness.me/mysql-transaction/

事务日志：预写式日志（Write-Ahead-log）。为了提高事务的效率，使用事务的日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再将内容记录到硬盘上的事务日志中。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。修改日志需要写两次磁盘

事务自动提交：

show variables like 'autocommit';

set autocommit =1;

改变事务提交级别：

set session transaction isolation level read committed

在非事务的表上执行事务相关操作的时候，mysql通常不会发出提醒，也不报错

Innodb事务的实现

持久性：redo log
隔离性：锁、时间戳、多版本和快照隔离
一致性：undo log

重做日志 redo：确保事务持久性，防止在发生脏页

回滚日志 undo：保存了事务之前的版本，在事务开始前将当前版本生成undo.log

Redo与二进制日志

都用来恢复数据，redo效率高于binlog

redo在事务层面，是物理日志（修改后的物理记录）
binlog在数据库层面，是逻辑日志（sql语句）

mysql调优“三部曲”

1. 慢查询配置（分析问题sql）

   开启慢查询：

   my.ini / mysql.cnf参数开启慢查询

   [mysqld]

   slow_query_log = ON

   slow_query_log_file = /usr/local/mysql/data/slow.log

   long_query_time = 1

mysql开启慢查询

   set global slow_query_log='ON';

   set global slow_query_log_file='/usr/local/mysql/data/slow.log';

   set global long_query_time=1;

mysqldumpslow -c(次数) -t(时间) -l(lock 时间) -r(返回记录数) [a表示倒序；t表示top n]

例：mysqldump -s c -t 20 host_slow.log

2. explain (使用索引优化select语句)

table	select_type	possible_keys	key	key_len	ref	rows	Extra
表	连接类型	可能用到的索引	实际索引	索引长度	哪一列使用	请求数据行数

select_type：system > const > eq_ref > ref > range > index > index_merge > index (扫描了所有索引) > all

Etra：

   Using filesort 额外排序操作，不能通过索引顺序达到排序效果，CPU资源消耗大
   Using index 性能不错
   Using temporay 使用临时表，效率不高

3. profile （查看最近sql历史与执行时间）

查看：show variables like '%profiling%';
启动：select @@profiling ; set global profiling = 1;set profiling=on;
使用：show profiles; / show profile for query 2

内部原理：当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。

p83 详细报告

4. show status 返回一些计数器

5.查看mysql的生命状态：show full processlist

状态包括：sleep、query、locked、analyzing and statistics、copying on tmp table、sorting result、sending data

扫描大量的数据但返回少数行的优化：

使用索引覆盖扫描，把所有需要用的列都放在索引中
改变库表结构
重写这个复杂的查询

分解关联查询的优势：

让缓存的效率更高
将查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
查询本身效率也可能有提升
可以减少冗余记录的查询
相当于做了哈希关联，而不是嵌套循环关联

select * from tag join tag_post on tag_post.tag_id=tag.id join post on tag_post.post_id = post.id where tag.tag='mysql'
==>
select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,456,1111);

mysql内部每秒能够扫描内存中上百万行数据，相比之下，mysql响应数据给客户端就慢很多，因此需要切分工作：

row_affected = 0
do{
row_affected = do_query(
"delete from message where created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000"
)
}while rows_affected > 0

mysql查询执行的路径：

mysql 客户端和服务器之间通信是“半双工”的（任意时刻，要么服务器向客户端发送数据，要么客户端向服务器发送数据）

mysql通常要等所有数据已经发送给客户端才能释放这条查询占用的资源

查询缓存：通过对大小写敏感的哈希查找实现
语法解析器和预处理：1）生成解析树 2）验证解析树是否合法
查询优化：基于成本的优化器，预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个（存在静态优化、动态优化）

当前会话的查询成本：show status like 'Last_query_cost'

优化类型：

重新定义关联表的顺序
将外连接转化为内连接
使用等价变换规则：在遇到右外连接时，会将其改写成等价的左外连接
优化COUNT() MIN() MAX()：MAX只需找B-Tree最右数据即可.....
预估并转换为常量表达式：检测到可以转化为常量时，会做转换常量优化处理
覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，直接使用索引返回数据
子查询优化
提前终止查询：发现已经满足条件时，立刻终止
等值传播：如果两个列的值通过等式关联，where条件也传递到另一个列上 || in列表都复制到关联的各个表中
列表IN 比较：将IN列表中的数据先排序，然后通过二值查找确定列表中的值是否满足条件

mysql在生成查询的执行计划时，需要向存储引擎获取相应的统计信息

union的过程：先将一系列单个查询结果放在一个临时表中，然后重新读取临时表的数据完成union操作

嵌套循环关联过程：先在一个表中循环读取单条数据，再嵌套循环到下一个表中寻找匹配的行。依次下去优化器使用贪婪搜索的方式查找“最优”的关联顺序

mysql排序优化：“快速排序”，小表在内存，大表会将数据分块，对每个独立的块使用快速排序，并将结果放在磁盘上，然后将排好序的块进行合并，最后返回结果。

mysql的排序算法：

两次传输排序：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行
单次传输排序：先读取查询所需要的所有行，然后再根据给定列进行排序，最后直接返回排序结果

执行引擎：完成生成的执行计划
返回结果：将结果返回客户端是一个增量，逐步返回的过程，一旦服务器开始生成一条结果时，就可以开始向客户端返回结果了

结果集合中每一行都会以一个满足mysql通信协议的封包发送，再通过TCP协议进行传输

where...in的子查询会将相关的外层表压到子查询中，转化为join连接，效率比较低。建议使用exists等待

mysql 无法并行执行select

mysql不允许在同一个表上同时进行查询和更新

查询优化器的提示：控制最终的执行计划

HIGH_PRIORITY : 会将语句重新调度到正在等待表锁以便修改数据的语句前
LOW_PRIORITY : 会将语句一直处于等待状态，只是简单地控制了mysql访问某个数据表的队列顺序
DELAYED : 对 insert replace有效，会立刻返回，插入的数据放入到缓冲区，然后在表空闲时批量将数据写入
STRAIGHT_JOIN : 对 select 有效，让查询的表按出现的顺序关联，固定其前后两个表的关联顺序
SQL_SMALL_RESULT : 对 select 有效，告诉优化器对group by 或 distinct 查询如何使用临时表及排序。告诉优化器结果集很小，可以放在内存中排序
SQL_BIG_RESULT : 结果集很大，建议使用磁盘临时表做排序
SQL_BUFFER_RESULT : 将结果集放入到临时表，尽可能快地释放表锁
SQL_CACHE / SQL_NO_CACHE : 是否缓存命中
SQL_CALC_FOUND_ROWS :返回更多信息
FOR UPDATE / LOCK IN SHARE MODE : 针对innodb
USE INDEX / IGNORE INDEX / FORCE INDEX
optimizer_search_depth : 控制优化器执行计划的限度
optimizer_prune_level : 让优化器根据需要扫描的行数来决定是否跳过某些计划
optimizer_switch

Mysql四种日志的意义：

错误日志
查询日志
二进制日志：记录所有更改数据语句，还用于主从复制
慢日志：记录所有执行时间超时的查询

锁

数据库锁：行锁、表锁、页级锁、意向锁、读锁、写锁、悲观锁、乐观锁

意向锁：表锁会覆盖行锁数据，产生冲突，因此引入意向锁。检测表锁，行锁冲突。先申请表的意向锁，再申请其他锁。。。。。。

悲观锁：每次去拿数据都认为别人会修改，在操作之前先上锁

乐观锁：拿数据认为别人不会修改，更新的时候判断一下。（适用在多读的情况）

锁表的情景：1. 用索引字段作条件进行修改，索引字段不唯一时 2. 同一个表进行删除时，删除条件不统一

读锁：flush tables with read lock; lock tables tablename WRITE; lock tables tablename READ; unlock tables

共享锁（读锁）：多个事务可封锁一个共享页：任何事务都不能修改该页

排他锁（写锁）：仅允许一个事务封锁此页，其他必须等待其释放才能访问

行级锁：行级锁可以最大程度地支持并发处理，在InnoDB 和 XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现。

InnoDB采用两阶段锁定协议（根据隔离级别在需要的时候自动加锁）

InnoDB三种行锁技术：

Record Lock：单个行记录锁，通过对索引加锁的方式实现。只有通过索引条件检索数据，才能用行级锁
Grap Lock：间隙锁，锁定一个范围，不包含自身
next-key 锁：Read Lock + Grap Lock (避免幻读)：锁定一个范围，并锁定记录本身。当查询索引含有唯一属性时，降级为Record Lock

多版本并发控制：MVCC

MVCC特点：读不加锁、读写不冲突

MVCC提升了并发性能，是行级锁的一个变种，在很多情况下避免了加锁操作，因此开销较低。

不同存储引擎的MVCC实现不同，典型的有乐观锁并发控制和悲观锁并发控制

- 非阻塞的读操作
- 锁定必要的行的写操作

innodb的mvcc实现：

保存某个时间点的快照来实现的

在每个记录后面保存两个隐藏列来实现：1、行的创建时间 2、行的过期时间

每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

SELECT	检查两点： 1、只查找版本早于当前事务版本的数据行 2、行的删除要么没定义，要么大于当前事务版本，可以确保在事务开始前未删除
INSERT	插入的每一行保存当前系统版本号
DELETE	删除每一行保存当前系统版本号作为删除标识
UPDATE	相当于插入并删除插入：保存当前版本号删除：原来的行做行删除标识

MVCC只在 REPEABLE READ 和 READ COMMITTED 两个隔离级别下工作

MyISAM将表存储在两个文件中：数据文件和索引文件，分别是.MYD 和 .MYI扩展名

myisam可以才能出行记录数

如果表在创建并导入数据以后，不会再进行修改操作，适合使用myisam压缩表（myisampack）

特性：

加锁与并发：对整张表加锁（表锁），而不是针对行
修复：可以手工或自动执行检查和修复操作--check table mytable repair table mytable
索引特性：即使BLOB和TEXT，也可以基于前500个字符创建索引
延迟更新索引键：不会立刻修改索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区的时候才会写入到磁盘

索引：是存储引擎用于快速找到记录的一种数据结构（在存储引擎层）

主键索引、唯一索引、普通索引、全文索引、聚集索引、组合索引（列的顺序十分重要，只能高效地使用索引的最左前缀列）

innodb 在访问行的时候才会对其加锁，索引减少了innodb访问的行数，从而减少了锁的数量

B-Tree 索引：适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找
- 全职匹配
- 匹配最左前缀
- 匹配列前缀
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 只访问索引的查询

注意点：

1. 非最左查找将无法使用索引

2. 不能跳过索引中的列

3. 如果查找中有某个列的范围查询，则其右边所有列所有列都无法使用索引优化查询

哈希索引：对所有索引列计算一个哈希码，并将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

如果多个列的哈希值相同，则将以临接链表的方式存放在同一个哈希条目中（临接链表）

局限性：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
哈希索引也不支持部分索引列匹配查找
不支持范围查询，只支持等值查询
访问哈希索引的数据非常快，除非有很多哈希冲突

自适应哈希索引：当innodb注意到某些索引值使用非常频繁时，会在内存中基于B-Tree索引纸上再创建一个哈希索引

SHA1() & MD5() 是强加密函数，会计算出非常长的字符串，浪费大量空间，比较时也比较慢。CRC32() 会出现大量的哈希冲突

为了防止哈希冲突，建议使用多重判断。例如：select xx from mytable where crc=CRC32('xxx') and word='ccc'

空间数据索引（R-Tree）

可以作为地理数据存储，会从所有维度来索引数据。也必须使用GIS相关的函数（如：MBRCONTAINTS()）

全文索引

查找文本中的关键词。适用于MATCH AGINST操作，而不是普通的WHERE条件查询

match 函数中指定的列必须与全文索引中指定的列完全相同

select film_id,title match(title,description) aginst('xxx') as relevance from tbl
where match(title,description) aginst('xxx');

alter table film_text add fulltext key(title);

布尔全文索引：全文索引只能用作全文搜索匹配，全文索引不存储索引列的实际值

dinosaur 包含“dinosaur”的行rank值更高

~dinosaur 包含“dinosaur”的行rank更低

+dinosaur 行必须包含“dinosaur”

-dinosaur 行不可以包含“dinosaur”

dino* 包含“dinosaur”开头的行单词的行rank值更高

where match(title,descirption) aginst('+factory +casualities' IN BOOLEAN Factory)

过程：先从索引中找出所有同时包含相关单词的索引条目，然后取出这些记录再判断是否是精确匹配短语

全文索引只有全部在内存中时，性能才非常好。update、delete操作时，全局索引的操作代价都很大

全局索引比普通索引更会产生碎片

向全文索引导入大量数据的时候，先通过DISABLE KEYS禁用全文索引，导入后使用ENABLE KEYS建立全文索引（管局索引更新消耗大）

其他索引类别：TokuDB

注意点：

如果查询中的列不是独立的，则mysql就不会使用索引

例如：select actor_id from sakila.actor where actor_id + 1 = 5;

2. 关于前缀索引：对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。一般长度7时比较合适

alter table sakila.city_demo add key （city(7)）

mysql 一般无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描

3. 多列索引：

实质是多个单索引的合并查找（“索引合并”）, 对索引的列顺序有要求

select film_id, actor_id from sakila.flim_actor where actor_id = 1 or film_id =1;

存储方式：

聚簇索引：小数目不同值，排序和范围查找

一个表只能有一个聚簇索引，innodb通过主键来聚集数据，如果没有定义主键，则会选择唯一的非空索引代替，如果没有这样的索引，innodb会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点：

可以把相关数据保存在一起，只需要从磁盘读取少数的数据页就能获取某个用户的全部数据
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点：

如果数据全部放在内存中，聚簇索引就不会有优势（提高了I/O密集型应用的性能）
插入速度严重依赖插入顺序
更新索引列的代价很高
存在“页分列”的问题
聚簇索引可能导致全表扫描变慢，尤其在行比较稀疏，页分列导致存储不连续的情况下
二级索引可能比想象中大
二级索引需要做重复的查找工作（自适应索引可以减少查找工作）

myisam与innodb在聚簇索引的结构上不同：

p165-p166

myisam: 每个列都是一个聚簇索引

innodb: 聚簇索引就是表

顺序插入：正在使用innodb可以定义一个代理键作为主键（最简单是ATUO_INCREMENT）。

插入到的记录存储在上一条记录的后面，当达到页的最大填充因子时，下一条记录就会写入到新的页中

在顺序主键高并发时会导致锁竞争，插入数据时会造成性能下降

覆盖索引：如果一个索引包含所有需要查询的字段的值，我们称为“覆盖索引”

mysql可以使用索引来直接获取列的数据，就不需要读取数据行了

覆盖索引的优点：

索引条目远小于数据行大小，极大减少数据访问量
因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少
由于innobd聚簇索引，覆盖索引对innodb表特别有用

覆盖索引中必须存储列的值，所以只能是B-Tree索引做覆盖索引

使用索引扫描来做排序：1. 排序操作 2. 按索引顺序扫描

索引排序的条件：

只有当索引的列顺序好order by 子句的顺序完全一致，并且所有列的顺序方向都一样时
如果关联多张表，只有order by 子句引用的字段全部都为第一个表时

压缩索引：myiam使用前缀压缩来减少索引的大小，从而让更多的索引可以放入到内存中（代价：操作变慢）

实现原理：先保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同的字节数和剩余的不同后缀部分，把这部分存储起来即可

冗余索引：在相同的列上按照相同的顺序创建的相同类型的索引（应避免，发现以后要移除）

create table (id primary key，unique(id),index(id))engine=innodb;
//唯一限制和主键限制都是通过索引实现的
//如果索引的类型不同，并不算重复索引，例如经常用到的key(col)和fulltext(col)

找到冗余索引的方法：1 common_schema 2 pt-duplicate_key-checher 3 pt-upgrade

未使用索引查找： 1 INFORMATION_SCHEMA_INDEX_STATISTICS 2 pt_index_usage

非聚簇索引：大数目不同值，频繁更新的列

聚簇索引 & 非聚簇索引

聚簇索引存储记录在物理上连续存在（修改慢，查询快） B+树，一定是唯一索引
非聚簇索引是逻辑上连续

Mysql使用B+树的原因：

高度原因：三次IO查找到数据
磁盘预读，局部性原理

索引失效：

(1) or，%开头

(2) 多列索引，不用第一部分

(3) 列型是字符串，不加引号

(4) 全表扫描比索引快（小表查询）

(5) not in，not exsit

(6) B-tree（is null 不会走，is not null会走），位图索引（is null 、is not null 都会走）

维护索引和表：

找到并修复损坏的表：

check table

repaire table

通过不做任何操作的alter重建表：alter table innodb_tbl engine=innodb;

如果表出现了损坏，一定要找到损坏原因（一般不会损坏）

更新索引统计信息：

record_in_range(): 通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录

info(): 返回各种类型的数据，包括索引的基数

打开INFORMATION_SCHEMA或者show table status 和 show index 或客户端自动补全功能都会触发索引统计信息的更新

减少索引和数据的碎片：

有三种类型的数据碎片：

1.   行碎片：数据行被存储为多个地方的多个片段中

2.   时间碎片：逻辑上顺序的页或行在磁盘上不是顺序存储的

3.   剩余空间碎片：数据页中存在大量空余空间

减少方法：

1.   optimize table

2.   导出再导入方式重新整理数据

3.   alter table xxx engine=yyy;

主从复制（读写分离）

主从复制过程：

master将数据变化记录到二进制日志中（log-bin）
slave将master二进制日志事件拷贝到它的中继日志
slave重做中继日志中的事件，将master上的改变反映到她自己的数据库中。两端一样

备份mysql数据库

备份类型：

完全备份
部分备份：增量备份、差异备份

热备份：读写不受影响
温备份：读可执行、但不能写
冷备份：读写都不能执行

备份工具：

mysqldump ：逻辑备份工具，支持温备，完全备份，部分备份
cp，tar等归档复制工具：物理备份工具
lvm2、snapshot：几乎热备，借助文件系统管理工具进行备份
mysqlhotcopy：冷备，仅支持MyISAM存储引擎
xtrabackup：innoDB / XtraDB 热备工具，支持完全备份，增量备份。备份过程快速、可靠；不会打断正在执行的事务；压缩功能节约磁盘空间；自动实现备份检验；还原速度快

备份策略：

直接cp、tar复制数据库文件: 数据量小，直接复制
mysqldump +　复制bin logs: 数据量还行，mysql完全备份，定期备份达到增量备份的效果
lvm2快照 +　复制bin logs
xtrabackup: 数据量很大，不过分影响业务运行

无影风Victorz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
【知识梳理】Mysql知识梳理

数据库连接池：c3p0 dbcp数据仓库的星型架构，雪花模型：上卷：从当前数据往上回归上层数据下钻：从当前数据展开下一层数据数据库范式：第一范式：无重复列第二范式：1 表必须有主键 2 没有包含在主键的列必须完全依赖主键，而不能只依赖一部分第三范式：2NF + 不能有传递依赖范式越多，表越多反范式：用空间换取时间，把数据冗余在多个表，避免表之间的............
复制链接

扫一扫

专栏目录