MySQL的存储引擎与索引的理解

1.MySQL的存储引擎：

　　存储引擎负责对表中的数据的进行读取和写入，常用的存储引擎有InnoDB、MyISAM、Memory等，不同的存储引擎有自己的特性，数据在不同存储引擎中存放的格式也是不同的，比如Memory都不用磁盘来存储数据。

　　在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

　　InnoDB数据页结构：

　　　　页是InnoDB管理存储空间的基本单位，一个页的大小默认是16KB。

SHOW GLOBAL STATUS like 'Innodb_page_size';

　　　　

 　　　　变长字段长度列表：

　　　　MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

　　　　CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。

　　　　VARCHAR(M)，M代表最大能存多少个字符。( MySQL5.0.3以前是字节，以后就是字符)

　　　　NULL值列表：

　　　　Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NULL值列表中，如果表中没有允许存储NULL 的列，则 NULL值列表也不存在了。

　　　　记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列：

　　　　

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

　　　　实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

　　　  一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

　　　　记录中的数据太多产生页溢出怎么办？

　　　　　　一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。

　　　　　　在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

2.MySQL的索引

　　聚簇索引：

　　　　1. 按主键值的大小进行记录和页的排序：

　　　　　　1）数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。

　　　　　　2）数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。

　　　　　　3）B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

　　　　2. B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

　　具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

　　二级索引（复制索引）：

　　　　聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。

　　二级索引与聚簇索引有几处不同：

　　　　1. 按指定的索引列的值来进行排序

　　　　2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。

　　　　3. 目录项记录中不是主键+页号，变成了索引列+页号。

　　　　4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表。

　　联合索引：

　　　　以多个列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。

　　B+树索引总结：

　　　　1. 每个索引都对应一棵B+树。用户记录都存储在B+树的叶子节点，所有目录记录都存储在非叶子节点。

　　　　2. InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

　　　　3. 可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

　　　　4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

　　　　5. 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中的查找非常快。

3.使用索引的注意点

　　使用索引的代价：

　　　　1）空间上的代价：一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。

　　　   2）时间上的代价：索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。
　　怎么才能使用到索引：

　　　　1.如果只给出后缀或者中间的某个字符串，比如：

select * from t1 where b like '%101%';

　　　　这种是用不到索引的，因为字符串中间有'101'的字符串并没有排好序，所以只能全表扫描了。

　　　　2.匹配范围值：

select * from t1 where b > 1 and c > 1;

　　　　上边这个查询可以分成两个部分：

　　　　　　1. 通过条件b > 1来对b进行范围，查找的结果可能有多条b值不同的记录，

　　　　　　2. 对这些b值不同的记录继续通过c > 1继续过滤

　　　　这样子对于联合索引来说，只能用到b列的部分，而用不到c列的部分，因为只有b值相同的情况下才能用c列的值进行排序，而这个查询中通过b进行范围查找的记录中可能并不是按照c列进行排序的，所以在搜索条件中继续以c列进行查找时是用不到这个B+树索引的。

　　　　3.排序：

select * from t1 order by b, c, d;

　　　　这个查询的结果集需要先按照b值排序，如果记录的b值相同，则需要按照c来排序，如果c的值相同，则需要按照d排序。因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

　　　　4.分组：

select b, c, d, count(*) from t1 group by b, c, d;

　　　　这个查询语句相当于做了3次分组操作：

　　　　　　1.先把记录按照b值进行分组，所有b值相同的记录划分为一组。

　　　　　　2.将每个b值相同的分组里的记录再按照c的值进行分组，将title值相同的记录放到一个分组里。

　　　　　　3.再将上一步中产生的分组按照d的值分成更小的分组。

　　　　如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有索引的话，正好这个分组顺序又和B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组。

　　　　使用联合索引进行排序或分组的注意事项：

　　　　　　对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出order by c, b, d 的顺序，那也是用不了B+树索引的。

　　　　　　同理， order by b҅order by b, c 这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样：

select * from t1 where b = 1 order by c, d;

　　　　这个查询能使用联合索引进行排序是因为b列的值相同的记录是按照c, d排序的。

　　不可以使用索引进行排序或分组的几种情况：

　　　　ASC，DESC混用:

　　　　　　对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

　　　　　　　ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

select * from t1 order by b ASC, c DESC;

　　　　这个查询是用不到索引的。

4.MySql的隔离级别：

　　在SQL规范中有四种隔离级别：1.读未提交（容易产生脏读，幻读，不可重复读问题），2.读已提交（产生不可重复读问题）,3.可重复读,4.串行化读取。

　　这里所谓的脏读，幻读，不可重复读，是指自己在开启一个事务期间，其他事务的操作对本事务的影响。

　　MySQL使用mvcc的方式，也就是在每个事务读取数据的时候通过ReadView来获取数据，这样就可以做到获取不同事务的数据了。

 

查看隔离级别：
select @@tx_isolation;

设置隔离级别：
set session transaction isolation level read uncommited;

 

5.读锁和写锁的解释：

　　读锁（共享锁）：是说在一个事务中如果使用读锁，那么其他事务只能加读锁，不能加写锁。（其他事务使用select 查询不受影响）

 

select ... lock in share model(加读锁)

　　写锁（排他锁）：如果在一个事务中加上了写锁，那么其他事务既不可以加读锁也不可以加写锁。

 

select ... for update (加写锁)

 

　　delete,update,insert语句默认会给数据加上写锁。

6.行锁和表锁

　　行锁:（只有在innodb搜索引擎中才会使用）

　　LOCK_REC_NOT_GAP：单个记录上的锁；

　　LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。

　　LOCK_ORDINARY:锁定一个范围，并且锁定记录本身。

 　　表锁：（对整个表加锁，平时不会用）

LOCK TABLES T1 READ;--对表T1加表级别的s锁
LOCK TABLES T1 WRITE；--对表T1加表级别的x锁