MySQL学习笔记2（体系结构）

一、MySQL体系结构

连接层、服务层、 引擎层、存储层

连接层：最上层。主要完成一些连接处理、授权认证、验证权限等

服务层：第二层。主要完成大多数的核心服务功能，如：SQL接口、SQL的分析和优化、内置函数的执行

引擎层：真正负责MySQL中数据的存储和提取，不同的存储引擎有不同的功能，可以根据需求选取合适的存储引擎。

存储层：主要将数据存储在文件系统上，并完成与存储引擎的交互

1. 存储引擎（默认是 InnoDB存储引擎）

存储引擎就是存储数据、建立索引等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以也被称为表类型

常用存储引擎：InnoDB、MyISAM、Memory

语法

在创建表时指定存储引擎

CREATE TABLE 表名（

          字段1 字段1类型 [COMMENT 字段1注释]，

          …

）ENGINE = INNODB [COMMENT 表注释];

查看当前数据库支持的存储引擎

SHOW ENGINES;

存储引擎特点：

InnoDB：

是一种兼顾高可靠性和高性能的同意存储引擎，是MySQL5.5之后默认的存储引擎

支持 事务、

行级锁（提高并发访问性能）、

外键（保证数据的完整性和正确性）

InnoDB引擎中的每张表都对应一个 表空间

表空间（Tablespace）中存放一个个段（Segment），段中存储一个个区（Extent）每个区大小1M，区中存储一个个页（Page）每个页大小16K，页中存储一个个行数据（Row），行数据中存储真正的数据

        

         MyISAM：是MySQL早期默认的存储引擎

         不支持事务、不支持外键

         支持表锁、访问速度快

         Memory：

         Memory引擎的表数据时存储在内存中的，断电会消失，只能将这些表作为临时表或缓存使用。

         内存存放、hash索引（默认）

1. InnoDB与MyISAM区别（面试）

1. 存储引擎选择

1. 索引（是在存储引擎层实现的）

是帮助MySQL 高效获取数据的有序数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

         优点：提高数据检索的效率，降低数据库访问磁盘的IO成本

                    通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

         缺点：索引也要占用空间

                    索引大大提高了查询效率，同时却也降低了更新表的速度，因为索引也要更新

         一个索引可以关联多个字段，称为联合索引（组合索引），关联一个字段称为单列索引

创建联合索引时要注意字段顺序

         索引的结构：

我们平时所说的索引，如果没有特别指明，都是B+Tree结构组织的索引

B树：

B+树：便于范围搜索和排序

1. hash：无法利用索引完成排序操作

hash索引特点：

1. hash索引只能用于对等比较（= 、in）不支持范围比较（between、>、<…）
2. 无法利用索引完成排序操作
3. 查询效率高，通常要高于B+Tree索引

MySQL中，Memory支持hash索引 而InnoDB中具有自适应hash的功能。Hash索引时存储引擎根据B+Tree索引在指定条件下自动构建的

1. 为什么InnoDB存储引擎选择使用B+Tree索引结构

相对于二叉树而言，层级更少，搜索效率更高

相对于B Tree而言，B Tree无论是叶子结点还是非叶子结点，都会保存数据，这样导致

一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。而B+树不管查找哪个数据都要到叶子结点才能找到对应的数据，此时搜索效率稳定，且B+树中叶子结点形成了一个双向链表，便于范围搜索和排序

         相对于hash，B+Tree支持范围匹配及排序操作

1. 索引分类

在InnoDB引擎中，根据索引的存储形式，又可以分为以下两种

聚集索引：B+树叶子结点下面挂的是这一行的行数据

二级索引：B+树叶子结点下面挂的是这一行的ID值（主键值）

聚集索引选取规则：

         如果存在主键，主键索引就是聚集索引

         如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引

         如果表没有主键，也没有合适的唯一索引，那么InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询：先在二级索引中找到该数据对应的主键值，然后拿着主键值去聚集索引中去找对应的行数据

   n表示key

1. 索引语法

创建索引

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名（要建立索引的列名）

