mysql 优化小结

近日阅读了《深入浅出MySQL》一书的MySQL优化部分,有一些结论性的东西,把它们记录下来,也结合自己的思考做了补充,方便以后查阅。


SQL优化

  • SQL语句优化
  • 数据库对象优化
  • 锁优化

优化点

  • 优化扫描行数
  • 优化计算时间
  • 优化IO

优化方式

  • 优化SQL
  • 存储引擎选择优化
  • 优化MySQL配置文件

调优步骤

1.了解各个SQL语句的执行频率

show status like 'Com_%'

关注Com_select,Com_insert,Com_updateCom_delete,它会对所有的存储引擎的表操作进行累计,也可以查innodb

show status like 'Innodb_rows_%'

结果是Innodb_rows_select...

还可以查事务提交和回滚次数,观察Com_commitCom_rollback,如果事务回滚过于频繁,可能应用程序存在问题

2.通过慢查询日志定位慢SQL语句

show variables like '%slow_query_log%';

查询慢查询日志是否开启(默认是关闭状态),并且查询慢查询日志文件路径

show variables like '%slow_query%'

同时可以设置和查询慢查询时间

show variables like 'long%' # set long_query_time=2;

通过mysql自带工具阅读慢查询日志(它会对相同语句进行分类统计)

mysqldumpslow

3.使用explain分析低效SQL的执行计划,支持SQL分区 在通过慢查询找到慢SQL之后可以通过explain分析该SQL的执行细节

explain select xxxx\G

小技巧:这里\G加到SQL语句中可以让显示结果更加友好,方便阅读

explain查到的结果有以下几个数据可以注意

  • type:访问行的方式,分为all(扫描全表),index(索引全扫描),range(>,<=,between等操作),ref(使用非唯一索引或者唯一索引前缀匹配单个值,即=,它也经常出现在join中),eq_ref(多表使用唯一索引做关联),const/system(单表使用唯一索引或者PK查单一值)越往右性能越好
  • row: 实际扫描的行数
  • key: 实际使用的索引

可以通过show warnings查看执行SQL之前MySQL优化器对SQL做了哪些改写

4.使用show profiles,默认该功能是关闭的

select @@profiling; # 查看是否开启

set profiling=1;

通过show profiles可以看每个语句的执行时间,我要看第二条语句的执行细节,下面是查看方式

show profile for query 2;

并且支持查看cpu,block io等执行的具体信息

show profile cpu for query 2;

5.使用trace分析优化器如何执行SQL

set optimizer_trace='enabled=on', end_markers_in_json=on; # 开启trace功能

查看上一条SQL的执行计划

select * from information_schema.optimizer_trace\G;

索引问题

索引在存储引擎层实现,因此各个存储引擎支持的索引可能不同

1.分类

  • B树索引: 大部分存储引擎支持
  • Hash索引: 只有Memory支持
  • R-Tree索引: 空间索引,MyISAM支持
  • 全文索引: 用于全文检索

前缀索引是对列的前面一部分进行索引,好处是大大减少索引文件的大小,缺点是在使用order bygroup by的时候无法使用

2.索引的匹配规则

  • 全列匹配
  • 最左前缀匹配
  • 匹配列前缀:在一个索引有多个索引列的情况下,可以用like%匹配第一个索引列的最前N个字符

3.聚集索引与覆盖索引

  • 聚集索引: 索引的叶子节点存放对应行记录,innodb每张表只存在一个聚集索引,它的所有数据也存在该索引的叶子节点上
  • 覆盖索引: 多列(联合)索引包含了所有需要查询的数据,就称为覆盖索引,它可以极大提高查询性能,因为它不需要再回表查一次聚集索引对应的数据。回表与否可以通过explain语句的extra info查看,Using index就不用回表

4.存在索引却不被优化器使用的情况

  • %like开头的查询,这种情况下可以尝试优化IO,先利用覆盖索引进行查询出主键ID,再根据主键回表检索记录,比如

    select * from (select id from actor where name like '%li%') a,actor b where a.id = b.id

    内层会使用覆盖索引扫描,然后通过主键(聚集索引)获取查询结果,虽然扫描的行数不变,但是IO减少了,因为它不用扫全表 - 数据类型出现隐式转换或者SQL语句的条件带有计算表达式(或者SQL函数)也不会使用索引 - 复合条件下,查询条件不包括索引列最左边部分,即不满足最左匹配原则 - 如果MySQL估计使用索引比全表扫描慢,则不会使用索引,这种情况出现在索引使用如like w%这种情况,w%特征不够明显,使用索引慢是因为如果索引不是聚集索引,那么它还需要回表 - 存在or分割的条件,如果or后面的条件没索引,而or前面的条件有索引,也不会使用索引,因为这种情况下or后面必须全表扫描,在前面的条件使用索引就没必要

