MySQL进阶笔记

MySQL学习笔记

前言：

MySQL的进阶主要在于三个方面，一个是索引的优化，一个是ACID事务的原理，最后就是Mysql的读写锁以及主从复制

MySQL解析sql语句的顺序

MySQL架构

1.最上层不是mysql独有的,大多数基于网络的客户端/服务器的工具或者服务都有类似的架构,比如,连接处理,授权认证,安全等

2.第二层包括了mysql的许多核心功能,例如,查询解析,分析,优化,缓存等

SQL Interface:主要能分析出请求的sql语句是什么类型的操作(增删改查)
Parser:起到过滤的作用
Optimizer:是mysql的分析器,可以将程序员写的代码转换成mysql认为最优的

3.第三层是mysql的存储引擎,例如InnoDB,NDB,MyISAM等
————————————————

不同的 SQL JOIN

事务以及ACID原理

我们首先要知道 重做日志、回滚日志以及锁技术就是实现事务的基础。

• 事务的原子性是通过undolog来实现的。

• 事务的持久性性是通过redolog来实现的。

• 事务的隔离性是通过(读写锁+MVCC)来实现的。

• 事务的一致性是通过原子性，持久性，隔离性来实现的！！！

• 原子性、持久性、隔离性 最终目的也是为了保障数据的一致性。

第一小节：原子性的实现

首先说一下，所谓回滚操作就是当发生错误异常或者显示的执行rollback语句时候，需要把数据还原到原先的模样。所以这时候也就需要用到 undo log 来进行回滚，接下来看一下 undo log 在实现事务原子性时怎么发挥作用的。

undo log 生成：

（1）每条数据变更（insert/update/delete）操作都伴随一跳undo log的生成，并且回滚日志必须先于数据持久化到磁盘。

（2）所谓的回滚就是根据回滚日志做逆向操作，比如 delete 的逆向操作为 insert ，insert的逆向操作为delete，update的逆向为update等。

根据 undo log 进行回滚 ：

为了做到同时成功或者失败，当系统发生错误或者执行 rollback 操作时需要根据 undo log 进行回滚。

回滚操作就是要还原到原来的状态，undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，比如：

(1) 如果在回滚日志里有新增数据记录，则生成删除该条的语句

(2) 如果在回滚日志里有删除数据记录，则生成生成该条的语句

(3) 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

第二小节：持久性的实现

先了解一下MySQL的数据存储机制，MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘 IO,然而即使是使用 SSD 磁盘 IO 也是非常消耗性能的。为此，为了提升性能 InnoDB 提供了缓冲池(Buffer Pool)，Buffer Pool 中包含了磁盘数据页的映射，可以当做缓存来使用：

读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；

写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；

上面这种缓冲池的措施虽然在性能方面带来了质的飞跃，但是它也带来了新的问题，当MySQL系统宕机，断电的时候可能会丢数据！！！

因为我们的数据已经提交了，但此时是在缓冲池里头，还没来得及在磁盘持久化，所以我们急需一种机制需要存一下已提交事务的数据，为恢复数据使用。

于是 redo log就派上用场了。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

​ 1.刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
​ 2.刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

