金九银十，金三银四（下）

大家好，我是小轩

接着上一篇

主要内容

• 大表怎么优化？
• MySQL 执行计划了解吗？
• bin log/redo log/undo log
• bin log和redo log有什么区别？
• 讲一下MySQL架构？
• 分库分表
• 什么是分区表？
• 分区表类型
• 分区的问题？
• 查询语句执行流程？
• 更新语句执行过程？
• exist和in的区别？
• MySQL中int(10)和char(10)的区别？　
• truncate、delete与drop区别？
• having和where区别？
• 什么是MySQL主从同步？
• 为什么要做主从同步？
• 乐观锁和悲观锁是什么？
• 用过processlist吗？

大表怎么优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；
2. 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；
3. 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

bin log/redo log/undo log

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

redo log

redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

undo log

除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

bin log和redo log有什么区别？

bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；

redo log只记录innoDB自身的事务日志。

bin log只在事务提交前写入到磁盘，一个事务只写一次；

而在事务进行过程，会有redo log不断写入磁盘。

bin log是逻辑日志，记录的是SQL语句的原始逻辑；

redo log是物理日志，记录的是在某个数据页上做了什么修改。

讲一下MySQL架构？

MySQL主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎：主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。

Server 层基本组件

连接器： 当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。

查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。

分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。

优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。

执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

分库分表

当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据切分可以分为两种方式：垂直划分和水平划分。

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

1. 主键出现冗余，需要管理冗余列；
2. 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
3. 依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

1. 分片事务一致性难以解决
2. 跨节点join性能差，逻辑复杂
3. 数据分片在扩容时需要迁移

什么是分区表？

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区表类型

按照范围分区

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

在/var/lib/mysql/data/可以找到对应的数据文件，每个分区表都有一个使用#分隔命名的表文件：

-rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
-rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list分区

对于List分区，分区字段必须是已知的，如果插入的字段不在分区时枚举值中，将无法插入。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash分区

可以将数据均匀地分布到预先定义的分区中。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

分区的问题？

打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE和一次删除多行数据。

维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。

所有分区必须使用相同的存储引擎。

查询语句执行流程？

查询语句的执行流程如下：

权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

select * from user where id > 1 and name = '大彬';

• 首先检查权限，没有权限则返回错误；
• MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
• 词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
• 两种执行方案，先查 id > 1 还是 name = '大彬'，优化器根据自己的优化算法选择执行效率最好的方案；
• 校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

更新语句执行过程？

更新语句执行流程如下：

分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '大彬' where id = 1;

先查询到 id 为1的记录，有缓存会使用缓存。

拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。

执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。

更新完成。

为什么记录完redo log，不直接提交，而是先进入prepare状态？

假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

exist和in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。

MySQL中int(10)和char(10)的区别？　

int(10)中的10表示的是显示数据的长度，而char(10)表示的是存储数据的长度。

truncate、delete与drop区别？

相同点：

• truncate和不带where子句的delete、以及drop都会删除表内的数据。
• drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。

不同点：

• truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
• 一般来说，执行速度: drop > truncate > delete。

having和where区别？

二者作用的对象不同，where子句作用于表和视图，having作用于组。

where在数据分组前进行过滤，having在数据分组后进行过滤。

什么是MySQL主从同步？

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

为什么要做主从同步？

• 读写分离，使数据库能支撑更大的并发。
• 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
• 数据备份，保证数据的安全。

乐观锁和悲观锁是什么？

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或CAS算法实现。

用过processlist吗？

show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：

id：线程ID，可以用kill id杀死某个线程
db：数据库名称
user：数据库用户
host：数据库实例的IP
command：当前执行的命令，比如Sleep，Query，Connect等
time：消耗时间，单位秒
state：执行状态，主要有以下状态：
Sleep，线程正在等待客户端发送新的请求
Locked，线程正在等待锁
Sending data，正在处理SELECT查询的记录，同时把结果发送给客户端
Kill，正在执行kill语句，杀死指定线程
Connect，一个从节点连上了主节点
Quit，线程正在退出
Sorting for group，正在为GROUP BY做排序
Sorting for order，正在为ORDER BY做排序
info：正在执行的SQL语句