MySQL优化学习
文章目录
一、 库表优化
1. 数据类型
类型选择原则：
- 更小的通常更好，但要确保没有低估
- 尽可能使用简单数据类型（ip用整型）
- 尽可能不使用null
浮点类型：
- DECIMAL存储精确的小数
- FLOAT和DOUBLE使用更少空间
- 对于需要高精度的财务类数据，可以乘以一定倍数后采用BIGINT计算，避免浮点数计算不精确和DECIMAL计算代价过高的问题
字符串类型：
- VARCHAR：可变长字符串，使用额外字节记录长度，适用于UTF-8数据集
- CHAR：定长，存储密码的md5值
- BLOB/TEXT：BLOB存储二进制大数据，TEXT类型有排序规则
- ENUM：枚举类型，两个ENUM列关联速度很快
时间类型：
- DATETIME：使用8字节存储空间，将日期和时间装到格式为YYYYMMDDHHMMSS的整数中
- TIMESTAMP：使用4字节存储空间，显示的值依赖于时区
2. 范式
优点和缺点：
- 范式化的表一般比较小，更新操作快
- 重复数据少，更少需要DISTINCT和GROUP BY语句
- 缺点是通常需要关联
混用反范式化和范式化：
- 新增一列作为缓存并建立索引，加快排序
- 缓存衍生，如论坛中记录发帖数
缓存表和汇总表：
- 缓存表：可以较简单从其他表获得的数据
- 汇总表：保存GROUP BY语句聚合数据的表
二、索引优化（存储引擎层）
1. 索引类型
B-Tree：
- MyISAM通过数据的物理位置引用被索引的行
- InnoDB根据主键引用被索引的行
- 存储引擎不再需要进行全表扫描，取而代之是从索引的根节点开始进行搜索
B-Tree索引查询类型：
- 全值匹配：和索引中所有列进行匹配
- 匹配最左前缀：索引从左开始的x列匹配
- 匹配列前缀：只匹配某一列值的开头部分
- 匹配范围值
- 精确匹配某一列，范围匹配另一列
- 只访问索引的查询
B-Tree索引查询限制：
- 最左开始，无法跳过中间的列(最左前缀)
- 如果查询中对某列进行了范围查询，则其右边的所有列都无法使用索引优化查找
哈希索引：
- 只包含行指针+哈希值，不包含数据
- 不按照索引值顺序存储，无法用于排序
- 不支持部分索引列匹配查找，必须使用全部
- 不支持范围查询
- InnoDB的自适应哈希索引（一种优化）
2. 索引策略
索引的优点：
- 索引可以大大减少数据库表的扫描量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变成顺序I/O
索引失效：
- 索引不能是表达式的一部分
- 索引不能是函数的参数
索引选择：
- 前缀索引：使得索引更小，更快，但是无法做GROUP BY和ORDER BY操作，也无法覆盖扫描
- 索引列顺序：经验法则是将选择性最高的放在最前面
聚簇索引：实际上是一种数据的存储方式
- 数据航存放在索引的叶子结点，且数据行和相邻的键值紧凑地存放在一起
优点：
- 可以将相关数据保存在一起，减少磁盘I/O
- 索引和数据保存在一个B-Tree，数据访问更快
- 使用覆盖索引的扫描时可以直接使用主键
缺点：
- 插入速度依赖于插入顺序，最好是按照主键顺序插入
- 更新列代价很高
- 插入行可能导致页分裂
InnoDB和MyISAM的索引：
- MyISAM索引中的叶子结点包含了行号
- InnoDB的聚簇索引，叶子结点包含主键，事务ID，回滚指针和所有剩余列
- InnoDB的二级索引存储的是主键值，不是行指针
三、 查询优化
1. 优化数据访问
- 避免查询不需要的记录：添加limit
- 避免多表查询查询所有列：不使用select *
- 重复查询相同数据：采用缓存即可
2. 从好到坏的where条件应用
- 最佳：存储引擎层在索引中使用where过滤不匹配的记录
- 次佳：使用索引覆盖扫描，直接从索引中过滤不需要的记录并返回，在服务器层完成
- 最次：先从数据表中返回数据，然后过滤，需要回表查询
3. 重构查询
- 一个复杂查询改为多个简单查询
- 对大查询分而治之，减少锁持有的时间：例如删除过期记录，每次LIMIT 10000
- 分解关联查询：常见使用IN方式替代
- 让缓存效率更高
- 减少锁的竞争
- 应用层关联便于表的拆分
- 减少冗余记录查询
4. 特定类型优化
COUNT()查询：
- COUNT(*)统计结果集
- COUNT(列名)统计列的非空值的数量12select count(color='blue' OR NULL) as blue,count(color='red' OR NULL) as red from items;
优化关联查询：
- 确保ON或者USING的列上有索引，其中两表关联，只需要在第二个表的相应列创建索引
- 确保GROUP BY或者ORDER BY只涉及一个表中的列
优化LIMIT分页：使用索引覆盖扫描，然后再进行关联
