MySQL进阶

一、存储过程

存储过程： 将一些处理的逻辑写进一个存储过程，将这个逻辑处理的过程存储在数据库中，在Java中调用即可

例如：添加用户先判断该账户是否存在，存在返回，不存在新增用户。如果这个场景使用Java首先的话需要和数据库进行两次交互

优点：将逻辑保存在数据库中，执行速度快
缺点： 在大型项目中，数据量庞大，会涉及到分库分表。因为存储过程和数据库耦合度高，存储过程和数据库绑定，所以使用越来越少
阿里巴巴规约：[强制] 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

二、MySQL架构

mysql体系结构

1.连接层

最上层是一些客户端连接服务，主要完成一些连接，授权，认证，及相关的安全方案

2.服务层

第二层架构主要完成大多数的核心服务功能，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等。
在该层，服务器会解析查询并创建对应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后再执行相应的操作。如果是select语句，服务器还会查询内部的缓存

3.引擎层

存储引擎层，该层真正负责了MySQL中数据的存储和提取，服务器通过API与存储引擎通信。不同的存储引擎具有的功能不同，我们可以根据实际的需要进行选取

4. 物理文件存储层

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

三、MySQL引擎

MySQL中的数据使用不同的技术存储在文件中。不同的技术使用了不同的存储机制、索引技巧并且提供了广泛的不同的功能。通过选择不同的技术，能够改善应用的整体功能

这些不同的技术及配套的相关功能在MySQL中被称为存储引擎（也被称为表类型）。MySQL默认配置了许多的存储引擎，可以预先设置或者在MySQL服务器中启用。

查看支持的引擎：show engines;
查看表引擎：SHOW TABLE STATUS LIKE '表名'
修改引擎
方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
方式 2:建表时指定 CREATE TABLE 表名(…)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;
这里讲述两种常用的存储引擎

1.MyIsam

MyIsam没有提供对数据库事物的支持，也不支持行级锁和外键，因此当插入和修改操作时需要锁定整个表，效率会低一些；支持全文检索；存储表的总行数

2.InnoDB默认的存储引擎

InnoDB是一个事务性的存储引擎，有行级锁和外键约束，支持全文检索。MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引；支持主键自增，不存储表的总行数

四、索引

1.为什么需要索引

当数据库中的数据越来越多时，因为查询是从id=1的位置开始遍历搜索的，所以当表中存储多条记录，会导致查询的时间非常长，无法接受这样的时间

2.什么是索引

上面谈到逐条查询的时间复杂度过大，要减少查询的时间复杂度，而索引就是一种查询时间复杂度非常小的数据结构，使用B+树实现，B+树就是多路查找的平衡树的升级版，并且叶子节点之间使用双向指针联系，这种数据结构的查询时间复杂度十分底，并且在范围查询上十分具有优势

3.索引的优势

提高了检索效率，降低了数据库IO成本
通过索引对数据库进行排序，降低了排序的排序的成本

4.索引劣势

由于索引不仅要维护我们的表数据，还要维护索引的结构，在更新表时，Mysql不仅要保存数据，还要改变索引的结构。B+树的增加，删除都会涉及到树的结构改变

5.索引创建原则

5.1需要的场景

主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询与其他表关联的字段，外键关系建立索引
查询排序字段
分组中的字段

5.2不需要索引的场景

表记录太少
经常增删的表
不作为查询条件字段
数据重复且平均的字段

6.索引分类

主键索引： 设置为主键后数据库自动创建索引
ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
删除主键索引
ALTER TABLE 表名 drop PRIMARY KEY;
单值索引： 即一个索引只包含一个列，一个表可以拥有多个单值索引
创建：CREATE INDEX 索引名 ON 表名(列名);
删除：DROP INDEX 索引名
唯一索引： 索引值必须为一，允许为空
创建：CREATE UNIQUE INDEX 索引名 ON 表名(列名);
删除：DROP INDEX 索引名 ON 表名;
组合索引（复合索引）： 一个索引包含多个列，复合索引比单值索引的开销小
创建：CREATE INDEX 索引名 ON 表名(col1,col2);
删除：DROP INDEX 索引名 ON 表名;
为什么需要复合索引，单值索引不可以吗？
单值索引是只包含一个索引的列，而组合索引是包含两个或两个以上字段的索引。并且在建立联合索引时，顺序非常重要
下面这个Sql语言在列X，列Y，列Z上建立了一个组合索引。
CREATE INDEX 索引名 ON 表名(列名X,列名Y,列名Z);
其实这样相当于建立了三个索引，分别是：
1.单值索引(列X)，2.组合索引(列X，列Y)，3.组合索引(列X，列Y，列Z)
为什么会产生这样的结果呢？
索引的底层存储是靠B+树作为底层结构，当建立上述的联合索引时，会先使用X列进行排序，当X列的数据重复时，再根据Y列排序，最后是Z列排序，所以才会产生组合索引的最左匹配

