深入浅出MySql（2）

优化篇

---

18.1 优化SQL语句的一般步骤

18.1.1 通过show status命令了解各种SQL的执行频率

show session|global status like "%...%";统计每种指令的执行次数等信息，了解sql操作的大致比例。

mysql> show global status like "%rollback%";+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Com_rollback               | 1     || Com_rollback_to_savepoint  | 1     || Com_xa_rollback            | 0     || Handler_rollback           | 14    || Handler_savepoint_rollback | 1     |+----------------------------+-------+5 rows in set (0.00 sec)

参数的意义：
- Connectinos：试图连接数据库的次数
- Uptimes：服务器工作时间
- Slow_queries：慢查询的次数

18.1.3 通过EXPLAIN分析低效SQL的执行计划

语句EXPLAIN select * from ...的结果有以下几个参数，其意义分别是：
- select_type:表示查询的类型，常见的有SIMPLE、PRIMARY、UNION、SUBQUERY
- table:输出结果集的表
- type:找到所需行的方式，或者叫访问类型。包括ALL、index、range、ref、eq_ref、const、system、NULL
- possible_keys:查询时可能用到的索引
- key:实际使用的索引
- key_len:使用的索引字段的长度
- rows:扫描行的数量
- Extra:执行情况的说明和描述。

---

select_type具体类型:
- SIMPLE：简单查询
- PRIMARY：主查询
- UNION：合并查询之后的select语句。
- SUBQUERY：子查询

---

type的具体类型：性能依次提升。
- ALL：全表查询
- index：索引全扫描
- range：索引范围查询
- ref：使用非唯一索引。
- eq_ref：唯一索引查询。
- const/system：最多只有一行匹配。唯一索引
- NULL：查询不需要访问数据库。

EMPLAIN EXTENDED + SHOW WARNINGS显示SQL优化后的查询语句。

5.1版本之后参数增加了Partition属性，用于快速定位到某个分区。

SHOW PROFILE 可以更加详细的分析SQL语句。

5.6版本之后支持通过TRACE根据SQL语句的执行。

---

18.2 索引问题

详细讨论索引的分类、存储、使用方法

18.2.1 索引的分类

• BTree：最常见的索引类型，大部分存储引擎都支持
• HASH索引：只有Memory引擎支持，使用场景简单
• R-Tree索引：MyISAM的特殊索引，用的少。
• Full-Text索引：MyISAM特殊索引，全文索引，5.6版本之后支持。

不支持函数索引，能对列的前面某一部分进行索引，可以只取前10个字符进行索引，可以降低索引所占空间。缺点是不能利用索引进行ORDER BY 和GROUP BY等操作了。

#创建一个10字符的的索引create index idx_name on table_name(col_name(10));

HASH索引对比BTree索引：
- HASH索引适用于Key-Value查询，更加迅速。但是不适用于范围查询。BTree索引相反。

---

18.2.2 MySQL中索引的使用

BTree：平衡多叉树。

一、使用索引的具体场景：
1、匹配全值。
2、匹配值的范围查询
3、匹配最左前缀：仅仅使用索引中的最左边列进行查找，BTree索引使用的首要原则。
4、仅仅对索引进行查询。
5、匹配列前缀。
6、索引精确匹配而其他部分进行范围匹配。
7、MYSQL5.6引入了Index Condition PushDown，进一步优化查询，将某些情况下的条件过滤（一半是索引的过滤条件）操作下放（pushdown）到了存储引擎，现在索引处进行过过滤，再回表到数据库，减少了不必要的IO访问，提高了性能。

二、存在索引但是不能使用索引的典型场景
1、以%开头的LIKE查询。由于BTree的结构决定的。
2、数据类型出现隐式转换时不会使用索引，字符串需要用引号。
3、假如查询条件不满足最左原则，也不会使用索引。
4、如果使用索引比全表扫描更慢，则不会使用索引。即在查询时，筛选性越高越容易使用索引。
5、用OR分割的条件，一半有索引、一半没索引，则不会使用索引。

---

18.3 两个简单实用的优化方法

定期分析表和检查表。
ANALYZE，CHECK

定期优化表。
OPTIMIZE

---

18.4 常用SQL的优化

18.4.1 大批量插入数据

LOAD指令
对于MyISAM引擎有效：

