学习笔记之 MySQL 学习第三部分NULL值、事务、索引文章是关于 MySQL 学习的第三部分，包括 NULL 值处理

书接上回~

NULL 值处理

MySQL提供了三个运算符用来处理null，分别是IS NULL、IS NOT NULL、<=>
不能使用等于号(=)或者不等于号(!=)在列中查找值
如果数据值使用的是null，那么对应的字段在前端展示的时候就可能会直接展示为字符串“null”
可以使用COALESCE替换查询结果中的null值
在使用ORDER BY 子句进行排序时，NULL值默认会被放在排序的最后
如果希望将null值放在最前面，可以使用 order by column_name ASC NULLS FIRST
如果希望将null值放在最后面，可以使用 order by column_name DESC NULLS LAST
<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符，对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。
在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。

事务

只有使用innodb数据库引擎的数据库和表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句

事务必须满足的四个条件

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括：
- 读未提交（Read Uncommitted）
  - 这是最低的隔离级别。在这个级别下，一个事务可以读取另一个未提交事务的数据。这种情况可能会导致脏读（Dirty Read）。
  - 例如，事务 T1 修改了一个数据但还未提交，事务 T2 在这个时候读取了 T1 修改的数据。如果 T1 后来回滚了修改，那么 T2 读取的数据就是无效的、“脏” 的数据。
- 读已提交（Read Committed）
  - 这个级别可以避免脏读。一个事务只能读取已经提交的其他事务的数据。
  - 例如，事务 T1 修改数据并提交后，事务 T2 才能读取到 T1 修改后的数据。不过，在这个级别下可能会出现不可重复读（Non - Repeatable Read）的问题。假设事务 T2 在同一个事务中两次读取同一数据，在两次读取之间，事务 T1 修改并提交了这个数据，那么 T2 两次读取的结果就不一样。
- 可重复读（Repeatable Read）
  - 可以避免不可重复读。在这个隔离级别下，一个事务在执行过程中多次读取同一数据，其结果是相同的，即使其他事务对该数据进行了修改并提交。
  - 例如，事务 T2 在事务过程中多次读取某一数据，其他事务 T1 修改并提交了这个数据，T2 读取的数据还是最初的值。不过，在这个级别下可能会出现幻读（Phantom Read）的情况。幻读是指一个事务在按照某个条件读取数据时，没有数据行符合条件。但是在这个事务还未结束时，另一个事务插入了新的数据行，使得第一个事务再次按照相同条件读取时，出现了之前没有的 “幻影” 数据行。
- 串行化（Serializable）
  - 这是最高的隔离级别。在这个级别下，事务是串行执行的，一个事务必须等待前一个事务完成后才能开始，就像事务是一个一个排队执行一样，完全避免了脏读、不可重复读和幻读的问题。不过，这种方式会严重影响数据库的并发性能。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。
打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分类

普通索引(Normal Index)：这是最基本的索引类型，它没有任何限制，只是简单地加快对表中数据行的访问速度。索引列的值可以为空值（NULL），并且可以包含重复的值。
唯一索引(Unique Index)：唯一索引要求索引列的值必须是唯一的，但是可以包含空值（NULL）。它在保证数据唯一性方面非常有用。
主键索引(Primary Key Index)：主键索引是一种特殊的唯一索引。它要求索引列的值不能为空（NULL）且必须是唯一的。一个表只能有一个主键，主键用于唯一标识表中的每一行数据。
组合索引(Composite Index)：又称为复合索引。组合索引是由多个列组合而成的索引。索引按照这些列的顺序进行排序和存储。当查询条件涉及组合索引中的多个列时，可以有效地提高查询效率。
全文索引(Full - Text Index)：全文索引主要用于在文本类型的数据中进行关键字搜索。它会对文本内容进行分词处理，将文本拆分成一个个的单词或词组，并建立索引。

索引优化

查询优化
- 使用索引优化查询
  - 如前面提到的索引优化，合理创建和使用索引能极大地提高查询速度。确保在经常用于筛选（WHERE）、排序（ORDER BY）和分组（GROUP BY）条件的列上建立索引。例如，在一个电商系统的商品表中，若经常按商品类别和价格进行查询和排序，就在“商品类别”和“价格”列上建立索引。
  - 避免在索引列上使用函数，因为这可能会导致索引失效。例如，SELECT * FROM users WHERE YEAR(birth_date) = 1990，这里在birth_date索引列上使用了YEAR函数，索引可能无法正常使用，更好的做法是可以先计算好年份并存储在另一个列中，或者重新设计查询方式。