组合索引的最左前缀原则？
假设表中有a,b,c三列，为a,b创建组合索引，在使用组合索引的列作为查询条件时，必须要出现最左侧列为条件，否则组合索引不生效

EXPLAIN SELECT * FROM test WHERE a = 231 AND b = 32 --生效
EXPLAIN SELECT * FROM test WHERE b = 231 AND a = 32 --生效
EXPLAIN SELECT * FROM test WHERE a = 231 AND c = 32 --生效
EXPLAIN SELECT * FROM test WHERE b = 231 AND c = 32 --不生效

全文索引： 需要模糊查询时，一般索引无效，这时候就可以使用全文索引
全文索引的实现非常复杂，同时也是我们使用最多的功能
例如我们逛B站，我们不会点击不同的视频分区区查询自己感兴趣的内容，而是通过顶部的所有引擎直接搜索我们感兴趣的内容，这样直接搜索所有的视频数据，并且还是模糊匹配，显然耗费的时间是巨大的，所以就使用了全文索引来进行大范围的模糊匹配。这里只讲述大概的实现，因为原理深究没完没了。
首先全文索引是靠倒排索引实现的，我们首先了解为什么要叫倒排索引，我们前面检索数据都是通过主键获取内容的，而倒排索引是通过文档中的关键词筛选符合条件的文档id，与我们之前查询的顺序相反，所以取了这个名字。具体的实现首先是通过分词技术将所有文章中的关键词所有出来。例如“中国经济正在好转”被分割成“中国”，“经济”，“正在”，“好转”，这样讲所有文档中的关键词查找出来，然后记录每个关键字存在于那哪些文档中，这种关键词和文档id之间的映射关系就是倒排索引

CREATE FULLTEXT INDEX index_name ON test(NAME) WITH PARSER ngram;
EXPLAIN SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(搜索词)

查看表中的所有所有索引：show index from 表名

主键索引和唯一索引的对比？
主键可以作为外键，而唯一索引的列不可以。
主键不可为空，而唯一索引的列可以

单值索引与复合/联合/组合索引
组合索引相对于单值索引的好处？
组合索引比对每个列都创建索引更有优势，索引创建的越多越浪费磁盘空间，其次就是组合索引因为多个索引绑定在一起，增加，修改，删除，引起索引结果改变的操作耗费的时间更短

7.索引的数据结构

我们平时使用的平衡二叉树，二分搜索树，红黑树，这些树高度过高导致查询效率较低，
而B树和B+树一个节点可以存储多个数据，并且一个节点还能拥有多个孩子，降低了树的高度，提高了查询效率。而B树无法解决范围查询的问题，B+树由于叶子节点之间使用双向指针连接，非常完美的解决了范围查询问题。
B+树特点：

• 排好序的，一个节点可以存储多个数据
• 非叶子节点不存储数据，只存储索引，并且可以放置多个索引
• 叶子节点存储数据
• 叶子节点之间都有指针相互连接

8.聚簇索引和非聚簇索引

聚簇索引： 找到了索引就找到了查询数据，那么这个索引就是聚簇索引，innoDB中的主键就是聚簇索引。innoDB中也存在非聚簇索引，就是除主键索引之外的其他索引。这部分索引要先找到改数据对应的主键，然后通过主键索引再查找数据。由于其他索引没有直接和数据相关，索引要通过主键索引再查找数据。为什么辅助索引不直接和数据关联呢？这也是数据复用性的体现，如果我们为每一个索引都设置了相关联的数据，那么我们表中的数据就会被多次存储，这是一笔很大的存储消耗
非聚簇索引间： 索引的存储和数据是分离的，找到了索引并没有找到数据，而是和索引相关的主键再次查询
MySQL中的InnoDB引擎的索引和数据文件是存放在一起的，找到了索引就找到了数据，是聚簇式设计。而MyIsam引擎索引和数据文件没有存放在一起，是非聚簇式设计

9.回表查询

回表查询也就是二次查询，例如通过学号/姓名（这些字段）查询学生的详细信息，
首先通过学号查询到主键索引，然后通过主键索引找到学生数据
如果只是查询该学号是否存在，这种情况不需要回表查询

五、锁机制

两个事物的写操作之间会相互影响。隔离性要求在统一时刻只能有一个事物对数据进行写操作，可以通过锁机制来保证这一点
锁机制的基本原理：事物在修改数据之前，必须要先获取锁；获取锁之后，便可以修改数据表；事物操作器间，该部分数据时锁定的，其他事物修改数据，需要等待当前事务提交或者回滚释放锁

