MySQL

一、MySql基础

1.基本认识与体系结构[帮助理解无需死记硬背]

• 关系型数据库
  基于行存储数据、结构化 schema、关联 relationship、SQL、支持事务 ACID
• 非关系型数据库
  非结构化的数据、表之间没有关联、最终一致性 BASE

1.1. sql执行流程：

1.2.模块分析：

连接层：[管理连接，权限认证]
服务层：
		解析器[词法分析，语法分析]→缓存[提高性能]
		  ↓
		优化器[执行计划生成，索引选择]
		  ↓
		执行器[操作引擎，返回结果]
存储引擎层：[存储数据，提供读写接口]

2.存储引擎

2.1.MySql是插件式存储引擎，是针对于表的(表类型，可以指定和修改不同的存储引擎)

• 1.MySql默认是InnoDB的存储引擎，支持事务，支持外键，支持行级别和表级别的锁，特殊索引方式可以减少IO提升查询效率（数据存在磁盘）。
• 2.Memory：数据存储在内存中。
• 3.其他存储引擎…
• 4.相关查询操作：

select version(); #查看MySql版本
show ENGINES; #查看MySql存储引擎
show table status; #方式1：查看所有库表信息
show table status from '库名称'; #方式2：查看指定库表信息。

2.2.InnoDB存储引擎

2.2.1.InnoDB架构图

2.2.2.内存结构（以下内容，纯文字，纯干货）

• 1.更新操作和读取操作前面流程是一样的都需要从存储引擎拿到数据在server层操作;不同的是拿到数据之后的操作，更新操作分为两层操作：server层操作和存储引擎层操作。
• 2.InnoDB是基于磁盘的存储，操作数据时首先会从磁盘加载数据到内存，而加载数据的最小逻辑单位称为[页]默认16KB大小，16384字节。
  • 1.默认读取16KB是为了减少IO操作，预先将磁盘的数据加载到内存的思想，称为磁盘的[预读]。
  • 2.并不是每一次操作数据都是将磁盘的数据加载到内存，InnoDB引入了缓冲池的技术，将磁盘读取的数据存入到[buffer pool]中，下次操作会先去内存(缓冲池)中找对应的页信息，如果存在直接使用不存在则去磁盘读取{5存在优化处理}，减少IO操作提升效率。
  • 3.所以在修改数据的时候先修改内存(缓存池)中的数据，此时内存(缓冲池)的页数据修改了而磁盘页数据没有更新，此时内存(缓冲池)中的数据页称为[脏页]。
  • 4.而InnoDB后台有专门的线程不断的将脏页刷到磁盘{7存在优化处理}，这个动作称为[刷脏]。
  • 5.针对内存(缓冲池)中不存在页数据信息需要进行磁盘操作的优化处理引入了[change buffer]，如果修改的数据所在的页不是唯一索引也不存在重复的情况不需要先进行IO操作，而是把修改的记录先放到这个区域，之后通过专门的线程刷到磁盘中。
  • 6.从change buffer到磁盘的这个动作称为[merge]，如果大部分都是唯一索引不需要判断重复性，且以写入为主不会写了之后马上读，就可以将change buffer参数调大。会触发更新的情况如下：
    • 1.访问磁盘上这个数据页的情况下会更新。
    • 2.后台线程会更新。
    • 3.数据库正常关闭的时候会更新。
    • 4.redo log写满的时候也会更新。
  • 7.为了防止宕机丢失数据引入了日志文件[redo log]，会在记录到buffer pool之后立即记录在日志文件redo log中，相当于对这个内存区域的持久化，降低了刷脏频率提高了吞吐量。
  • 8.那刷脏和redo log都是写磁盘为什么能提高效率？刷脏为随机IO，需要从扇区寻址；而记录到redo log为顺序IO；
  • 9.redo log的特点是：只为InnoDB提供了崩溃恢复的特性，并不是所有引擎都能用；它记录的不是结果而是逻辑过程；大小是固定的存满时需要将脏页全部刷到磁盘中，把redo log空出来才可以继续记录。
  • 10.总结：buffer pool记录的是各种页信息；change buffer提高了修改数据的效率；log buffer是redo log的一部分用来对内存区域做持久化的实现(崩溃恢复)。

2.2.3.磁盘结构（帮助理解无需死记硬背）