ALTER TABLE tbl_name DISABLE KEYS;#关闭MyISAM表非唯一索引的更新，会影响效率。load data;ALTER TABLE tbl_name ENABLE KEYS;#开启更新。

对于InnoBD引擎。使用以下几种方式：
1、将插入的数据按照主键的顺序排列。
2、插入之前执行SET UNIQUE_CHECKS = 0，插入之后执行SET UNIQUE_CHECK = 1。关闭和开启唯一性校验。唯一性校验在插入过程中会花费大量的时间。
3、关闭和开启自动提交。SET AUTOCOMMIT = 0

18.4.2 优化INSERT语句

尽量采用以下几种方式插入：
- 如果同时从同一客户插入多行，尽量使用多个值表的INSERT语句，以减少数据库的连接和关闭的消耗。insert into test values(1,2),(1,3),(1,4)...
- INSERT DELAYED可以将数据先缓存到内存的队列中。
- 将索引文件和磁盘文件分在不同的磁盘上存放。
- 当从一个文本文件装载一个表时，使用LOAD DATA INFILE，比INSERT语句快20倍。

---

18.4.3 优化ORDER BY语句

1、MySQL有两种排序方式
第一种：通过有序索引顺序查询直接返回。速度快。
第二种：对返回的数据在内存中或者磁盘上进行排序。称为Filesort排序。

优化：尽量减少额外的排序，通过索引直接返回有序数据。避免出现FileSort。

总结：
下列SQL语句可以使用索引：

SELECT * FROM tabname ORDER BY key_part1, key_part2 ,...;SELECT * FROM tabname WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;SELECT * FROM tabname ORDER BY key_part1 DESC, key_part2 DESC;

key_part指组成索引的其中一个字段。

下列SQL语句不会使用索引：

SELECT * FROM tabname ORDER BY key_part1 DESC, key_part2 ASC;-- order by字段混合ASC和DESCSELECT * FROM tabname WHERE key2 = constant ORDER  BY key1;-- where的条件和order的条件不同SELECT * FROM tabname ORDER BY key1, key2;-- 对不同的关键字使用ORDER BY

2、Filesort的优化
适当加大系统变量max_length_for_sort_data的值，通过一次扫描算法进行排序。

不得已使用了Filesort的时候，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。通过设置sort_buffer_size排序区。

18.4.4 优化GROUP BY语句

GROUP BY的结果可能会出现无意义的排序，如果想要排除掉无意义的排序，可以通过ORDER BY NULL语句禁止排序。

explain select * from table1 group by field1 order by null;#order by null表示不对之前的属性进行无意义的排序。

18.4.5 优化嵌套排序

尽量使用连接查询来代替子查询。

select * from table1 where table1_id NOT IN (select id from table2);-- 嵌套查询#使用连接查询来替代嵌套查询select * from table1 a LEFT JOIN table2 b on a.id = b.id WHERE b.id IS NULL;#连接查询会将右表中对不上的值设置为null，，可以在这里加以利用mysql> select a.id,b.id from bd_message a left join pu_req_plan_b b on a.id = b.id; +----------------------+------+| id                   | id   |+----------------------+------+| 00SU00080B1927D58400 | NULL || 00SU00080B19CC328400 | NULL || 00SU00080B2AA7BA4400 | NULL |...+----------------------+------+19 rows in set

18.4.6 MySQL优化OR条件

对于含有OR的查询语句，如果要利用索引，则OR之间的每个条件都必须要用的索引，如果没有索引，要考虑增加索引。

18.4.7 优化分页查询

分页需要排序时，代价会很高，例如查询1001~1020条数据时，前面的1000条数据不需要但仍然需要排序。
1、第一种优化思路
在索引上完成排序，最后根据主键关联回查询所需要的其他列内容。

原：

select film_id ,describtion from film order by title limit 50,5;#改进，先在索引上进行排序（很快）查出id，在内连接查询出其他的数据。select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id = b.film_id;

2、第二种优化思路
把LIMIT查询转换成某个位置的查询，例如：查询43页时，如果知道42页最后一项的序号就可以准确定位了。

个人认为，这种方式适用范围较小。序号必须有序并且不能重复。还是第一种思路有效可行。

18.4.8 使用SQL提示