1.行锁

行锁是Mysql中锁粒度最小的一种锁，表示只针对当前操作的行加锁。行级锁可以大大减少冲突的概率。行锁粒度最小，由于加锁，释放锁，获得锁都需要消耗资源，所以行级锁的消耗最大。行级锁分为共享锁和排他锁

2.间隙锁

当我们使用范围条件检索数据时，InnoDB就会为符合条件的数据记录的索引项添加锁；对于在条件范围内但并不存在的记录，叫做“间隙”，InnoDB也会对这个间隙加锁

3.表锁

表锁是Mysql中锁粒度最大的一种锁，表示锁住档当前操作的整张表，实现简单，消耗资源少，操作冲突概率大，并发度低，被大部分引擎支持。最长使用的InnoDB和MyIsam都支持表级锁定。表锁分为表共享锁和表排他锁

共享锁（S）： 共享锁也称为读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上了S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直至T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A
做任何修改。
排他锁（X）： 排他锁又称为写锁。允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。若事务T对数据对象A加上了X锁，事务T可以读A也可以修改A，其他事务不能对A加任何锁，直至事务T释放A上的锁。update，delete，insert都会自动给涉及到的数据加上排他锁，select默认不会加任何锁类型，如果加排他锁可以使用 select ... for update语句，加共享锁可以使用select ... lock in share mode语句

六、MySQL事务

数据库事务若干数据库操作的集合，是一个不可分割的整体。
在MySQL中只有InnoDB引擎支持数据库事务

1.事务特性

事务必须满足四个条件（ACID）：原子性，一致性，隔离性，持久性。
原子性： 组成事务的所有数据库操作要么全部执行，要么全部都不执行。事务在执行过程中发生错误，会回滚到事务开始之前的状态
持久性： 事务处理完成之后，对数据的修改是永久的，即使系统故障也不会丢失
隔离性： 数据库允许多个并发事务同时对数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。事务隔离分为不同级别，包括读未提交，读提交，可重复读和串行化。
一致性： 数据的执行符合预期的结果。前面的原子性，隔离性，一致性都是为了保证数据的一致性

2.事务设置

默认情况下，MySQL启用自动提交模式。只要提交数据库操作，MySQL会自动隐式提交事务。
MySQL事务处理主要有两种方法：
1.用BEGIN，ROLLBACK，COMMIT来实现
BEGIN开始一个事务
ROLLBACK事务回滚
COMMIT事务提交
2.直接用SET改变MySQL的自动提交模式
SET GLOBAL autocommit=0;禁止自动提交
SET GLOBAL autocommit=1;开启自动提交
查看autocommit模式
show global variables like 'autocommit';

3.事务隔离级别

多个事务对同一个数据操作时，采用哪种隔离级别，达到想要的隔离效果
查看隔离级别：SELECT @@global.transaction_isolation,@@transaction_isolation;
设置隔离界别：SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
读 未提交 (read uncommitted)： 一个事务可以读取到另一个事务未提交的修改。这会带来脏读，幻读，不可重复读问题
脏读：读取到了未提交的数据
幻读：同一个事务中同一个查询条件的两次查询结果行数不一致
不可重复读：同一个事务同一个查询条件中的两次查询结果数据不一致
读 已提交： 一个事务只能读取到另一个事务已经提交的修改。其避免了脏读，仍然存在不可重复读和幻读问题。
可重复读： 同一个事物中多次读取相同的数据的数据返回都是一样的。其解决了脏读，幻读，不可重复读问题。
串行化： 当一个事物执行操作时，其他的事物不能执行

4.事物实现原理

