数据库的优化

image.png

• MySQL的缓存（什么是缓存）

1.MySQL的缓存是一种KV结构，保存在服务器的内存当中。（K是SQL语句，V查询结果）
2.表的结构发生改变，或值发生改变（删、改、查），缓存都会失效，不适用于频繁修改的场景。
3.自定义函数，存储函数，视图，变量、系统表等缓存都不会记录。
4.缓存会给数据库系统带来额外的开销。读操作，无论是否命中都检查缓存，并保存至缓存。

• 那些场景使用缓存

根据业务不同做好拆表工作。批处理代替单条数据插入。

• MySQL的语法解析

通过对关键字的匹配（SELECT、*、FROM等），产生语法解析树。
如果有预处理操作则生成新的解析树，使用预处理也可以提高效率。

• MySQL的执行计划

explain + SQL语句可查看MySQL的执行计划

MySQL执行计划.png

id

包含一组数字，表示查询中执行select子句或操作表的顺序，从上往下按顺序执行，数字越大，优先级越高，越先执行。

select_type

表示查询的语句的种类型，简单或者复杂。

MySQL在表中找到所需行的方式，又称访问类型。

访问类型的方式

访问类型.png

• All全表查询
• index查找索引树
• range 索引范围查询，对索引的查找开始于某个位置，例如 in 、 between 、< 、> 等。
• eq_ref唯一索引扫描，每个索引键只有一条记录与之匹配。
• const,system mysql对查询部分进行优化,转换为一个常量,例如主键作为WHERE语句的条件.

possible_keys

指出Mysql在使用哪个索引查询,如果没有使用索引则为null.

key_len

索引的长度

ref

哪些列或常量被用于查找索引列上的值.

rows

估算的找到所需的记录所需要读取的行数

Extra

重要的额外信息
例如:
Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index） Using where：表示MySQL服务器在存储引擎搜索到记录后进行“后过滤”（Post-filter）
如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

数据库优化的三个方面

• 表结构优化
  1.符合范式（三大范式）
  2.拆表(水平/垂直)
  3.使用InnoDB存储引擎,支持事务,行级锁.并发性能更好、CPU以及内存的使用率更高。
  4.谨慎使用存储过程、视图、触发器、Event：并发量太大的情况下，这些功能会可能将数据库拖死，业务逻辑放到业务逻辑上会有更好的扩展性，数据库性能很难扩展，放到服务器上可以通过提升硬件的性能来提升效率。
  5.单个实例表的行数少于500，表的字段少于30。
• 数据类型的优化
  1.char >varchar >text
  MyISAM建议使用char，InnoDB使用varchar。

2. 日期类型避免使用Datetime，建议使用Timestamp(4个字节)。 对于只需要精确至某一天的数据，建议使用Date（只需要3个字节，比Timestamp还少）。

• 索引优化
  1.一个表的索引建议在5个以内，字段数不超过5个，不要使用区分度不高的索引。
  2.组合索引将区分度高的放在前面，查询条件会优先匹配。

3. 索引应建立在MAX( )、MIN( )、ORDER BY、WHERE、JOIN等函数/语句对应的字段。
   4.避免在有索引的字段使用函数，将无法使用索引。
   5.避免使用like时使用索引，“%xx”不走索引，“xx%”才能走索引。