本文将根据我的面试经历和近期复习情况简单介绍一下我所认识的MySQL，涵盖的知识点可能相对琐碎，深度一般，后面找时间就某些部分展开讨论。
一、存储引擎
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
可以通过指定 show engines ， 来查询当前数据库支持的存储引擎：
下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ：
| 特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持(5.6版本之后) | 支持 | |||
| 集群索引 | 支持 | ||||
| 数据索引 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外键 | 支持 |
下面我们将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM。
1.1 InnoDB
InnoDB是大家最熟悉的存储引擎，也是MySQL5.5之后的默认存储引擎，查询默认存储引擎可以用命令：show variables like '%storage_engine%';。与传统的 ISAM 与 MyISAM 相比，InnoDB 的最大特色就是支持了ACID兼容的事务（Transaction）功能，其默认隔离级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。只有当 InnoDB 的某些功能不能满足特殊场景的需求时我们才考虑其他存储引擎。如对比 MyISAM，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。使用start transaction和commit来开启和提交事务，使用rollback回滚事务。
同时 MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候， 要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。但是考虑到外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度，因此阿里巴巴Java开发手册已经明确禁止对级联、外键的使用，一切外键概念必须在应用层解决。
InnoDB 支持真正的在线热备份，MySQL 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。
1.2 MyISAM
MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：.frm (存储表定义)；.MYD(MYData , 存储数据)；.MYI(MYIndex , 存储索引)。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
1.3 比较
- 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
- 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
- 外键：InnoDB 支持外键。
- 备份：InnoDB 支持在线热备份。
- 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
- 其它特性：MyISAM 支持压缩表和空间数据索引。
二、事务
2.1 ACID 四大特征
- 原子性 Atomicity：不可分割的最小操作单位，一次操作要么同时成功，要么同时失败。
- 一致性 Consistency：事务操作前后，数据总量保持不变。
- 隔离性 Isolation：多个事务之间相互独立。
- 持久性 Durability：当事务提交或回滚后，数据库会永久化的保存数据。
2.2 隔离级别
事务的隔离级别：多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。先介绍几种并发操作中可能遇到的问题：
脏读：一个事务，读取到另一个事务中没有提交的数据。
虚读：在同一个事务中，两个读取到的数据不一样。（不可重复读）
幻读：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。一般针对插入。
隔离级别：
read uncommitted：读未提交-->脏读，虚读，幻读read committed：读已提交-->虚读，幻读（Oracle默认）repeatable read：可重复读-->幻读（MySQL默认）serializable：串行化
查看当前隔离级别：select @@tx_isolation;
设置当前隔离级别：set session transaction isolatin level 隔离级别;
三、索引
索引是 MySQL 绕不开的问题，它对数据库效率起到了巨大作用。MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
3.1 磁盘I/O预知识
磁盘IO：磁盘读取数据靠的是机械运动，每一次读取数据需要寻道、寻点、拷贝到内存三步操作。寻道时间是磁臂移动到指定磁道所需要的时间，一般在 5ms 以下；寻点是从磁道中找到数据存在的那个点，平均时间是半圈时间，如果是一个7200转/min的磁盘，寻点时间平均是600000/7200/2=4.17ms；拷贝到内存的时间很快，和前面两个时间比起来可以忽略不计，所以一次IO的时间平均是在 9ms 左右。听起来很快，但数据库百万级别的数据过一遍就达到了9000s，显然就是灾难级别的了。
考虑到磁盘 I/O 是非常高昂的操作，计算机操作系统做了预读的优化，当一次 I/O 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
每一次 I/O 读取的数据我们称之为一页(page)，具体一页有多大数据跟操作系统有关，一般为 4k 或 8k，也就是我们读取一页内的数据时候，实际上才发生了一次 I/O。那我们想要优化数据库查询，就要尽量减少磁盘的 I/O 操作，所以就出现了索引。
3.2 索引优势劣势
优势
- 类似于书籍的目录索引，提高数据检索的效率，降低数据库的 I/O 成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。
劣势
- 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
3.3 索引结构
索引是在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的，InnoDB 使用的是B+树索引。
B+树是B树的变种，为了了解B+树的相关知识，我们先从B树开始介绍。
BTree又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：
- 树中每个节点最多包含 m 个孩子。
- 除根节点与叶子节点外，每个节点至少有
[ceil(m/2)]个孩子。 - 若根节点不是叶子节点，则至少有两个孩子。`
- 所有的叶子节点都在同一层。
- 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中
[ceil(m/2)-1] <= n <= m-1。
B树和二叉树相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE 的层级结构比二叉树小，因此搜索速度快。
B+Tree 结构
B+Tree为BTree的变种，B+Tree与BTree的区别为：
- n 叉 B+Tree 最多含有 n 个 key，而 BTree 最多含有 n-1 个 key。
- B+Tree 的叶子节点保存所有的 key 信息，依 key 大小顺序排列。
- 所有的非叶子节点都可以看作是 key 的索引部分。
InnoDB 中的B+树每个叶子节点都存有相邻叶子节点的指针，更加加快了访问效率，尤其是区间查询。此外：
- 由于 B+Tree 只有叶子节点保存key信息，查询任何 key 都要从 root 走到叶子。所以 B+Tree 的查询效率更加稳定。
- B树每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 BTree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。
3.4 索引分类
- 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引 ：索引列的值必须唯一，但允许有空值
- 复合索引 ：即一个索引包含多个列
3.5 索引的基本操作
-
创建：
crate [UNIQUE|FULLTEXT|SPATIAL] index 索引名 [USING index_type] on 表名 (index_col_name,...) -
查看：
show index from 表名; -
编辑：
-
alter table 表名 add primary key(column_list);添加一个主键，这意味着索引值必须是唯一的，且不能为NULL -
alter table 表名 add unique 索引名(column_list);创建索引的值必须是唯一的（除了 NULL 外，NULL 可能会出现多次） -
alter table 表名 add index 索引名(column_list);添加普通索引， 索引值可以出现多次。 -
alter table 表名 add fulltext 索引名(column_list);指定了索引为 FULLTEXT， 用于全文索引
-
-
删除：
drop index 索引名 on 表名;
3.6 设计原则
-
单表最多 16 个索引
-
对查询频次较高，且数据量比较大的表建立索引。
-
索引字段的选择，最佳候选列应当从 where 子句的条件中提取，如果 where 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
-
使用唯一索引，区分度越高，使用索引的效率越高。
-
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
-
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。
-
利用最左前缀，N 个列组合而成的组合索引，那么相当于是创建了 N 个索引，如果查询时 where 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率。
四、锁
4.1 锁分类
从对数据操作的粒度分 ：
1） 表锁：操作时，会锁定整个表。
2） 行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：
| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
4.2 加锁解锁操作
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
显示加表锁语法：
加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；
读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
InnoDB 实现了以下两种类型的行锁。
- 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
- 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁； 可以通过以下语句显示给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE;
如果不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，实际效果跟表锁一样。
五、SQL 优化
5.1 使用索引提高查询效率
查询时避免索引失效，索引的使用建议和可能失效的情况有：
-
尽量使用覆盖索引，避免 select *，全值匹配 ，对索引中所有列都指定具体值。
-
最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。若跳过了中间的列，则只有左边部分使用了索引。
-
不要在索引列上进行运算操作， 索引将失效。
-
字符串不加单引号，造成索引失效，这是隐式类型转换导致的。
-
用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
-
以 % 开头的 like 模糊查询，索引失效。
-
in 走索引，not in 索引失效。
-
如果MySQL评估使用索引比全表更慢，则不使用索引。
5.2 SQL 优化的基本步骤和逻辑
5.2.1 查看 SQL 执行频率
MySQL 客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息，可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
Com_xxx 表示每个 xxx 语句执行的次数，显然 Innodb_xxx 是只针对 InnoDB 的次数统计。
5.2.2 寻找低效的 SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句：
- 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用
--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。 show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
参数解析：
-
id：用户登录 MySQL 时，系统分配的"connection_id"，可以使用函数connection_id()查看 -
user：显示当前用户。如果不是 root，这个命令就只显示用户权限范围的 SQL 语句 -
host：显示这个语句是从哪个 ip 的哪个端口上发的，可以用来跟踪出现问题语句的用户 -
db：显示这个进程目前连接的是哪个数据库 -
command：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等 -
time：显示这个状态持续的时间，单位是秒 -
state：显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个 sql 语句，以查询为例，可能需要经过 copying to tmp table、sorting result、sending data 等状态才可以完成 -
info：显示这个 sql 语句，是判断问题语句的一个重要依据
5.2.3 explain 分析执行计划
通过explain分析执行计划，我们可以了解表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。
字段解析：
-
id： select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。 -
select_type： 表示 select 的类型，常见的取值有：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：表示表的连接类型，mysql5.7 中 type 的类型达到了 14 种之多。常见的性能由好到差的连接类型为（system ---> const ---> eq_ref ---> ref ---> ref_or_null ---> index_merge ---> index_subquery ---> range ---> index ---> all），一般来说我们的优化目标是 ref，至少要达到 range。NULL：MySQL 不访问任何表，索引，直接返回结果。system：表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现。const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。const 将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较。eq_ref：类似 ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描。ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）。range：有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。where 之后出现 between ， < , > , in 等操作。index：index 与 all 的区别为 index 类型只是遍历了索引树， 通常比 all 快，all 是遍历数据文件。all：将遍历全表以找到匹配的行。
-
possible_keys： 表示查询时，可能使用的索引。 -
key：表示实际使用的索引。 -
key_len：索引字段的长度。 -
rows：扫描行的数量。 -
extra：执行情况的说明和描述。using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”, 效率低。using temporary：。使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于 order by 和 group by，效率低using index：表示相应的select操作使用了覆盖索引， 避免访问表的数据行，效率不错。
5.2.4 profiles 分析
show profiles语句能够在做 SQL 优化时帮助我们了解时间都耗费发生在何处。通过have_profiling参数，能够看到当前 MySQL 是否支持 profile：
默认profiling是关闭的，可以通过 set 语句在 Session 级别开启 profiling：set profiling=1;。现在我们执行 select * from t_cas where id<5; select count(*) from t-cas;，再通过show profiles;可以看到每条命令的具体执行时间：
通过show profile [all|cpu|block io|context switch|page faults] for query query_id;语句可以查看到该 SQL 执行过程中每个线程的状态和消耗的时间：
5.3 提高数据导入效率
-
主键顺序插入：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
-
关闭唯一性校验：在导入数据前执行
SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。 -
手动提交事务：如果应用使用自动提交的方式，建议在导入前执行
SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
5.4 优化 insert 语句
-
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的 insert 语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快。
示例， 原始方式为：
insert into tb_test values(1,'xie'); insert into tb_test values(2,'Cat'); insert into tb_test values(3,'you');优化后的方案为 ：
insert into tb_test values(1,'xie'),(2,'wu')，(3,'you'); -
在事务中进行数据插入。
start transaction; insert into tb_test values(1,'xie'); insert into tb_test values(2,'wu'); insert into tb_test values(3,'you'); commit; -
数据有序插入
insert into tb_test values(4,'li'); insert into tb_test values(1,'xie'); insert into tb_test values(3,'you'); insert into tb_test values(2,'wu');优化后：
insert into tb_test values(1,'xie'); insert into tb_test values(2,'wu'); insert into tb_test values(3,'you'); insert into tb_test values(4,'li');
5.5 优化 order by 语句
两种排序方式
- 通过对返回数据进行排序，也就是通常说的
filesort排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 - 通过有序索引顺序扫描直接返回有序数据，这种情况即为
using index，不需要额外排序，操作效率高。
因此应该尽量减少额外的排序，通过索引直接返回有序数据。where 条件和 order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且 order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现 FileSort。
MySQL 有两种排序算法：
1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
5.6 优化 group by 语句
由于 group by 实际上也同样会进行排序操作，而且与 order by 相比，group by 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 group by 的实现过程中，与 order by 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null禁止排序。如：
explain select age,count(*) from user group by age order by null;这里是不需要filesort的。
5.7 优化 or 条件
在 5.1 中曾提过，在对于包含 or 的查询子句，如果要利用索引，则 or 之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。
此外，如select * from user where id=1 or age=30;的查询可以用union来替代：select * from user where id=1 union select * from user where age=30;
5.8 优化分页查询
一般而言，分页查询时，通过创建覆盖索引能够比较好地提高性能。一但是当数据量巨大的时候，如limit 10000000,10，此时需要 MySQL 排序前 10000010 记录，仅仅返回 10000000 - 10000010 的记录，其他记录丢弃，查询排序的代价非常大。
一个思路是在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容：explain select * from t_cas t, (select id from t_cas order by id limit 10000000,10) a where t.id = a.id;
另一个思路就是，如果我们查询的是主键自增的表，可以把 limit 转换为某个位置查询：explain select * from t_cas where id>10000000 limit 10;
5.9 使用 SQL 提示
- 在查询语句中表名的后面，添加
use index来提供希望 MySQL 去参考的索引列表，就可以让MySQL不再考虑其他可用的索引：explain select * from t_cas use index(idx_name) where name='xie'; - 如果只是单纯的想让 MySQL 忽略一个或者多个索引，则可以使用
ignore index：explain select * from t_cas use index(idx_name) where name='xie'; - 为强制 MySQL 使用一个特定的索引，可在查询中使用
force index：explain select * from t_cas force index(idx_name) where name='xie';
六、缓存与并发调整
6.1 缓存
开启 Mysql 的查询缓存，当执行完全相同的 SQL 语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。
-
客户端发送一条查询给服务器；
-
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
-
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
-
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
-
将结果返回给客户端。
查看当前的 MySQL 数据库是否支持查询缓存：SHOW VARIABLES LIKE 'have_query_cache';
查看当前 MySQL 是否开启了查询缓存：SHOW VARIABLES LIKE 'query_cache_type';。MySQL的查询缓存默认是关闭的，需要手动配置参数query_cache_type， 来开启查询缓存，该参数的可取值有三个 ：① OFF 或 0：查询缓存功能关闭；② ON 或 1查询缓存功能打开：SELECT 的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存；③ DEMAND 或 2：查询缓存功能按需进行，显式指定SQL_CACHE的 SELECT 语句才会缓存；其它均不予缓存
查看查询缓存的占用大小：SHOW VARIABLES LIKE 'query_cache_size';
查看查询缓存的状态变量：SHOW STATUS LIKE 'Qcache%';
| 参数 | 含义 |
|---|---|
| Qcache_free_blocks | 查询缓存中的可用内存块数 |
| Qcache_free_memory | 查询缓存的可用内存量 |
| Qcache_hits | 查询缓存命中数 |
| Qcache_inserts | 添加到查询缓存的查询数 |
| Qcache_lowmen_prunes | 由于内存不足而从查询缓存中删除的查询数 |
| Qcache_not_cached | 非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存） |
| Qcache_queries_in_cache | 查询缓存中注册的查询数 |
| Qcache_total_blocks | 查询缓存中的块总数 |
可以在SELECT语句中指定两个与查询缓存相关的选项：
- SQL_CACHE : 如果查询结果是可缓存的，并且
query_cache_type系统变量的值为 ON 或 DEMAND ，则缓存查询结果 。 - SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。
例子：
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
以下情况时缓存将失效:
- SQL 语句不一致.
- 当查询语句中有一些不确定的时，则不会缓存。如 ： now(), current_date(), curdate(), curtime(), rand(), uuid(), user(), database()。
- 不使用任何表查询语句。
- 查询
mysql，information_schema或performance_schema数据库中的表时，不会走查询缓存。 - 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。
6.2 InnoDB 内存优化
InnoDB 用一块内存区做 I/O 缓存池，该缓存池不仅用来缓存 InnoDB 的索引块，而且也用来缓存 InnoDB 的数据块。InnoDB 存储引擎使用一个指定大小的 Redo log空间（一个环形的数据结构）。Redo log 的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。
-
innodb_log_file_size：该参数决定着 MySQL事务日志文件的大小。设置的太小：当一个日志文件写满后，InnoDB 会自动切换到另外一个日志文件，而且会触发数据库的检查点（Checkpoint），这会导致 InnoDB 缓存脏页的小批量刷新，会明显降低 InnoDB 的性能。由于日志切换更频繁，也就直接导致更多的 BUFFER FLUSH，由于日志切换的时候是不能 BUFFER FLUSH 的， BUFFER 写不下去，导致没有多余的 buffer 写 redo， 那么整个 MySQL 就 HANG 住。还有一种情况是如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换（因为实例恢复还需要，不能被循环复写）这样 MySQL 就 hang 住了。可以根据文件修改时间来判断日志文件的旋转频率，旋转频率太频繁，说明日志文件太小了。设置的太大：设置很大以后减少了 Checkpoint，并且由于 redo log 是顺序 I/O，大大提高了 I/O 性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务（也就是实例恢复中的前滚, 利用 redo 从演变化来恢复 buffer cache 中的数据），如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。 -
innodb_buffer_pool_size：该参数决定了 InnoDB 存储引擎表数据和索引数据的最大缓存区大小，默认值 128M。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问 InnoDB 表需要的磁盘 I/O 就越少，性能也就越高。
innodb_buffer_pool_size=512M
innodb_log_buffer_size：决定了 InnoDB 重做日志缓存的大小，默认值是 1MB，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免 InnoDB 在事务提交前就执行不必要的日志写入磁盘操作。
innodb_log_buffer_size=10M
6.3 并发参数
从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。
-
max_connections：最大连接数，默认值是 151。如果状态变量connection_errors_max_connections不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections的值。MySQL 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在 Linux 平台下，性能好的服务器，支持 500 -1000 个连接不是难事，需要根据服务器性能进行评估设定。 -
back_log：控制 MySQL 监听 TCP 端口时设置的积压请求栈大小。如果 MySQL 的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log的值。 -
table_open_cache：该参数用来控制所有 SQL 语句执行线程可打开表缓存的数量， 而在执行 SQL 语句时，每一个 SQL 执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定：max_connections x N ; -
thread_cache_size：为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制 MySQL 缓存客户服务线程的数量。 -
innodb_lock_wait_timeout：该参数是用来设置 InnoDB 事务等待行锁的时间，默认值是50ms， 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说， 可以将行锁的等待时间调大， 以避免发生大的回滚操作。
七、主从复制与读写分离
复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。MySQL 支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
7.1 复制原理
从上层来看复制分成三步：
- Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。
- 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay log。
- Slave 重做中继日志中的事件，将改变反映它自己的数据。
二进制日志（Binlog）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该 Binlog 实现的。二进制日志，默认情况下是没有开启的，需要到 MySQL 的配置文件my.cnf中开启，并配置 MySQL 日志的格式。
Binlog 日志格式
-
STATEMENT：该日志格式在日志文件中记录的都是 SQL 语句（statement），每一条对数据进行修改的 SQL 都会记录在日志文件中，通过 MySQL 提供的 mysqlbinlog 工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。 -
ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录 SQL 语句。如执行SQL语句：update tb_book set status='1';, 如果是 STATEMENT 日志格式，在日志中会记录一行 SQL 文件； 如果是 ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。 -
MIXED：这是目前 MySQL 默认的日志格式，即混合了STATEMENT 和 ROW 两种格式。默认情况下采用 STATEMENT，但是在一些特殊情况下采用 ROW 来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。
日志的拓展：
1. 错误日志
记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。该日志是默认开启的 ， 默认存放目录为 MySQL 的数据目录, 默认的日志文件名为 hostname.err（hostname 是主机名）。
2. 查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的 SQL 语句。默认情况下， 查询日志是未开启的。如果需要开启查询日志，在 MySQL 的配置文件my.cnf中可以设置以下配置：general_log=1，general_log_file=file_name
3.慢查询日志
记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的 SQL 语句的日志。可以借助于 MySQL 自带的 mysqldumpslow 工具， 来对慢查询日志进行分类汇总。long_query_time默认为 10 秒，最小为 0， 精度可以到微秒。慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志：slow_query_log=1，slow_query_log_file=slow_query.log
7.2 主从复制的搭建
1. 在两台数据库中分别创建数据库
create database db_name;
2. 在主服务器进行如下配置：
# 修改配置文件，执行以下命令打开mysql配置文件
vi /etc/my.cnf
# 在[mysqld]模块中添加如下配置信息
log-bin=master-bin # 二进制文件名称
binlog-format=ROW # 二进制日志格式，默认mixed
server-id=1 # 要求各个服务器的id必须不一样
binlog-do-db=shop # 同步的数据库名称
binlog-ignore-db=ndb # 不同步的数据库名称
3. 配置从服务器登录主服务器的账号授权
--授权操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by 'root';
--刷新权限
flush privileges;
4. 从服务器的配置
# 修改配置文件，执行以下命令打开mysql配置文件
vi /etc/my.cnf
# 在mysqld模块中添加如下配置信息
log-bin=master-bin # 二进制文件的名称
binlog-format=ROW # 二进制文件的格式
server-id=2 # 服务器的id
# replicate-do-db 设定需要复制的数据库(多数据库使用逗号，隔开)
# replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号，隔开)
# replicate-do-table 设定需要复制的表
# replicate-ignore-table 设定需要忽略的复制表
# replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
# replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符
5. 重启主服务器的mysqld服务
# 重启mysql服务
service mysqld restart
# 登录mysql数据库
mysql -uroot -p
# 查看master的状态
show master status；
6. 重启从服务器并进行相关配置
# 重启mysql服务
service mysqld restart
# 登录mysql
mysql -uroot -p
# 连接主服务器
change master to master_host='192.168.150.11',master_user='root',master_password='123456',master_port=3306,
master_log_file='master-bin.000001',master_log_pos=334;
# 启动slave
start slave
# 查看slave的状态
show slave status\G #注意没有分号
7.3 读写分离
读写分离与分库分表是紧密联系的，同时分库分表虽然是两个概念，但是它们往往是在一起使用的，分出来的表一般也是存放在不同的库中。
7.3.1 水平拆分与垂直拆分
水平拆分是指数据表行的拆分，表的行数超过 200 万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。通常情况下，我们使用取模的方式来进行表的拆分。
举个例子：一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，通过用 ID 取模的方法把数据分散到四张表内，然后查询、更新、删除也是通过取模的方法来查询。在 insert 时还需要一张临时表 uid_temp 来提供自增的 ID,该表的唯一用处就是提供自增的 ID;
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的 I/O，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。通常我们按以下原则进行垂直拆分：① 把不常用的字段单独放在一张表； ② 把诸如 text，blob 等大字段拆分出来放在附表中; ③ 经常组合查询的字段放在一张表中。
7.3.2 读写分离中间件
我们一般选择使用 MySQL 的 proxy 中间件代理工具来实现读写分离，proxy 固定连接一个数据库，即使数据库地址更换也无需更换项目中的数据库连接配置。常见的中间件工具中有：mysql-proxy、atlas、cobar、mycat、tddl、tinnydbrouter和mysql router等，关于它们之间的差异可以参考这篇文章。后面我会补充一个用 Mycat 实现读写分离的 demo。
参考
- 《高性能MySQL(第3版)》
- 《MySQL必知必会》
- blog.itpub.net/29654823/vi…
