MySQL进阶知识梳理

一.存储引擎

1.常见的存储引擎

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。
字符编码是基于创建的数据库的一个数据库一个字符编码。

常见的三个存储引擎常见的概念

存储引擎的存储的文件

InnoDB：

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构、数据和索引。
有一个默认的参数 innodb_file_per_table 是打开的，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件

MyISAM：

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

Memory：

xxx.sdi：存储表结构信息

2.存储引擎的选择

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

二.索引

索引优点：提高查询效率

索引缺点：占空间，减慢增删改的效率

索引结构

不同引擎对索引的支持

B+树的优势

1. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
2. 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

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

分类	含义	特点
聚集索引 (Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引 (Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

索引语法

1. 创建索引
create [ unique | fulltext ] index index_name on table_name (index_col_name,... ) ;2. 查看索引
show index from table_name ;3. 删除索引
drop index index_name on table_name ;

性能分析

1.SQL语句的执行频率

2.配置慢查询日志（就是超过多少时间的SQL语句会被记录到日志）

3.profile详情（查看所有执行过的sql语句的查询时间）

4.explain查看执行计划

索引使用

索引使用失效的原因：

1. 要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

2. 范围查询。联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效，改成 >= 或者  <= 就好了。

3. 不要在索引列上进行运算操作， 索引将失效。

4. 字符串类型字段使用时，不加引号，索引将失效。

5. 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配(及百分号不能在前面)，索引失效。

6. 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

7. 如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示(一个字段存在多个索引我们可以选择是那一个)

1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

2. ignore index ： 忽略指定的索引。

3. force index ： 强制使用索引。

索引覆盖和回表查询

回表查询：当查询所需的字段不完全包含在索引树中时，数据库需要通过索引定位到数据表的行，再从数据表中读取额外的字段。

索引覆盖： 查询所需要的所有字段都能从索引(这种索引是联合索引也叫多列索引)树中直接获取，查询不需要访问表中的数据页。

前缀索引

有的字段比较长，我们通过截取字段的前几个字符来建立索引。

语法：

create index idx_xxxx on table_name(column(n)) ;

三.SQL优化

总结：

四.视图/存储过程/触发器

1.视图

概念

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
作用

1). 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2). 安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
3). 数据独立

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

语法

1). 创建
create [or replace] view 视图名称 [(列名列表)] as select 语句 [ with [cascaded | local ] check option ]2). 查询
查看创建视图语句：show create view 视图名称；
查看视图数据：select * from 视图名称 ...... ;3). 修改
方式一：create [or replace] view 视图名称 [(列名列表)] as select 语句 [ with [ cascaded | local ] check option ]
方式二：alter view 视图名称 [(列名列表)] as select 语句 [ with [ cascaded | local ] check option ]4). 删除
drop view [if exists] 视图名称 [, 视图名称] ...

2.存储过程

概念

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

作用

1. 封装，复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。

2. 可以接收参数，也可以返回数据：再存储过程中，可以传递参数，也可以接收返回值。

3. 减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

基本语法

1). 创建
create procedure 存储过程名称 ([参数列表])
begin-- sql 语句
end ;2). 调用
call 名称 ([参数]);3). 查看
select * from information_schema.routines where routine_schema = 'xxx'; -- 查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称；-- 查询某个存储过程的定义4). 删除
drop procedure [if exists] 存储过程名称 ；

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

3.触发器

总结

五.锁

1. 全局锁

作用：

添加了全局锁所有的数据库就不能进行写操作了，用于全库逻辑备份。

特点：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

但是在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。这里涉及到 MVCC 

语法：

1). 加全局锁
flush tables with read lock ;2). 数据备份
mysqldump -uroot -p1234 itcast > itcast.sql3). 释放锁
unlock tables ;

2. 表级锁

①表锁

表共享读锁：

一个客户端添加了之后，大家都可以读，但是大家都不能写

表独占写锁：