1、USE INDEX
提供希望MySQL参考的索引列表，MySQL不再考虑其他的列表。
2、IGNORE INDEX
提供希望MySQL忽略的索引列表，MySQL不再考虑这个列表。
3、FORCE INDEX
MySQL会自动选择某些索引，如果该索引的花费太大，即使使用了USE INDEX，也会被舍弃。这是可以通过FORCE INDEX的提示让MySQL强制使用该索引进行查询。比USE INDEX更为强硬的提示。

18.5 常用的SQL技巧

18.5.1 正则表达式

18.5.2 巧用RAND()提取随机数

---

19 优化数据库对象

19.2 拆分表

1、垂直拆分
把主键和一些常用列放在一个表，把主键和一些不常用的字段放在另外一个表。
2、水平拆分
根据一列或多列的值将数据放在多个独立的表中，适合：
- 表很大，降低索引的页数…
- 表中的数据本来就具备独立性，有些数据常用有些数据不常用。

19.4 使用中间表提高查询速度

---

20 锁问题

20.1 锁概述

• MyISAM、MEMORY采用的是表级锁。
• InnoDB默认采用行级锁，支持表级锁。
• BDB采用页面锁。

锁的特性：
- 表级锁：粒度最大，发生锁冲突的概率最大，并发度最低；开销小，加锁快；不会出现死锁。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页面锁：介于以上两者之间。

---

20.2 MyISAM表锁

使用最广泛的锁类型。

20.2.2 表级锁的锁模式

表共享读锁，表独占写锁。

锁都是引擎自动加入，不需用户操作。
语句为：

#local表示允许其他用户并发插入记录LOCK TABLE table_name read|write local;

当一个session使用LOCK TABLE命令给某个表加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表会提示报错；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。

---

20.2.4 并发插入

一定条件下，MyISAM支持查询和插入操作的并发进行。MyISAM有一个concurrent_insert的系统变量，专门控制并发插入的行为。
- 0：不允许并发插入。
- 1：没有中间空洞的情况下允许并发插入。
- 2：不管有没有空洞，都允许并发插入。

可以里用并发插入特性解决对同一表查询和插入的锁争用问题。同时，顶起进行OPTIMIZE操作可以整理空间碎片。

---

20.2.5 MyISAM的锁调度

若有同时的读请求和锁清秋，MyISAM默认支持写请求。因此在存在大量更新操作时，查询操作可能会被阻塞很久。可以通过一些设置调节MyISAM的锁调度行为。
- 指定启动参数low_priority_updates，使默认优先读操作。
- 执行命令SET LOW_PRIORITY_UPDATES = 1，使该连接发出的更新请求优先级降低。
- 指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

---

20.3 InnoDB锁问题

InnoDB：1、支持事务。2、采用了行级锁。

---

20.3.1 背景知识

1、事务及其属性
- 原子性（atomic）
- 一致性（consistent）
- 隔离性（isolation）
- 持久性（durable）

2、并发事务带来的问题
- 更新丢失：被覆盖造成丢失。
- 脏读：读到另外事务未提交的数据。
- 不可重复读：同一事务中，读出来的数据发生改变，或者被删除了。
- 幻读：同一事务中，读到上次未读到的新数据。

3、事务隔离级别
四个级别
- Read uncommited
- Read commited
- Repeatable read
- Serializable
隔离级别越高，并发行越差。一般应用Read Commited已经足够了。

---

20.3.3 InnoDB的行锁模式和加锁方法

共享锁（Share lock）
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据

SELECT * FROM table_name WHERE .. LOCK IN SHARE MODE:
可重入，不可加排它锁。其他事务可读。

排它锁（Exclusive lock）
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT * FROM table_name WHERE ... FOR UPDATE
不可重入，其他事务可读，不可写。

---

20.3.4 InnoDB实现行锁的方式

1、InnoDB通过对索引加锁实现行锁，当不使用索引时，和表锁别无二致。如果有索引，查询时只会锁定符合条件的行。

2、由于MySQL的行锁是针对索引家的锁，两条数据如果索引相同，不同的session在分别同时时，由于索引相同，也会出现竞争。

3、当表中有多个索引时，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引、普通索引，InnoDB都会使用行锁来对数据加锁。

4、即便在条件中使用了索引字段，MySQL通过判断不同的执行计划的代价来决定是否使用索引，因此在分析锁冲突时，应确定MySQL实际的执行计划。

