Mysql总结

Mysql包含哪些模块

客户端，连接器，分析器，优化器，执行器，存储引擎

一条sql语句的执行流程

1. 客户端先通过连接器连接到MySQL服务器
2. 连接器进行权限验证
3. 先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器
4. 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器
5. 优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好
6. 执行器执行对存储引擎的查询操作

myisam 和 innodb的区别

myisam

• 不支持事务、行锁、外键
• 索引和数据是分开存储的
• 一般用于有大量查询少量插入的场景

innodb

• 支持事务、行锁、外键
• 基于聚簇索引建立，索引和数据存储在一起
• 并且通过MVCC来支持高并发

Mysql的索引结构

B+树：

• 是一颗M叉树，每个节点存储多个数据，并将尽量将节点设置为磁盘页大小，充分利用磁盘的预读功能，每次读取磁盘页时读取一整节，有利于数据缓存，减少IO次数
• 叶子节点存储数据，非叶子节点存储索引
• 所有叶子节点形成有序链表，便于范围查询

优点：

• 单一节点存储更多的元素，使得查询的IO次数更少
• 所有查询都要查找到叶子节点，查询性能稳定
• 所有叶子节点形成有序链表，便于范围查询

聚簇索引和非聚簇索引的区别

聚簇索引

• 聚簇索引以主键id作为索引列，叶子节点存储主键id值和行数据
• InnoDB存储引擎以聚簇索引组织数据
• 一张表只能有一个聚簇索引
• 假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引

非聚簇索引

• 非聚簇索引只保存索引列的值和主键id值
• 以非聚簇索引作为查询条件时，还需要回源到聚簇索引上检索一遍
• 一张表可以有多个非聚簇索引

建索引的原则

1. 为经常作为查询字段的列建立索引
2. 与其他表进行连接的字段上应该建立索引
3. 建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引，非要对大的varchar字段建立索引可以指定索引长度
4. 合理使用复合索引。如：使用一个字段查询后使用另一个字段排序
5. 尽量选择区分度高的列作为索引。不能有效区分数据的列不适合做索引列，如性别等枚举值
6. 索引列不宜过多，会影响数据更新操作效率，占存储空间

不走索引场景

• 对索引字段进行函数运算或算术表达式计算
• 隐式转换，输入的数据类型与数据库字段数据类型不一致
• null值判断，is null、is not null不走索引
• 反向条件不走索引，!=、<>、not in
• or条件两边存在不是索引的字段
• like查询以%号开头
• 复合索引不符合最左前缀匹配

• mysql估算使用全表扫描比使用索引快

  • Mysql查询优化器估算的代价包括CPU代价和IO代价

其他走索引的情况：
范围查询，a > x and a < y
between and，a between x and y
in，in取值范围过大可能不走索引，多列in不走索引？(a,b) in ((1,2),(3,4))

索引突变

一般突变为走时间索引，执行优化器计算到走时间索引需要扫描的行数小于目标索引

解决：使用语法固化索引

索引优化

order by

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

order by 覆盖索引中的字段，升序、逆序都能利用索引排序

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

sql优化的步骤

• 慢查询日志定位问题sql，或运维监控定位问题sql
• 在cow库执行sql，使用Explain分析sql执行计划，首页关注索引的使用情况。考虑新建索引，固化索引，调整语法
• 关注rows参数，分析是否请求了不必要的数据，是否扫描了其他多余的记录
• 分析是否一次查询出的数据过大。考虑分多次查询
• 分析表结构是否合理，如：索引字段列过长，字段太多
• 分析表数据量是否太大。可考虑分库分表，归档历史数据
• 分析语法使用情况，是否关联太多表，子查询是否使用过多，sql太复杂可考虑拆分sql

使用EXPLAIN关注的参数

id: 各个子查询的执行顺序

id越大优先级越高，越先被执行
id相同执行顺序由上往下

select_type： 子查询的查询类型

type： 访问类型(非常重要，可以看到有没有走索引，如果是ALL河index需要优化)

• ALL 扫描全表数据
• index 遍历索引
• range 索引范围查找
• ref 使用非唯一索引查找数据
• consts 使用唯一索引查找数据

possible_keys： 可能用到的索引
key： 用到的索引
key_length： 索引长度
rows： 故事扫描到的记录数，并不是一个准确的值
extra： 额外信息

• Using index：使用覆盖索引
• Using where：使用了用where子句来过滤结果集
• Using filesort：使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化
• using temporary：联表查询排序时注意，表示数据库的排序基础是临时表

大表数据查询，怎么优化

1. 优化语法、sql语句+索引；
2. 限定查询范围，务必禁止不带任何限制范围条件的查询语句
3. 加缓存，memcached, redis；
4. 主从复制，读写分离；
5. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
6. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

一条sql执行很慢怎么分析和定位？

1、大多数情况下很正常，偶尔很慢

• 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘
• 执行的时候，遇到锁，如表锁、行锁

2、一直执行的很慢

• 没有用上索引
• 数据库选错了索引
• 查询出的结果集太大
• 表数据太多

