CS-Notes MySQL 核心技术指南索引原理、查询优化、存储引擎与分库分表全面解析【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes本篇基于 notes/MySQL.md 系统整理 CS-Notes 仓库中的 MySQL 面试与实战知识体系围绕索引的 BTree 底层原理、Explain 查询优化、InnoDB/MyISAM 存储引擎选型、数据类型选择、水平/垂直切分Sharding策略以及主从复制与读写分离六大主题展开。读完后你将掌握从为什么 MySQL 索引选 BTree到如何切分一张大表并保证 ID 唯一的完整技术链路并能在仓库内 notes/数据库系统原理.md 中对照 MVCC、锁协议等实现细节做纵深验证。一、索引BTree 原理与 MySQL 索引实现1.1 BTree 数据结构B Tree 指的是 Balance Tree即平衡树。平衡树是一种查找树并且所有叶子节点位于同一层。B Tree 基于 B Tree 和叶子节点顺序访问指针实现既具有 B Tree 的平衡性又通过顺序访问指针提高了区间查询的性能。在 B Tree 中一个节点中的 key 从左到右非递减排列。如果某个指针的左右相邻 key 分别是 keyi和 keyi1且不为 null则该指针指向节点的所有 key 满足大于等于 keyi且小于等于 keyi1。查找操作首先在根节点进行二分查找找到 key 所在的指针然后递归地在指针所指向的节点继续查找直到到达叶子节点再在叶子节点上进行二分查找找出 key 对应的 data。插入与删除插入删除操作会破坏平衡树的平衡性因此在操作之后需要对树进行分裂、合并、旋转等维护操作来恢复平衡。1.2 为什么索引用 BTree 而不是红黑树红黑树等平衡树同样可以实现索引但文件系统与数据库系统普遍采用 B Tree核心原因是访问磁盘数据的性能更高更低的树高平衡树树高 O(h) O(logdN)其中 d 为每个节点的出度。红黑树出度为 2而 B Tree 出度通常非常大因此红黑树树高比 B Tree 大得多磁盘访问原理操作系统一般将内存和磁盘分割成固定大小的块每一块称为一页内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点大小设置为页的大小使一次 I/O 就能完全载入一个节点。若数据不在同一磁盘块上通常需要移动磁臂进行寻道而磁臂因物理结构导致移动效率低下从而增加读取时间。B 树树高更低寻道次数与树高成正比同一磁盘块上访问只需很短的磁盘旋转时间因此 B 树更适合磁盘数据读取磁盘预读特性为减少 I/O磁盘往往不是严格按需读取而是每次预读。预读过程中磁盘顺序读取无需寻道、旋转时间短、速度极快同时相邻节点也能被预先载入。1.3 MySQL 的四种索引类型索引在存储引擎层实现而非服务器层因此不同存储引擎具有不同的索引类型和实现。BTree 索引大多数 MySQL 存储引擎的默认索引类型特点如下无需全表扫描只需对树进行搜索查找速度快很多因为 B Tree 的有序性除查找外还可用于排序和分组ORDER BY / GROUP BY可指定多个列作为索引列多个索引列共同组成键适用于全键值、键值范围和键前缀查找其中键前缀查找只适用于最左前缀查找——如果查询不按索引列顺序则无法使用索引。InnoDB 的 BTree 索引分为主索引和辅助索引主索引聚簇索引叶子节点的 data 域记录完整的数据记录称为聚簇索引。由于数据行无法存放在两个不同位置一个表只能有一个聚簇索引辅助索引叶子节点的 data 域记录的是主键值。因此使用辅助索引查找时需要先查到主键值再到主索引中二次查找回表。哈希索引哈希索引能以 O(1) 时间查找但失去了有序性无法用于排序与分组只支持精确查找无法用于部分查找和范围查找。InnoDB 有一个特殊功能——自适应哈希索引当某个索引值被使用得非常频繁时会在 BTree 索引之上再创建一个哈希索引使 BTree 索引兼具哈希索引的快速查找优点且无需用户干预。全文索引MyISAM 存储引擎支持全文索引用于查找文本中的关键词而不是直接比较是否相等。查找条件使用MATCH AGAINST而非普通WHERE。全文索引使用倒排索引实现记录着关键词到其所在文档的映射。InnoDB 从 MySQL 5.6.4 版本开始也支持全文索引。空间数据索引MyISAM 支持空间数据索引R-Tree可用于地理数据存储。空间数据索引从所有维度索引数据可以有效地使用任意维度进行组合查询维护数据必须使用 GIS 相关函数。1.4 索引优化五大技巧1使用独立的列查询时索引列不能是表达式的一部分也不能是函数的参数否则无法使用索引。例如下面的查询无法使用 actor_id 列的索引SELECT actor_id FROM sakila.actor WHERE actor_id 1 5;应改写为WHERE actor_id 4的形式。2多列索引优于多个单列索引在需要多个列作为条件查询时一个多列索引比多个单列索引性能更好。例如SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id 1 AND film_id 1;此时最好把 actor_id 和 film_id 设置为同一个多列索引。3合理选择索引列顺序让选择性最强的索引列放在前面。索引的选择性是指不重复的索引值与记录总数的比值最大值为 1每条记录都有唯一索引值对应选择性越高区分度越高查询效率也越高。可以通过如下语句评估SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, COUNT(*) FROM payment;查询结果为staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 16049customer_id 的选择性更高因此在多列索引中应放在前面。4前缀索引对于 BLOB、TEXT 和 VARCHAR 类型的长列必须使用前缀索引只索引开头的一部分字符前缀长度的选取需要根据索引选择性来确定——在长度尽可能短与选择性尽可能高之间取得平衡。5覆盖索引索引包含所有需要查询的字段的值。其优点索引通常远小于数据行大小只读取索引能大大减少数据访问量一些存储引擎如 MyISAM在内存中只缓存索引而数据依赖操作系统缓存只访问索引可避免昂贵的系统调用对 InnoDB若辅助索引能覆盖查询则无需访问主索引避免回表。1.5 索引的优点与适用条件优点大大减少服务器需要扫描的数据行数帮助服务器避免排序和分组以及避免创建临时表BTree 索引是有序的可用于 ORDER BY 和 GROUP BY将随机 I/O 变为顺序 I/OBTree 有序相邻数据都存储在一起。适用条件非常小的表大部分情况下简单全表扫描比建立索引更高效中到大型表索引非常有效特大型表建立和维护索引的代价随之增长需要一种直接定位出需要查询的一组数据而非逐条记录匹配的技术例如分区技术。二、查询性能优化Explain 分析与查询重构2.1 使用 Explain 进行分析EXPLAIN用来分析 SELECT 查询语句开发人员通过分析 Explain 结果来优化查询。比较重要的字段有select_type查询类型有简单查询、联合查询、子查询等key实际使用的索引rows需要扫描的行数。rows是判断索引是否生效的直观指标若key为 NULL 且rows接近全表行数说明查询走了全表扫描应检查 WHERE 条件是否满足最左前缀、索引列上是否做了函数或表达式运算见 1.4 节独立列原则。2.2 优化数据访问减少请求的数据量只返回必要的列尽量不要使用SELECT *只返回必要的行使用LIMIT限制返回数据量缓存重复查询的数据使用缓存可避免在数据库中进行查询当查询的数据经常被重复访问时缓存带来的性能提升非常明显。减少服务器端扫描的行数最有效的方式是使用索引来覆盖查询即覆盖索引。2.3 重构查询方式切分大查询一个大查询一次性执行可能一次锁住大量数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。例如一次性删除三个月前的消息DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH);应改为分批删除rows_affected 0 do { rows_affected do_query( DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000 ) } while rows_affected 0分解大连接查询将一个大连接查询分解成对每个表各做一次单表查询然后在应用程序中进行关联。好处有让缓存更高效连接查询中只要一个表发生变化整个查询缓存即失效分解后即使其中一个表变化其它表的查询缓存依然可用缓存复用分解后的单表查询结果更可能被其它查询使用减少冗余记录查询减少锁竞争更易水平拆分应用层连接使数据库拆分更简单更易做到高性能与可伸缩查询效率可能提升用IN()代替连接查询可以让 MySQL 按 ID 顺序查询可能比随机的连接更高效。例如SELECT * FROM tag JOIN tag_post ON tag_post.tag_idtag.id JOIN post ON tag_post.post_idpost.id WHERE tag.tagmysql;可分解为SELECT * FROM tag WHERE tagmysql; SELECT * FROM tag_post WHERE tag_id1234; SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);三、存储引擎InnoDB 与 MyISAM 深度对比3.1 InnoDB默认事务型存储引擎InnoDB 是 MySQL 默认的事务型存储引擎只有在需要它不支持的特性时才考虑使用其它存储引擎。事务与隔离级别实现了四个标准的隔离级别默认级别是可重复读REPEATABLE READ。在可重复读级别下通过MVCC Next-Key Locking防止幻影读聚簇索引主索引是聚簇索引在索引中直接保存数据避免直接读取磁盘对查询性能有很大提升内部优化包括读取数据时的可预测性读、自动创建并能加快读操作的自适应哈希索引、能加速插入操作的插入缓冲区等在线热备份InnoDB 支持真正的在线热备份。其它存储引擎不支持——要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入往往也意味着停止读取。仓库中 notes/数据库系统原理.md 对 InnoDB 的实现机制作了源码级的展开可与本节互相印证MVCC 部分InnoDB 的 MVCC 通过系统版本号 SYS_ID、事务版本号 TRX_ID、Undo 日志中的版本快照链ROLL_PTR和 ReadView含未提交事务列表 TRX_IDs、TRX_ID_MIN、TRX_ID_MAX判断快照是否可见从而支撑提交读与可重复读两个隔离级别。普通 SELECT 走快照读、不加锁INSERT/UPDATE/DELETE 走当前读、需要加锁Next-Key Locks 部分Record Locks 锁定记录上的索引Gap Locks 锁定索引之间的间隙Next-Key Locks 是二者的结合锁定一个前开后闭区间如索引值 10、11、13、20 对应区间 (-∞,10]、(10,11]、(11,13]、(13,20]、(20,∞)。这正是 InnoDB 在可重复读级别下用 MVCC Next-Key Locks 消除幻影读的机制锁协议部分InnoDB 采用两段锁协议会根据隔离级别在需要时自动加锁且所有锁在同一时刻释放隐式锁定也可通过SELECT ... LOCK IN SHARE MODES 锁与SELECT ... FOR UPDATEX 锁显式锁定。3.2 MyISAM简单快速的非事务引擎MyISAM 设计简单数据以紧密格式存储。对于只读数据或者表比较小、可以容忍修复操作依然可以使用它。提供压缩表、空间数据索引等特性不支持事务只支持表级锁读取时对需要读到的所有表加共享锁写入时对表加排它锁。但表有读取操作的同时也可以插入新记录称为并发插入CONCURRENT INSERT可手工或自动执行检查和修复操作但与事务恢复和崩溃恢复不同可能导致数据丢失且修复操作非常慢指定DELAY_KEY_WRITE选项时修改执行完成后不立即将索引写入磁盘而是写入内存键缓冲区直到清理键缓冲区或关闭表时才写入磁盘。这能极大提升写入性能但数据库或主机崩溃时会造成本索引损坏需要执行修复操作。3.3 引擎选型对比维度InnoDBMyISAM事务事务型可用 Commit / Rollback不支持并发控制支持行级锁也支持表锁只支持表级锁外键支持不支持备份支持在线热备份不支持崩溃恢复恢复机制完善崩溃后损坏概率更高、恢复更慢其它特性自适应哈希、插入缓冲区、MVCC压缩表、空间数据索引、并发插入结论默认使用 InnoDB只有在需要压缩表、空间数据索引等 InnoDB 不支持的特性或数据基本只读时才选择 MyISAM。四、数据类型整型、浮点数、字符串与日期时间4.1 整型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 分别使用 8、16、24、32、64 位存储空间一般情况下越小的列越好。注意INT(11)中的数字只规定了交互工具显示字符的个数对存储和计算没有意义。4.2 浮点数FLOAT 和 DOUBLE 为浮点类型DECIMAL 为高精度小数类型。CPU 原生支持浮点运算但不支持 DECIMAL 计算因此 DECIMAL 计算代价更高金额、账目类场景仍需 DECIMAL 保证精度。三者都可以指定列宽例如DECIMAL(18, 9)表示总共 18 位取 9 位存储小数部分剩下 9 位存储整数部分。4.3 字符串主要有 CHAR 和 VARCHAR 两种类型前者定长、后者变长VARCHAR 变长类型能节省空间只需存储必要内容但执行 UPDATE 时行可能变长超出一个页所能容纳的大小时需要额外操作——MyISAM 会将行拆成不同片段存储而 InnoDB 需要分裂页使行放进页内存储和检索时VARCHAR 保留末尾空格CHAR 删除末尾空格。4.4 时间和日期MySQL 提供两种相似的日期时间类型DATETIME 和 TIMESTAMP。DATETIME能保存从 1000 年到 9999 年的日期和时间精度为秒使用 8 字节与时区无关默认以可排序、无歧义的格式显示如 2008-01-16 22:37:08ANSI 标准表示法。TIMESTAMP与 UNIX 时间戳相同保存从 1970 年 1 月 1 日午夜格林威治时间以来的秒数使用 4 字节只能表示 1970 年到 2038 年与时区有关同一时间戳在不同时区代表的具体时间不同提供FROM_UNIXTIME()把 UNIX 时间戳转换为日期UNIX_TIMESTAMP()把日期转换为 UNIX 时间戳默认插入时未指定值会设置为当前时间。选型建议尽量使用 TIMESTAMP因为它比 DATETIME 空间效率更高4 字节 vs 8 字节但需要表示 2038 年之后的时间或必须与时区无关时应使用 DATETIME。五、切分水平切分Sharding、垂直切分与分片策略5.1 水平切分水平切分又称为 Sharding是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时Sharding 是必然的选择它可以将数据分布到集群的不同节点上从而减缓单个数据库的压力。5.2 垂直切分垂直切分是将一张表按列切分成多个表通常按照列的关系密集程度切分也可以利用垂直切分把经常被使用的列和不经常被使用的列分到不同的表中。在数据库层面垂直切分按表中数据的密集程度部署到不同的库中例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。5.3 Sharding 策略哈希取模hash(key) % N范围可以是 ID 范围也可以是时间范围映射表使用单独的一个数据库来存储映射关系。5.4 Sharding 带来的问题与应对事务问题跨分片操作无法使用本地事务需使用分布式事务来解决比如 XA 接口。连接问题跨分表的 JOIN 无法在数据库内完成可以将原来的连接分解成多个单表查询然后在用户程序中进行连接与 2.3 节分解大连接查询思路一致同时让分片更易于水平拆分。ID 唯一性使用全局唯一 IDGUID为每个分片指定一个 ID 范围使用分布式 ID 生成器如 Twitter 的 Snowflake 算法。六、复制主从复制与读写分离6.1 主从复制主从复制主要涉及三个线程binlog 线程负责将主服务器上的数据更改写入二进制日志Binary log中I/O 线程负责从主服务器上读取二进制日志并写入从服务器的中继日志Relay logSQL 线程负责读取中继日志解析出主服务器已经执行的数据更改并在从服务器中重放Replay。6.2 读写分离主服务器处理写操作以及实时性要求比较高的读操作从服务器处理普通读操作。读写分离能提高性能的原因主从服务器各负责读和写极大程度缓解了锁的争用从服务器可以使用 MyISAM提升查询性能并节约系统开销增加冗余提高可用性。读写分离常用代理方式实现代理服务器接收应用层传来的读写请求然后决定转发到哪个服务器。参考资料Baron Schwartz、Peter Zaitsev、Vadim Tkachenko 等.《高性能 MySQL》. 电子工业出版社, 2013.姜承尧.《MySQL 技术内幕InnoDB 存储引擎》. 机械工业出版社, 2011.延伸阅读可参考仓库内文档notes/数据库系统原理.md事务、封锁协议、隔离级别、MVCC、Next-Key Locks 的完整推导、notes/SQL.mdSQL 语法与查询基础、notes/Redis.md缓存层与数据库的协同设计。【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
CS-Notes MySQL 核心技术指南索引原理、查询优化、存储引擎与分库分表全面解析【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes本篇基于 notes/MySQL.md 系统整理 CS-Notes 仓库中的 MySQL 面试与实战知识体系围绕索引的 BTree 底层原理、Explain 查询优化、InnoDB/MyISAM 存储引擎选型、数据类型选择、水平/垂直切分Sharding策略以及主从复制与读写分离六大主题展开。读完后你将掌握从为什么 MySQL 索引选 BTree到如何切分一张大表并保证 ID 唯一的完整技术链路并能在仓库内 notes/数据库系统原理.md 中对照 MVCC、锁协议等实现细节做纵深验证。一、索引BTree 原理与 MySQL 索引实现1.1 BTree 数据结构B Tree 指的是 Balance Tree即平衡树。平衡树是一种查找树并且所有叶子节点位于同一层。B Tree 基于 B Tree 和叶子节点顺序访问指针实现既具有 B Tree 的平衡性又通过顺序访问指针提高了区间查询的性能。在 B Tree 中一个节点中的 key 从左到右非递减排列。如果某个指针的左右相邻 key 分别是 keyi和 keyi1且不为 null则该指针指向节点的所有 key 满足大于等于 keyi且小于等于 keyi1。查找操作首先在根节点进行二分查找找到 key 所在的指针然后递归地在指针所指向的节点继续查找直到到达叶子节点再在叶子节点上进行二分查找找出 key 对应的 data。插入与删除插入删除操作会破坏平衡树的平衡性因此在操作之后需要对树进行分裂、合并、旋转等维护操作来恢复平衡。1.2 为什么索引用 BTree 而不是红黑树红黑树等平衡树同样可以实现索引但文件系统与数据库系统普遍采用 B Tree核心原因是访问磁盘数据的性能更高更低的树高平衡树树高 O(h) O(logdN)其中 d 为每个节点的出度。红黑树出度为 2而 B Tree 出度通常非常大因此红黑树树高比 B Tree 大得多磁盘访问原理操作系统一般将内存和磁盘分割成固定大小的块每一块称为一页内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点大小设置为页的大小使一次 I/O 就能完全载入一个节点。若数据不在同一磁盘块上通常需要移动磁臂进行寻道而磁臂因物理结构导致移动效率低下从而增加读取时间。B 树树高更低寻道次数与树高成正比同一磁盘块上访问只需很短的磁盘旋转时间因此 B 树更适合磁盘数据读取磁盘预读特性为减少 I/O磁盘往往不是严格按需读取而是每次预读。预读过程中磁盘顺序读取无需寻道、旋转时间短、速度极快同时相邻节点也能被预先载入。1.3 MySQL 的四种索引类型索引在存储引擎层实现而非服务器层因此不同存储引擎具有不同的索引类型和实现。BTree 索引大多数 MySQL 存储引擎的默认索引类型特点如下无需全表扫描只需对树进行搜索查找速度快很多因为 B Tree 的有序性除查找外还可用于排序和分组ORDER BY / GROUP BY可指定多个列作为索引列多个索引列共同组成键适用于全键值、键值范围和键前缀查找其中键前缀查找只适用于最左前缀查找——如果查询不按索引列顺序则无法使用索引。InnoDB 的 BTree 索引分为主索引和辅助索引主索引聚簇索引叶子节点的 data 域记录完整的数据记录称为聚簇索引。由于数据行无法存放在两个不同位置一个表只能有一个聚簇索引辅助索引叶子节点的 data 域记录的是主键值。因此使用辅助索引查找时需要先查到主键值再到主索引中二次查找回表。哈希索引哈希索引能以 O(1) 时间查找但失去了有序性无法用于排序与分组只支持精确查找无法用于部分查找和范围查找。InnoDB 有一个特殊功能——自适应哈希索引当某个索引值被使用得非常频繁时会在 BTree 索引之上再创建一个哈希索引使 BTree 索引兼具哈希索引的快速查找优点且无需用户干预。全文索引MyISAM 存储引擎支持全文索引用于查找文本中的关键词而不是直接比较是否相等。查找条件使用MATCH AGAINST而非普通WHERE。全文索引使用倒排索引实现记录着关键词到其所在文档的映射。InnoDB 从 MySQL 5.6.4 版本开始也支持全文索引。空间数据索引MyISAM 支持空间数据索引R-Tree可用于地理数据存储。空间数据索引从所有维度索引数据可以有效地使用任意维度进行组合查询维护数据必须使用 GIS 相关函数。1.4 索引优化五大技巧1使用独立的列查询时索引列不能是表达式的一部分也不能是函数的参数否则无法使用索引。例如下面的查询无法使用 actor_id 列的索引SELECT actor_id FROM sakila.actor WHERE actor_id 1 5;应改写为WHERE actor_id 4的形式。2多列索引优于多个单列索引在需要多个列作为条件查询时一个多列索引比多个单列索引性能更好。例如SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id 1 AND film_id 1;此时最好把 actor_id 和 film_id 设置为同一个多列索引。3合理选择索引列顺序让选择性最强的索引列放在前面。索引的选择性是指不重复的索引值与记录总数的比值最大值为 1每条记录都有唯一索引值对应选择性越高区分度越高查询效率也越高。可以通过如下语句评估SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, COUNT(*) FROM payment;查询结果为staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 16049customer_id 的选择性更高因此在多列索引中应放在前面。4前缀索引对于 BLOB、TEXT 和 VARCHAR 类型的长列必须使用前缀索引只索引开头的一部分字符前缀长度的选取需要根据索引选择性来确定——在长度尽可能短与选择性尽可能高之间取得平衡。5覆盖索引索引包含所有需要查询的字段的值。其优点索引通常远小于数据行大小只读取索引能大大减少数据访问量一些存储引擎如 MyISAM在内存中只缓存索引而数据依赖操作系统缓存只访问索引可避免昂贵的系统调用对 InnoDB若辅助索引能覆盖查询则无需访问主索引避免回表。1.5 索引的优点与适用条件优点大大减少服务器需要扫描的数据行数帮助服务器避免排序和分组以及避免创建临时表BTree 索引是有序的可用于 ORDER BY 和 GROUP BY将随机 I/O 变为顺序 I/OBTree 有序相邻数据都存储在一起。适用条件非常小的表大部分情况下简单全表扫描比建立索引更高效中到大型表索引非常有效特大型表建立和维护索引的代价随之增长需要一种直接定位出需要查询的一组数据而非逐条记录匹配的技术例如分区技术。二、查询性能优化Explain 分析与查询重构2.1 使用 Explain 进行分析EXPLAIN用来分析 SELECT 查询语句开发人员通过分析 Explain 结果来优化查询。比较重要的字段有select_type查询类型有简单查询、联合查询、子查询等key实际使用的索引rows需要扫描的行数。rows是判断索引是否生效的直观指标若key为 NULL 且rows接近全表行数说明查询走了全表扫描应检查 WHERE 条件是否满足最左前缀、索引列上是否做了函数或表达式运算见 1.4 节独立列原则。2.2 优化数据访问减少请求的数据量只返回必要的列尽量不要使用SELECT *只返回必要的行使用LIMIT限制返回数据量缓存重复查询的数据使用缓存可避免在数据库中进行查询当查询的数据经常被重复访问时缓存带来的性能提升非常明显。减少服务器端扫描的行数最有效的方式是使用索引来覆盖查询即覆盖索引。2.3 重构查询方式切分大查询一个大查询一次性执行可能一次锁住大量数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。例如一次性删除三个月前的消息DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH);应改为分批删除rows_affected 0 do { rows_affected do_query( DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000 ) } while rows_affected 0分解大连接查询将一个大连接查询分解成对每个表各做一次单表查询然后在应用程序中进行关联。好处有让缓存更高效连接查询中只要一个表发生变化整个查询缓存即失效分解后即使其中一个表变化其它表的查询缓存依然可用缓存复用分解后的单表查询结果更可能被其它查询使用减少冗余记录查询减少锁竞争更易水平拆分应用层连接使数据库拆分更简单更易做到高性能与可伸缩查询效率可能提升用IN()代替连接查询可以让 MySQL 按 ID 顺序查询可能比随机的连接更高效。例如SELECT * FROM tag JOIN tag_post ON tag_post.tag_idtag.id JOIN post ON tag_post.post_idpost.id WHERE tag.tagmysql;可分解为SELECT * FROM tag WHERE tagmysql; SELECT * FROM tag_post WHERE tag_id1234; SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);三、存储引擎InnoDB 与 MyISAM 深度对比3.1 InnoDB默认事务型存储引擎InnoDB 是 MySQL 默认的事务型存储引擎只有在需要它不支持的特性时才考虑使用其它存储引擎。事务与隔离级别实现了四个标准的隔离级别默认级别是可重复读REPEATABLE READ。在可重复读级别下通过MVCC Next-Key Locking防止幻影读聚簇索引主索引是聚簇索引在索引中直接保存数据避免直接读取磁盘对查询性能有很大提升内部优化包括读取数据时的可预测性读、自动创建并能加快读操作的自适应哈希索引、能加速插入操作的插入缓冲区等在线热备份InnoDB 支持真正的在线热备份。其它存储引擎不支持——要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入往往也意味着停止读取。仓库中 notes/数据库系统原理.md 对 InnoDB 的实现机制作了源码级的展开可与本节互相印证MVCC 部分InnoDB 的 MVCC 通过系统版本号 SYS_ID、事务版本号 TRX_ID、Undo 日志中的版本快照链ROLL_PTR和 ReadView含未提交事务列表 TRX_IDs、TRX_ID_MIN、TRX_ID_MAX判断快照是否可见从而支撑提交读与可重复读两个隔离级别。普通 SELECT 走快照读、不加锁INSERT/UPDATE/DELETE 走当前读、需要加锁Next-Key Locks 部分Record Locks 锁定记录上的索引Gap Locks 锁定索引之间的间隙Next-Key Locks 是二者的结合锁定一个前开后闭区间如索引值 10、11、13、20 对应区间 (-∞,10]、(10,11]、(11,13]、(13,20]、(20,∞)。这正是 InnoDB 在可重复读级别下用 MVCC Next-Key Locks 消除幻影读的机制锁协议部分InnoDB 采用两段锁协议会根据隔离级别在需要时自动加锁且所有锁在同一时刻释放隐式锁定也可通过SELECT ... LOCK IN SHARE MODES 锁与SELECT ... FOR UPDATEX 锁显式锁定。3.2 MyISAM简单快速的非事务引擎MyISAM 设计简单数据以紧密格式存储。对于只读数据或者表比较小、可以容忍修复操作依然可以使用它。提供压缩表、空间数据索引等特性不支持事务只支持表级锁读取时对需要读到的所有表加共享锁写入时对表加排它锁。但表有读取操作的同时也可以插入新记录称为并发插入CONCURRENT INSERT可手工或自动执行检查和修复操作但与事务恢复和崩溃恢复不同可能导致数据丢失且修复操作非常慢指定DELAY_KEY_WRITE选项时修改执行完成后不立即将索引写入磁盘而是写入内存键缓冲区直到清理键缓冲区或关闭表时才写入磁盘。这能极大提升写入性能但数据库或主机崩溃时会造成本索引损坏需要执行修复操作。3.3 引擎选型对比维度InnoDBMyISAM事务事务型可用 Commit / Rollback不支持并发控制支持行级锁也支持表锁只支持表级锁外键支持不支持备份支持在线热备份不支持崩溃恢复恢复机制完善崩溃后损坏概率更高、恢复更慢其它特性自适应哈希、插入缓冲区、MVCC压缩表、空间数据索引、并发插入结论默认使用 InnoDB只有在需要压缩表、空间数据索引等 InnoDB 不支持的特性或数据基本只读时才选择 MyISAM。四、数据类型整型、浮点数、字符串与日期时间4.1 整型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 分别使用 8、16、24、32、64 位存储空间一般情况下越小的列越好。注意INT(11)中的数字只规定了交互工具显示字符的个数对存储和计算没有意义。4.2 浮点数FLOAT 和 DOUBLE 为浮点类型DECIMAL 为高精度小数类型。CPU 原生支持浮点运算但不支持 DECIMAL 计算因此 DECIMAL 计算代价更高金额、账目类场景仍需 DECIMAL 保证精度。三者都可以指定列宽例如DECIMAL(18, 9)表示总共 18 位取 9 位存储小数部分剩下 9 位存储整数部分。4.3 字符串主要有 CHAR 和 VARCHAR 两种类型前者定长、后者变长VARCHAR 变长类型能节省空间只需存储必要内容但执行 UPDATE 时行可能变长超出一个页所能容纳的大小时需要额外操作——MyISAM 会将行拆成不同片段存储而 InnoDB 需要分裂页使行放进页内存储和检索时VARCHAR 保留末尾空格CHAR 删除末尾空格。4.4 时间和日期MySQL 提供两种相似的日期时间类型DATETIME 和 TIMESTAMP。DATETIME能保存从 1000 年到 9999 年的日期和时间精度为秒使用 8 字节与时区无关默认以可排序、无歧义的格式显示如 2008-01-16 22:37:08ANSI 标准表示法。TIMESTAMP与 UNIX 时间戳相同保存从 1970 年 1 月 1 日午夜格林威治时间以来的秒数使用 4 字节只能表示 1970 年到 2038 年与时区有关同一时间戳在不同时区代表的具体时间不同提供FROM_UNIXTIME()把 UNIX 时间戳转换为日期UNIX_TIMESTAMP()把日期转换为 UNIX 时间戳默认插入时未指定值会设置为当前时间。选型建议尽量使用 TIMESTAMP因为它比 DATETIME 空间效率更高4 字节 vs 8 字节但需要表示 2038 年之后的时间或必须与时区无关时应使用 DATETIME。五、切分水平切分Sharding、垂直切分与分片策略5.1 水平切分水平切分又称为 Sharding是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时Sharding 是必然的选择它可以将数据分布到集群的不同节点上从而减缓单个数据库的压力。5.2 垂直切分垂直切分是将一张表按列切分成多个表通常按照列的关系密集程度切分也可以利用垂直切分把经常被使用的列和不经常被使用的列分到不同的表中。在数据库层面垂直切分按表中数据的密集程度部署到不同的库中例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。5.3 Sharding 策略哈希取模hash(key) % N范围可以是 ID 范围也可以是时间范围映射表使用单独的一个数据库来存储映射关系。5.4 Sharding 带来的问题与应对事务问题跨分片操作无法使用本地事务需使用分布式事务来解决比如 XA 接口。连接问题跨分表的 JOIN 无法在数据库内完成可以将原来的连接分解成多个单表查询然后在用户程序中进行连接与 2.3 节分解大连接查询思路一致同时让分片更易于水平拆分。ID 唯一性使用全局唯一 IDGUID为每个分片指定一个 ID 范围使用分布式 ID 生成器如 Twitter 的 Snowflake 算法。六、复制主从复制与读写分离6.1 主从复制主从复制主要涉及三个线程binlog 线程负责将主服务器上的数据更改写入二进制日志Binary log中I/O 线程负责从主服务器上读取二进制日志并写入从服务器的中继日志Relay logSQL 线程负责读取中继日志解析出主服务器已经执行的数据更改并在从服务器中重放Replay。6.2 读写分离主服务器处理写操作以及实时性要求比较高的读操作从服务器处理普通读操作。读写分离能提高性能的原因主从服务器各负责读和写极大程度缓解了锁的争用从服务器可以使用 MyISAM提升查询性能并节约系统开销增加冗余提高可用性。读写分离常用代理方式实现代理服务器接收应用层传来的读写请求然后决定转发到哪个服务器。参考资料Baron Schwartz、Peter Zaitsev、Vadim Tkachenko 等.《高性能 MySQL》. 电子工业出版社, 2013.姜承尧.《MySQL 技术内幕InnoDB 存储引擎》. 机械工业出版社, 2011.延伸阅读可参考仓库内文档notes/数据库系统原理.md事务、封锁协议、隔离级别、MVCC、Next-Key Locks 的完整推导、notes/SQL.mdSQL 语法与查询基础、notes/Redis.md缓存层与数据库的协同设计。【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
