MySQL进阶篇

一、存储引擎

（一）、MySQL体系结构

连接层：‌主要负责客户端和服务器之间的连接处理、‌授权认证以及相关的安全方案。‌服务器会为每个安全接入的客户端验证其所具有的操作权限。‌ 

服务层：‌实现大多数的核心服务功能，‌如SQL接口，‌完成缓存的查询、‌SQL的分析和优化，‌以及部分内置函数的执行。‌所有跨存储引擎的功能也在这一层实现，‌如过程、‌函数等

存储引擎层：‌真正负责MySQL中数据的存储和提取。‌服务器通过API和存储引擎进行通信。‌不同的存储引擎具有不同的功能，‌用户可以根据自己的需要选择合适的存储引擎。‌MySQL支持多种存储引擎，‌如MyISAM和InnoDB，‌它们各有特点，‌满足不同的应用需求。‌

系统文件层：‌负责将数据库的数据和日志存储在文件系统之上，‌完成与存储引擎的交互。‌这一层包括日志文件、‌数据文件、‌配置文件等，‌其中日志文件记录了数据库的更改操作和其他重要信息，‌对于数据库的恢复和复制非常重要。‌

查询缓存层（‌如果启用）‌：‌如果查询缓存有命中的查询结果，‌查询语句可以直接从查询缓存中获取数据，‌提高查询效率。‌

（二）、什么是存储引擎

数据库中的存储引擎是数据库管理系统(DBMS)中负责执行数据存储、‌检索和管理操作的软件组件。‌它们各自有不同的特点和适用场景。‌常见的存储引擎包括MyISAM、‌InnoDB、‌Memory、‌Archive等。‌不同的存储引擎提供不同的存储机制、‌索引技巧、‌锁定水平等功能。‌

存储引擎是基于表的，而不是基于库的，所以存储引擎也可以称为表类型。

常见存储引擎对比分析：

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+Tree索引	支持	支持	支持
hash索引	--	--	支持
全文索引	5.6版本 之后支持	支持	--
空间使用	高	低	N/A/
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	--	--

（三）、存储引擎的选择

选择数据库存储引擎时，‌应根据具体的应用场景和需求来决定。‌如果应用需要高并发、‌事务支持和数据一致性，‌InnoDB是一个很好的选择。‌如果应用主要是读密集型且对事务完整性要求不高，‌MyISAM可能更适合。‌对于需要高速处理但数据安全性要求不高的场景，‌可以考虑使用MEMORY存储引擎。‌而对于特定的非结构化数据存储或高速缓存需求，‌MongoDB和Redis可能是更好的选择。

（四）、查看当前数据库支持的存储引擎情况

我当前为MySQL：

show engines;

创建表的时候指定存储引擎,首先创建两张表并且指定存储引擎

create table test(id int ,name varchar(10))engine=innodb;

create table test1(id int,name varchar(10))engine=memory;

然后查看表状态，发现存储引擎已经被指定了：

二、索引

（一）索引的概述

索引是数据库中一种重要的数据结构，‌用于加快数据的检索速度。‌它相当于图书的目录，‌允许数据库系统快速定位到表中的特定数据，‌而无需扫描整个表。‌索引的创建和维护确实会带来一些额外的开销，‌包括时间和空间上的消耗，‌但它们在提高查询性能方面的作用是显著的。

优点：‌

1. 提高查询速度：‌索引可以显著加快数据的检索速度，‌特别是对于大型数据库表，‌索引能够显著减少查询时间12。‌
2. 保证数据唯一性：‌通过创建唯一性索引，‌可以确保数据库表中每一行数据的唯一性1。‌
3. 加速表与表之间的连接：‌索引有助于加速表与表之间的连接操作，‌特别是在实现数据参考完整性方面特别有意义1。‌
4. 优化查询处理器：‌使用索引可以在查询过程中使用优化器，‌提高系统的性能1。‌

缺点：‌