- 优化查询语句结构
  - 减少子查询的使用，尤其是嵌套较深的子查询。子查询可能会使查询执行效率降低，很多时候可以用连接（JOIN）来代替。例如，若有一个查询是查询每个部门的员工数量，用子查询可能是SELECT department_id, (SELECT COUNT(*) FROM employees WHERE department_id = d.department_id) AS employee_count FROM departments d，而用连接可以写成SELECT d.department_id, COUNT(e.employee_id) AS employee_count FROM departments d JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id，后者通常效率更高。
  - 合理使用LIMIT限制返回结果的数量。如果只需要查询部分结果，不要返回所有数据。例如，在一个分页查询中，每页显示10条记录，就可以使用LIMIT来获取指定页的记录，这样可以减少数据传输和处理的时间。
表结构优化
- 选择合适的数据类型
  - 根据实际需求选择数据类型，避免使用过大的数据类型。例如，如果一个字段存储的是年龄，使用TINYINT（范围是 - 128到127）就足够了，而不需要使用INT。对于字符串字段，如果长度是固定的且已知，比如存储手机号码（11位），可以使用CHAR(11)，如果长度不固定，如用户评论，使用VARCHAR更合适。
  - 尽量使用数字类型代替字符串类型存储可以用数字表示的数据。例如，用0和1表示“否”和“是”，而不是使用字符串"no"和"yes"，因为数字类型在存储和比较时效率更高。
- 避免过度冗余的表结构
  - 虽然适当的冗余可以提高查询性能，但过度冗余会导致数据不一致和维护成本增加。例如，在一个学生 - 课程 - 教师关系表中，如果每个学生记录中都存储课程教师的详细信息，当教师信息发生变化时，需要更新所有相关的学生记录，容易出现数据不一致的情况。可以通过合理的关联查询来获取相关信息，减少不必要的冗余。
- 范式化和反范式化的合理应用
  - 范式化是将数据分解到多个表中，以减少数据冗余和提高数据的一致性。例如，在一个订单 - 商品关系中，将订单信息和商品信息分别存储在不同的表中，通过外键关联，这样可以避免订单表中商品信息的过度冗余。
  - 反范式化则是在某些情况下，为了提高查询性能，故意增加数据冗余。例如，在上述订单 - 商品关系中，如果经常需要查询订单的总金额（需要根据商品价格和数量计算），可以在订单表中增加一个“总金额”列，这样在查询订单总金额时，不需要每次都进行计算，直接从订单表中获取即可。但要注意反范式化可能带来的数据不一致问题，需要谨慎使用。
存储引擎优化
- 了解不同存储引擎的特点
  - InnoDB是MySQL默认的存储引擎，它支持事务、行级锁定和外键约束，适合大多数的应用场景，尤其是对数据一致性和并发性能要求较高的场景。例如，在金融系统的数据库中，InnoDB可以很好地处理账户交易等事务操作。
  - MyISAM存储引擎不支持事务和行级锁定，但它的全文索引性能较好，并且在插入和查询简单结构数据时速度可能更快。如果是一个主要用于存储和查询文章内容（对全文索引有较高要求）的数据库，MyISAM可能是一个选择。
- 根据应用场景选择存储引擎
  - 对于读写操作频繁且需要事务支持的应用，如电商系统的订单处理和库存管理，选择InnoDB。对于以读操作为主，且对全文索引有需求的应用，如新闻网站的文章存储和检索，考虑MyISAM或InnoDB（配合全文索引插件）。
- 优化存储引擎的参数设置
  - 对于InnoDB存储引擎，可以调整缓冲池大小（innodb_buffer_pool_size）来提高性能。缓冲池是InnoDB存储和读取数据的主要区域，适当增大缓冲池可以减少磁盘I/O。例如，通过监控数据库的性能指标，发现磁盘I/O频繁，可以尝试增大缓冲池大小来改善性能。
服务器硬件和配置优化
- 硬件优化
  - 增加内存可以为数据库缓存更多的数据，减少磁盘访问。例如，将服务器的内存从4GB增加到8GB，可以使数据库的缓冲池（如InnoDB的缓冲池）能够缓存更多的数据行和索引，从而提高查询速度。
  - 选择更快的磁盘，如固态硬盘（SSD）代替机械硬盘。SSD的读写速度比机械硬盘快很多，尤其是随机读写性能。这可以大大减少数据库的I/O等待时间，提高数据库的整体性能。