事务

定义：数据库的事务是指一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败

事务的基本特性和隔离级别

特性：ACID

原子性：指的是一个事务中的操作要么全部成功，要么全部失败
一致性：一致性是指执行事务前后的状态要一致，可以理解为数据一致性
隔离性：隔离性侧重指事务之间相互隔离，不受影响，这个与事务设置的隔离级别有密切的关系
永久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中

隔离级别：
读未提交：可能会读到其他事务未提交的数据，也叫做脏读
读已提交：只会读取已经提交的事务数据；两次读取数据可能不一致，叫不可重复读；oracle默认级别
可重复读：读取快照，两次读取数据一致；做插入操作时还是可能出现幻读；mysql默认级别
序列化：加锁串行执行

ACID靠什么保证？

• A原子性，由undo log保证，undo log记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
• C一致性，由原子性、隔离性、持久性最终保证数据的一致性
• I隔离性，由写锁和MVCC来保证
• D持久性，由内存 + redo log + 磁盘来保证，mysql修改数据同时在内存和redo log记录这次操作，系统异步将内存中数据刷入磁盘，宕机的时候从redo log恢复

Mysql事务的实现原理

同上

Mysql的几种日志文件

mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志（redo log、undo log）、binlog几大类

binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog的几种录入格式：

• statement：

  • 记录修改数据的sql语句
  • 不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能
  • 在某些情况下会导致主从数据不一致，比如执行sysdate()等时间函数。

• row：

  • 仅记录哪条数据被修改了
  • 某些批量操作可导致大量行改动（尤其是alter table的时候会让日志暴涨），因此这种模式的日志量很大
  • 不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题

• mixed：

  • 一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

binlog使用场景

• 主从复制。在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
• 数据恢复。通过使用mysql binlog工具来恢复数据。
• 同步表数据
• 其他定制业务操作。如读取binlog发kafka消息同步大数据HBase

binlog刷盘时机

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次commit的时候都要将binlog写入磁盘；
• N：每N个事务，才会将binlog写入磁盘。

redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

redo log日志的作用：

• mysql的Innodb引擎以B+树组织数据，Mysql往磁盘写入数据的位置在物理上不连续，需要使用随机IO写入性能太差，为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里，然后使用后台线程去做缓冲池和磁盘之间的同步。

  1、Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
  2、一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

• 引入Boffer Pool后，如果宕机，缓存池里的数据会丢失

• 因此mysql设计了redo log，redo log用来记录数据的修改信息

  • redo log采用了大小固定，循环写入的方式，实现了对磁盘的顺序写入，IO效率高
  • 宕机后还未从缓存池刷入磁盘的数据可以从redo log恢复

undo log

undo log记录数据的逻辑变化，一条INSERT语句，对应一条DELETE的undo log，一条UPDATE语句，对应一条相反的UPDATE的undo log

作用：

• 事务回滚或发生错误时，通过undo log回滚到事务之前的数据状态
• MVCC实现的关键。当数据被修改后，通过undo log读取之前版本的数据

Mysql锁的类型有哪些

读锁：又称共享锁，加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁，lock in share mode为加共享锁读，select默认不加锁读
写锁：又称排他锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁

写锁按照锁定的粒度可分为：
表锁：一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作
行锁：

行锁可分为：
记录锁：普通的行锁
间隙锁 Gap Lock：锁住间隙，防止幻读
临键锁 Next-Key Lock：记录锁和间隙锁的组合

按锁定的时机可分为：
悲观锁：
乐观锁：Mysql通过一致性视图MVCC机制实现读写并发

隔离等级对加锁的影响

• 读未提交：不加锁
• 读已提交：加记录锁
• 可重复读：对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入，导致幻读。
• 序列化：从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降，不建议使用。

不同 SQL 语句对加锁的影响

不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况：

• SELECT ... 语句正常情况下为快照读，不加锁；
• SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
• SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
• 常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
• 常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚。

执行update时以非索引字段作为查询条件会有什么问题？

对于非索引字段进行update或select .. for update操作，代价极高。所有记录上锁，以及所有间隔的锁。
对于索引字段进行上述操作，代价一般。只有索引字段本身和附近的间隔会被加锁。

所以update、delete语句用不上索引是很恐怖的

MVCC机制

MVCC叫做多版本并发控制

作用：

• 实现了多个事务并发操作下，读操作的非阻塞
• 可重复读隔离级别下，能防止查询时幻读的发生

RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）

实现原理：

• mysql表中的每行数据隐藏保存了两列，创建时间版本号和删除时间版本号
• 每开始一个事务，系统会自动分配一个版本号，且该版本号是递增的
• 查询数据时，会对比当前事务版本号和数据的版本号大小
• 如果发现事务的版本号小于数据版本号，说明此时数据被其他事务锁定或修改，转而从undo log中分析出该记录以前的数据

幻读

幻读是指在同一个事务中，存在前后两次查询同一个范围的数据，第二次查询到了其他事务插入的行

为什么会出现幻读？
行锁只能锁定存在的行，针对新插入的操作没有限定