此外，在MySQL中还存在bin log(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

​ 1.作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
层次不同：redo log是InnoDB存储引擎实现的，而bin log是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎。

​ 2.内容不同：redo log是物理日志，内容基于磁盘的Page；bin log的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。

​ 3.写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

​ 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证，除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

那么怎么保证redo log和bin log中的数据一致呢？

innodb首先会进行redo log写盘，然后innodb事务进入prepare状态。如果前面prepare成功，就将bin log写盘，将事务日志持久化到bin log中，如果bin log持久化成功，那么innodb会将redo log中的事务写一个commit记录

第三小节：隔离性的实现

隔离性是事务ACID特性里最复杂的一个。在SQL标准里定义了四种隔离级别，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的。

级别越低的隔离级别可以执行越高的并发，但同时实现复杂度以及开销也越大。

Mysql 隔离级别有以下四种（级别由低到高）：

READUNCOMMITED(未提交读)

READCOMMITED(提交读)

REPEATABLEREAD(可重复读)

SERIALIZABLE (可重复读)

只要彻底理解了隔离级别以及他的实现原理就相当于理解了ACID里的隔离型。前面说过原子性，隔离性，持久性的目的都是为了要做到一致性，但隔离型跟其他两个有所区别，原子性和持久性是为了要实现数据的可性保障靠，比如要做到宕机后的恢复，以及错误后的回滚。

那么隔离性是要做到什么呢？ 隔离性是要管理多个并发读写请求的访问顺序。 这种顺序包括串行或者是并行

说明一点，写请求不仅仅是指insert操作，又包括update操作。

总之，从隔离性的实现可以看出这是一场数据的可靠性与性能之间的权衡。

第四小节：一致性的实现

数据库总是从一个一致性的状态转移到另一个一致性的状态。

举个例：小盟从银行卡转 400 到 基金账户

‍1.假如执行完 update bank set balance = balance - 400;之发生异常了，银行卡的钱也不能平白无辜的减少，而是回滚到最初状态。

2.又或者事务提交之后，缓冲池还没同步到磁盘的时候宕机了，这也是不能接受的，应该在重启的时候恢复并持久化。

3.假如有并发事务请求的时候也应该做好事务之间的可见性问题，避免造成脏读，不可重复读，幻读等。在涉及并发的情况下往往在性能和一致性之间做平衡，做一定的取舍，所以隔离性也是对一致性的一种破坏。

总结

实现事务采取了哪些技术以及思想？

★ 原子性：使用 undo log ，从而达到回滚

★ 持久性：使用 redo log，从而达到故障后恢复

★ 隔离性：使用锁以及MVCC,运用的优化思想有读写分离，读读并行，读写并行

★ 一致性：通过回滚，以及恢复，和在并发环境下的隔离做到一致性。
————————————————

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

注意：大部分数据库的默认级别都是read-committed，MySQL默认事务隔离级别为repeatable-read。

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理 员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

1. MVCC简介

1.1 什么是MVCC

MVCC是一种多版本并发控制机制。 类似于版本控制

1.2 MVCC是为了解决什么问题?

• 大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用.
• 大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.

1.3 MVCC实现

MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制.

2.MVCC 具体实现分析

下面,我们通过InnoDB的MVCC实现来分析MVCC使怎样进行并发控制的.
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.下面看一下在REPEATABLE READ隔离级别下,MVCC具体是如何操作的.

简单的小例子

create table yang(
id int primary key auto_increment,
name varchar(20));

假设系统的版本号从1开始.

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为版本号.
第一个事务ID为1；

start transaction;
insert into yang values(NULL,'yang') ;
insert into yang values(NULL,'long');
insert into yang values(NULL,'fei');
commit;

对应在数据中的表如下(后面两列是隐藏列,我们通过查询语句并看不到)

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	undefined
2	long	1	undefined
3	fei	1	undefined

SELECT

InnoDB会根据以下两个条件检查每行记录:
a.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

只有a,b同时满足的记录，才能返回作为查询结果.

DELETE

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.
看下面的具体例子分析:
第二个事务,ID为2;

start transaction;
select * from yang;  //(1)
select * from yang;  //(2)
commit; 

假设1

假设在执行这个事务ID为2的过程中,刚执行到(1),这时,有另一个事务ID为3往这个表里插入了一条数据;
第三个事务ID为3;

start transaction;
insert into yang values(NULL,'tian');
commit;

这时表中的数据如下:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	undefined
2	long	1	undefined
3	fei	1	undefined
4	tian	3	undefined

然后接着执行事务2中的(2),由于id=4的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以id=4的数据行并不会在执行事务2中的(2)被检索出来,在事务2中的两条select 语句检索出来的数据都只会下表:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	undefined
2	long	1	undefined
3	fei	1	undefined

假设2

假设在执行这个事务ID为2的过程中,刚执行到(1),假设事务执行完事务3后，接着又执行了事务4;
第四个事务:

start   transaction;  
delete from yang where id=1;
commit;  

此时数据库中的表如下:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	4
2	long	1	undefined
3	fei	1	undefined
4	tian	3	undefined

接着执行事务ID为2的事务(2),根据SELECT 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,而id=4的行上面已经说过,而id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务2的(2)select * from yang也会把id=1的数据检索出来.所以,事务2中的两条select 语句检索出来的数据都如下:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	4
2	long	1	undefined
3	fei	1	undefined

UPDATE

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间.

假设3

假设在执行完事务2的(1)后又执行,其它用户执行了事务3,4,这时，又有一个用户对这张表执行了UPDATE操作:
第5个事务:

start  transaction;
update yang set name='Long' where id=2;
commit;

根据update的更新原则:会生成新的一行,并在原来要修改的列的删除时间列上添加本事务ID,得到表如下:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	4
2	long	1	5
3	fei	1	undefined
4	tian	3	undefined
2	Long	5	undefined

继续执行事务2的(2),根据select 语句的检索条件,得到下表:

id	name	创建时间(事务ID)	删除时间(事务ID)
1	yang	1	4
2	long	1	5
3	fei	1	undefined

还是和事务2中(1)select 得到相同的结果.

索引简介

索引是什么：索引是一种数据结构，可以理解成，排好序了的可以提升查找速度的数据结构，一般来说索引很大，因此不太可能一直存在内存中，一般以索引文件形式存储在磁盘上。

官方解释：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构都以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这些数据结构就是索引。索引是为了查找和排序

索引类型:

index 普通的索引，索引所指的数据可以重复

复合索引：由多个列组成的索引结构

unique 唯一索引，索引所指的值必须唯一

创建索引:

create [unique] index [indexname] on [tablename]([column_name])
//cloumn_name可以有多个，相当于多个字段判断一个索引

显示创建的索引

show index from [tablename]

索引的结构

BTree索引，Hash索引，全文索引fulltext，R-Tree索引

适合建立索引的情况

1. 主键自动建立索引

2. 频繁作为查找的列应该创建索引

3. 频繁修改的字段不应该创建索引（因为索引需要有序，所以每次增删改都会去修改索引的数据结构，因此频繁修改的字段不应该创建索引）

4. 高并发下建议多用复合索引（多个字段联合的索引）

5. where条件中用不到的字段不应该创建索引

6. 查询中的外键，与外表相关联的字段最好建立索引

7. 排序的字段如果通过索引去访问将大大增加排序速度

8. 分组的字段应该建立索引（分组的前提是排序）

不适合建立索引的情况

1. 表的记录太少
2. 频繁增删改查的字段
3. 拥有太多重复字段的列值，一般如果列的值的种类/行数越接近于1，那么索引的效率就越高

Explain分析

我们在做SQL性能调优时，一般会使用Explain去模拟SQL对语句的执行顺序，因为Mysql一般会对语句优化，并且以最优的执行顺序去执行，但是这个最优可能和我们理解的最优有些许出入，所以我们需要Explain去查看SQL的执行

使用方法：Explain + SQL

例如：

输出的列为

id

简而言之：表的读取顺序。其中id表示select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

id取值有三种情况

1. 所有的行id都相同，表示执行顺序由上到下

2. 所有行的id执行都不相同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

   然后primary表示最外层执行，subquery表示里层执行

3. id相同和不同同时存在

   例如

   先执行最里面的t3，然后t3是个虚表，所以第三行的select_type和第一行的table等于DERIVED，表示衍生。其中，第一行的table命名规则为<derived+生成虚表所在行的id>，本例中为derived2。

select_type

简而言之：表示此次select查询的类型（数据读取操作的操作类型）

第四行 null表示最后执行，其本来就是把两个集合union起来，执行最后的合并操作。

table

简而言之:表示这一行数据是关于哪张表的

partitions

type

简而言之：表示查询使用了何种类型，即访问类型，一共有：System,const,eq_ref,ref,range,index,All,null，其推荐性从左到右降序排列，最好是System类型，最坏是All类型

其中All表示全表扫描访问，性能理所应当的最差。

一般我们需要优化到range和ref级别即可。

System：表只有一行记录，即系统表，这是const类型的特例。

Const：表示通过索引一次就找到了，用于比较primary key或者unique key的索引，因为只用匹配一行数据，所以mysql可以很快找到，并且将该查询转换成一个常量。

Eq_ref:唯一性索引扫描，表示对于每个索引key，表中只有一个记录与之匹配，常见于主键索引和唯一索引。

在这个select语句中，虽然t2是全表扫描，但是相对应的t2的id在t1只有一个记录与之匹配，即为eq_ref。

Ref：非唯一性索引扫描，返回匹配某个单独值得所有行，本质上是索引，因为是返回符合某个单独值得所有行，例如去查找一个公司得所有程序员，条件是程序员，条件是唯一得，但是返回的员工会有很多条（Eq_ref就是查找一个公司得CEO，条件唯一，返回的结果也唯一只有一个）。

Range：区间范围索引扫描，扫描在范围之内的索引，不用扫描全部索引，一般就是where中有<,>,between,in等。

Index：Full index scan，index和All的区别就在于，index类型只是遍历索引树，这通常比All快，虽然all和index都是读全表，但是index是从索引中读取的，而All是从磁盘中读取。

注意：：：我们这里说的以上都是索引的Type，没有索引只可能是All。

ALL：ALL table scan，扫描全表以筛选出满足的列。

注意：：：：如果索引失效，那么访问的是ALL而不是index。例如：

possible_keys

简而言之：可以使用的索引，哪些索引可以被使用

key

简而言之：实际使用的索引，如果查询中使用了覆盖索引，则该索引列表只会出现在key中。

覆盖索引例子：

我们可以看到，虽然在possible_keys中没有，但是我们还是user index，而且key中有primary索引。

稍微解释下覆盖索引：简单而言我需要的字段（select 后面的字段，或者我需要的列，例如我需要article表的uid用来当条件)和我建立的复合索引的包含的列的顺序和字段刚好吻合，这个时候不用返回数据库去查询整个行，而是可以直接返回索引的列，这种索引就称为覆盖索引。

