个人文档

# mysql优化

# 对mysql进行优化的前提是了解为何会慢以及如何优化

 对一个查询而言，真正重要的是响应时间，如果把查询看作一个任务，那么它就是由一系列子任务组成的，我们要做的就是消除一些其中的子任务或者减少子任务的执行次数或者加快子任务的执行速度

# 常用方法

# 1.增加缓存以减少数据库访问

# 2.明确查询的条数时使用limit限制

# 3.select明确字段取代select *

  • 取出全部的列会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O，内存和CPU消耗。
  • 不使用select *不代表不能查询返回超过需要的数据，在使用缓存机制或者后续的代码复用上，返回更多的数据也可能对整个应用带来更多的好处。

# 4.除非有真实需要，否则尽可能使用not null以避免null判断

# 5.搜索字段建立索引，但需要合理使用

# 6.合适的搜索引擎（5.6版本及以上）

  • innodb，默认引擎也是最常用的引擎。
  • 支持b-tree索引
  • 支持备份/时间点恢复
  • 支持聚集索引
  • 支持压缩数据
  • 支持数据缓存
  • 支持数据加密，5.7版本后支持Data-at-rest tablespace encryption
  • 支持外键
  • 5.6之后支持全文索引
  • 支持地理数据类型
  • 支持地理索引，（5.7以后）
  • 支持索引缓存
  • 支持行级锁定
  • 支持mvcc
  • 支持复制
  • 存储限制64TB
  • 支持事务
  • 支持Update statistics for data dictionary
  • myisam,5.1之前默认引擎
  • 支持b-tree索引
  • 支持备份/时间点恢复
  • 支持压缩数据（只有压缩行格式才支持，且使用后未只读格式）
  • 支持数据加密，5.7版本后支持Data-at-rest tablespace encryption
  • 支持全文索引
  • 支持地理数据类型
  • 支持地理索引
  • 支持表级锁定
  • 存储限制256TB
  • 支持Update statistics for data dictionary
  • 这二个是最常用的，根据自身的情况来选择合适的引擎

# 7.慢查询日志

 可以对查询慢的sql进行记录分析，然后通过肉眼debug或者使用mysqldumpslow进行分析,最好是在调试的时候开启，或者通过shell将日志设为按天记录,删除时间较长的记录等方法。

  • show variables like 'slow_query_log' 查看慢查询是否开启
  • set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log'将慢查询日志记录到指定文件中
  • set global log_queries_not_using_indexes = on; // 开启未使用索引的日志时间记录
  • set global long_query_time=1 慢查询的阙值
  • set global slow_query_log=on 开启慢查询

 通过命令行开启的会在重启时关闭，要永久生效，可以修改mysql配置文件

# 8.合适的字段属性，注意一下几个原则

  • 更小的通常更好，尽量选取可以正确存储数据的最小数据类型。这样会占用更小的磁盘，内存和缓存，并且处理是需要的cpu周期也更短
  • 简单就好，比如整型比字符串的代价更低，因为字符集和校对规则使字符比较比整型比较更复杂
  • unsigned的使用，unsigned代表无符号，可以使正数的上限提高将近一倍，如tinyint.unsigned是0~255，而不加unsigned范围是-128~127

# 9.尽量少使用通配符，如有必要，在使用通配符搜索时，除非有必要，否则不要将其放在搜索模式的开始处

# 10.对不常用的字段进行冗余处理以避免关联查询呢

# 11.某些特定情况下，可以增加缓存表和汇总表

# 特定方法的优化

# 1.count的优化

  • 首先了解一下count的特性
  • count()用于统计某个列值的数量或者统计行数，在统计列值时要求列为非空
  • count(*)不会扩展成所有的列，他会直接统计所有的行数
  • myisam的count会非常快，不过这是有前提条件的，它只有没有任何where条件下才快，因为这时候不用去计算表的行数，直接利用存储引擎的特性获取值.因此，在带有where语句进行统计时，myisam和innodb并没有什么区别
  • 在统计某个列的值，而mysql知道这个列不可能为null，mysql内部实际会将其优化为count(*)
  • 简单优化
  • 在进行带条件的查询时，有时候可以一些小技巧来进行统计，如在统计查询id大于等于5的行数时，可以查询id小于5的函数，然后用总行数减去查出的函数。
  • 使用近似值，计算精确值的代价是很高的，在某些场景中并不需要精确值，有一个近似值就可以满足需求，使用explain获取的估算值或者可以每隔一段时间进行统计后放入缓存
  • 复杂优化
  • 使用索引覆盖扫描
  • 增加汇总表
  • 使用redis,memcached等外部缓存系统

# 2.分页查询的优化

  • select user,content from logs where name='zhangsan' order by created_time limit 100000, 10;这是一个很常见的分页排序查询，但是无论创建合种索引，这种查询都是个很严重的问题，随着偏移量的增大，mysql需要扫描丢弃的数据越来越大，此时，预先计算，增加缓存，反范式化，加标签是可选的方法，更好的方法限制用户能够翻页的数量（百度限制为76页），这对用户的体验影响不大，因为很少会搜索10000页以后的数据。
  • 优化该类索引的一个好点的方法是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行 select user,content from logs inner join (select id from logs where name='zhaangsan' order by created_time limit 100000, 10) as x using(id)
  • 某些情况下，可以使用between...and来代替limit offset

# 3.关联查询的优化

  • 确保group by和order by中的表达式只涉及到一个表中的列以便mysql使用索引来进行优化
  • 关联表的顺序也会对速度有一定的影响，尽可能的以小的表来驱动大的表，具体的执行可以使用explain来进行一步步的实验分析
  • 一般情况下，可以在关联顺序的第二个表的相应列上加上索引

# 4.子查询优化

  • 5.6版本前的优化是尽可能使用关联查询来代替子查询

# 5.group by与distinct优化

  • 这二种都可以使用索引来进行优化
  • 在无法使用索引的情况下，group by使用临时表或者文件排序来做分组，可以通过使用提示sql_big_result和sql_small_result来让优化器按照指定方式运行
  • 在对关联查询进行分组时，查找表的标识列进行分组的效率要比其他列高

# 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句

# show processlist查看所有进程以确定执行缓慢的原因，kill用于杀死特定进程

←