• 1.表空间是InnoDB存储引擎逻辑的最高层，所有数据都是放在表空间里的；分为五大类。
  • 1.系统表空间：在InnoDB默认共享的表空间，对应服务器上的文件是安装根目录的ibdata1文件。
    • 1.innodb data dictionary：数据字段
    • 2.doublewrite buffer：双写缓冲区
    • 3.change buffer
    • 4.undo logs
  • 2.独占表空间：一个表为一个表空间，存储的是数据和索引信息，其他信息还是在系统表空间。
  • 3.通用表空间：创建一个通用表空间，可以让多个表共用一个表空间。
  • 4.零时表空间：存储零时表数据，包括用户创建的零时表，还有磁盘内部的零时表，对应服务器上的文件是安装根目录的ibtmp1文件。
  • 5.RedoLog：崩溃恢复的特性。
  • 6.Undo表空间：
    • 1.默认是在系统表空间，也可以独立出来。
    • 2.undo log和redo log一起被称为事务日志。
    • 3.reod log可以实现持久性
    • 4.undo log可以实现原子性：记录事务发生之前的状态。
• 2.更新语句执行简化过程：update user set naem = ‘zhangsan’ where id = 1;(最好能记住这个过程)
  • 1.server层从内存或磁盘读取到这条数据，返回给server层。
  • 2.server层执行器把这行数据的值改为"zhangsan"。
  • 5.存储引擎层在buffer poll记录naem=“zhangsan”。
  • 3.存储引擎层记录undo Log事务信息。
  • 4.存储引擎层记录redo Log，并将这行记录状态设置为prepare预提交。
  • 6.server层写入bin log：记录DDL、DML操作，它的作用：数据恢复；主从复制。
  • 7.server层commit事务提交
  • 8.存储引擎层记录redo Log，将这个事务的状态置为commit状态。
  • 9.总结：
    • 1.先在buffer poll内存操作->然后记录到redo log->最后同步到磁盘库。
    • 2.redo log不是一次写入成功的，而是分为两阶段提交。
    • 3.bin log是server层记录的可以被所有存储引擎共用。
    • 4.先记录redlog再记录binlog最后commit。

3.MySql参数查看及调优[持续更新]

show status like '%Thread%'; #查看线程状态
show status like '%innodb_buffer_pool%'; #查看buffer pool状态

show variables like 'max_connections%'; #查看最大连接数，5.7的版本中最大可设置为10w连接数。
show variables like 'wait_timeout%'; #查看连接超时时间

show variables like '%innodb_buffer_pool_size%'; #查看buffer pool缓冲池大小，LRU算法最近最少使用的清除。
show variables like '%innodb_change_buffer_max_size%'; #查看change buffer缓冲池最大比例（相对于buffer poll的比例）。
show variables like '%innodb_log_buffer_size%'; #查看log buffer缓冲池大小。
show variables like '%innodb_doublewrite%'; #双写缓冲。
show variables like '%innodb_file_per_table%'; #独占表空间。
show variables like '%undo%'; #Undo表空间。
show variables like 'ft_min_word_len'; #全文索引最少字符数

set session/global max_connections = 512; #参数修改与参数级别：session当前会话 global全局；1.动态修改，服务端重启后修改还原。2.永久修改 vim /ect/my.cnf

create tablespace test123 add datafile '/var/lib/mysql/test123.ibd' file_block_size=16K engine=innodb; #创建通用表空间
create table t123(id integer) tablespace test123; #使用表空间

alter table tuser engine innodb; #修改存储引擎 myisam、innodb

二、MySql索引

1.索引基本认识

1.1.索引是什么

官方定义：索引是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
其实就是数据库的数据目录，可以更快的找到对应的数据。

1.2.索引类型与方法

在InnoDB中有：
1.Normal-普通索引(非唯一索引)
2.Unique-唯一索引(要求字段的值唯一)；特殊的唯一索引 Primary key(额外要求不能有空值)
3.Fulltext-全文索引(针对文本类型的字段，char/varchar/text)

# Fulltext-全文索引
#针对大文本匹配字符，like查询耗时且部分情况不走索引。
explain select * from tuser where content like '%家园北路%';
# 说是MySQL5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，所以我用的8.0.36最新版本。
alter table tuser add fulltext index fidx_content (content) with parser ngram;
# 至少要四个字符才会走全文索引
show variables like 'ft_min_word_len'; 
#全文索引查询
explain select content from customer_database.tuser where match(content) against ('家园北路' in natural language mode);
# 自然语言检索： in natural language mode
# 布尔检索： in boolean mode
# 查询扩展检索： with query expansion

2.索引的存储结构(数据结构演示)

索引是一种数据结构，也就对应着一种检索方法。

• 二叉查找树(BST-Binary Search Tree)

