MYSQL

目录

数据库之MYSQL相关知识

数据库基础知识

1、MySQL基本架构

2、连接器
进行数据查询前，第一步就是连接数据库，这时就是连接器和我们对接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接的时候会经过TCP握手，然后身份验证，输入用户名密码。

验证OK连上MySQL服务，这时我们处于空闲状态。

注：查看空闲连接列表：
show processlist，下图就是我在自己的数据库表执行命令的结果，其中的Command列显示为Sleep的这一行，就表示现在系统里面有一个空闲连接。

这里需要注意的是，我们数据库的客户端太久没响应，连接器就会自动断开了，这个时间参数是wait_timeout控制住的，默认时长为8小时。

除了重新连接（建立连接是比较麻烦），还可以使用长连接，但是需要注意：
使用长连接之后，内存会飙得很快，我们知道MySQL在执行过程中临时使用的内存是管理在连接对象里面的。只有在链接断开的时候才能得到释放，那如果一直使用长连接，那就会导致OOM（Out Of Memory），会导致MySQL重启，在JVM里面就会导致频繁的Full GC。

一般解决方案是：一般会定期断开长连接，使用一段时间后，或者程序里面判断执行过一个占用内存比较大的查询后就断开连接，需要的时候重连就好了。

或者，执行比较大的一个查询后，执行mysql_reset_connection可以重新初始化连接资源。这个过程相比上面一种会好点，不需要重连，但是会初始化连接的状态。

3、查询缓存
MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。大家是不是好奇同一条语句在MySQL执行两次，第一次和后面的时间是不一样的，后者明显快一些，这就是因为缓存的存在。他跟Redis一样，只要是你之前执行过的语句，都会在内存里面用key-value形式存储着。查询的时候就会拿着语句先去缓存中查询，如果能够命中就返回缓存的value，如果不命中就执行后面的阶段。

注：但缓存的弊大于利，缓存的失效很容易，只要对表有任何的更新，这个表的所有查询缓存就会全部被清空，就会出现缓存还没使用，就直接被清空了，或者积累了很多缓存准备用来着，但是一个更新打回原形。这就导致查询的命中率低的可怕，只有那种只查询不更新的表适用缓存，但是这样的表往往很少存在，一般都是什么配置表之类的。

如果不想用缓存，或者用缓存，可以显示调用，把query_cache_type设置成DEMAND，这样SQL默认不适用缓存，想用缓存就用SQL_CACHE。（不过缓存在MySQL8.0之后就取消了。）

4、分析器
在缓存没有命中的情况下，就开始执行语句了，你写的语句有没有语法错误，这是接下来MySQL比较关心的点。那他会怎么做呢？会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了。

5、优化器
优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引，比如使用你的主键索引，联合索引还是什么索引更好。还有就是对执行顺序进行优化，条件那么多，先查哪个表，还是先关联，会出现很多方案，最后由优化器决定选用哪种方案。

6、执行器
第一步可能就是权限的判断，执行的时候，就一行一行的去判断是否满足条件，有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快。

数据库的慢日志有个rows_examined字段，扫描多少行可以看到，还有explain也可以看到执行计划，我们扫描了多少行。

储存引擎（即表类型）

MYSQL常用的两个储存引擎是MyIsam和InnoDB，主要的差别在于，MyISAM不支持事务处理等高级处理，强调的是性能，性能优先，执行速度比InnoDB快，而InnoDB支持事务处理和外部键等高级数据库功能。
总：InnoDB支持事务与外键和行级锁。而MyISAM不支持。

1. MyISAM ，是MYSQL默认的引擎，不支持行级锁和外键，因此当INSERT（插入）或update(更新)数据时即写操作需要锁定整个表，效率会低一些。但执行读取操作的速度很快，而且不占用大量的内存的储存资源。
2. InnoDB，底层存储结构为B+树，每个节点对应的InnoDB的一个page，page大小是固定的，一般设为16k。其中非叶子节点只有键值，叶子节点包含完成数据。比较适用于经常更新的表，适合处理多重并发的更新请求。
   ①MySQL架构图
   
   ②InnoDB架构图
   
   通常我们说的MySQL高性能高可靠，都是指基于InnoDB存储引擎的MySQL。
   InnoDB主要分为两块：

• InnoDB In-Memory Structures
• InnoDB On-Disk Structures
  内存和磁盘。

InnoDB内存架构
1、Buffer Pool
缓冲池是主存中InnoDB缓存被访问的表和索引数据的区域。

MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。

而这些数据存放的地方，就是Buffer Pool。

MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool是一个以页为元素的链表。

为什么是链表？因为和缓存一样，它也需要一套淘汰算法来管理数据。

Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存：

2、Change Buffer
3、Adaptive Hash Index
4、Log Buffer

Operating System Cache

InnoDB磁盘架构
1、表空间(Tablespaces)
2、Doublewrite Buffer

数据库索引

关于索引常考的面试题有：
索引有哪些数据类型？
索引是怎么样的一种结构？
哪些字段又适合索引呢？
B+树的优点？
聚合索引和非聚合索引的区别？
为什么说索引会降低插入、删除、修改等维护任务的速度？
…
##1.索引有哪些数据类型？
答：Hash 和 B+树，在设计索引的时候，会发现索引类型是可以选择的。
①字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突，哈希表的特点就是可以快速的精确查询，但不支持范围查询。
eg：
select * from table_1 where name = ‘xiaozhu’(支持)；
select * from table_1 where name > ‘xiaozhu’(不支持)。
题外话：