查看索引

SHOW INDEX FROM 表名

删除索引

DROP INDEX 索引名 ON 表名

1. 索引使用

最左前缀法则：如果索引引用了多列（联合索引），就要遵循最左前缀法则

         查询时从索引的最左列开始，并且不能跳过索引中的列，如果跳了某一列，则索引中的一部分会失效（从中断处开始 之后的索引全部失效）

在范围查询时（>  <），范围查询右侧的列索引会失效，但 >=  <=时不会失效，所以在条件允许的情况下 尽量使用带  =  的

不要再索引列上进行运算操作，否则索引将失效

select * from user where substring(phone,10,2) = ‘15’;  #索引失效

字符串类型字段使用时，不加引号 会导致索引失效，因为会存在隐式类型转换

第一条不会失效，第二条失效

模糊查询时：

                  尾部模糊匹配，索引不会失效  like ‘软件%’

                  头部模糊匹配，索引失效   like ‘%工程

Or连接：用or分割的条件，如果or前的条件中的列有索引，而后面条件的列没有索引，那么所有设计到的索引都会失效

由于age没有索引，所以主键id的索引会失效

注意：

         如果MySQL评估使用索引比全表检索更慢，则不会使用索引（当满足条件的记录超过全表记录的一半时）

覆盖索引：尽量使用覆盖索引。

         指 查询中使用了索引，并且所要返回的列的信息，在使用的索引中可以全部找到，不需要进行回表查询，效率高 ，减少使用  select *  因为会回表查询

前缀索引：

         当字段类型为字符串时，并且存储了大量的信息（如文章），这会让索引变得很大，在查询时会浪费大量的磁盘IO，影响查询效率。

         此时可以只对字符串的一部分前缀建立索引，可以节约索引空间，提高查找效率，所建立的索引就称为前缀索引

语法：

create index 索引名 on 表名(列名（前缀长度）)

前缀的长度可以根据索引的选择性来决定 索引的选择性表示 所指定的前缀索引和数据表的记录总数的比值    选择性为1时 是最好的（范围：0~1），性能也是最好的

通过以下语句计算

前缀索引查询流程

拿到行数据后，去除email的数据，与传进来的数据进行匹配看是否一样，是则返回该行数据，然后再二级索引中查看下一个结点的数据是否符合要求，是则继续回表查询，不是则直接返回之前符合的数据，最后进行数据组装返回

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含多个列

如果存在多个查询条件，建议对涉及字段建立联合索引，而非单列索引

创建联合索引时要考虑字段顺序，因为联合索引要满足 最左前缀法则

1. SQL性能分析

慢查询日志：

         慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的所有SQL语句的日志

         MySQL的慢查询日志默认是关闭的，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

         #开启MySQL慢日志查询开关

         slow_query_log = 1

         #设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

         long_query_time = 2

         配置完毕后，重启MySQL服务进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

Profile详情:

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

         默认profiling是关闭的，需要手动开启

         查看当前MySQL是否支持profile操作

         SELECT  @@have_profiling;

         开启profiling（session/global）

         SET profiling = 1;

         查看每一条SQL的耗时基本情况

         show profiles;

         查看指定query_id的SQL语句各个阶段的耗时情况

         show profile for query query_id;

         查看指定query_id的SQL语句CPU的使用情况

         Show profile cpu for query query_id;

Explain执行计划：

Explain或者DESC 命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序

语法：直接在 SELECT语句前面加上 explain/dese 关键字

         如：Explain select * from user;

含义：

         Id:

select查询的序列号，表示查询中执行select子句或者操作表的顺序（id相同，执行顺序从上到下。Id不同，值越大，越先执行）

         select_type：

         表示select的类型  常见取值：↓

                  Simple：简单表，即不使用表连接或者子查询

                  Primary：主查询，即外层的查询

                  Union：union中的第二个或者后面的查询语句

                  Subquery：select/where之后包含了子查询 等

         Type：表示表连接类型，性能由好到差 null>system>const>eq_ref>ref>range>index>all

          优化时type尽量往前优化