• 平衡二叉树(AVL Tree-Balanced Binary Search Tree)

  • 猜想一个节点需要包含索引的键值、数据磁盘地址、子节点的引用
  • 带来的问题就是每个节点都会发生IO操作，而InnoDB操作磁盘最小的单子[页]是16KB，所以一次IO操作的数据远达不到16384bytes，就会导致资源的浪费。
  • 所以就需要在一个节点存储更多的数据，当关键数越多也可以让指针数越多，也就是二叉变为多叉，专业术语称为[路数/度/degree]，降低深度，提高路数。

• 多路平衡查找树(B树-Balanced Tree)

  • 1.N个键值就有N+1个度，节点拥有的子树数量称为[度]。
  • 2.查找规则找到对应的磁盘地址，根据磁盘地址取数据文件中读取数据。
  • 3.子节点数即将超过定义最大度数(3)时，通过分裂合并保证树的平衡。

• 多路平衡查找树增强版(B+ 树-Balanced Tree)

  • 1.关键字和度数相等，节点拥有的子树数量称为[度]。
  • 2.根节点和内节点不会存储数据，只有在叶子节点存储数据，第一层命中也会到叶子节点获取数据。（扫库扫表能力更强，只需要遍历叶子节点；磁盘读写能力更强，接在节点的时候能访问到跟多的关键字；效率更加稳定，无论查询什么数据都去叶子节点获取3次IO）
  • 3.每个叶子节点之间增加了指向相邻叶子节点的指针，形成了有序链表的结构，可以提高查询范围查询的效率。（排序能力更强，因为叶子节点有指针的存在）
  • 4.B+Tree存储索引数据
    • 1.假设数据类型是bigint类型8bytes，而InnoDB的指针是6bytes，所以一个单元是14bytes。
    • 2.根节点和枝节点一个节点是16384bytes，所以16384/14可以存放1170个单元。
    • 3.那么有1170个键值就有1170个分叉，就是1170*1170=1368900路。
    • 4.一个叶子节点可以存16KB大小的数据，假设一个记录1KB，就可以存储16条这样的数据，就是1368900*16=21902400条记录(两千万的数据)。所以树的深度是2，只需要3次IO。
      

3.索引在存储引擎中的实现

show variables like 'datadir'; #查看索引与数据的存放目录
# xxx.frm 表结构定义文件
# xxx.MYI 索引文件 B+树的数据文件，通过拿到地址去MYD文件中获取数据
# xxx.MYD 数据文件 实际数据内容
# xxx.ibd Innodb文件，索引和数据都放在一起

1.MyISAM-主键索引、MyISAM-辅助索引：都是从MYI索引文件找到磁盘地址去MYD数据文件中获取数据。
2.InnoDB-主键索引：一个ibd文件，内节点存放的是键值，叶子节点存放的数据，主键决定着数据的顺序。也就是键值的逻辑(索引)顺序与数据的物理顺序相同称为[聚集索引]
3.InnoDB-辅助索引:存储的是索引和主键值，通过主键值再去主键索引中获取数据，所以需要遍历两颗B+树。
4.聚集索引：primary key、unique ley not null、rowid；所以一张表不可能没有索引也不可能没有聚集索引。

4.索引的创建原则和使用原则

4.1.列的离散度：公式 count(distinct(column_name)):count(*)

#创建普通索引
alter table tuser add index idx_gender (gender);
#创建唯一索引
alter table tuser add unique index idx_gender(gender);
#查看执行情况
explain select * from user where gender = 0; 

rows 预估扫描行数会非常多，所以不要在离散度低的字段上建立索引

4.2.联合索引最左匹配

#1.创建联合索引
alter table tuser add index comidx_name_phone(name,phone);
#2.会使用到联合索引
explain select * from tuser where name = 'zhangsan' and phone = '13866665555';
#3.会使用到，OPTIMIZER优化器组件会自动调整顺序
explain select * from tuser where phone = '13866665555' and name = 'zhangsan';
#4.会使用到，联合所以的B+树就是用name字段构建的
explain select * from tuser where name = 'zhangsan';
#5.不会使用
explain select * from tuser where phone = '13866665555';

1.建立联合索引要把用的最多的放到最左边。
2.建立联合索引 idx(A,B,C) 实际上是3个索引[A、A B、A B C]。
3.使用时不能跳过某个字段[如：B、A C 、BC、C]。

4.3.覆盖索引：非索引类型，而是使用索引的一种情况

