mysql原理分析（可用于培训）

1聚簇索引和非聚簇索引

MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。

MyISAM还采用压缩机制存储索引，比如，第一个索引为“her”，第二个索引为“here”，那么第二个索引会被存储为“3,e”，这样的缺点是同一个节点中的索引只能采用顺序查找。

InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-Treenode的页，存放的即是表中行的实际数据了。

InnoDB中的页大小为16KB，且不可以更改

InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外，即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节，但实际只能存储65532个。同时InnoDB是B+树结构的，因此每个页中至少应该有两个行记录，否则失去了B+树的意义，变成了链表，所以一行记录最大长度的阈值是8098，如果大于这个值就会将其存到溢出行中。

2覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

explainselectg_c_id_fromhouse_whereg_c_id_="010110066"

explainselect*fromhouse_whereg_c_id_="010110066"

3组合索引

建组合索引的时候，区分度最高的在最左边。

1）如果wherea=?andb=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。

2）存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：wherea>?andb=?那么即使a的区分度更高，也必须把b放在索引的最前列。

4索引字段不要有null

首先，我们要搞清楚“空值”和“NULL”的概念：

1、空值是不占用空间的

2、MySQL中的NULL其实是占用空间的

所谓的NULL就是什么都没有，连\0都没有，\0在字符串中是结束符，但是在物理内存是占空间的，等于一个字节，

而NULL就是连这一个字节都没有。在数据库里是严格区分的，任何数跟NULL进行运算都是NULL,判断值是否等于NULL，不能简单用=，而要用ISNULL关键字。

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam中固定大小的索引变成可变大小的索引。

不要使用count(列名)或count(常量)来替代count(*)

5limit和orderby

如果你orderby和limit一起使用，那么mysql在排序结果中找到最初的row_count行之后就会完成这条语句，而不是对整个结果集进行排序。如果使用了索引排序，它就非常快地完成。如果整个filesort必须都做完的话，

那么在找到最初的row_count行之前，匹配该查询的所有行都将被select，并且做sort操作。如果这些行找到了，mysql将不会对剩余的结果集进行排序。

排序缓存有一个参数是sort_buffer_size，如果这个参数大小足够上面范例中的N行的排序结果集(如果M也被定义，那就是M+N行的结果集大小)，那么服务器将会避免一个文件排序操作，使得排序完全在内存中完成。

内存排序+limit原理

1扫描表，在内存中插入那些被选择排序的列的数据到一个排好序的队列中，比如orderbycol1，col2，则插入col1和col2列的数据。如果队列满了，则挤出排序在末尾的数据。

2返回队列中的前N行记录，如果M也被定义，则调到第M行开始返回后续的N行记录。

文件排序+limit原理

1扫描表，重复步骤2和3，直到表的结尾

2选中这些行数直到排序缓存被填满

3在排序缓存中写入第一个N行（如果M被定义，则M+N行）到一个排序文件中。

两者比较

在内存中排序和使用文件排序相比，扫描表的代价几乎是一样的，不同的是其他的开销：

内存排序的方法在插入数据到一个有序队列中会牵扯到更多的cpu资源，而文件排序会消耗更多的磁盘IO，优化器在考虑两者的平衡性上会主要考虑N的值大小

6利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，

要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联

SELECT*FROMhouse_LIMIT10000,2;

SELECTa.*FROMhouse_a,(selectid_fromhouse_LIMIT100000,2)bwherea.id_=b.id_

select*fromtable_namewhereid>=(selectidfromtable_namelimitoffset,1)limitrows

7join的原理

（1）SimpleNested-LoopJoin

这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了

（2）IndexNested-LoopJoin

索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。

这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

（3）BlockNested-LoopJoin

在有索引的情况下，MySQL会尝试去使用IndexNested-LoopJoin算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的SimpleNested-LoopJoin算法，而是会优先使用BlockNested-LoopJoin的算法。

BlockNested-LoopJoin对比SimpleNested-LoopJoin多了一个中间处理的过程，也就是joinbuffer，使用joinbuffer将驱动表的查询JOIN相关列都给缓冲到了JOINBUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问joinbuffer。

在MySQL当中，我们可以通过参数join_buffer_size来设置joinbuffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到joinbuffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个joinbuffer。

explainSELECTa.*FROMhouse_ajoinhouse_bona.men_=b.men_;

explainSELECTa.*FROMhouse_ajoinhouse_bona.id_=b.id_;

8in和exists

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false

select*fromAwhereexists(select*fromBwhereB.id=A.id);

可以转化以下伪代码，便于理解

for($i=0;$i<count(A);$i++){

$a=get_record(A,$i);#从A表逐条获取记录

if(B.id=$a[id])#如果子条件成立

$result[]=$a;}

主要是用到了B表的索引，A表如何对查询的效率影响应该不大

select*fromAwhereA.idin(selectidfromB);

主要是用到了A的索引子查询结果是放到一个hash中

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）

1：

select*fromAwhereccin(selectccfromB)效率低，用到了A表上cc列的索引；

select*fromAwhereexists(selectccfromBwherecc=A.cc)效率高，用到了B表上cc列的索引。

2：

select*fromBwhereccin(selectccfromA)效率高，用到了B表上cc列的索引；

select*fromBwhereexists(selectccfromAwherecc=B.cc)效率低，用到了A表上cc列的索引。

notexists和notin

1.select*fromAwherenotexists(select*fromBwhereB.id=A.id);

2.select*fromAwhereA.idnotin(selectidfromB);

看查询1，还是和上面一样，用了B的索引

而对于查询2，可以转化成如下语句select*fromAwhereA.id!=1andA.id!=2andA.id!=3;

可以知道notin是个范围查询，这种!=的范围查询无法使用任何索引,等于说A表的每条记录，都要在B表里遍历一次，查看B表里是否存在这条记录故notexists比notin效率高

notin和notexists如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而notextsts的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比notin要快。

9SQL性能优化的目标

至少要达到range级别，要求是ref级别，如果可以是consts最好

ALL全表扫描

index这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。

ref出现该连接类型的条件是：查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描

ref_eqref_eq与ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

const/system通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量

nullMySQL不访问任何表或索引，直接返回结果

groupby

http://tech.it168.com/a2009/0324/269/000000269430_3.shtml

使用松散(Loose)索引扫描实现

EXPLAINselectcategoryfromratinggroupbycategory

　要利用到松散索引扫描实现GROUPBY，需要至少满足以下几个条件：

◆GROUPBY条件字段必须在同一个索引中最前面的连续位置;

◆在使用GROUPBY的同时，只能使用MAX和MIN这两个聚合函数;

紧凑索引扫描实现GROUPBY和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键

EXPLAINselectcategoryfromratingwherecategory=2groupbycategory

使用临时表实现GROUPBY

前面两种GROUPBY的实现方式都是在有可以利用的索引的时候使用的，当MySQLQueryOptimizer无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成GROUPBY操作。

锁

SELECT...LOCKINSHAREMODE;

SELECT...FORUPDATE;

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

意向共享锁（IS锁）：事务T在对表中数据对象加S锁前，首先需要对该表加IS（或更强的IX）锁。

意向排他锁（IX锁）：事务T在对表中的数据对象加X锁前，首先需要对该表加IX锁。