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

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 + N2log2M。
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不连续的第一种原因。
• 同样地，事务回滚也会产生类似的现象，这就是第二种原因。

此文章是学习网课后的笔记，写的比较散乱，供自己复习使用
原文链接：https://time.geekbang.org/column/intro/139?utm_term=zeusFW7QE&utm_source=wechat&utm_medium=geektime&utm_campaign=citiao&utm_content=0608