所以对于第二个图，虽然是全表扫描，但是我们col1和col2有一个复合索引，所以可以直接从索引取值，所以type为All。

在extra的时候会详细介绍using index 和using where。

key_len

简而言之：表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。（但是确实key_len的值越大，效果就会越好）

注意：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索得到的。

ref

简而言之：表之间的引用，显示索引的哪一列被使用了，如果可能的话最好是个常数。表示那些列或者常量被用于查找索引列上的值，参考上面的图片。

正如上图所示，优化器先执行遍历整个t2，取出对应的col1，然后给t1做条件，所以t1的ref就等于t2的col1以及const。

rows

简而言之：表示每张表有多少行被优化器查询。

filtered

extra

简而言之，Extra就是此次查询所表示出的信息，一共可能会有这些值：

• Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"（这种情况尽量要避免），如下图所示：

  对于第一个sql语句，虽然我使用了索引，并且索引里面包含col1,col2,col3，但是这里对col3排序没有使用索引排序，而是使用了文件排序（索引和排序有关系，我们希望排序的时候，最好希望所有的值和建立索引的顺序一样。也就是说上面虽然有col1,col3，但是少了个col2，中间的兄弟断了，所以无法使用索引排序）。

  按照第二条sql语句，所以我们需要把col1,col2和col3都放进去，怎么建立的索引顺序，排序就得怎么按照索引列的顺序列出值。

  还有一种例外的情况

  虽然第一条sql语句使用了索引，并且索引的每个值我们都用上了，但是我们可以看到仍然会使用using filesort。

  原因：第二个条件是一个范围，范围以后的索引会导致失效。就比如第一个可以确定，但是第二个条件是一个范围，范围的话索引不好找，我不知道去找哪个值，所以索引就会断裂，无法排序。（这里的优化在后面会说）

  解决方法：在索引(category_id，comments，views)中的comments踢出去，直接建立一个(category_id,views)的索引。

• Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by或者是union（这个也很严重，也得改，这个直接生成了新的内部临时表，这个拷贝临时表会比较麻烦，因为涉及到了建表，插入数据，删除表，耗时多）。

  如图所示，解决方法就是group by和你的索引里面的列的值，顺序一定要严格相同，这样才不会产生临时表。

• Using index:表示相应的select操作中使用了覆盖索引（Covering Index），覆盖索引在上面有所介绍，避免访问了表的数据行，效率不错。

  如果同时出现using where，表明索引被用来执行索引键值的查找（在索引的列集合中查找值）；

  如果没有同时出现using where，表明索引用来读取数据而非执行查找动作（读取某索引的值）。

  其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的（查询的列要被索引覆盖，不用读取数据行）。

• Using where:表明使用了where过滤

• Using join buffer:表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表

• Impossible where:表示where子句的值总是false,不能用来获取任何元祖

• Distinct：优化了distinct，在找到第一个匹配的时候就不找了。

查看链接数

show processlist; //查看100条连接的进程
show full processlist; //查看所有的连接的进程

慢查询日志

一般我们都会设置慢查询日志来配合explain来对超过阈值的sql语句调优

启动:

show variables like '%slow_query_log%';//获取慢查询日志状态

set global slow_query_log=1;//开启

