最全面的MySQL知识点总结

前言

最近在回顾之前学的知识点，mysql部分涉及的东西很多，所以想写写文章记录一些重要的知识点，方便以后回顾，同时也分享给大家，如果文章中有描述的不对或不足的地方，欢迎指出和交流。

一、架构原理

1、基础架构

1.1、架构概览

二、存储引擎

关于存储引擎这边只接受InnoDB引擎，其他引擎用的不多，所以了解的也不是很深。

1、存储结构

1.1、总体存储结构

1.2.1、Buffer Pool

缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率，其中page有三种状态类型：

1、free page： 空闲page，未被使用
2、clean page： 被使用page，数据没有被修改过
3、dirty page： 脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

三种链表结构：

1、free list： 表示空闲缓冲区，管理free page
2、lru list： 表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少据据，占37%。
3、flush list： 表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作

1.2.2、Change Buffer

写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，会先进行磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。不过，仅适用于非唯一普通索引页，如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。

• change buffer 读过程：
  

1.2.3、Adaptive Hash Index（自适应哈希索引）

用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

1.2.4、Log Buffer

日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘。

其中可以优化的参数：

• innodb_log_buffer_size： 将这个参数调大，可以减少磁盘IO频率
• innodb_flush_log_at_trx_commit： 控制日志刷盘行为，默认为1。
  • 0 ： 每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer–>OS cache，刷盘OS cache–>磁盘文件），最多丢失1秒数据；
  • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作；
  • 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

1.3、磁盘结构

1.3.1、表空间（Tablespaces）

• 系统表空间（The System Tablespace）
  包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数
  innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)
• 独立表空间（File-Per-Table Tablespaces）
  默认开启，每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。
• 通用表空间（General Tablespaces）
  通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间
• 撤销表空间（Undo Tablespaces）
  InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0（0表示使用系统表空间ibdata1，大于0表示使用undo表空间）
• 临时表空间（Temporary Tablespaces）

1.3.2、数据字典（InnoDB Data Dictionary）

由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据

1.3.3、双写缓冲区（Doublewrite Buffer）

在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区，使用Doublewrite 缓冲区时建议将innodb_flush_method设置为O_DIRECT

innodb_flush_method有三个值可以配置：

• fdatasync(默认)：fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文件与redo log的缓存信息
• O_DIRECT：O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件
• O_DSYNC：表示写日志时，数据都要写到磁盘，并且元数据也需要更新，才返回成功。

1.3.4、重做日志（Redo Log）

重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据，默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示

1.3.5、撤销日志（Undo Logs）

用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中

1.4、线程模型

1.4.5、Master Thread

主线程，负责调度其他各线程，优先级最高，它会在每隔一段时间执行一些操作，比如：

• 每1秒的操作：
  • 1、刷新日志缓冲区，刷到磁盘
  • 2、合并写缓冲区数据，根据IO读写压力来决定是否操作
  • 3、刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）
• 每10秒的操作：
  • 1.刷新脏页数据到磁盘
  • 2.合并写缓冲区数据
  • 3.刷新日志缓冲区
  • 4.删除无用的undo页

1.5、数据文件

1.5.1、文件存储结构

2、redo log(InnoDB引擎特有的）

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)

2.1、工作原理

2.2、存储结构

是环形结构，固定大小

• wirte是指把日志写入到文件系统的page cache，fsync才会将数据写入磁盘
• wirte和fsync的时机是由参数sync_binlog控制的

1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；
3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

3.4、三种模式

3.4.1、Row Level 行模式

日志中会记录每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，由参数binlog_format=’row’控制
优点： 在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题
缺点： row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

3.4.2、Statement Level（默认）

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行，由参数binlog_format=’statement’控制
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上所执行的语句的细节，以及执行语句的上下文的信息。
缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现，比如now()。

3.4.3、 Mixed 混合模式

在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3.5、清除

通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉

二、索引