NULL：查询时没有用到任何表时，是NULL  select ‘A’;

system：查询系统表时，是system

const：采用主键查询时（唯一索引）   如：根据id查

ref：采用非唯一索引时，是ref  如：查询根据name查询

index：用到索引时会对整个索引树进行扫描

all：全文扫描

         possible_key：显示可能应用在这张表上的索引，一个或多个

         key：实际使用到的索引，若为null表示没有使用索引

         key_len：表示索引中使用的字节数，为索引字段最大可能长度，并非实际长度，在不损失精度的前提下，越短越好

         rows：MySQL认为必须要执行查询的行数，在InnoDB引擎表中，是个估计值

         filtered：表示返回结果的行数占需读行数的百分比，越大越好

主要需要关注以下字段：

         Type、possible_key、key、key_len、rows、extra

1. SQL提示：是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示达到优化操作的目的

如：当一个字段有多个索引时，可以指定用哪个索引

语法：在select语句 from后的表后面 添加关键字：

use index(索引名):表示建议使用 所指定 的索引（MySQL会进行评估，看效率，可能会不接受你得建议）

ignore(索引名): 表示不要使用 所指定 的索引

force index(索引名)：表示必须使用 所指定 的索引

1. SQL优化

Select：

基础写法优化

1. 少使用select * ，尽量使用具体字段
2. 字符串要加单引号，否则索引会失效
3. 避免使用> 、<尽量使用 >=、<=
4. 使用or时要尽量保证两边的字段都有索引，否则索引会失效
5. 使用like时 尽量避免 %出现在头部
6. 不要对索引列进行运算操作，否则索引会失效
7. 在范围查询时（>  <），范围查询右侧的列索引会失效，但 >=  <=时不会失效，所以在条件允许的情况下 尽量使用带  =  的

建立合适的索引

1. 对高频筛选字段建立合适的索引
2. 一个表的索引不要太多，否则会影响修改和插入的性能
3. 使用联合索引时一定要遵循 最左前缀法则

替代优化

1. 使用连接（join）来代替子查询
2. 减少使用in和not in，使用exists和not exists

Insert：

1. 当我们要插入很多数据时，尽量使用批量插入，不要一条条插入，因为会频繁的与数据库建立连接，并且一次批量插入不要超过1000条

若要插入海量数据，使用load指令代替insert

1. 尽量手动去控制事务

主键优化：

1. 尽量降低主键长度
2. 尽量主键顺序插入，可以设置主键自增
3. 不要去修改主键

Order by：尽量优化为 Using index 这种情况不需要额外排序

                   Using filesort 将满足条件的数据行在缓冲区中进行排序

1. 根据排序字段建立合适的索引，多字段排序时也要遵循最左前缀法则
2. 尽量使用覆盖索引
3. 多字段排序，一个升序一个降序，此时要注意索引创建时的规则（ASC/DESC）
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区大小（默认256K）

Group by：

1. 可以通过索引来提高效率，索引也要满足最左前缀法则

Limit：

1. 可以创建覆盖索引，使用覆盖索引加子查询  进行优化

Update：会对所操作的行加锁，事务不提交就不会释放锁

1）更新字段时尽量根据索引字段更新，如果更新的字段没有索引，那么update会将整个表锁住，由行锁升级为表锁

Count：

1. 视图

是一种虚拟存在的表。视图中的数据来自于定义视图的查询语句，并且在使用视图时动态生成的。所用到的表称为基表

视图保存的是查询的SQL的逻辑，不保存查询结果

视图并不存储数据，所对应的数据是在基表中存储的，对视图进行增删改数据实际上是在对基表继续操作

视图是虚拟存在的表，我们可以像操作表一样操作视图

修改视图的方式一：关键在于  or replace 去替换原有视图

视图的检查选项：

LOCAL：加了检查选项才会检查，不加不检查，递归找下一级时也会判断加没加，加了检查，没加不检查

CASCADED：加了检查选项才会检查，不加不检查，递归找下一级时没加也会检查

更新包含 增 删 改