//这个时候一般都会开启慢查询日志

结果图:

我们可以看到慢查询的开关已经为ON，并且贴出了慢查询日志的路径。（这个一般都不会是永久生效，慢查询也会导致一定的问题，所以不会永久生效挺好的，我们只需要在需要调优的时候才打开慢查询日志，如果要永久生效，则要在mysql的配置文件中添加那两行代码）

默认的阈值为10S，当然我们也可以通过代码设置：

Show profile

1.将profiling设置为ON

set profiling =ON;

2.执行select语句

3.输入show profiles;

4.具体查看到各个步骤占用资源情况

show profile cpu, block io for query id(id为上面的Query_ID)

后面的类型可以为以下类型:

我们可以看到一共有18rows，分别为mysql的执行步骤以及各个步骤的消耗的时间资源。

上面这些都是正常的，但是如果在show profiles 出现如下情况：

就会很危险，就需要我们去调优，例如：

InnoDB和MyISAM

在InnoDB中。索引都为聚集索引，即索引字段的元素为整个元组，而在MyISAM中，索引仅存储在磁盘中的地址，因此InnoDB的数据库文件大小约为数据大小的两倍。

但是InnoDB更快！！！

InnoDB会自动创建一个主键索引，即主键默认创建为聚集索引。如果表不设置主键，则会自动选择第一个不为null的列创建聚集索引，如果都没有，那么会自动使用一个6字节长整型的隐式字段创建索引。

数据库引擎	MyISAM	inoodb
事务	不支持	支持
索引	非聚集索引	默认都有个聚集索引
外键	不支持	支持
关于count(*)的计算	会保存行数，直接返回行数字段	不保存行数，直接全表扫描
全文索引	支持	不支持

1.对于自增长的字段，innodb中必须包含只含有该字段的索引，而myisam中可以和其他地段一起建立联合索引

2.delete from table时，innodb不会重新建表，会一行行删除

3.innodb的行锁不是绝对性的，执行一个sql语句时，mysql不确定扫描的范围，同样会锁全表。如果确定扫描的范围，则只会 对该范围加锁。

例如:不确定范围 like等会使索引失效
update table set num=11 where name like"%aaa%"

MySQL索引数据结构和算法原理

数据结构及算法基础

索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

看一个例子：

图1

图1展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍。

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于索引，这一节先单纯从数据结构角度描述它们。

B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。

h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。

图2是一个d=2的B-Tree示意图。

