MySQL

最近在使用 MySQL 数据库时遇到了一个问题，就是在进行查询时，MySQL 并不会区分查询条件中的大小写。 经过一番研究，发现这是 MySQL 的一个特性：**MySQL 默认使用的编码是 utf8mb4，在 utf8mb4 编码下，MySQL 默认情况下是不区分大小写的。**这是因为在 utf8mb4 编码下，大小写字母在二进制表示上是相同的，因此在进行比较时，MySQL 会忽略大小写。 所以无论在查询条件中使用的是大写字母、小写字母还是混合字母，MySQL 都会将其视为相同的查询条件。 解决方案： 使用 COLLATE 关键字来指定查询时的字符集。 具体来说，我们可以将查询条件中的字符串转换为特定的字符集，以便在查询时进行区分。例如，我们可以使用以下语句来进行查询： SELECT * FROM table WHERE column COLLATE utf8mb4_bin = 'SearchString'; // SELECT * FROM table WHERE column COLLATE utf8_bin = 'SearchString'; 使用 BINARY 运算符 SELECT * FROM table WHERE BINARY column = 'SearchString'; COLLATE 和 BINARY 运算符在 MySQL 中的效率都比较高。COLLATE 关键字通常用于对特定列或查询进行设置，而 BINARY 运算符通常用于对整个查询进行设置。 在使用 COLLATE关键字时，由于会对查询条件进行字符集转换，因此可能会稍微降低一些查询性能。但是，这种性能下降通常是可以忽略不计的。在实际使用中，我们可以先尝试使用 COLLATE关键字，如果查询性能不足，再考虑使用其他优化策略。 在使用 BINARY运算符时，由于不需要进行字符集转换，因此查询性能通常比使用 COLLATE关键字要高一些。但是，这种性能优势也是非常小的，通常不会对查询性能产生重大影响。 ...

MySQL 分为Server层和存储层。Server层包括连接器、查询缓存、分析器、优化器和执行器。 连接器 连接器负责跟客户端建立连接、获取权限、维持和管理连接。 查看连接： SHOW PROCESSLIST Sleep 表示空闲连接，默认8小时自动断开，通过参数wait_timeout控制。 长连接 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。 MySQL在执行过程中，临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才会释放。 所以长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），会导致MySQL异常重启。 解决方案： 定期关闭长连接。使用一段时间之后，获者程序里面判断执行过一个占用内存的大查询后，断开连接，之后查询再重连。 每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是回不将连接恢复到刚刚创建完时的状态。 分析器 词法分析和语法分析。 优化器 索引优化和多表关联优化。 执行器 判断是否有表的权限 调用存储引擎接口

redo log binlog 重做日志：redo log redo log 是InnoDB特有的日志。 WAL（Write-Ahead-Log）: 先写日志，再写磁盘。 具体来说： 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。 InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块"粉板"总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。 归档日志： Binlog binlog 是MySQL Server层的日志。 和redo log 的不同之处在于： redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 redo log 是物理日志，记录的是"在某个数据页上做了什么修改"；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如"给 ID=2 这一行的 c 字段加 1 “。 redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写"是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。 例子： update T set c=c+1 where ID=2; 执行器和InnoDB在执行一个update语句时内部流程： ...

事务 事务就是一组SQL语句，作为一个工作单元以原子方式进行处理。如果数据库引擎能够成功地对数据库应用整组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么整组语句都不执行。也就是说，作为事务的一组语句，要么全部执行成功，要么全部执行失败。 ACID代表原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability) 隔离性和隔离级别 隔离性 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。在前面的例子中，当执行完第3条语句、第4条语句还未开始时，此时有另外一个账户汇总程序开始运行，其看到的支票账户的余额并没有被减去200美元。后面我们讨论隔离级别(isolation level)的时候，会发现为什么我们要说"通常来说"是不可见的。 隔离级别 多个事务同时执行的时候，可能出现的问题： 脏读 不可重复读 幻读 为了解决这些问题，就有了"隔离级别"的概念。 SQL标准的事务隔离级别有： 读未提交（read uncommitted） 一个事务还没提交时，它的变更就能被别的事务看到。 读提交（read committed） 一个事务提交后，它的变更才会被其他事务看到。 这个级别仍然允许不可重复读(nonrepeatable read)，这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果。 可重复读（repeatable read） 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 串行化（serializable） 顾名思义是对于同一行记录，“写"会加"写锁”，“读"会加"读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

索引 提高数据查询的效率 数据存储在磁盘上面，需要索引按某个key（搜索码）记录数据存储的位置，提高查询效率。 索引常见模型 有两种基本的索引类型： 顺序索引 基于值的顺序排序。 散列索引（hash） 基于将值平均分布到若干散列桶中。一个值所属的散列桶是由一个函数决定的，该函数称为散列函数（hash function） 指标 查询类型 能有效支持的访问类型。访问类型可以包括找到具体特定属性值的记录，以及找到属性值落在某个特定范围内的记录。 查询时间 查询一个特定数据项或数据项集所需的时间。 写入时间 插入一个新数据项所需的时间。该值包括找到待删除项所需的时间，以及更新索引结构所需的时间。 删除时间 删除一个数据项所需的时间。该值包括找到待删除项所需的时间，以及更新索引结构所需的时间。 空间开销 索引结构所占用的额外存储空间。倘若存储索引结构的额外空间大小适度，通常牺牲一定的空间代价来换取性能的提高是值得的。 顺序索引 为了快速随机访问文件中的记录，可以使用索引结构。每个索引结构与一个特定的搜索码相关联。顺序索引按顺序存储搜索码的值，并将每个搜索码与包含该搜索码的记录关联起来。 graph LR key1(1) --> record1(id:1 name: a) key2(2) --> record2(id:2 name: b) key3(3) --> record3(id:3 name: c) record1 --> file record2 --> file record3 --> file 文件中的记录自身也可以按照某种排序顺序存储。一个文件可以有多个索引，分别基于不同的搜索码。 聚簇索引（clustering index）(primary index) 如果文件按照某个搜索码指定的顺序排序，那么该搜索码对应的索引称为聚簇索引。聚簇索引也称为主索引。 非聚簇索引(non-clustering index)（二级索引）（secondary index） 搜索码指定的顺序与文件中记录的物理顺序不同的索引称为非聚簇索引。也称为辅助索引。 索引顺序文件 按照聚簇索引顺序排列的文件称为索引顺序文件。 B+树索引文件 // TODO 哈希表 //TODO 有序数组 //TODO 搜索树 //TODO