5.查看索引使用的情况

show status like 'Handler_read%'

Handler_read_key表示索引被使用的次数;Handler_read_rnd_next表示读下一行的请求数,大的话就说明正在进行大量的表扫描,可能没建立索引或者SQL查询没用到索引

索引设计原则

  1. 搜索的索引列,即出现在where子句中的列或者连接子句中指定的列

  2. 使用唯一索引(区分度高的列建立索引)

  3. 使用短索引,如果一个列为char(200)的长度,那么可以选择区分度较高的前10(n)个字符建立索引,这样可以减少磁盘IO,索引高速缓存中也能容纳更多键值

  4. 利用最左前缀

  5. 不要过度索引,因为索引维护需要很大的开销,索引过多,可能MySQL无法选择到最优的索引

  6. InnoDB会把记录按一定顺序保存,明确定义了主键,则按主键保存,没有主键,但有唯一索引,就按唯一索引保存。否则就会生成一个内部列。按照主键或者内部列访问是最快的,因此尽量选择最常作为访问条件的列作为主键

表管理优化

  • 定期分析和检查表 > analyze table user; # 分析和存储表的关键字分布

    check table user; # 检查表是否有错误(比如删掉了视图依赖的表)

  • 定期优化表 > optimize table user;

    如果表数据进行较大的改动,可以使用该命令进行优化,它会对表中的碎片进行合并,消除空间浪费,该命令对innodb,myisam等有效

上述命令在执行的时候都会锁表,所以注意在数据库不繁忙的时候使用

常用SQL优化

1.load语句的优化 - myisam - 关闭非唯一索引更新

    alter table table_name disable keys;
    load data 'data.txt' into table datas;
    alter table table_name enable keys;
    
- innodb - 被导入的文件按表主键顺序存储 - 在导入数据之前,关闭唯一性校验
      set unique_check=0;
      load...
     # 忽略重复的唯一索引数据,关于唯一索 引冲突图的处理,有ignore,replace和up  date等,下面语句会按升序排列,然后再执行unique_check=1之后,将排列在后面的重复的索引的列给删除
     alter table t_name order by id asc
      set unique_check=1;
      
- 在导入数据之前,如果设置了自动提交事务的话,先关闭它
      set autocommit = 0
      load ...
      set autocommit = 1
      

2.优化insert语句

  • 一次性批量插入多行数据
  • 使用INSERT DELAYED INTO,具体细节是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。这样的好处是,提高插入的速度,客户端不需要等待太长时间。坏处是,不能返回自动递增的ID。在系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。
  • 对于MyISAM,可以指定bulk_insert_buffer_size,扩大写的缓存空间
  • 能用load的时候用load而非用insert

3.优化order by语句

在MySQL中存在两种排序方式

  • 通过有序索引顺序扫描直接返回有序数据(覆盖索引或者聚集索引)
  • 对查询到的数据进行排序,会有Filesort操作,在排序过程中其实就相当于扫描了所有结果。如果在排序的时候内存不够,那么会把部分数据放到磁盘上

优化目标: 尽量减少额外的排序,通过索引直接返回有序数据

在使用order by不会使用到索引的情况

  • order by语句含有ascdesc
  • order bywhere的字段不同
  • 分别对两个不是同一个索引的一部分的列进行order by操作
  • 适当增加sort_buffer_size的值,让排序操作在内存中完成。但是不能太大,因为sort操作是线程独占,太大可能引起swap严重
  • 使用select 具体字段以减少排序区的使用和IO占用

4.优化group by语句

  • 使用order by null来禁止排序

5.优化嵌套查询 - 使用join代替子查询,join效率更高,因为MySQL不需要在内存中创建临时表来完成查询

6.优化or - 对or的条件分别建立独立索引。注意,联合索引是无效的