视图的作用：

         简单：

         可以简化用户对数据的理解，也可以简化他们的操作。比如多对多关系的表，可以把结果定义为视图，这样就不用每次查询都把条件写出来，直接查询视图即可

         安全：

         数据库可以授权，使用户只能查询和修改他们所能见到的数据

         数据独立：

         可帮助用户屏蔽真实表结构变化带来的影响

1. 锁

锁是协调多个进程或线程并发访问某一资源的机制。保证数据并发访问的一致性、有效性

按粒度分：全局锁、表锁、行锁

全局锁：锁定数据库中的所有表，加锁后使数据库实例边为 只读状态，可用于数据库备份

特点：

备份期间不能执行更新，基本上等于停摆

无法同步二进制日志

在InnoDB引擎中，可以在备份时加上 –single-transaction参数来完成不加锁的一致性数据备份，底层是快照读，不需加全局锁

表级锁：每次操作锁住整张表

         分为：表锁、元数据锁、意向锁

         表锁：

表共享读锁：当前客户端可以读，不能写会报错，其他客户端能读，不能写会阻塞

表独占写锁：当前客户端可以读，可以写，其他客户端不能读，不能写会阻塞

元数据锁：是系统自动控制的，在访问表时，会自动加上。

         用于保护元数据的一致性，在表上有活动事务时，不可对元数据进行写操作

当一个客户端开启事务对一张表进行增删改查时，会默认加上读、写锁，此时不能对该表进行修改表结构操作 会阻塞，直至对应客户端提交事务

         意向锁：避免行锁与表锁冲突，在InnoDB中引入了意向锁

意向锁之前

如果线程A对M表加了行锁，此时线程B要对M表加读/写锁，那么它会一行一行的判断该行是否加了行锁以及锁的类型，此时效率会很低，所以引入了意向锁

意向锁之后

当线程A对M表加了行锁时，会默认加一个意向锁，此时线程B对M表进行加读/写锁时，回先判断意向锁的类型来决定能否加锁成功，若不能加成功则会阻塞至线程A提交事务

行锁：每次操作锁住对应的行数据

1. InnoDB

内存架构：

Buffer Pool：缓冲池

         可以缓存磁盘上经常操作的真实数据，在执行增删改查时会先操作缓冲池中的数据（若缓冲池中没有，则从磁盘加载并缓存），然后以一定的频率将数据刷新到磁盘，从而减少磁盘IO，加快处理速度

         缓存池以页为单位，采用链表的数据结构管理页，根据状态可将页分为三种类型：  free page：空闲的，未被使用的

           clean page：被使用，数据未更改

           dirty page：脏页，数据被修改过，并且与磁盘中数据不一致，也就是未刷新到磁盘

change buffer：更改缓冲区（针对非唯一二级索引）

         在执行DML语句时，如果这些数据页不在 缓冲池中，不会直接操作磁盘，而会将数据变更存在 更改缓冲区中，在未来数据被读取时，在合并到缓冲池，然后把合并的数据刷新到磁盘

         意义：二级索引通常都是非唯一的，并且相对随机的顺序插入二级索引，删除和更新可能会影响索引树中不相邻的二级索引页，如果没一次都操作磁盘，会造成大量的磁盘IO，有了change buffer后，我们就可以在 buffer pool进行数据合并，减少磁盘IO

Adaptive Hash Index：自适应hash索引

用于优化 buffer pool 数据的查询，InnoDB引擎会监控对表上各索引页的查询，若观察到使用hash索引可以提高速度，则会建立hash索引，称为自适应hash索引，不需要人工干预，系统根据情况自动完成

         参数 adaptive_hash_index

Log Buffer：日志缓冲区

         用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小16MB，log buffer中的数据会定期刷新到磁盘。如果需要更新、插入、删除许多行的事务，可以增加log buffer的大小以节省磁盘IO

参数：innodb_log_size:缓冲区大下

                   Innodb_flush_log_at_trx_commit:日志刷新到磁盘的时机（0：每秒将日志写入并刷新到磁盘一次  1：每次事务提交时写入并刷新到磁盘  2：日志在每次事务提交时写入，并每秒刷新到磁盘一次）