间隙锁

间隙锁，是专门用于解决幻读这种问题的锁，它锁的是行与行之间的间隙，能够阻塞新插入的操作。
间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

间隙锁之间是不冲突的，间隙锁会阻塞插入操作
间隙锁在可重复读隔离级别下才是有效的

MVCC真的解决了幻读？

测试前数据：

事物 1	事物 2
begin	begin
select * from dept
-	insert into dept(name) values("研发部")
-	commit
update dept set name="财务部"(工作中如果不想被辞退一定要写where条件)
commit

根据上面的结果我们期望的结果是这样的：

id  name
1   财务部
2   研发部 

但是实际上我们的经过是：

本来我们希望得到的结果只是第一条数据的部门改为财务，但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。

分布式事务

• 最大努力通知
• TCC事务补偿方案
• 基于可靠消息的最终一致性方案

幂等

重复调用多次产生的业务结果与调用一次产生的结果相同

分库分表

数据量级：亿级
表数据量多少时建议分库分表：1000万

分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平

垂直分库
基于现在微服务拆分来说，都是已经做到了垂直分库了

垂直分表
如果表字段比较多，将不常用的、数据较大的等等做拆分

水平分表
根据业务场景来决定使用什么字段作为分表字段(sharding_key)

水平分表策略：

Hash取模：集群扩展时需要进行数据迁移，比较麻烦。
范围分片：可能存在负载不均问题，新数据都存在新库，但一般访问率较高
时间分片：

分库分表会引入的问题

分布式事务问题
跨库join问题
跨节点oder by、group by以及count等聚合函数问题
数据迁移，容量规划，扩容等问题
自增ID问题

分表后的ID怎么保证唯一性？

因为我们主键默认都是自增的，那么分表之后的主键在不同表就肯定会有冲突了。有几个办法考虑：

1. 设定步长。比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。
2. 分布式ID。自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
3. 分布式自增ID。待补充

分布式自增ID生产策略

• oracle sequence:

  • 基于oracle的SEQ.NEXTVAL来获取一个ID
  • 优势：简单可用 缺点：需要依赖oracle数据库

• mysql id区间隔离:

  • 不同分库设置不同的起始值和步长，比如2台mysql，就可以设置一台只生成奇数，另一台生成偶数. 或者1台用0~10亿，另一台用10~20亿
  • 优势：利用mysql自增id 缺点：运维成本比较高，数据扩容时需要重新设置步长

• 基于数据库更新＋内存分配：

  • 在数据库中维护一个ID，获取下一个ID时，会对数据库进行ID=ID+步长 WHERE ID=XX，拿到100个ID后，在内存中进行分配
  • 优势：简单高效 缺点：无法保证自增顺序

• 使用redis的incr命令：

  • 最好多设置几个key，设定不同初始值和步长，避免单点
  • 若想完全保证自增顺序，可加一个list记录使用失败的序列值，取序列时先检查失败列表中有没有，有则优先使用

分表后非sharding_key的查询怎么处理？

1. 建映射表。建立一张查询字段与分库key的映射表。从映射表查到分库key，再用分库key去查数据
2. 打宽表，让es提供查询服务。适用于对数据实时性要求不高的场景，把数据同步到离线（实时）数仓，再让es提供查询服务。

怎么提高数据库的读性能

• 建立索引
  对于一主多从的结构，还可以在主库不建索引，不同的从库建不同的索引，如业务库和CMS库对不同字段建索引
• 分库分表
• 主从复制、读写分离
• 加缓存

主从同步

主从同步怎么解决数据的一致性问题

• 忽略
• 强制读主
• 选择性读主：插入数据后记录一个1秒的缓存key，读数据时检查这个key，若存在则读主库

多机房多活

标准情况下，生产数据库都是主备组合，主备配置一致，紧急情况下主机异常时，可以启用备机，平时备机均处于闲置状态

目前的架构：
服务器同城双活，数据库一主多从

数据库做多活存在的问题：
数据异地同步延时问题，即使用专线，延时也可能很高

数据库多活折中方案：
写同一个主库，读不同的从库，这样数据不一致问题就变成了主从之间的同步的延时问题

其他

mysql中 in 和 exists 区别

in语句先执行子查询in，再将子查询结果与外表做笛卡尔积
exists语句是对外表作loop循环，每次loop循环再对exists内表进行查询

如果查询的两个表大小相当，那么用in和exists差别不大。
如果一个表大一个表小，则子查询表大的用exists，子查询表小的用in。

not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

varchar与char的区别

char的特点

char表示定长字符串，长度是固定的；

如果插入数据的长度小于char的固定长度时，则用空格填充；

因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

对于char来说，最多能存放的字符个数为255，和编码无关

超大分页查询问题

示例sql：

select * from t5 order by text limit 100000, 10;

采用这种SQL查询分页的话，从200万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。

优化方式
1、使用join语句延迟关联

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

利用join子查询和覆盖索引，在子查询中只查主键id，并完成排序，在主查询中再使用主键id查询出数据