#1.属于覆盖索引
explain select name,phone from user where name = '13866665555' and phone = 'zhangsan';
#2.属于覆盖索引
explain select name from user where name = '13866665555' and phone = 'zhangsan';
#3.属于覆盖索引
explain select phone from user where name = '13866665555' and phone = 'zhangsan';
#4.特殊情况，也属于覆盖索引，优化器最终觉得扫描这个联合索引并从中获取phone的值
explain select phone from user where and phone = 'zhangsan';
#5.不属于覆盖索引的情况，涉及到其他字段，只能去其他树中获取
explain select * from user where name = '13866665555' and phone = 'zhangsan';

1.Extra中存在Using index 就是属于覆盖索引的情况，不需要回表
2.所以在写SQL的时候需要用什么字段就写什么字段不要用*代替。

4.4.总结

• 创建索引的原则：
• 1.要基于where判断order排序和join的(on)的字段上创建索引。
• 2.索引的本质是此案上的B+树数据结构，所以个数不要过多，增删改会涉及B+树的数据调整导致增删改速度变慢，而且会浪费磁盘空间。
• 3.区分度(散列度)低的字段，例如性别，不要建索引。
• 4.频繁更新的值，不要作为主键或者索引。
• 5.联合索引把区分度(散列性)高的值放在前面。
• 6.创建联合索引，而不是单列索引，因为联合索引包含单列索引的情况。
• 7.过长的字段，怎么建立索引？(前缀索引，截取一定的字符，涉及到存储空间和区分度的把握)

• 使用索引的原则：
• 1.不要在索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式。
• 2.字符串不加引号，出现隐式转换。
• 3.like条件中前面带%，(索引下推 index condition pushing down，属于特殊情况)。
• 4.负向查询能用到索引吗？<> ! = NOT in。
  • 最终是由OPTIMIZER优化器决定，基于成本(I/O成本,CPU成本)的优化器(CBO-Cost Based Optimizer)。
  • RBO-Rule Base Optimizer，早期时Oracle的优化器

三、MySQL事务与锁

1.事务是什么

官方定义：事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
其实就是数据库中一次完整的操作。

2.事务四大特性

原子性：指一个或一组操作要么全部成功要么全部全部失败，不能出现既有成功又有失败的情况。(MySql中使用undo log来实现原子性)
一致性：指在事务执行过程中要始终保持数据的正确性和完整性。
隔离性：事务与事务之间的操作是隔离的，不相互干扰的。
持久性：事务一旦提交就是永久性的，不能因为其他原因而丢失数据。(MySql中使用redo log来实现持久性)

#是否自动开启事务
show variables like 'autocommit';
#手动开启事务，start等价begin，start可以提供更多参数
start transaction;
begin;
#事务回滚
rollback;
#事务提交
commit;

3.事务并发带来的问题

脏读：读取到了其他事务未提交的数据。(如果后续回滚了，就带来数据不一致的问题)
不可重复读：前后两次读取到了不一样的结果。(针对于其他事务已提交的update/delete操作)
幻读：前后两次读取到了不一样的范围。(针对于其他事务已提交的insert操作)
以上均为数据库读一致性的问题，必须由数据库提供一定的事务隔离机制解决。

4.事务隔离级别(SQL92 ANSI/ISO标准)

MVCC：是 “多版本并发控制” 读一致性的解决方案。当事务修改数据时，MVCC 会保留旧的数据版本，而不是直接覆盖它，这样其他事务仍然可以看到旧的数据版本。
在InnoDB中为每行记录都实现了三个隐藏字段：
1.DB_ROW_ID：6字节，行标识
2.DB_TRX_ID：6字节，插入或更新行的最后一个事务的ID，自动递增(创建版本号)。
3.DB_ROLL_PTR：7字节，回滚指针(删除版本号)
4.具体实现就是只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事务ID的行(或未删除)。(这句话简单理解就是在我事务开启之前的数据不管咋样我都能看到，不受其他事务操作影响)。

5.InnoDB锁的基本类型

• 1.表锁与行锁的区别
  • 锁定力度，加锁效率，冲突概率（表锁>行锁）
  • 并发性能（表锁<行锁）
• 2.MyISAM只支持表锁，InnoDB支持表锁和行锁
• 3.InnoDB中的锁类型：
  • 共享锁(S行锁)：又称读锁，多个事务对于同一数据可以共享一把锁，都能读数据但不能修改。
    加锁：select * from user where id=1 LOCK IN SHARE MODE;
    释放：commit/rollback;
  • 排他锁(X行锁)：又称写锁，如果一个事务获取了一行数据的排他锁，其它事务就不能获取该行的锁(共享锁，排他锁)。
    自动：delete/update/insert 默认加上排他锁。
    手动：select * from user where id=1 FOR UPDATE;
    释放：commit/rollback;
  • 意向共享锁(IS锁表锁)和意向排他锁(IX表锁)：要给一行数据加锁，必然先给这个表加上锁，作用就是提供一个标志，告诉其他然这个表至少有一行已经被锁定，提高加表锁的效率。
