MySQL性能优化

一. MySQL逻辑架构图

1. 连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于C/S工具实现的类似于tcp/ip的通信，主要完成一些类似于连接处理，授权认证，及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。

2. 服务层

第二层架构主要完成大多数的核心功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有缓存引擎功能也在这一层实现。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

3. 引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，不同的存储引擎具有的功能不同。

4. 存储层

数据存储器，主要是将数据存储在运行与设备的文件系统之上，并完成与存储引擎的交互。

注意：
存储引擎是基于表的，而不是数据库。

二. 索引优化分析

1. SQL的执行顺序

首先需要知道的是SQL的执行顺序，一般不是按照写SQL语句的顺序执行的。机器的执行顺序如下：

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY
10. LIMIT

2. 索引简介

索引：是帮助MySQL高效获取数据的数据结构，可以简单理解为"排好序的快速查找数据结构"。数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。
• 平时说的索引，如果没有特别介绍，都是指B数索引。其中聚集索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引。除了BTree索引，还有Hash索引、full-text索引、R-Tree索引。
• 索引分类：
  • 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 服务索引：一个索引包含多个列。
• 索引缺点：
  • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度。因为更新表时，MySQL不仅要存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
  • 表记录太少、经常增删改的表和数据列中重复的内容大多的这三种情况最好都不要建索引。
• 索引的相关语法：
  • 创建：

  CREATE INDEX indexName ON mytable(columnname1, columnname2);
  

  ALTER mytable ADD INDEX [indexName] ON (columnname1,columnname2);
  

  • 删除：

  DROP INDEX [indexName] ON mytable;
  

  • 查看：

  SHOW INDEX FROM table_name\G
  

3. Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈。

• 使用方式：EXPLAIN + SQL语句

3.1 Explain各个字段的含义

• id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

  • id相同：执行顺序由上至下。
  • id不同：如果是子查询，id的序列号会递增，id值越大优先级越高，越先被执行。
  • id中既有相同的也有不同的。id值越大，优先级越高，越先执行。id如果相同，则从上往下顺序执行。

• select_type: 查询的类型，主要用于区别，普通查询、联合查询、子查询等的复杂查询。

  • SIMPLE: 简单的select查询，查询中不包含子查询或者UNION。
  • PRIMARY: 查询中若包含任何复杂的子部分，最外层查询则被标记为此。
  • SUBQUERY: 在SELECT或者WHERE列表中包含了子查询。
  • DERIVED: 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
  • UNION: 若果第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层的SELECT将被标记为：DERIVED。
  • UNION RESULT: 从UNION表获取结果的SELECT。

• table: 显示这一张数据是关于哪张表的。

• type: 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>range>index>ALL

  • system: 表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，这个可以忽略不计。
  • const: 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量。
  • eq_ref: 唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
  • ref: 非唯一索引扫描，返回匹配某个单独值的所有行。本质上夜市一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符号条件的行，所以他应该属于查找和扫描的混合体。
  • range: 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引。
  • index: index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)。
  • all: 将遍历全表以找到匹配的行。
  • 一般来说，保证查询达到range级别，最好达到ref级别。

• possible_keys: 显示可能应用在这张表中的索引，一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

• key: 实际使用的索引。如果为NULL则没有使用索引。查询中若使用了覆盖索引，则索引和查询的select字段重叠。

• key_len: 表示索引列中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

• ref: 显示索引那一列被使用了，一般是常数或是另一个表某个字段。

• rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

• extra: 包含不适合在其他列中显示但十分重要的额外信息。

  • Using_filesort: 说明mysql会对数据使用一个外部排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为称为文件排序。
  • Using temporary: 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.
  • USING index: 表示相应的select操作中使用了覆盖索引，避免访问表的数据行，如果同时出现了using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where，表明索引用来读取数据而非执行查找操作。
  • Using where: 表明使用了where过滤。
  • Using join buffer: 使用了连接缓存。
  • impossible where: where子句的值总是false，不能用来获取任何元祖。
  • distinct: 优化distinct，在找到第一匹配的元祖后即停止找同样值的工作。

4. 索引优化

4.1 索引失效