磁盘架构

System Tablespace：系统表空间，是 change buffer的存储区域

File-Per-Table Tablespace：每个表的文件表空间，包含单个innoDB表的数据和索引，存储在文件系统上的单个数据文件中

         参数：innodb_file_per_table

General Tablespace：通用表空间 需要自己创建通用表空间，在创建表可以指定该表空间

         创建通用表空间：

create tablespace 名 ADD DATAFILE ‘文件名’ engine = engine_name;

指定表空间：

Create table 名 tablespace 名;

Undo Tablespace：撤销表空间 用于存储undo log日志。会在实例初始化时自动创建两个默认的undo Tablespace （16M）

Temporary Tablespace：临时表空间 ， innoDB存储用户创建的临时表等数据

Doublewrite Buffer Files：双写缓冲区  innoDB将数据页从 buffer pool刷新到磁盘前，先将数据页写如 doublewrite buffer files中，便于系统异常时恢复数据

Redo log：重做日志  用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲区（redo log buffer）

                                                     重做日志文件（redo log）

前者在内存中，后者在磁盘中。当事务提交后把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复

Redo log不会永久保存，每隔一段时间就会清理没用的redo log ，以循环方式写入

1. 后台线程：在合适的时机将innoDB缓冲池中的数据刷新到磁盘文件中

查看innoDB引擎状态信息

17、事务原理

原子性：undo log实现原子性

一致性：：undo log、redo log 共同实现一致性

隔离性：锁+MVCC实现 隔离性

持久性：redo log实现持久性

Redo log：重做日志 记录的是事务提交时数据页的物理修改。

                  当事务提交后会把所有修改信息存到该日志文件中，用于在刷新脏页到磁盘发生错误时，恢复数据

                 当一条记录要更新时，会先记录在redo log中，在适当的时机将其更新到磁盘

                 Redo log采用WAL（预写式日志），所有修改先写入日志，在更新到buffer pool，保证了数据不会因为MySQL宕机而丢失，从而保证了持久性

Undo log：回滚日志 用于记录数据被修改前的信息  作业：提供回滚和MVCC

         Undo log记录的是逻辑日志，记录与操作相反的sql语句 如：执行delete操作，会记录一条对应相反的 insert记录 当执行rollback时，就可以从undo log中读取相应内容进行回滚

         Undo log销毁：undo log在事务执行时产生。当事务提交时不会立即删除，因为还可能用于MVCC。 Insert产生的undo log日志只在回滚时需要，事务提交后可立即删除   update、delete不仅在回滚时需要在快照读也需要 不能立即删除

         Undo log存储：undo log采用段的方式管理和记录，存放在rollback segment回滚段中，内部包含1024个undo log segment

         当事务失败需要回滚时，就要用到 undo log进行回滚

锁+MVCC

         有了锁，当前事务在写数据时，会对其加上写锁，其他事务没得到锁就无法读和写，但我们只想让数据不能被别人修改，可以读 所以就需要MVCC

         MVCC他维护了一个数据的多个版本，使得读写操作没有了冲突，提供了非阻塞读的功能

1. MVCC（多版本并发控制）（面试）

维护一个数据的多个版本，使得读写操作没有冲突，提供了非阻塞读的功能，MVCC的具体实现需要依赖数据库的：三个隐式字段（TRX_ID、ROLL_PTR、ROW_ID）、undo log、readView

当前读：读取的是最新版本，读取时还要保证其他事物不能修改当前记录，会对读取的记录进行加锁

快照读：简单的select就是快照读，读的是记录数据的可见版本，有可能是历史数据

         RC下：每次select都生成一个快照读

         RR：开启事务后的第一个select才是快照读，之后的都是读的第一个

         Serializable：快照读会退化为当前读

Undo log版本链：

Readview：读视图

         是快照读SQL执行时MVCC提取数据的以及，记录并维护当前活跃事务的ID（未提交）