• 4.行锁锁住的是索引项

#获取锁超时时间
show variables like 'innodb_lock_wait_timeout';

6.InnoDB行锁算法

假设：tuser有id=1，3，7的数据。

1.记录锁：唯一性索引(唯一/主键)等值查询，精准匹配的时候。
select * from tuser where id = 1 for update; #手动开启事务的情况。
结果：锁住id=1的数据，此时更新id=1的数据时，需等待锁。

2.间隙锁：记录不存在的时候锁定。
select * from tuser where id > 3 and id<7 for update; #手动开启事务的情况。
select * from tuser where id = 6 for update; #手动开启事务的情况。
结果：锁住(3,7)的数据，此时插入id=4~6的数据时，需等待锁。
select * from tuser where id > 20 for update; #手动开启事务的情况。
结果：锁住(7,+∞)的数据，此时插入id=8及以上数据时，需等待锁。

3.临键锁：范围查询，包含记录和区间。
select * from tuser where id > 1 and id < 7 for update;
结果：锁住(1,7)的数据，此时插入id=2~6的数据或更新id=3的数据时，需等待锁。这里说是锁定(1,3]和(3,7]，但测试发现锁定的是(1,7)不排除版本问题。

四、MySQL优化思路与工具

1.配置优化

1.服务端连接数不够用：调整可用连接(max_connections)、调整连接超时时间(wait_timeout)
2.客户端引用连接池：DBCP、C3P0、Druid默认维护8个连接

2.架构优化

1.加入缓存的服务，可降低数据库的访问连接
2.主从复制：读写分离，减轻单台数据库的压力
3.分库分表:

• 3.1.垂直分库：将不同功能的表划分为不同的库
• 3.2.水平分库：将同一个功能的表划分为多个数据库

3.慢查询日志

1.开启慢日志配置：
slow_query_log = ON #慢日志开启
long_query_time = 5 #慢日志超时记录时间，5秒
slow_query_log_file = /var/lib/mysql/slowfile.log #慢日志路径
mysqldumpslow -s t -t 10 -g ‘select’ /var/lib/mysql/slowfile.log #找出查询最多的慢sql
2.查询服务端相关运行状态：
select @@profiling; #0代表关闭，1-代表开启，只在需要的时候开启，使用完之后关闭
show profiles; #查询所以的sql执行顺序
show profile for query 1; #查询某一条sql的执行过程
show processlist; #查看服务端线程的运行状态
show global status like ‘com_select’; #查看select的次数
show engine innodb status; #查看存储引擎运行的信息

4.执行计划 explain

• 1.id(sql执行顺序)：id不一样的时候是从大到小，否则就是从上往下的顺序。
• 2.select type(查询类型)：
  • SIMPLE-简单查询
  • PRIMARY-包含子查询的主查询
  • SUBQUERY-包含子查询的内查询
  • DERIVED-衍生查询，在得到最终查询结果之前用的临时表
  • UNION-先执行右边的table，再执行左边的table
  • UNION RESULT-union的结果
• 3.type(连接类型)：
  • system：查询系统表的时候只有一条数据(不常见)。
  • const：使用主键或者唯一索引查询到一条数据的情况(不常见)。
  • eq_ref：关联查询时用到了主键或者唯一索引的情况。
  • ref：查询用到了普通索引的情况。
  • range：查询条件是索引字段范围的扫描。
  • index：查询结果是索引字段的全部扫描。
  • all：全表扫描的情况。
  • null：没有访问表，不需要索引就可以得到结果的情况。
• 4.possible_key(可能用到的索引)、key(实际用到的索引)
• 5.key_len(使用索引的长度或字节数)
• 6.ref(执行过滤的时候用到的哪个常亮、哪个字段或是哪张表一起去筛选数据)
• 7.rows(预估扫描行数)
• 8.extra(额外的信息)

5.存储引擎和表结构优化

存储引擎的选择、表结构的创建、字段类型的选择

6.优化总结

• 1.越往下成本越高收效约小
  • Sql与索引
  • 存储引擎与表结构
  • 数据库架构
  • MySql配置
  • 硬件与操作系统
• 2.业务层面的优化
  • 1.例如：阿里的余额宝付款，减少外部调用。
  • 2.例如：双十一0点-1点只能查询当天发生的交易，不能查询历史的账单。
  • 3.例如：双十一业务提前。
• 3.关系型数据库已经满足不了的情况下，就要使用搜索引擎。

五、MySql数据类型