---

20.3.5 Next-Key锁

当使用范围条件查询时，不存在的索引区间会使用间隙（GAP）锁，即所谓的Next-Key锁。主要目的是为了防止幻读，和满足恢复和复制的需要。

select * from emp where empid > 100 for update;#如果数据只到101，之后的区间也会被锁定。主要为了避免其他事务在此时插入102条数据，从而出现幻读。

在实际开发中，尤其是并发插入较多的应用中，尽量使用相等条件来访问更新数据，避免使用范围条件。

MySQL在使用相同条件请求给一个不存在的数据时，也会使用Next-Key锁。

---

20.3.8 什么时候使用表锁

行锁和事务是选择InnoDB的理由。
使用表锁的情形：
- 事务需要更新大部分或者全部的数据。
- 事务涉及多个表，比较复杂，可能会引起死锁。

表锁不支持事务，使用UNLOCK TABLES释放表锁，InnoDB的表锁不是引擎层管理的，而是由其上一层MySQL Server负责的。

---

20.3.9 关于表锁

避免死锁：
- 约定访问顺序。
- 对数据排序。
- 申请足够级别的锁，即排它锁。避免出现锁冲突。
- 隔离级别不宜过高。

---

20.4 小结

对于MyISAM的表锁，主要学习了：
1、共享读锁（S）之间是兼容的，但（S）与排他写锁（X）之间以及，X之间是互斥的，即不可重入。
2、一定条件下，MyISAM允许查询和插入并发执行，可以利用这一点解决同一表的查询和插入的索争用问题。
3、MyISAM默认是写优先，读操作可能会被持续阻塞。可以设置更改优先级。
4、表锁颗粒大，读写之间又是串行的，如果更新操作较多，可能会出现严重的锁等待，可以考虑InnoDB更为细粒度的行级锁。

---

对于InnoDB，主要学习了：
- InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会对所有的数据加锁。
- 介绍了InnoDB NEXT-KEY的锁机制，以及InnoDB使用NEXT-KEY锁的原因。
- 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
- 锁冲突甚至死锁很难完全避免。

---

减少死锁的措施：
- 尽量使用较低的隔离级别。
- 精心设计索引，并尽量使用索引访问数据，使加锁更加精确，减少锁冲突的机会；
- 选择合适的事务，小事务发生冲突的几率也更小。
- 显式加锁时，最好一次性请求足够级别的锁，即排他锁，避免出现锁冲突。
- 不同的程序访问一组表时，尽量使用固定相同的顺序访问；对一个表而言，尽量以固定的顺序存取表中的行，这样可以大大减少死锁的机会。
- 尽量使用相等条件访问数据，这样可以避免Next—Key锁对并发插入的影响。
- 不要申请超过实际需要的锁级别，除非必须，查询时不要显式加锁。
- 对于一些特定（复杂、大）的事务，可以使用表锁提高处理速度或者减少死锁发生的几率。

---

23 应用优化

23.1 使用连接池

23.2 减少对MySQL的访问

23.2.1 避免对同一数据做重复检索

23.2.2 使用查询缓存

查询缓存的适用对象是更新不频繁的表，当表更改（表结构和表数据）后，缓存的数据被清空。

查询缓存的参数主要有：

mysql> show variables like "%query_cache%";+------------------------------+----------+| Variable_name                | Value    |+------------------------------+----------+| have_query_cache             | YES      || query_cache_limit            | 1048576  || query_cache_min_res_unit     | 4096     || query_cache_size             | 15728640 || query_cache_type             | ON       || query_cache_wlock_invalidate | OFF      |+------------------------------+----------+6 rows in set (0.00 sec)

参数的意义：
- have query cache：已经配置了高速缓存。
- query cache size：缓存区的大小，单位是KB
- query cache type：不重要啦。。。

23.2.3 增加CACHE层

比如MyBatis和Hibernate中的缓存，提供了CACHE层的功能。

---

23.3 负载均衡

机制：利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。负载均衡可以运用到各个层面中，从Web服务器、应用服务器、MySQL服务器。

MySQL数据库端的一些负载均衡的方法

23.3.1 利用MySQL复制分流查询操作

利用MySQL的主从复制，主服务器负责更新，多台从服务器负责查询操作。

23.3.2 采用分布式数据库架构