MySQL优化

1. 数据库是如何实现分页的，假设有100万条数据如何优化分页查询

分页查询的语句

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

select * from tbl limit 1000,10;  // 很快，0.01s
select * from tbl limit 90000,10;  // 从9万条开始分页，很慢，9s

优化：

• 使用子查询结合id进行优化，当id是连续递增的时候，根据查询的页数和查询的记录数可以算出查询id的范围。

  select * from tbl 
  where id>=(select id from tbl order by order_id limit 1000000,1) 
  limit 10;
  

• 通过索引进行查询分页。

2. 如何优化MySQL

1. 存储引擎的选择：根据不同的需求选择不同的存储引擎
   1. Innodb：数据完整性，支持事务，适用于高并发、擅长更新和删除。（财务系统、经常修改删除、需要事务回滚）
   2. MyISAM：删除查询及插入。（微博插入、查询，很少更新和删除，数据完整性没有太强烈要求）。
2. 字段类型的选择
3. 范式与逆范式
4. 索引，提高查询速度

3. 索引设计规范

1. 选择合适的字段

   • 不为NULL
   • 频繁查询排序的
   • 频繁更新的慎重建立索引

2. 尽可能建立联合索引，索引对应B+树，索引多了，占用空间。

3. 避免冗余索引，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。

4. 考虑在字符串类型的字段上使用前缀索引代替普通索引。

4. SQL开发规范

1. 避免使用前置 %，索引失效

2. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

   • SELECT * 消耗更多的 CPU 和 IO 以网络带宽资源
   • SELECT * 无法使用覆盖索引
   • SELECT <字段列表> 可减少表结构变更带来的影响

3. 避免使用子查询，可以把子查询优化为 join 操作

   • 子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源

4. 避免使用 JOIN 关联太多的表

   • 多关联（join）一个表，就会多分配一个关联缓存，占用内存
   • 产生临时表操作，影响查询效率

5. or 的查询尽量用 union 或者 union all 代替（在确认没有重复数据或者不用剔除重复数据时，union all 会更好）

6. 尽量避免在 where 子句中使用 != 或 <> 操作符，避免在 where 子句中对字段进行 null 值判断（可以设置默认值0），否则引擎将放弃使用索引而进行全表扫描。

7. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在 num 上设置默认值 0，确保 num 别没有 null 值，如何这样查询：select id from t where num = 0。

5. 优化

做 MySQL 优化，我们要善用 EXPLAIN 查看 SQL 执行计划。主要查看的字段：

• type：优化 sql 的重要字段，是判断 sql 性能和优化程度重要指标
  • 执行效率 system > const > eq_ref > ref > range > index > ALL
  • 优化目标至少达到 range 级
• key：真正走的索引，无索引就是 null
• rows：读取的数据量，越少越优