MySQL

MyISAM与 InnoDB引擎

1. count运算上的区别：因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好 的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。 2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型 更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务 (commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 3. 是否支持外键： MyISAM不支持，而InnoDB支持。

索引

其实底层结构就是B+树

最左前缀原则

最左前缀原则（Leftmost Prefix Principle）是数据库中使用复合索引（多列索引）时的一个重要优化规则。它规定在查询条件中，必须从复合索引的最左列开始，按顺序使用索引中的列，才能有效利用该索引。

定义

假设有一个复合索引 (A, B, C)，根据最左前缀原则：

• 查询条件为 WHERE A = ? 时，可以使用索引。

• 查询条件为 WHERE A = ? AND B = ? 时，也可以使用索引。

• 查询条件为 WHERE A = ? AND B = ? AND C = ? 时，同样可以使用索引。

• 但如果查询条件为 WHERE B = ? 或 WHERE C = ?，则无法使用该复合索引，因为没有从最左列开始。

应用场景

1. 多列查询优化：当查询条件中经常涉及多列时，创建复合索引并遵循最左前缀原则可以显著提升查询性能。

2. 范围查询优化：在涉及范围查询（如 >、<、BETWEEN 等）时，最左前缀原则尤为重要。一旦查询条件中包含范围查询，索引的使用将受到限制，只能匹配到范围查询列之前的索引列。

3. 排序优化：当查询中包含 ORDER BY 子句时，如果排序列符合索引的最左前缀规则，MySQL可以利用索引进行排序，从而避免额外的排序操作。

注意事项

• 范围查询字段后停止：如果查询条件中包含范围查询（如 A > ?），则该列之后的索引列将无法被利用。

• 列的顺序很重要：在创建复合索引时，应根据查询条件的使用频率和过滤性来确定列的顺序。最常用于查询条件的列应放在索引的最左列。

• 部分匹配：即使查询条件没有完全匹配复合索引的所有列，只要满足最左前缀规则，索引仍然可以被部分利用。

常见的Mysql优化

1. 查询优化

1.1 避免使用SELECT *

• 问题：SELECT *会检索表中的所有列，即使某些列并不需要，也会增加I/O和网络传输开销。

• 优化：明确指定需要的列，例如SELECT column1, column2 FROM table。

1.2 使用EXPLAIN分析查询

• 问题：不了解查询的执行计划，可能导致性能瓶颈。

• 优化：使用EXPLAIN关键字查看查询的执行计划，检查是否使用了索引、是否进行了全表扫描等。

1.3 避免在WHERE子句中使用函数

• 问题：在WHERE子句中使用函数会导致索引失效。

• 优化：尽量避免在WHERE子句中对列使用函数，例如将WHERE YEAR(date_column) = 2024改为WHERE date_column BETWEEN '2024-01-01' AND '2024-12-31'。

1.4 使用IN代替多个OR

• 问题：多个OR条件可能导致查询性能下降。

• 优化：使用IN代替多个OR，例如将WHERE column = 'a' OR column = 'b' OR column = 'c'改为WHERE column IN ('a', 'b', 'c')。

1.5 使用EXISTS代替IN

• 问题：IN子句在某些情况下可能不如EXISTS高效。

• 优化：对于子查询，优先使用EXISTS，例如将SELECT * FROM table1 WHERE column IN (SELECT column FROM table2)改为SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column)。

1.6 避免使用NOT IN和NOT EXISTS

• 问题：NOT IN和NOT EXISTS可能导致性能问题。

• 优化：尽量使用LEFT JOIN和IS NULL代替NOT IN和NOT EXISTS。

2. 索引优化

2.1 合理创建索引

• 问题：索引过多会增加写操作的开销，索引过少会导致查询性能下降。

• 优化：根据查询条件和排序需求合理创建索引，避免冗余索引。

2.2 使用复合索引

• 问题：单列索引可能无法满足复杂查询的需求。

• 优化：创建复合索引，并遵循最左前缀原则。例如，对于查询条件WHERE column1 = ? AND column2 = ?，创建复合索引(column1, column2)。

2.3 定期维护索引

• 问题：索引可能会因为数据的频繁插入、删除和更新而变得碎片化。

• 优化：定期对索引进行优化，例如使用OPTIMIZE TABLE命令或手动重建索引。

2.4 避免过度索引

• 问题：过多的索引会增加写操作的开销。

• 优化：只创建必要的索引，避免创建冗余索引。

3. 配置优化

3.1 调整innodb_buffer_pool_size

• 问题：innodb_buffer_pool_size设置不当会导致缓存不足或浪费内存。

• 优化：根据服务器的内存大小，将innodb_buffer_pool_size设置为内存的60%-80%。

3.2 调整innodb_log_file_size和innodb_log_buffer_size

• 问题：日志文件大小和缓冲区大小设置不当会影响写操作的性能。

• 优化：适当增大innodb_log_file_size和innodb_log_buffer_size，以减少日志写入的频率。

3.3 调整query_cache_size

• 问题：查询缓存可能会导致性能问题，尤其是在高并发场景下。

• 优化：在MySQL 5.7及以上版本中，查询缓存默认是禁用的。如果需要启用，应根据实际情况调整query_cache_size。

3.4 调整max_connections

• 问题：max_connections设置过低可能导致连接数不足，设置过高可能导致资源耗尽。

• 优化：根据服务器的硬件资源和应用需求，合理设置max_connections。

4. 架构优化

4.1 使用主从复制

• 问题：单个数据库实例可能无法满足高并发读写需求。

• 优化：使用主从复制架构，将读操作分发到从节点，减轻主节点的压力。

4.2 使用分库分表

• 问题：单表数据量过大可能导致查询性能下降。

• 优化：通过分库分表将数据分散到多个表或数据库中，减少单表数据量。

4.3 使用缓存技术

• 问题：频繁的数据库查询可能导致性能瓶颈。

• 优化：使用缓存技术（如Redis、Memcached）缓存热点数据，减少数据库的查询压力。

4.4 使用分布式数据库

• 问题：单机数据库无法满足大规模数据存储和高并发访问需求。

• 优化：使用分布式数据库（如ShardingSphere、Cassandra）来解决大规模数据存储和高并发访问问题。

5. 其他优化

5.1 定期清理数据

• 问题：表中积累了大量无用数据，会影响查询性能。

• 优化：定期清理表中的无用数据，例如使用DELETE语句或TRUNCATE TABLE。

5.2 使用分区表

• 问题：单表数据量过大可能导致查询性能下降。

• 优化：使用分区表将数据分散到多个分区中，提高查询效率。

5.3 使用ANALYZE TABLE更新统计信息

• 问题：统计信息过时可能导致查询优化器选择不合适的执行计划。

• 优化：定期使用ANALYZE TABLE更新表的统计信息。

5.4 避免大事务

• 问题：大事务会占用大量资源，影响数据库性能。

• 优化：尽量将大事务拆分为多个小事务，减少事务的锁时间和资源占用。

单表记录数过大

1. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时 候，我们可以控制在一个月的范围内。

 2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区： 根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信 息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

4. 水平分区： 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达 到了分布式的目的。 水平拆分可以支撑非常大的数据量。

事务

原子、一致、并发、持久

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这 时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个 事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事 务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢 失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最 终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务 也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的 数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发 事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就 好像发生了幻觉一样，所以称为幻读。