7.优化分页查询 - 按索引分页再回表 > select a.id, a.name from file a join (select id from file order by title limit 50,5) b on a.id=b.id

这里子查询先查`id`,然后父查询只需要查5行
  • 带上本页应该查到尾部的记录数,这种情况适用于排序字段不会出现重复值的情况,有重复值那么分页结果可能丢失部分记录 > select * from pay where id < 15640 order by id desc limit 10

8.使用SQL提示 - 使用use index建议mysql使用指定索引 > explain select count() from user use index (id)\G; - 使用ignore indexmysql忽略一个或者多个指定索引 > explain select count() from user ignore index (id)\G; - 使用force index强制mysql使用指定索引,即使使用索引效率不高


数据库对象优化

  1. 优化表的数据类型,输出的每一列信息都会对数据表中的列的数据类型提出优化建议 > select * from user PROCEDURE ANALYSE()\G;

2.水平拆分和垂直拆分 - 水平拆分: 根据一列或者多列数据的值把数据行放到两个独立的表中。这种拆分方式,会加大应用的复杂度,需要根据实际情况考虑数据量的增长速度,是否值得拆分 - 表很大,拆分后降低需要读的页数和索引数,降低了索引层数,提高了查询速度 - 表中的数据具有独立性,比如表中分别记录各个地区/不同时期的数据 - 垂直拆分: 把主键和一些列放一张表,把主键和另一些列放另外的表,比如某些列常用,某些列不常用,就可以用这种方式。它可以让数据行变小,一个数据页可以存放更多数据,查询时会减少IO。缺点是需要管理冗余列,查询需要join

3.使用中间表提高统计查询速度

在数据量较大的时候,在原表上统计查询效率通常会很慢,还得考虑统计查询是否对在线的应用产生负面影响。这种情况下,使用中间表可以提高统计查询速度 - 中间表复制原表部分数据,并且与原表隔离,不会在统计的时候对在线应用产生负面影响 - 中间表可以灵活的添加索引或者临时用的新字段,从而提高统计效率或者辅助统计查询作用


数据库锁优化

MySQL支持三种类型的锁

  • 表级锁: 开销小,加锁快,不会出现死锁,锁粒度大,容易出现锁争用
  • 行级锁: 开销大,加锁慢,会出现死锁,锁粒度小,发生锁冲突概率小
  • 页面锁: 会出现死锁,开销和粒度介于表级锁和行级锁之间

表级锁适合以查询为主,只有少量按索引条件更新数据的场景,行级锁适合有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用

innodb支持表级锁和行级锁,而myisam只支持表级锁

表级锁(这里以MyISAM为例)

1.查询锁争用的情况

show status like 'table%'; # 查看Table_locks_waited值是否过高

2.表级锁的锁模式 - 表共享读锁 - 表独占写锁

MyISAM在执行select的时候会自动给涉及的所有表加读锁,在执行update等操作的时候会给相应的表加写锁

MySQL不支持锁升级,也就是在执行LOCK TABLES之后,只能访问显示加锁的表,不能访问未加锁的表。MyISAM总是一次性获取到SQL语句所要的全部锁(两段锁协议)

3.并发插入

总体而言,MyISAM的读和写是串行的。一定条件下,也可以并发进行查询和插入,不支持并发更新或者删除。主要由concurrent_insert控制 - 值为0: 不允许并发插入 - 值为1: 如果MyISAM表中没有空洞(即表中间没有行被删除),则MyISAM允许一个进程在读表的同时,另一个进程从表尾进行插入(默认设置) - 值为2: 不管是否有空洞,都允许并发插入

可以通过OPTIMIZE TABLE来整理空间碎片,回收产生的中间空洞

4.MyISAM的锁调度

默认情况下,MyISAM认为写操作比读操作重要,会优先执行写操作,这也是它不适用于同时有大量更新操作和查询操作的原因,大量读操作可能会被写操作一直阻塞,可以从全局级别,session级别和SQL级别三个粒度调节它的调度行为 - 指定读操作优先: low-priority-updates - set low_priority_updates=1,使该连接的更新操作优先级降低 - 指定insert,update等语句的low_priority属性,降低该语句优先级

一些需要长时间运行的查询操作也可能让写进程饿死,对长SQL语句要学会分解,使每个查询可以在较短时间完成,减少锁冲突。如果复杂查询不可避免,那么应该尽量安排在数据库空闲时段执行