1、索引类型

索引类型可以按照不同的划分方式进行划分，主要的划分方式有以下几点

1.1、按索引存储结构划分

可分为：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

1.2、按应用层次划分

1.2.1、普通索引

最基本的索引类型，基于普通字段建立的索引，没有任何限制，创建方式：

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

1.2.2、唯一索引

索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引，创建方式：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

1.2.3、主键索引

一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键，创建方式：

CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1.2.4、复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效

创建方式：

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

1.2.5、前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

• 通过计算不同的区分度来决定使用多长的前缀

select count(distinct email)as l from user;
select count(distinct left(email,5))as l1 from user;

注意事项： 使用前缀索引就用不上覆盖索引的优化了

1.2.6、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持，创建方式：

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名)

使用方法：全文索引有自己的语法格式，使用 match 和 against 关键字

select * from user where match(name) against(‘aaa’);
select * from user where match(name) against(‘a*’ in boolean mode);

注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

1.3、按索引键值类型划分

主键索引、辅助索引（二级索引）

1.4、按数据存储和索引键值逻辑关系划分

1.4.1、聚集索引（聚簇索引）

InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引

1.4.2、非聚集索引（非聚簇索引）

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构

2、索引原理

2.1、定义

是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作，索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价

2.2、B+Tree结构

• 结构： 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。叶子节点包含了所有的索引值和data数据。叶子节点用指针连接，提高区间的访问性能
• 自适应哈希索引： InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页

2.3、索引分析与优化

2.3.1、EXPLAIN命令重要参数解析

• select_type（查询的类型）

SIMPLE ： 表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询

• type（表示存储引擎查询数据时采用的方式）

ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用>、>=、<、<=、in等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快

• possible_keys: 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称
• key: 表示查询时真正使用到的索引，显示的是索引名称
• rows: 估算SQL要查询到结果需要扫描多少行记录
• key_len: 表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引
• Extra:

Using where
表示查询需要通过索引回表查询数据。
Using index
表示查询需要通过索引，索引就可以满足所需数据。
Using filesort
表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
Using temprorary
查询使用到了临时表，一般出现于去重、分组等操作

2.3.2、覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做覆盖索引也叫索引覆盖。（回表查询：通过二级索引查询主键值，然后再去聚簇索引查询记录信息）

2.3.3、最左匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

2.3.4、 LIKE查询优化

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引

2.3.5、NULL查询优化

NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等

2.3.6、索引与排序优化

• filesort排序
  • 原理： 先把结果查出，然后在缓存或磁盘进行排序操作，效率较低
  • 算法：
    • 双路排序： 需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据
    • 单路排序： 从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量
  • 会走filesort排序的场景

1、WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等），explain select id from user where age>10 order by name; //对应(age,name)索引
2、对索引列同时使用了ASC和DESC，explain select id from user order by age asc,name desc; //对应(age,name)索引
3、使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引，explain select id from user order by name,age; //对应(name)、(age)两个索引
4、WHERE子句与ORDER BY子句，使用了不同的索引。explain select id from user where name=‘tom’ order by age; //对应（name)、(age)索引
5、WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式。explain select id from user order by abs(age); //对应(age)索引

• index排序
  • 原理： 是指利用索引自动实现排序，不需另做排序操作，效率会比较高
  • 会走index排序的场景：

1、 ORDER BY 子句索引列组合满足索引最左前列。explain select id from user order by id; //对应(id)、(id,name)索引有效
2、WHERE子句+ORDER BY子句索引列组合满足索引最左前列。explain select id from user where age=18 order by name; //对应(age,name)索引

2.4、查询优化

2.4.1、慢查询优化

• 全表扫描：explain分析type属性all
• 全索引扫描：explain分析type属性index
• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
• 频繁的回表查询开销：尽量少用s

  来源：IRON_MAN_KD

  声明：本站部分文章及图片转载于互联网，内容版权归原作者所有，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！