Mysql的日志有很多种，如二进制文件、错误日志、查询日志、慢查询日志等，此外InnoDB引擎还提供了两种事物日志：redolog (重做日志) 和 undolog (回滚日志)。其中redolog用于保证事物的持久性；undolog则是事物原子性和隔离性实现的基础。
原子性实现
在底层使用一个undolog日志记录与之相反的操作，
例如：执行了删除操作，就记录一条增加操作
执行增加操作，就记录一条删除操作
执行修改操作，就记录一条与与之相反的修改操作
最后，只需要依次执行这些相反操作就能回滚到事物执行之前的状态了
持久性实现
在底层使用了一个redolog日志存储将要执行的数据库操作
当数据库执行过程中如果出现宕机情况，在mysql启动时执行redolog日志中的操作，保证数据的持久性
隔离级别实现原理
MVCC是什么？
MVCC（多版本并发控制Multi-Version Concurrent Control），是MySQL提高性能的一种方式，配合undolog和版本链，让不同事物的读-写，写-读操作可以并发执行，从而提高性能。
MVCC使得数据库不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMIT，REPEATABLE READ等隔离级别
InnoDB的MVCC是通过每行记录后面保存了两个隐藏的链实现的。一个保存了行的事物ID（TRX_ID），一个保存了行的回滚指针（ROLL_PT）。
TRX_ID：每次对记录进行修改时，都会把对应事物的id赋值给该列
ROLL_PT：每次对某记录进行修改时，都会把旧版本写进undolog中，然后让这个隐藏列指向旧版本信息的地方
对某条记录每次更新后，都会将一条旧数据放到undolog中，随着更新次数的增多，所有版本的ROLL_PT属性练成一个链表，我们将这个链表称之为版本链，版本的头节点存储该条记录最新的值。
读以提交级别： 称为当前读，当每个事物每次读取时，读取最新数据
可重复读界别： 称为快照读，当一个事务第一次查询时，会生成一个读视图，第二次查询时仍会从当前读视图中读取数据

七、SQL优化

我们通过sql查询数据库中的数据，优化sql可以提高sql的查询速度，优化我们系统的性能。

1.SQL优化方法

1.查询sql尽量不要使用select *，而是具体字段
节省资源，减少网络开销
可能用到覆盖索引，减少回表，提高查询效率

2.避免在where子句中使用or作为连接条件
例如：select * from user where id = 1 or salary = 2000
正例：使用union all把两个SQL结果合并
使用 or可能会导致索引失效，从而进行全表扫描

3.尽量使用数值代替字符串类型
例如：性别列，我们使用1代表男性，使用0代表女性，使用tinyint
因为引擎在处理查询时会逐个匹配字符类型，而数字型只需要比较一次即可

4.使用varchar代替char
varchar存储数据时，按照实体大小存储，而char类型，字符长度不足时，补空格
这样既节省了空间又提高了查询效率

5.应给在经常作为查询条件或者排序的字段添加索引

6.避免索引失效
6.1谨慎or关键词的使用
6.2in和not in也要慎用，对于in(连续的数值)，能使用between就不要使用in
6.3模糊查询也会导致全表扫描
6.4应该避免在wher子句中对字段进行函数操作，这将导致引擎放弃索引而进行全表扫描

7.inner join、left join 、right join，优先使用inner join
inner join内连接，只保留两张表中完全匹配结果集；a∩b
left join左连接，会返回左表中的所有行，(a∪b)∩a
right join右连接，会返回游标中的所有行，(a∪b)∩b

8.提高group by语句的效率
先过滤再分组

9.清空表时优先使用truncate
delete语句每次删除一行，并且在事务日志中记录删除的一行，而truncate通过释放存储表数据所拥的数据也来删除数据

10.表不宜连接过多，索引不宜太多

11.使用EXPLAIN分析SQL执行计划

2.执行计划

EXPLAIN
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理查询语句的。分析查询结果或是表结构的性能瓶颈

EXPLAIN使用

在select语句之前增加explain关键字，执行查询会返回执行计划的信息，而不是执行SQL
id
这是select查询序列号，id如果相同，可以认为是同一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行。
select_type
表示查询中每个select子句的类型
1.SIMPLE：简单查询，不适用UNION或子查询等
2.PRIMARY：子查询中最外层查询，最外层的select被标记为PRIMARY
3.SUBQUERY：子查询的第一个SELECT，结果不依赖于外部查询
4.DERIVED：派生表的SELECT，FROM子句的子查询
type
对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常见的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，性能从好到差）。
1.system：表中只有一行记录
2.const：表示通过索引依次就找到了
3.eq_ref：唯一性索引查询，对于每一个索引键，表中只有一条记录与之匹配
4.ref：非唯一性索引扫描，通过索引访问，返回匹配某个单独值的行，可能会找到多个符号条件的行
5.range：使用索引来检索给定范围的行
6.index：全表扫描，index和ALL区别为index类型只遍历索引树
7.ALL：遍历全表找到匹配的行
possible_keys
显示可能应用在这张表中的索引，一个或多个
查询到涉及到的字段若存在索引，就将索引列出，但不一定被查询实际使用
key
实际使用的索引。如果为NULL，则没有使用索引，或者索引失效
ken_len
表示索引中使用的字节数，可同构该列查询使用的索引的长度。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数
extra
额外的信息说明
1.Using filesort：当Query中包含ORDER BY操作，而且无法利用索引完成排序工作的时候，MySQL无法利用索引完成排序的操作称为“文件排序”。
2.Using where：表示使用到了索引，但是也进行了where过滤