MySQL在实际开发中的一些经验

向右看齐 2022-12-01 15:41 235阅读 0赞

## 1，MySQL的逻辑架构 ##

MySQL可以分为Server层和存储引擎层两部分。

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

## 2，redo log（重做日志）和 binlog（归档日志） ##

这两种日志有以下三点不同。  
1），redo log是InnoDB引擎特有的，负责存储相关的具体事宜，有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。  
binlog是MySQL的Server层实现的，所有引擎都可以使用，它主要做的是MySQL功能层面的事情。  
2），redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。  
3），redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

## 3，事务与隔离 ##

1），务的特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）  
2），多事务同时执行的时候，可能会出现的问题：脏读、不可重复读、幻读  
3），事务隔离级别：读未提交、读提交、可重复读、串行化  
4），不同事务隔离级别的区别：

*  读未提交：一个事务还未提交，它所做的变更就可以被别的事务看到
 *  读提交：一个事务提交之后，它所做的变更才可以被别的事务看到
 *  可重复读：一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的
 *  串行化：对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行

5），配置方法：启动参数transaction-isolation

## 4，数据库的索引 ##

*  索引的作用：其实就是为了提高数据查询的效率，就像书的目录一样。  
    索引的常见模型：  
    1，哈希表这种结构适用于只有等值查询的场景。  
    2，有序数组索引只适用于静态存储引擎。  
    3，二叉搜索树：为了维持O(log(N))的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。
 *  InnoDB 的索引模型：主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询，避免回表查询。  
    InnoDB采用的：B+树结构，B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
 *  主键索引的选择：自增主键是指自增列上定义的主键，插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索  
    引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

## 5，全局锁和表锁 ##

在MySQL 5.5版本中引入了MDL（metadata lock)，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

## 6，索引的选择 ##

创建唯一索引对应的字段也必须唯一。

*  对于查找操作，唯一索引只比普通多度一次，差别不大。
 *  对于更新操作，当这个记录要更新的目标页在内存中时，唯一索引只多一次判断，只会耗费微小的CPU时间。当更新的目标页不在内存中时，对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。

change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。有一种特殊场景，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说change buffer反而起到了副作用。

总结：对于普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

补充：redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

## 7，优化器选择索引 ##

用explain +sql查询语句，可以通过rows字段显示估计要扫描的行数。  
加了limit 1 能减少扫描多少行，其实优化器也不确定，【得执行才知道】，所以显示的时候还是按照“最多可能扫多少行”来显示。

对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。而对于其他优化器误判的情况，你可以在应用端用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

## 8，收缩数据库的表空间 ##

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。实际上，不止是删除数据会造成空洞，插入数据也会。更改其实是删除加插入，也会记录的复用，只限于符合范围条件的数据。而当整个页从B+树里面摘掉以后，可以复用到任何位置。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。而重建表，就可以达到这样的目的。

在MySQL 5.5版本之前，可以使用alter table A engine=InnoDB（alter table t  
engine=innodb,ALGORITHM=inplace;跟inplace对应的就是拷贝表的方式了）命令来重建表，但是有新的数据要写入的话，就会造成数据丢失，这个DDL不是Online的。

而在MySQL 5.6版本开始引入的Online DDL（生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中）DDL过程如果是Online的，就一定是inplace的；反过来未必

从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）；analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；optimize table t 等于recreate+analyze。

## 9，计算一个表的行数 ##

*  count(*)的实现方式  
    MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，如果加了where 条件的话，MyISAM表也是不能返回得这么快的；而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。  
    按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用count(\*)。

## 10，orderby的工作顺序 ##

*  场景：city字段建立了索引  
    select city,name,age from t where city=‘杭州’ order by name limit 1000 ;  
    通常情况下，这个语句执行流程如下所示 ：  
    1，初始化sort\_buffer，确定放入name、city、age这三个字段；  
    2，从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID\_X；  
    3，到主键id索引取出整行，取name、city、age三个字段的值，存入sort\_buffer中；  
    4，从索引city取下一个记录的主键id；  
    5，重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID\_Y；  
    6，对sort\_buffer中的数据按照字段name做快速排序；  
    7，按照排序结果取前1000行返回给客户端。  
    在数据量很大的情况下，适当的使用LIMIT 1对查询操作的优化效果还是相当明显的。  
    注意：如果以上表字段中username被设置为了索引的话，这个时候使用LIMIT 1在查询速度上没有明显的效果。