行级锁(以Innodb为例)

Innodb与MyISAM最大两点不同

  • 支持事务
  • 采用了行级锁

1.查看InnoDB行锁争用情况

show status like 'innodb_row_lock%'

观察InnoDB_row_lock_waitsInnoDB_row_lock_time_avg的值,如果过高则说明锁争用严重

进一步可以通过查询information_schema数据库中的innodb_locks表或者设置InnoDB Monitors来观察锁冲突的情况

show engine innodb status\G;

2.给SQL语句显示加锁

select ... clock in share mode; # 共享锁

select ... for update; # 排它锁

3.InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁实现的,如果没有索引,则会通过隐藏的聚簇索引来对记录进行加锁。

如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。

正是由于行锁是对索引加的锁,如果前面一个事务对某个索引键加锁,之后的事务如果使用了相同的索引,则会出现锁等待。并且如果前一个事务通过行锁锁定了某些记录行,那么后面的事务也是无法访问的。

具体实现有三种方式

  • record lock: 对索引项加锁
  • gap lock: 对索引项之间的“间隙”、第一条记录之前的间隙或者最后一条记录后的间隙加锁。间隙指的是键值在条件范围内但是并不存在的记录
  • Next-key lock: 前两种的组合,对记录及间隙加锁

select * from emp where id > 100 for update;

这是一个范围检索,innode不仅会对表中已存在的大于100的id进行加锁,也会对不存在的“间隙”进行加锁。这样可以 1)防止幻读 2)满足恢复和复制的需要,因为MySQL的binlog是按照事务提交的先后顺序记录的,在一个事务未提交前,其它并发事务不能插入满足其锁定条件的任何记录,即不允许出现幻读。

这种加锁机制可能会阻塞符合条件范围内键值的并发插入。实际业务中,应该尽量使用相等条件来访问更新数据,避免使用范围条件。

对于不存在的索引进行相等条件的请求,InnoDB也会使用Next-Key锁.导致写阻塞

  1. 少用insert into tb_name select...create table new_tb ...select ... from tb_name这类语句,因为MySQL对其做了特殊处理,会对tb_name加共享锁,导致另外的事务被阻塞。可以使用innodb_locks_unsafe_for binlog=on,它会强制MySQL使用MVCC进行一致性读,但是可能造成binlog无法正确恢复或者复制数据。解决方法是用select * from tb_name...into outfileload data infile ...间接实现,这种方式MySQL不会对tb_name加锁

5.Innodb锁的选择

  • 大部分情况下使用行锁
  • 事务需要更新大部分或者全部数据,表又比较大,这时候为了减少锁等待和冲突,可以使用表锁
  • 事务涉及多个表,可以考虑一次性锁定事务涉及的表,避免造成死锁,减少事务回滚带来的开销

在Innodb使用表锁的时候,需要设置autocommit=0,在commit之后进行unlock tables

6.Innodb死锁

MyISAM是deadlock free的,因为它会一次性获得全部需要的锁。那么全部满足,要么等待

Innodb避免死锁的方法

  • 并发程序按相同的顺序来访问不同的表
  • 事先对同一个表的数据排序,保证线程按固定的顺序处理记录
  • 优先获取排它锁

确定最后一个死锁产生的原因

show engine innodb status \G;

8.Innodb 锁优化概括

  • 尽量使用较低的隔离级别
  • 尽量使用索引访问数据,使加锁更精确
  • 选择合理的事务大小,小事务发生锁冲突概率更小
  • 显示加锁时,一次性请求足够级别的锁,比如要修改数据,直接申请排它锁
  • 不同程序尽量按约定以相同的顺序访问同一组表
  • 尽量用相等条件访问数据,避免Next-key对并发插入的影响
  • 不要申请超过实际需要的锁级别
  • 对于特定的事务,可以使用表锁来提高处理速度或者减少死锁发生的几率

MySQL Server的优化

MySQL线程

  • 主线程
  • IO线程
    • 读线程
    • 写线程
  • log线程
  • lock线程
  • 错误监控线程
  • purge线程

MySQL内存管理

MyISAM内存优化

MyISAM表的数据块,没有额外的缓存机制,完全依赖操作系统的IO操作

1.合理设置key_buffer_size,该参数决定MyISAM索引块缓存区的大小

2.使用多个索引缓存,即hot_cache.key_buffer_size

