数据库优化

SQL优化、索引优化、数据库结构优化、存储引擎优化、服务器优化

数据库优化目的是合理安排资源、调整系统参数，使mysql运行更快、更节省资源。
优化包括查询、更新、服务器等。
优化原则：减少系统瓶颈、减少资源占用、增加系统反应速度

SQL语句优化

  1. 尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而使用全表扫描。
  2. 尽量避免在where子句中对字段null值判断，否则引擎将放弃使用索引而使用全表扫描。
  3. 很多时候用exsits代替in是一个不错的选择。
  4. 用where子句替换having子句，因为having子句只会在检索出所有记录之后才对结果进行过滤。
  5. 一般情况下不鼓励使用like操作，如果非用不可，也要考虑如何使用。例如，like “%aaa%”不会使用索引，而like “aaa%”可以使用索引。
  6. 避免在每个行上进行运算，否则引擎将放弃使用索引而使用全表扫描。例如，select * from users where YEAR（adddate）<2007；可以改写成select * from users where adddate<‘2007-01-01’；
  7. 使用连接join代替子查询，因为子查询需要在内存中创建临时表来完成查询工作，而join不需要创建临时表。
  8. 查询语句的查询条件中只有or关键字时且or前后两个条件的列都是索引时，索引才会生效，否则索引不生效。

索引优化

  1. 创建索引
  2. 复合索引
  3. 索引不包含有null值的列
  4. 使用短索引
  5. 避免在order by后面跟有索引的列

数据库结构优化

  1. 范式优化，消除冗余
  2. 反范式优化，适当增加冗余增加查询速度，但冗余的字段在一个表中更新后就要在其他表中也更新，保持数据的一致性
  3. 将字段很多的表拆分成多个表，拆分可分为垂直拆分和水平拆分
  4. 增加中间表，对于需要经常联合查询的表，可以建立中间表提高查询效率
  5. 优化插入数据。插入数据时，影响速度的主要是索引、唯一性校验、一次插入的数据条数等。此外，不同的存书引擎优化手段也不同：

  • Myiasm：

    （1）禁用索引：对于非空表，如果插入大量数据，建立索引会降低插入数据速度
    （2）禁用唯一性检查
    （3）批量插入数据
    （4）使用load data infile

  • Innodb：

    （1）禁用唯一性检查
    （2）禁用外键检查
    （3）禁止自动提交

存储引擎优化

MyIASM：

  • 尽量索引（缓存机制）
  • 调整读写优先级，根据实际需要确保重要操作优先
  • 启用延迟插入，可以改善大批量写入性能
  • 尽量顺序操作，让insert操作写到尾部，减少阻塞
  • 分解大的动作，减少单个操作的阻塞时间
  • 降低并发数
  • 对于静态的数据，可以利用队列缓存来提高访问效率
  • MyIASM的count只有在全表扫描时才特别高效，带有其他条件时和别的引擎就没区别了

Innodb：

  • 主键尽可能小，避免二级索引带来过大的空间负担
  • 避免全表扫描
  • 尽可能缓存所有索引和数据，提高响应速度
  • 在大批量小插入的时候，尽量自己控制事务而不要用自动提交
  • 合理设置参数，不要过分追求安全性
  • 避免主键更新

服务器优化

硬件优化：内存，SSD，IO，CPU

优化mysql参数：

  • key_buffer_size：表示索引缓冲区大小
  • table_cache：表示可以同时打开的表的数量
  • query_cache_size：表示查询缓冲区的大小
  • sort_buffer_size：表示排序缓冲区的大小
  • read_buffer_size：表示每个线程扫描每个表时分配的缓冲区大小
  • read_rnd_buffer_size：表死为每个线程保留的缓冲区大小
  • innodb_buffer_pool_size：表示innodb类型的表和索引的最大缓存
  • max_connection：表示数据库的最大连接数
  • innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志，并将日志写入磁盘
  • back_log：表示mysql在暂停回答新请求的短时间内，多少个请求可以被存在堆栈
  • interactive_timeout：表示服务器关闭连接前等待行动的秒数
  • sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区大小
  • thread_cache_size：表示可以复用的线程数量
  • wait_timeout：表示服务器在关闭一个连接时等待行动的秒数
Written on January 10, 2018