## 11，索引会失效 ##

*  第一种：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。  
    mysql> select count(*) from tradelog where month(t\_modified)=7;  
    改写成：mysql> select count(*) from tradelog where  
    \-> (t\_modified >= ‘2016-7-1’ and t\_modified<‘2016-8-1’) or  
    \-> (t\_modified >= ‘2017-7-1’ and t\_modified<‘2017-8-1’) or  
    \-> (t\_modified >= ‘2018-7-1’ and t\_modified<‘2018-8-1’);  
    select \* from tradelog where id + 1 = 10000  
    应该改写成 where id = 10000 -1
 *  第二种：隐式类型转换，mysql> select \* from tradelog where tradeid=110717;tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

## 12，查询慢可能的原因 ##

1），有100万行数据，其中有10万行数据的b的值是’1234567890’，字段b定义的是varchar(10)，mysql> select \* from table\_a where b=‘1234567890abcd’;在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是10，所以只截了前10个字节，就是’1234567890’进去做匹配；这样满足条件的数据有10万行；因为是select \*， 所以要做10万次回表；但是每次回表以后查出整行，到server层一判断，b的值都不是’1234567890abcd’;返回结果是空。  
2），mysql> select \* from t where c=50000 limit 1;由于字段c上没有索引，这个语句只能走id主键顺序扫描，因此需要扫描5万行。  
3），用select \* from t where id=1 lock in share mode，能避免在可重复读情况下，其他会话大量的更新语句  
4），等flush。出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。  
5），等DML锁

## 13，如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO上，可以通过哪些方法来提升性能呢？ ##

1），设置 binlog\_group\_commit\_sync\_delay 和  
binlog\_group\_commit\_sync\_no\_delay\_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。  
2），将sync\_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志。  
3），将innodb\_flush\_log\_at\_trx\_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。我不建议你把innodb\_flush\_log\_at\_trx\_commit 设置成0。因为把这个参数设置成0，表示redo log只保存在内存中，这样的话MySQL本身异常重启也会丢数据，风险太大。而redo log写到文件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0其实性能差不多，但这样做MySQL异常重启时就不会丢数据了，相比之下风险会更小。

## 14，加锁规则包含了两个“原则”、两个“优化”和一个“bug” ##

*  原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。
 *  原则2：查找过程中访问到的对象才会加锁。
 *  优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
 *  优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
 *  一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

## 15，怎么用join ##

1），select \* from t1 straight\_join t2 on (t1.a=t2.a);straight\_join让MySQL使用固定的连接方式执行查询，在这个语句里，t1 是驱动表，t2是被驱动表。  
2），应该让有索引的表作为被驱动表，这样可以走树搜索，而无索引走的是全盘扫描，因此整个执行过程，近似复杂度是 N + N*2*log2M。  
3），应该让小表来做驱动表。

## 16，InnoDB和Memory引擎 ##

InnoDB和Memory引擎的数据组织方式是不同的：  
InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。这种方式，我们称之为索引组织表（Index Organizied Table）。  
而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。  
从中我们可以看出，这两个引擎的一些典型不同：  
1），InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；  
2），当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；  
3），数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；  
4），InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。  
5），InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。  
由于内存表的这些特性，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用

## 17，自增主键不是连续的 ##

在MyISAM引擎里面，自增值是被写在数据文件上的。而在InnoDB中，自增值是被记录在内存的。MySQL直到8.0版本，才给InnoDB表的自增值加上了持久化的能力，确保重启前后一个表的自增值不变。

MySQL 5.1.22版本开始引入的参数innodb\_autoinc\_lock\_mode，控制了自增值申请时的锁范围。从并发性能的角度考虑，我建议你将其设置为2，同时将binlog\_format设置为row

*  其他唯一键冲突是导致自增主键id不连续的第一种原因。
 *  同样地，事务回滚也会产生类似的现象，这就是第二种原因。

此文章是学习网课后的笔记，写的比较散乱，供自己复习使用  
[原文链接][Link 1]：https://time.geekbang.org/column/intro/139?utm\_term=zeusFW7QE&utm\_source=wechat&utm\_medium=geektime&utm\_campaign=citiao&utm\_content=0608

[Link 1]: https://time.geekbang.org/column/intro/139?utm_term=zeusFW7QE&utm_source=wechat&utm_medium=geektime&utm_campaign=citiao&utm_content=0608