MySQL 的体系结构

存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含 ：InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

• InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

• InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。
②. 使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

日志系统

每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL的设计者WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。

• Server 层的 binlog 逻辑日志

记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。

• 搜索引擎的 redolog 物理日志

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。

redolog是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
redolog是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定，会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

SQL语句执行流程（两段式提交）：

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

• 图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

索引

索引的基本原理

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的事后，先拿到倒排表记录，再取出数据地址链，从而拿到具体数据

• InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
• 索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引

主键和非主键索引的区别：

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。
• 基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

聚簇索引

• 聚簇索引：将数据与索引按照一定的组织顺序存放在一起，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的。即：只要索引是相邻的，那么对应的数据也一定存放在磁盘的相邻位置（适合范围查询）。
• 非聚簇索引：叶子节点不存储数据，存储的是数据行地址。也就是说根据索引查找到数据行的位置再去磁盘查找数据。

聚簇索引优势：

1. 通过聚簇索引可以直接获取数据，不用回表
2. 聚簇索引对于范围查询的效率很高
3. 聚簇索引适用于排序查询的场合（索引字段是排序的）

聚簇索引劣势：

1. 维护索引十分昂贵。插入行或者主键需要更新时有可能导致页分裂
2. 如果使用UUID随机生成主键，则可能导致数据存储稀疏，使用的页较多，造成表结构过大，查询效率下降
3. 主键较大时会消耗过多的存储空间（分主键索引中需要存放主键）

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

• 对查询频次较高，且数据量比较大的表建立索引。
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 使用短索引，避免索引页分裂。索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
• 利用最左前缀。N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
• 索引数量不是多多益善。索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

索引优化

• 复合索引与最左前缀

指的是查询从索引的最左前列开始，并且不跳过索引中的列。

# 创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
# 就相当于创建了三个索引 ： 
# name
# name + status
# name + status + address
select * from tb_seller where name = 'xxx'; # 索引生效
select * from tb_seller where status = '0'; # 违背最左前缀原则，索引失效

• 覆盖索引避免回表

如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据。（回到主键索引树搜索的过程，我们称为回表）
如下图，对id建立主键索引，同时对k建立索引。在执行select * from T where k between 3 and 5 查询语句时，需要进行回表：在k索引树上查出符合条件的k对应的id，回到id索引树查询id对应的数据

如果将查询sql优化为 select id from T where k between 3 and 5 则不用进行回表。这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

索引失效

• 以%开头的Like模糊查询

头部模糊匹配会导致索引失效，而只有尾部模糊匹配则不会导致索引失效

• 解决方案：通过索引覆盖进行解决

• 违反最左前缀法则

• 范围查询右边的列，不能使用索引

前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引

• 对索引进行运算

• 字符串不加单引号，造成索引失效

若查询时没有对字符串加单引号，则MySQL的优化器会进行自动类型转换，导致索引失效（相当于对索引进行了运算）。

• in 走索引，not in 索引失效

SQL 语句优化

explain

可以通过以下两种方式定位执行效率较低的 SQL 语句：

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用—log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。
• show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

type 显示的是访问类型，是较为重要的一个指标，可取值为：

DML 优化

• 大批量插入数据优化

1) 主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
2) 关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
3) 手动提交事务
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

• 优化insert插入

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

insert into t values(1, 'zhangsan');
insert into t values(2, 'lisi');
insert into t values(3, 'wangwu');

# 优化为一条sql语句，减少连接资源的消耗
insert into t values(1, 'zhangsan'), (2, 'lisi'), (3, 'wangwu');

数据有序插入，避免页分裂

insert into t values(2, 'lisi');
insert into t values(3, 'wangwu');
insert into t values(1, 'zhangsan');

# 有序插入
insert into t values(1, 'zhangsan');
insert into t values(2, 'lisi');
insert into t values(3, 'wangwu');

DQL 优化

• 优化 Filesort

两种排序方式：

• 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序。都叫 FileSort 排序。
• 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

优化方式：
1、尽量减少额外的排序，通过索引直接返回有序数据。where 条件和 Order by 使用相同的索引，并且 Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现Filesort。
2、如果 Filesort 不可避免，则需要加快排序操作速度。可以适当增加排序缓冲区的大小，提高排序的效率

• 优化 group by

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序

# order by null 禁止排序
select age, count(*) from t_emp group by age order by null;

• 优化嵌套查询

可以使用连接查询代替子查询。
连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

# 子查询
select * from t_user where id in (select user_id from user_role );

# 优化子查询为连接查询
select * from t_user u join user_role r on u.id = r.user_id;

• 优化 or 条件查询

对于包含 OR 的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引; 如果没有索引，则应该考虑增加索引。
可以使用 UNION 替换 OR

select * from emp where id = 1 or age = 30;

# 使用 UNION 替换 OR 进行优化
select * from emp where id = 1 UNION select * from emp where age = 30;

UNION查询不会在内存中创建临时表

• 优化分页查询

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

select * from tb_item a, 
(select id from tb_item order by id limit 20000, 10) b
where  a.id = b.id;

对于主键自增的表，可以把 limit 查询转换为某个具体位置的查询

事务

事务ACID

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

• 事务ACID特性的保证

原子性：由存储引擎的undo-log记录需要回滚的日志信息（sql语句的反向执行信息），事务回滚时撤销执行成功的sql
一致性：由其他三大特性保证，程序代码要保证业务上的一致性
隔离性：由MVCC来保证
持久性：bin-log + redo-log 来保证（两段式提交）

并发事务处理带来的问题

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

备注 ： √ 代表可能出现 ， × 代表不会出现 。

Mysql 的数据库的默认隔离级别为 Repeatable read。

• MVCC

多版本并发控制：不同的事务会看到不同的版本的数据
MVCC在 READ COMMITTED 和 REPEATABLE READ 两种隔离级别下工作。READ UNCOMMITTED 总是读取最新的数据，而 SERIALIZABLE 则会对所有读取的行加锁。
RC 隔离级别下每次查询开始都会生成一个视图；RR 则在第一次读的时候生成一个视图，以后都重复读第一次生成的视图

幻读
1、在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。
2、幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。

幻读的影响

• 会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。
• 产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过binlog恢复的数据是会将所有符合条件的目标行都进行变更的。

幻读产生的原因

• 行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

如何解决幻读

• 可重复读隔离级别下，一个事务中只使用当前读，或者只使用快照读都能避免幻读。
• 将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。

锁

• 从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。
2） 行锁：操作时，会锁定当前操作行。

• 从对数据操作的类型分：

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

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

InnoDB行锁

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。

InnoDB 实现了以下两种类型的行锁。

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；
对于普通SELECT语句，InnoDB不会加任何锁；

行锁升级

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

• 当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是所谓的间隙锁（Next-Key锁） 。

优化建议：
尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，及索引范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可使用低级别事务隔离(但是需要业务层面满足需求)