3.增大read_buffer_sizeread_rnd_buffer_size,它会增大读缓冲大小

InnoDB内存优化

1.innodb_buffer_pool_size大小的设置,它会决定Innodb索引和实际数据的最大缓存区大小,它的值越大,那么所需要的磁盘I/O就越少。经验值是80%的物理内存,但是要注意设置太大而出现页交换

2.根据innodb_buffer_pool_instances调整缓存池数量,以此降低并发导致的内部缓存访问冲突

3.控制innodb buffer刷,延长数据缓存时间,减少磁盘I/O.通过innodb_max_dirty_page_pct来设置缓存池中脏页的最大比例,通过innodb_io_capacity设置一批待刷新脏页的数量,可以根据磁盘IO来做设置,比如SSD可以设置比机械硬盘更高

4.设置较小的join_buffer_size,然后针对复杂连接操作的session单独设置较大的join_buffer_sizesort size,因为它们是面向客户服务线程分配的,设置过大可能会造成内存交换。

并发相关参数

1.调整max_connections,提高并发连接。但它会增大文件描述符的使用量

2.如果短时间内要处理大量请求连接,可以增大back_log,它控制MySQL监听TCP端口时设置的积压请求栈的大小

3.增大table_open_cache,这样每个SQL线程可以打开更多的表缓存数量。它也会增大系统文件描述符的使用量

4.合理设置innodb_lock_wait_timeout,它表示InnoDB事务等待行锁的时间,小事务可以将该值设置得较小,而大事务可以将行锁等待超时设置得更大,避免发生大的回滚

优化BinLog

1.innodb_flush_log_at_trx_commit,设置将redo buffer中的更新记录写入到日志文件和将日志文件数刷新到磁盘的操作时机

  • 0:事务提交的时候,InnoDB不会立即触发将缓存日志写到磁盘的操作,而是每秒定时触发一次缓存日志回写到磁盘的操作,并调用fsync刷新IO缓存
  • 1:每个事务提交都会立即回写日志文件,并且调用fsync刷新IO缓存
  • 2:每个事务提交,都会触发回写操作,但是不会立即刷新IO,如果是mysqld崩溃,那么数据不会丢失,因为日志都在缓冲中,如果是服务器崩溃,那么数据就会丢失

1最安全,其次是2,再次是0,而0是性能最好的

2.sync_binlog,设置二进制日志同步到磁盘(即fsync操作)的频率。对于安全性要求比较高的系统,可以设置其值为1(表示每次触发回写操作,都会调用fsync),对于可以容忍数据丢失的系统,可以设置值为100或者0,0是又操作系统决定

3.合理设置innodb_log_file_size,如果设置小了可能导致缓存脏页刷新频繁,影响性能。可以根据每小时产生的日志大小来进行设置

4.增大innodb_log_buffer_size,避免InnoDB在事务提交前将不必要的日志写入磁盘的操作

应用优化

1.使用连接池

2.减少对MySQL的访问 - 避免对同一数据做重复检索,能用一次连接提取出所有结果就不要用两次连接 - 使用查询缓存,它的作用是存储select查询的文本及相关的结果,使用场景是查询对象更新不频繁 - > show varliables like '%query_cache%'; # 查看SQL缓存是否开启

其中要注意query_cache_type,值为0表示关闭缓存,1表示除SQL_NO_CACHE之外都会使用缓存,2表示只为SQL_CACHE使用缓存

  • select SQL_CACHE * from ... # 显示使用缓存,SQL_NO_CACHE是不使用缓存

  • 在应用端增加CACHE层来减轻数据库负担,比如建立二级数据库,把访问频率非常大的数据放到二级库上

3.负载均衡 - 使用复制分流查询: 一主多从模式,主数据库负责更新,从数据库负责查询,主从之间通过复制实现数据的同步。它的问题在于主数据库更新频繁或者网络出现问题,导致从库延迟更新,造成查询结果和主库不一致

  • 分布式数据库架构: 适合大数据量、负载高的情况,MySQL CLUSTER支持分布式,当前只有InnoDB支持分布式事务

4.充分利用列有默认值的特性,只有当插入值不同于默认值时,才明确插入值,可以减少MySQL的语法分析

5.表的字段尽量不使用自增长,高并发情况下自增可能对效率有较大影响,可以由引用实现字段自增长