1819 字
9 分钟
MySQL
MySQL的执行顺序
from > join > where > group by > 聚合函数 > having > select > order by > limit
格式化函数(格式化数字：FROMAT(),格式化日期：DATE_FORMAT())会在
group by之前执行
count的条件用法
count 函数用于计算非 null 值的数量。
count(age > 20 or null)里面的or null必须加，否则就等于count(*)
count对于不管是0还是1，都会计数一次，只有null不会被计数。
age > 20：这是一个布尔表达式，当age大于 20 时返回TRUE，否则返回FALSE。or null：当布尔表达式age > 20为FALSE时，将其转换为null，从而使得这些记录不会被count计数。
或者可以写为sum(age > 20)
MySQL中的数据排序
MySQL中的数据排序主要通过ORDERBY子句实现，其底层实现机制主要有两种方式：
- 使用索引排序：当ORDERBY子句中的列恰好有对应的索引时，MySQL可以直接利用索引来完成排序操作，这是最高效的排序方式。索引本身是有序的，所以MySQL只需要按照索引的顺序扫描即可得到排序后的结果。
- 文件排序（filesort）：当无法使用索引进行排序时，MySQL会使用文件排序。这种方法会将需要排序的数据加载到内存中，然后在内存中进行排序。如果数据量较大，超过了系统变量sort_buffer_size的大小，还会使用临时文件来协助排序。
具体排序过程：
首先，检查ORDER BY 子句中的列是否有可用的索引。
如果有可用的索引，直接使用索引排序；如果没有可用的索引，MySQL会执行以下步骤：
- 从表中读取满足条件的所有行，每一行只保存需要排序的列和可以唯一标识行的列（如主键）
- 数据量小时，使用快排算法进行排序；数据量太大时，使用临时文件来辅助排序
- 根据排序结果回表查询所需的列
ORDER BY的使用可能会对查询性能产生显著影响。我们可以通过以下方式优化排序操作:
- 在经常需要排序的列上创建适当的索引。
- 尽量避免对大数据集进行排序。
- 如果可能，尽量使用覆盖索引，即索引包含了查询所需的所有列，这样可以避免回表操作。
- 适当调整系统变量，如增加sortbuffer_size的大小。
- 考虑使用限制结果集大小的LIMIT子句，特别是在只需要前几条记录的情况下。
MySQL的索引类型
| 划分方向 | 类型 |
|---|---|
| 数据结构 | B+树索引、哈希索引、倒排索引（Full text）、R-树索引（多维空间树）、位图索引（Bitmap) |
| 物理存储 | 聚簇索引、非聚簇索引 |
| 字段特性 | 主键索引、唯一索引、普通索引（二级索引、辅助索引）、前缀索引 |
| 字段个数 | 单列索引、联合索引 |
- B+树索引：通过树形结构存储数据，适用于范围查询和精确查询，支持有效数据的快速查找、排序和聚合操作，MySQL的默认索引类型，常用于InnoDB和MyISAM。
- 聚簇索引：索引的叶子节点存储完整数据记录
- 非聚簇索引：索引的叶子节点存储了主键值和对应的索引字段
- 主键索引：唯一且不能为NULL，每个表只能有一个，InnoDB中主键索引是聚簇索引结构实现的
Q：InnoDB为什么使用B+树实现索引？
- B+树是一棵平衡树，每个叶子节点到根节点的路径长度相同，查询效率高
- 所有关键字都在叶子节点上，因此范围查询时只需要遍历一遍叶子节点即可。
- 叶子节点都按照关键字的大小顺序存放，因此可以快速的根据关键字大小进行排序。
- 非叶子节点不存储实际数据，因此可以存储更多的 索引数据
- 非叶子节点使用指针链接叶子节点，因此可以快速的支持范围查询和倒序查询。
- 叶子节点之间通过双向链表链接，方便进行范围查询。所以可以总结得出，使用B+树有以下有几点，支持范围查询、支持排序、可以存储更多的索引数据、因为叶子节点大小固定，节点分裂和合并时，IO操作少，同时因为大小固定，还有利于磁盘预读，因为非叶子节点只存储指向子节点的指针，而不存储数据，所以可以缓存更多的索引数据，有利于缓存。
Q：B+树索引和Hash索引有什么区别？
- 因为B+树索引将索引列的值按照大小排序存储，所以更适合于范围查询，而哈希索引是基于Hash表的结 构，所以哈希索引更适合等值查询，但不适合范围查询和排序操作。
- 如果B+树索引插入数据和删除数据时需要调整索引结构，可能涉及到页分裂和页合并等操作（无序插入），维护成本较高，而哈希索引在插入和删除数据只需要计算哈希值并插入或者删除相应的记录。
- B+树索引在磁盘上是有序存储的，而哈希索引是无序存储的
Q：唯一索引和主键索引的区别？
- 两者都具有唯一性，但是主键索引不能为null，唯一索引可以
- 主键索引每表只能有一个，唯一索引可以创建多个
- 在InnoDB中，主键索引就是聚簇索引，但唯一索引通常是非聚簇索引（除了特殊情况，就是在没有创 建主键索引的情况下，MySQL会默认选择一个唯一的非空索引作为聚簇索引）
- 主键索引一定不需要回表，但是唯一索引查询通常是需要回表的
- 主键可以被其他表引用为外键，而唯一索引不可以。
Q：MySQL如何保证唯一索引的唯一性？
支持事务的存储引擎中（例如lnnoDB）中，事务机制和锁定协议帮助维护索引的唯一性，当一个事务正在修改索引列时，其他事务对相同键值的修改会被适当的阻塞，直到第一个事务提交或回滚，确保了数据的一致性和唯一性，并且在实际的写入数据到磁盘之前，MySQL也会执行约束检查，确保不会违反唯一性约束。相应的因为唯一索引保证了指定列的值唯一，会让唯一性索引查询比非唯一性查询根块，因为能够快速的匹配到唯一的记录，但是也是因为要保证索引列的唯一性，因此在插入的时候需要检查是否存在相同的索引值，会对插入性能产生一定的影响。