1. 时间和空间的消耗：‌创建和维护索引需要一定的时间，‌并且索引本身也需要占用物理空间。‌随着数据量的增加，‌这种开销也会相应增加12。‌
2. 降低数据维护速度：‌当对表中的数据进行增加、‌删除和修改时，‌索引也需要动态维护，‌这可能会降低数据的维护速度12。‌

索引的分类包括直接创建和间接创建索引、‌普通索引和唯一性索引、‌单个索引和复合索引等。‌直接创建索引是通过CREATE INDEX语句直接在表上创建，‌而间接创建索引则通过定义主键约束或唯一性键约束来实现1。‌普通索引不保证索引列数据的唯一性，‌而唯一性索引则保证索引列中的数据唯一。‌此外，‌还有单个索引和复合索引之分，‌单个索引针对单个列，‌而复合索引涉及多个列。‌

在使用索引时，‌需要根据具体的应用场景和数据特点来合理选择。‌例如，‌对于经常用于查询的字段应该创建索引，‌但对于经常更新的表或数据量较小的表，‌过多的索引可能会带来不必要的维护开销，‌因此需要权衡利弊，‌合理使用索引。

（二）、索引的结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含一下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
R-tree(空间索引)	空间索引是MVISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Luce

 在这里推荐一个网站，数据结构可视化：Data Structure Visualization，帮助大家理解数据结构。

• B+Tree：MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
• Hash索引特点
  Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，1.无法利用索引完成排序操作2.查询效率高，通常只需要一次检索就可以了，效率通常要高

（三）、索引的分类

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

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

 （四）、索引的语法

查看索引(之前已经创建了索引)

show index from tb_user;

 创建索引

create index idx_age_1 on tb_user(age);
#模板语法
create [unique | fulltext] index index_name on table_name(index_col_name,...);

 删除索引

drop index idx_age_1 on tb_user;

(五)、性能分析

1、SQL执行频率

查看服务器状态信息：

show global status;

通过一下语句查看各种SQL语句的执行频率其中七个下划线

show global status like'Com_______'

2、慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

第一个参数为开启慢查询日志，第二个为设置慢日志时间为2秒，SQL语句执行时间如果超过两秒，就会视为慢查询，记录慢查询日志。

3、profile详情

查看时间消耗：

show profiles;

查看profile操作

SELECT @@have_profiling;

开启profiling

set profiling=1;

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

show profile for query query_id;

4、执行计划explain

执行计划各个字段含义：
ld：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
select type：表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBOUERY(SELECT/WHERE之后包含了子查询)等
type：表示连接类型，性能由好到差的连接类型为NUNL、system、const、eq_ref、ref、range、index、all。
possible key：可能用到的索引。

Key：实际使用的索引，如果为NULL，则没有使用索引。
Key len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows:MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered:表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

(六)索引的使用

1、最左前缀法则

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

2、索引失效

• 联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效。
• 如果在索引列上进行运算操作，索引将失效。
• 字符串不叫引号，索引也将失效。
• 模糊查询：如果仅仅只是尾部进行模糊匹配，那么索引不会失效，如果头部进行模糊匹配，索引将失效。
• or连接的条件：如果or前面有索引，后面没有索引，那么涉及到的索引都不会被用到。
• 数据分布影响：如果使用全表查询比使用索引快，则不会走索引。

3、SQL提示

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

推荐使用索引：use index:

explain select * from tb_user use index(idx user pro) where profession='软件工程'

忽略索引ignore index:

explain select * from tb_user ignore index(idx user pro) where profession = '软件工程'

强制使用force index:

explain select * from tb_user force index(idx user pro) where profession='软件工程”

4、覆盖索引 

在使用索引时尽量使用覆盖索引：查询使用了索引，并且要返回的列，已经在该索引中能够找到。

5、前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

create index index_name on table_name(colmn(n));

三、SQL优化

（一）插入优化

如果一个个插入，每一个插入都要用于数据库建立连接网络传输，效率比较慢，所以数据较多建议批量插入、手动提交事务、主键顺序插入（提高查询性能、‌提高数据插入效率、‌支持范围查询以及减少数据移动操作）；大批量数据使用：load进行载入。

（二）主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