图2

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {    
    if(node == null) return null;    
    foreach(node.key) {        
        if(node.key[i] == key) return node.data[i];            
        if(node.key[i] > key) return BTree_Search(point[i]->node);    
    }    
    return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。

另外，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质，本文不打算完整讨论B-Tree这些内容，因为已经有许多资料详细说明了B-Tree的数学性质及插入删除算法，有兴趣的朋友可以在本文末的参考文献一栏找到相应的资料进行阅读。

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意。

图3

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

图4

如图4所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

这一节对B-Tree和B+Tree进行了一个简单的介绍，下一节结合存储器存取原理介绍为什么目前B+Tree是数据库系统实现索引的首选数据结构。

为什么使用B-Tree（B+Tree）

上文说过，红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

主存存取原理

目前计算机使用的主存基本都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理。

图5

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。图5展示了一个4 x 4的主存模型。

主存的存取过程如下：

当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。

写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的。

磁盘存取原理

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

图6是磁盘的整体结构示意图。

图6

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。

图7是磁盘结构的示意图。

图7

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了。

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：
$$
dmax=floor(pagesize/(keysize+datasize+pointsize))dmax=floor(pagesize/(keysize+datasize+pointsize))
$$
floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

这一章从理论角度讨论了与索引相关的数据结构与算法问题，下一章将讨论B+Tree是如何具体实现为MySQL中索引，同时将结合MyISAM和InnDB存储引擎介绍非聚集索引和聚集索引两种不同的索引实现形式。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

图8

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

图9

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图10

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

下一章将具体讨论这些与索引有关的优化策略。

索引使用策略及优化

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

示例数据库

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

图12

MySQL官方文档中关于此数据库的页面为http://dev.mysql.com/doc/employee/en/employee.html。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。

最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为1的特例。

以employees.titles表为例，下面先查看其上都有哪些索引：

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：

ALTER TABLE employees.titles DROP INDEX emp_no;

这样就可以专心分析索引PRIMARY的行为了。

情况一：全列匹配。

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

效果是一样的。

情况二：最左前缀匹配。

当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几种不同的值：

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。看下两种查询的性能比较：

“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。

情况四：查询条件没有指定索引第一列。

由于不是最左前缀，索引这样的查询显然用不到索引。

情况五：匹配某列的前缀字符串。

此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引。（原文表述有误，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）

情况六：范围查询。

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

情况七：查询条件中含有函数或表达式。

很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：

虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。再如：

显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

索引选择性与前缀索引

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

从图12可以看到employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引 建上：

ALTER TABLE employees.employeesADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

图13

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

图14

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

索引优化总结

全值匹配我最爱，最左前缀要遵守；（最左前缀：查询从索引的最左边的列开始，并且不跳过中间的列）
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

小例子：

orderBy注意：

如果是

select * from index where a=3 and c=5 order by b;

就只使用了索引里面的a，因为此时b是用于排序（而且表示b断裂了，所以查询不到c），而且使用索引来排序，所以也无filesort。

另外，我们都知道mysql的优化器会优化顺序，但是在orderby上的字段就不会自动优化顺序了，例如

例如第一个sql最后的orderby的顺序是c2,c3。然后第二个sql最后的orderby是c3，c2。由于orderby无法优化顺序，所以其实顺序是c1,c3,c2，中间断裂了，所以只用了c1，而且c3，c2无法使用索引，所以会有filesort。

另外，如果是这种

order by a asc ,b desc

也会产生using filesort。

GroupBy注意：

这里其实就和order by差不多，我们都知道group by叫分组，其实，分组之前，必排序，所以其实这里和orderBy有异曲同工之妙。所以正如前面所说，如果你要group by，最好和索引里面的列的顺序一摸一样，才不会生成临时表，也不会外部排序。

另外，group by最好能把搜索条件能写在where中的就写在where中，最好不要把条件写在having中。

单表优化案例

单表太简单 可以看Explain的extra的部分的案例。

两表优化案例

create table book{id varchaer(20) primary key, card varchar(20) }

create table class{id varchaer(20) primary key, card varchar(20) }

当我们执行

Explain select * from class left join book on class.card=book.card;

然后我们添加索引优化，这里有两个card，那么到底在哪个建立索引呢，先一个个的尝试，我们现在第一个添加索引。

Alter table 'book' add index index_card('card');

再次执行之后

虽然class还是All，但是对于book表，已经是ref的优化等级了。

总结：左连接的时候 在右表建立索引，达到优化效果。

接下来试试把索引加在左连接的左边中

drop index index_card on book;
Alter table 'class' add index index_card('card');

这个时候，神奇的事情发生了！！！！

如果是在左连接的左表建立索引，那么对于class表居然只到了index的地步。

得出结论：

左连接 索引得加右表

右连接 索引得加左表

三表优化案例

在二表查询的基础上多加上一个表

create table phone(phoneid varchar(20) primary key,card varchar(20));

首先没有索引的时候，执行下面的语句，当然全都是All

Explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card

按照二表优化的经验来看，我们先在book和phone表加上索引。

然后可以看到查询的Explain

我们可以看到，book和phone都是ref，这里优化还是比较成功。

总结（Join优化总结）：在连接表的时候，尽量用小的结果集驱动大的结果集，这样可以减少join中的NestedLoop的次数，所以把小的结果集放在left join的左边，这样All的时候就是一个小的结果集。

优化NestedLoop的内层循环（括号括起来的部分），并且保证join的on上的字段已经在索引中。最后，当无法保证被驱动表的Join条件字段被索引且内存资源充足的条件下，不要吝啬于JoinBuffer（Join的缓存）的设置。

小表驱动大表

in和exists的区别：

In我们都很清楚，无非是判断（例如第一个sql）先查询select * from B然后再判断A的id是否存在B中，这样的话，如果B的数据集小，就符合小表驱动大表的条件

对于Exists：

也就是说，会先做主查询，然后将结果带入副查询中。

锁

锁分为读锁，写锁。还可分为行锁，表锁，页锁。

读写锁（以MyISAM为例）

给一个table表加读/写锁的操作：

lock table [tablename] read|write

例如

local table article read

结果为：

但是，如果我们给一个表加了读锁，那么我们就不能对齐修改，只能释放读锁然后添加写锁。

另外还有个小知识，如果我们一个session会话读锁一张表，那么在这个期间，由于mysql的自我保护机制，我们不能在锁这张表的期间去读别人的表。其他会话可以读取。

另外，这里补充一个热知识：

1.如果在读锁的表上加读锁，会立即生效

2.如果在写锁的表上加读锁，会阻塞

3.如果在读锁的表上加写锁，会阻塞

4.如果在写锁的表上加写锁，会阻塞

但是哦，如果我另一个会话，在其他会话读锁着的表修改数据，那么也会堵塞，直到其他会话释放这个读锁。

总结：

表,行,页锁

表锁：偏向于myisam，加锁快，但是一般都是用于读取。

行锁：偏向于innodb，开销大，加锁慢，会出现死锁，但是锁的粒度最小，锁冲突的概率最低，而且并发度高。

具体myisam和innodb的不同，可以查看之前的内容。

行锁的读写锁和上面的读写锁差不多原理，只不过读写锁的粒度将表锁变成了行锁。（同样，如果一个session更改了某一行的值，但是没有commit，那么其他session无法更新该行，但是可以读取到该行）。

小知识：（关闭mysql自动提交事务）

• 当一个事务提交修改之后，没有开启事务的session可以立刻查看到结果，然而已经开始事务的session在本次事务中只能取到之前的值，事务提交之后才能看到最新修改的值。
• 当索引失效时，innodb的行锁会变成锁整张表

间隙锁：

例如：我们在数据库中对于a字段不是连续的，只有a=1,a=3,a=5；

但如果是范围查询，例如a>1,a<5，那么innodb会在索引中把1-5都加上锁，我们后期插入a=2时还是会被锁定，所以会阻塞。

面试题：如何锁定一行

begin;

select * from article where id='123' for update;

//这个时候就已经上了行锁

commit

主从复制

原理：

Master上有一个IO线程，Slave上有两个线程，分别是IO和SQL。下面是主从复制五步原理。

1、Slave服务器执行start slave；

2、Slave服务器会通过在Master上授权的复制用户权限请求连接Master服务器，并请求从指定Binlog日志文件的指定位置之后发送Binlog日志内容；

3、Master服务器接收到来自Slave服务器的IO线程的请求后，Master服务器上负责复制的IO线程根据Slave服务器IO线程请求的信息读取指定Binlog日志文件指定位置之后的Binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了Binlog日志内容外，还有本次返回日志内容后再Master服务器端的新的Binlog文件名称以及在Binlog中的下一个指定更新的位置；

4、当Slave服务器的IO线程获取到来自Master服务器上的IO线程发送日志内容及日志文件及位置点后，将Binlog日志内容依次写入到Slave端自身的Relay Log（中继日志）中文件（MySQL-relay-bin.xxxxxx）的最末端，并将新的Binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新Binlog日志时能够告诉Master服务器需要从新Binlog日志的哪个文件哪个位置开始请求新的Binlog日志内容；

5、Slave服务器端的SQL线程会实时的检测本地的Relay Log中新增加的日志内容，然后及时的把Log文件中的内容解析成在Master端曾经执行过的SQL语句，并在自身Slave服务器上安语句的顺序执行应用这些SQL语句，应用完毕后清理应用过的日志。

以linux环境下为例

第一步、关闭主机和从机的防火墙，保证两个ip能ping通

setenforce 0;
iptables -F

第二步、编辑mysql配置文件，默认在/etc/my.cnf文件，添加log-bin 以及server-id（我这里主机设置为1，从机设置为2)，然后重启mysql服务

service mysqld restart

这个时候会看到mysql生成的binlog文件 mysql-bin

第三步、授予用户权力，这里可以直接用主机root，grant repulication slave on.............

第四步、主机show master status，记住此时的file和position

第五步、在从机设置

mysql>change master to master_host='Master地址',master_port=mysql端口号,//不加引号
master_user='rep',master_password='root',
master_log_file='第三步记录的file',master_log_pos=第三步记录的position;//不加引号

例如：change master to master_host='1.15.81.4',master_port=3306,master_user='root',master_password='yuan',master_log_file='mysql-bin.000003',master_log_pos=156;

第六步、开启从机,start slave，然后show slave status，得到如下即可完成。

即完成主从复制。