- 配置优化
  - 调整MySQL的配置参数，如max_connections（最大连接数）。如果应用程序的并发用户数较多，需要适当增大最大连接数，以避免出现“连接太多”的错误。但也要注意，过大的最大连接数可能会导致服务器资源耗尽。
  - 优化查询缓存（query_cache_size）设置。查询缓存可以缓存查询结果，当再次执行相同的查询时，可以直接从缓存中获取结果。但在高并发环境下或者数据更新频繁的情况下，查询缓存可能会带来性能下降，需要根据实际情况合理设置其大小或考虑关闭查询缓存。

慢查询分析

开启慢查询日志（Slow Query Log）
- 配置文件方式：在MySQL的配置文件（通常是my.cnf或my.ini）中，找到并修改与慢查询日志相关的参数。需要设置slow_query_log为1来开启慢查询日志，long_query_time参数用于定义什么样的查询被认为是“慢查询”，单位是秒。例如，将long_query_time设置为2，表示查询执行时间超过2秒的查询会被记录到慢查询日志中。
```
[mysqld] 
slow_query_log = 1
long_query_time = 2 
```
- 命令行方式：也可以通过SQL命令来开启慢查询日志。例如，使用SET GLOBAL slow_query_log = 1;来开启慢查询日志，SET GLOBAL long_query_time = 2;来设置慢查询的时间阈值。不过这种方式在MySQL服务重启后配置会失效，适合临时测试。
分析慢查询日志内容
- 日志文件位置：默认情况下，慢查询日志文件的位置由slow_query_log_file参数指定。可以在配置文件中找到这个参数的设置。在Linux系统中，常见的位置可能是/var/lib/mysql/目录下。
- 日志内容解读：慢查询日志中记录了慢查询的相关信息，包括查询开始时间、查询执行时间、查询语句等。例如，日志记录可能如下：
```
# Time: 2024-01-01T10:00:00.000000Z 
# User: root 
# Query_time: 3.000000 Lock_time: 0.100000 Rows_sent: 100 Rows_examined: 1000
SELECT * FROM large_table WHERE condition; 
```
- 其中，Query_time是查询执行时间，Lock_time是获取锁的时间，Rows_sent是发送给客户端的行数，Rows_examined是查询检查的行数。通过这些信息可以初步判断查询慢的原因，比如是否因为查询的数据量太大（Rows_examined值很大）或者查询本身的复杂度高。
使用EXPLAIN命令
- 基本用法：在查询语句前加上EXPLAIN关键字，MySQL会返回一个关于查询执行计划的结果。例如，对于SELECT * FROM users WHERE age > 30;这个查询，使用EXPLAIN SELECT * FROM users WHERE age > 30;，会得到一个表格形式的结果。
- 结果解读：
  - id列：表示查询中每个SELECT子句的标识符，用于区分不同的SELECT子句。
  - select_type列：表示查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
  - table列：显示查询涉及的表。
  - type列：这是很重要的一列，它表示MySQL在表中找到所需行的方式，从最优到最差的顺序为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。例如，eq_ref表示对于每个来自前面的表的行组合，从该表中读取一行，这是一种比较高效的连接方式。
  - possible_keys列：显示可能使用的索引。
  - key列：实际使用的索引。
  - key_len列：表示索引使用的字节数。
  - ref列：显示索引的哪一列或常量被用于查找索引列的值。
  - rows列：表示MySQL根据表统计信息及索引选用情况，估算要读取的行数。
  - Extra列：包含了一些额外的信息，如Using index（使用了覆盖索引）、Using where（使用了WHERE条件过滤）等。通过分析这些信息，可以了解查询是否有效地利用了索引，以及是否可以进行优化。
使用性能分析工具
- MySQL自带的性能分析工具：MySQL Workbench提供了性能分析功能。它可以帮助用户可视化地查看数据库的性能指标，如查询执行时间、资源占用等。通过它可以直观地找到慢查询，并对查询进行优化。
- 第三方性能分析工具：如pt - query - digester，它可以对慢查询日志进行分析，提供更详细的查询性能报告，包括查询的分布情况、执行时间的统计等，帮助用户更好地理解慢查询的情况并采取相应的优化措施。