主键设计原则：

1. 满足业务需求的情况下，尽量降低主键长度（主键索引只有一个，但是二级索引（非聚集索引）有很多，并且都是关联着主键，如果过长会增加IO成本）
2. 插入数据时，尽量选择顺序插入，选择使用auto_increament自增主键（防止页分裂）
3. 尽量不要使用UUID做主键或者其他自然主键，如身份证。（避免主键过长和非顺序插入）
4. 业务操作时，避免对主键的修改（会修改对应的索引结构）

（三）排序优化

• Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
• Using index： 通过有序索引顺序扫描直接返回有序数据，这种情况基于using index，不需要额外排序，操作效率高

尽量使用覆盖索引，多字段排序，一个升序一个降序，则在创建索引的时候就要指定好规则

（四）分组优化

• 在分组操作时，可以通过索引来提高效率

• 分组操作时，索引的使用也是满足最左前缀法则的

（五）分页优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

(六)、更新优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，因此update的时候条件尽量使用索引字段，否则行锁升级为表锁，并发性能就降低。

四、视图

（一）概述

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

（二）操作

1、创建视图

create or replace view  tb_v_1 as select id,name,age from tb_user where age>23 ORDER BY age asc;

2、查看视图

#查看视图数据
select * from tb_v_1;
#查看创建视图的语句
show create view tb_v_1;

3、修改视图

create or replace view  tb_v_1 as select id,name,age from tb_user where age>23 ORDER BY age asc;
#方式二
alter view tb_v_1 as SELECT * from tb_user where age>21 ORDER BY age;

4、删除视图

drop view if exists tb_v_2;

（三）视图检查

当使用with check option字句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以及其符号视图的定义。MySQL允许基于另一个视图创建视图，它会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认值为cascaded。

（四）视图的作用

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

• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

五、存储过程

（一）概述

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

（二）创建使用

#创建
create procedure p1()
begin
select id from tb_user where name='吕布1';
end;
#调用
call p1();

模板

#创建
create procedure 存储过程名称（【参数列表】）
begin
-----sql语句
end；
#调用
call 存储过程名称（【参数】）；

查看

#查询某个存储过程的定义
show create procedure p1;
#查询指定数据库的存储过程以及状态信息
SELECT * from information_schema.ROUTINES WHERE routine_schema='itcast';

删除

drop procedure if exists p1;

（三）变量

@@代表系统变量 @代表用户自定义变量，

局部变量：declare 变量名 变量类型 

六、触发器

（一）概述

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。使用别名old和new来引用触发器中发生变化的记录内容，这与其他数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

行级触发：语句影响了几行就触发几次

语句触发：一个语句不管影响几行，也触发一次

（二）使用

创建trigger

#准备一张表
create table tb_user_log(id int primary key auto_increment comment'日志id',
change_time datetime comment'记录改变时间',
op_type varchar(10) comment'操作类型insert/update/delete',
user_info varchar(100) comment'记录改变说明')comment '用户日志记录表';
#创建触发器
create trigger my_trigger
before update on tb_user for each row
begin
#逻辑：这里演示将其写入一个日志表
insert into tb_user_log values(null,now(),'update',concat(old.name,old.age,old.profession,old.gender));
end;
#测试
update tb_user set name='吕布' where id=1;

查看和删除触发器

show triggers;
drop trigger my_trigger;

 七、锁

（一）概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源（cpu、ram、I/O）的争用以外，数据也是一个供多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤为重要，也更加复杂。

（二）分类

MySQL中的锁，按照锁的粒度分，分为以下三类：

1. 全局锁：锁定数据库中的所有表

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

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

（三）全局锁

全局锁就是对整个数据库实现加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性。

（四）表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB等存储引擎汇总。

对于表级锁，主要分为以下三类：

1. 表锁

2. 元数据锁（meta data lock，MDL）

3. 意向锁

（五）行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个记录的锁，防止其他事务对此进行update和delete。在Read committed、Repeatable Read隔离级别下都支持

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

临键锁（Next-key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隔Gap。在RR隔离级别下支持。。