1. 如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
2. 不要在索引列上做任何操作(如计算、函数、or、类型转换)，会导致索引失效而转向全表扫描。
3. 存储引擎不能使用索引中范围条件右边的列。
4. 尽量使用覆盖索引。
5. mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
6. is null, is not null也无法使用索引。
7. like以通配符(’%abc%’)，mysql索引失效，但是like以通配符(‘abc%’)不会失效。
8. 字符串不加单引号索引失效。
9. 使用or会索引失效。
   

三. 查询分析

1. 查询优化

• ORDER BY子句，尽量使用index方式排序，避免使用FileSort方式排序。
  • ORDERT BY语句使用最左前列时，会使用index方式排序。
  • 使用where子句和ORDER BY子句条件列组合满足索引最左前列会使用index排序。
• 不在索引列上时，filesort有两种算法，双路排序和单路排序：
  • 双路排序：mysql4.1之前是使用双路排序，即扫描两次磁盘，最终得到数据。读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。
  • 单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。但是，有可能单路排序算法一次拿不出数据，那么就还比双路排序更消耗IO，效率更慢！
• 优化策略
  • 增大sort_buffer_size参数的设置。
  • 增大max_length_for_sort_data参数设置。
• GROUP BY关键字优化：
  • GROUP BY实质是先排序后分组，遵照索引键的最佳左前缀。
  • 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
  • 能写在where限定的条件下就不要去HAVING限定。

2. 慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数。

• 查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
• 查看阙值时间(多少秒算慢)：SHOW VARIABLES LIKE '%long_query_time%';
• 日志分析工具mysqldumpshow可以帮助分析日志。

3. Show profiles

MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，保存最近15次运行的结果。

• 日志开发中的一些结论：
  • converting HEAP to MyISAM: 查询结果太大，内存都不够用了往磁盘上放了。
  • Creating tmp table: 创建临时表。
  • Copying to tmp table on disk: 把内存中临时表复制到磁盘。

四. MySQL锁机制

• 读锁(共享锁)： 针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁(排它锁)： 当前写操作没有完成前，它会阻断其他写锁和读锁。

1. 表锁(偏读)

偏向MyISAM引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低。MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

• 表锁分析：show status like 'table%';
  • Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每次立即获取锁值加1。
  • Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值越高说明存在着较严重的表级锁争用情况。

结论:

1. 对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写操作，和对其他表的读操作。只有当读锁释放后，才会执行其他进程的写操作。
2. 对MyISAM表的写操作(如写锁)，会阻塞其他进程对同一表的读和写操作，只有当锁释放后，才会执行其他进程的读写操作。
   简言之：读锁会阻塞写，但是不会阻塞读，而写锁会阻塞读和写。

2. 行锁(偏写)

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，并发度高。InnoDB与MyISAM最大不同的两点：一是支持事务，二是采用了行级锁。并且InnoDB行锁是通过索引上的索引项来实现的，InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！

• 并发事务处理带来的问题：

  • 更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务所做的更新。
  • 脏读：事务A读取到了事务B已修改但尚未提交的数据。
  • 不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现读出的数据已经发生了改变。
  • 幻读：事务A读取到了事务B提交的新增数据。
    脏读是事务B里面修改了数据，幻读是事务B里面新增了事务。

• **事务隔离级别：**事务隔离越严格，并发副作用越小，同时付出的代价也越大。数据库默认的隔离级别是：可重复读。

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别、只能保证不读取物理上损坏的数据	是	是	是
已提交读(Read committed)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

• 行锁分析：show status like ’innodb_row_lock%';
  • Innodb_row_lock_time_avg: 等待平均时长
  • Innodb_row_lock_waits: 等待总次数
  • Innodb_row_lock_time: 等待总时长

五. 主从复制

• 复制的基本原理：slave会从master读取binlog来进行数据同步。
  1. maste将改变记录到二进制日志。这些记录过程叫做二进制日志事件。
  2. slave将master的二进制日志拷贝到它的中继日志(relay log)。
  3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行的。
• 复制的基本原则：
  • 每个slave只有一个master。
  • 每个slave只能有一个唯一的服务器ID。
    数据同步。
  1. maste将改变记录到二进制日志。这些记录过程叫做二进制日志事件。
  2. slave将master的二进制日志拷贝到它的中继日志(relay log)。
  3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行的。
• 复制的基本原则：
  • 每个slave只有一个master。
  • 每个slave只能有一个唯一的服务器ID。
  • 每个master可以有多个salve.