只有加锁的用户可以读和写，其他客户端读和写都不行

语法

加锁：lock tables 表名... read/write。

释放锁：unlock tables / 客户端断开连接 。

②元数据锁（meta data lock，MDL）

这个MDL锁作用是：在开启事务还没有提交事务增删改查的时候不给你修改表的结构。

MDL锁分为：MDL共享锁 和 MDL排他锁；MDL共享锁分为：共享读锁和共享写锁。

对应 sql自动添加的锁	锁类型	说明
select、select ... lock in share mode	shared_read	与 shared_read、shared_write 兼容，与 exclusive 互斥
insert、update、delete、select ... for update	shared_write	与 shared_read、shared_write 兼容，与 exclusive 互斥
alter table ...	exclusive	与其他的 mdl 都互斥

语法

自动

③意向锁

意向锁的作用：

在开启事务还没有提交事务避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向锁分类：

1. 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
2. 意向排他锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。

语法

自动

3. 行级锁

① 行锁（Record Lock）针对索引添加的锁

作用

锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在 RC、RR 隔离级别下都支持。

行锁分类

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	执行语句添加相应的锁
INSERT ...	排他锁	自动加锁
UPDATE ...	排他锁	自动加锁
DELETE ...	排他锁	自动加锁
SELECT (正常)	不加任何锁
SELECT ... LOCK IN SHARE MODE	共享锁	需要手动在 SELECT 之后加 LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他锁	需要手动在 SELECT 之后加 FOR UPDATE

MySQL自己加锁的策略                                                                                                            默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。                                                                                        优化

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

② 间隙锁（Gap Lock）

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。在 RR 隔离级别下都支持。

③ 临键锁（Next-Key Lock）

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。在 RR 隔离级别下支持。

MySQL自己加锁的策略

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

优化

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
2. 索引上的等值查询(非唯一普通索引，因为是非唯一的所以数据的前后都必须锁住)，向右遍历时最后一个值不满足查询需求时，next-key lock 优化为间隙锁。
3. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

六.InnoDB引擎

1.架构

①逻辑存储接结构

②内存结构

③磁盘存储结构

④后台线程

整理

2.事务原理

①事务基础

②redo log

做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

③undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。

3.MVCC

①基本概念

当前读

快照读

②隐藏字段

③undolog版本链

undolog介绍和删除时机：

回滚日志，在 insert、update、delete 的时候产生的便于数据回滚的日志。
当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除。
而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

版本链：在读的时候会使用undo log 版本链来告诉我们那个版本是符合条件的。

④readview

ReadView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务 ID 集合
min_trx_id	最小活跃事务 ID
max_trx_id	预分配事务 ID，当前最大事务 ID+1（因为事务 ID 是自增的）
creator_trx_id	ReadView 创建者的事务 ID

RC级别的MVCC原理分析

使用生成的readview和undolog版本连访问规则来查看访问那个版本

RR级别的MVCC原理分析

复用第一次生成的readview

七.MySQL管理

1. mysql
   Mysql 客户端工具，-e 执行 SQL 并退出
2. mysqladmin
   Mysql 管理工具
3. mysqlbinlog
   二进制日志查看工具
4. mysqlshow
   查看数据库、表、字段的统计信息
5. mysqldump
   数据备份工具
6. mysqlimport/source
   数据导入工具

八.日志

1.错误日志

记录mysql在启动时出现的错误和异常

2.二进制日志(binlog)（记录DDL，DML语句）

3.查询日志（DDL，DML，DQL）

4.慢查询日志

要自己配置多慢的速度才算慢

九.主从复制

MySQL 复制的优点主要包含以下三个方面：

        主库出现问题，可以快速切换到从库提供服务。

实现读写分离，降低主库的访问压力。

        可以在从库中执行备份，以避免备份期间影响主库服务。

原理：基于 binlog 日志复制

十.分库分表

十一.读写分离