Mysql索引，事务和锁

　　一、索引
　　1、存储引擎：InnoDB，MyISAM，Memory。
　　2、InnoDB：数据会存储到磁盘上，在真正处理数据时，需要先将数据加载到内存，表中读取某些记录时，
　　InnoDB不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分位若干个页，以
　　页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16kb，也就是说，当需要从磁盘
　　中读取数据时，每次最少从磁盘读取16kb的内容到内存，每次最少也会把内存中的16kb内容写到磁盘中。
　　3、页是InnoDB管理存储空间的基本单位，一个页的默认大小是16kb
　　showglobalstatuslikeI页结构：名称中文名称占用空间描述FileHeader文件头部38字节页的一些通用信息PageHeader页面头部56字节数据页专有的一些信息InfimumSupremum最小记录和最大记录26字节两个虚拟的行记录UserRecords用户记录不确定实际存储的行记录内容FreeSpace空闲空间不确定页中尚未使用的空间PageDirector页面目录不确定页中某些记录的相对位置（当一行的数据量很小时，会出现一页存储更多的记录数，查询时通过页目录可以提高查询效率）FileTrailer文件尾部8字节校验页是否完整
　　4、InnoDB行格式
　　一行记录可以以不同的格式存在Innodb中，行格式分别是Compact，Redundant，Dynamic和Compressed
　　可以在创建或修改表的语句中指定行格式：
　　createtable表名（列信息）ROWFORMAT行格式名称
　　ALTERTABLE表名ROWFORMAT行格式名称Compact行格式：变长字段长度列表NULL标志位记录头信息列1数据列2数据。。。1）MySQL支持一些变长的数据类型，比如VARCHAR（M）、VARBINARY（M）、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。VARCHAR（M），M代表最大能存多少个字符。（MySQL5。0。3以前是字节，以后就是字符）；2）Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NULL值列表中，如果表中没有允许存储NULL的列，则NULL值列表也不存在了。二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL3）除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如：名称大小（单位：位：bit））描述预留位11没有使用预留位21没有使用deletemask1标记该记录是否被删除minrecmask1B树的每层非叶子节点中的最小记录都会添加该标记nowned4表示当前记录拥有的记录数heapno13表示当前记录在记录堆的位置信息recordtype3表示当前记录的类型，0表示普通记录，1表示B树非叶子节点记录，2表示最小记录，3表示最大记录nextrecord16表示下一条记录的相对位置4）记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列列名是否必须占用空间描述rowid否6字节行ID，唯一标识一条记录transactionid是6字节事务IDrollpointer是7字节回滚指针实际上这几个列的真正名称其实是：DBROWID、DBTRXID、DBROLLPTR一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为rowid的隐藏列作为主键。所以rowid是在没有自定义主键以及Unique键的情况下才会存在的。5）VARCHAR（M）类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR（65535）是否可用：mysqlCREATETABLEvarcharsizedemo（cVARCHAR（65535））CHARSETasciiROWFORMATCERROR1118（42000）：Rowsizetoolarge。Themaximumrowsizefortheusedtabletype，notcountingBLOBs，is65535。Thisincludesstorageoverhead，checkthemanual。YouhavetochangesomecolumnstoTEXTorBLOBsmysql报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，比如说我们为了存储一个VARCHAR（M）类型的列，其实需要占用3部分存储空间：1。真实数据2。变长字段真实数据的长度3。NULL值标识如果该VARCHAR类型的列没有NOTNULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用2个字节，NULL值标识需要占用1个字节。mysqlCREATETABLEvarcharsizedemo（cVARCHAR（65532））CHARSETasciiROWFORMATCQueryOK，0rowsaffected（0。02sec）CREATETABLEvarcharsizedemo（cVARCHAR（65533）notnull）CHARSETasciiROWFORMATCQueryOK，0rowsaffected（0。02sec）6）一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR（M）类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。Dynamic和Compressed行格式：这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩。
　　5、聚族索引：聚簇索引的特点：1。按主键值的大小进行记录和页的排序：数据页（叶子节点）里的记录是按照主键值从小到大排序的一个单向链表。数据页（叶子节点）之间也是是按照主键值从小到大排序的一个双向链表。B树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。2。B树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。具有这两种特性的B树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
　　6、二级索引：聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B树，不同的B树中的数据采用不同的排序规则。二级索引与聚簇索引有几处不同：1。按指定的索引列的值来进行排序2。叶子节点存储的不是完整的用户记录，而只是索引列主键。3。目录项记录中不是主键页号，变成了索引列页号。4。在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表。
　　7、联合索引：以多个列的大小为排序规则建立的B树称为联合索引，本质上也是一个二级索引。
　　8、B树索引：1。每个索引都对应一棵B树。用户记录都存储在B树的叶子节点，所有目录记录都存储在非叶子节点。2。InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。3。可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。4。B树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。5。通过索引查找记录是从B树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中的查找非常快。
　　9、根页：创建表时，mysql先创建一页，当插入的数据第一页放不下时，会把第一页复制一份，然后新开辟新的一页存新插入的数据，原来的第一页改为根页。
　　10、b树
　　11、mysql数据存储结构
　　二、查询优化1、开启查询优化器日志：开启执行sql查看日志信息：selectfrominformationschema。OPTIMIZERTRACE；关闭2、常量传递（constantpropagation）a1andba上面这个sql可以转换为：a1andb13、等值传递（equalitypropagation）abandbcandc5上面这个sql可以转换为：a5andb5andc54、移除没用的条件（trivialconditionremoval）a1and11上面这个sql可以转换为：a15、成本：一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。6、IO成本：InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为IO成本。7、CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。InnoDB存储引擎规定读取一个页面花费的成本默认是1。0，读取以及检测一条记录是否符合搜索条件的成本默认是0。2。三、事务和锁1、事务原子性（Atomicity）、隔离性（Isolation）、一致性（Consistency）、持久性（Durability）显示开启事务（提交、回滚）savepointp1；保存点（名称）ROLLBACKTOp1；
　　2、隔离级别：1未提交读（readuncommitted）：一个事务可以读到其他事务还没有提交的数据，会出现脏读2）已提交读（readcommitted）：一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，会出现不可重复读、幻读。如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，这就是幻读。3）可重复读（repeatableread）：一个事务第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据，这就是可重复读，这种隔离级别解决了不可重复，但是还是会出现幻读。4）串行化（serializable）：以上3种隔离级别都允许对同一条记录同时进行读读、读写、写读的并发操作，如果我们不允许读写、写读的并发操作，可以使用SERIALIZABLE隔离级别，这种隔离基金因为对同一条记录的操作都是串行的，所以不会出现脏读、幻读等现象。总结：READUNCOMMITTED隔离级别下，可能发生脏读脏读、不可重复读和和幻读问题。READCOMMITTED隔离级别下，可能发生不可重复读不可重复读和幻读问题，但是不会发生问题，但是不会发生脏读问题。REPEATABLEREAD隔离级别下，可能发生幻读幻读问题，不会发生脏读脏读和不可重复读的问题。SERIALIZABLE隔离级别下，各种问题都不可以发生。注意：这四种隔离级别是SQL的标准定义，不同的数据库会有不同的实现，特别需要注意的是MySQL在在REPEATABLEREAD隔离级别下，是可以禁止幻读问题的发生的隔离级别下，是可以禁止幻读问题的发生的
　　3、版本链：对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（rowid并不是必要的，我们
　　创建的表中有主键或者非NULL唯一键时都不会包含rowid列）：
　　trxid：每次对某条记录进行改动时，都会把对应的事务id赋值给trxid隐藏列。
　　rollpointer：每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记
　　录修改前的信息。
　　4、ReadView：对于使用READUNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用
　　SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READCOMMITTED和
　　REPEATABLEREAD隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下
　　版本链中的哪个版本是当前事务可见的
　　ReadView中主要包含4个比较重要的内容：1。mids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。2。mintrxid：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是mids中的最小值。3。maxtrxid：表示生成ReadView时系统中应该分配给下一个事务的id值。4。creatortrxid：表示生成该ReadView的事务的事务id。注意maxtrxid并不是mids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，mids就包括1和2，mintrxid的值就是1，maxtrxid的值就是4。有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：如果被访问版本的trxid属性值与ReadView中的creatortrxid值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。如果被访问版本的trxid属性值小于ReadView中的mintrxid值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。如果被访问版本的trxid属性值大于ReadView中的maxtrxid值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。如果被访问版本的trxid属性值在ReadView的mintrxid和maxtrxid之间，那就需要判断一下trxid属性值是不是在mids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。READCOMMITTED的实现方式：每次读取数据前都生成一个ReadView。REPEATABLEREAD实现方式：在第一次读取数据时生成一个ReadView。
　　5、MVCC（版本链readview）总结：
　　MVCC（MultiVersionConcurrencyControl，多版本并发控制）指的就是在使用READCOMMITTD、
　　REPEATABLEREAD这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。可以使不同
　　事务的读写、写读操作并发执行，从而提升系统性能。READCOMMITTD、REPEATABLEREAD这两个隔离级
　　别的一个很大不同就是：生成ReadView的时机不同，READCOMMITTD在每一次进行普通SELECT操作前都会
　　生成一个ReadView，而REPEATABLEREAD只在第一次进行普通SELECT操作前生成一个ReadView，之后的查
　　询操作都重复使用这个ReadView就好了
　　6、锁
　　读锁：共享锁、SharedLocks、S锁
　　写锁：排他锁、ExclusiveLocks、X锁1）读操作：对于普通SELECT语句，InnoDB不会加任何锁select。。。将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁（会阻塞）select。。。将查找到的数据加上一个X锁，不允许其他事务获取这些记录的S锁和X锁。2）写操作：DELETE：删除一条数据时，先对记录加X锁，再执行删除操作INSERT：插入一条记录时，会先加隐式锁隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问到。UPDATE：如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改。如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再Insert一条新记录隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想来读取这个记录会发现事务id不对应，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式锁。
　　7、行锁和表锁：1）行锁：LOCKRECNOTGAP：单个行记录上的锁LOCKGAP：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。LOCKORDINARY：锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。2）间隙锁（LOCKGAP、GAP锁）：a、ReadCommitted级别下：主键查询：abcde11111selectfromt1wherea1selectfromt1wherea1阻塞selectfromt1wherea2不阻塞查询使用的是主键时，只需要在主键值对应的那一个条数据加锁即可。唯一索引查询：abcde11111selectfromt1whereb1selectfromt1whereb1会阻塞selectfromt1whereb2不阻塞查询使用的是唯一索引时，只需要对查询值所对应的唯一索引记录项和对应的聚集索引上的项加锁即可。普通索引查询：abcde661461212116selectfromt1wheree6selectfromt1wherea6阻塞selectfromt1wherea12阻塞selectfromt1wherea1不阻塞selectfromt1wherea2不阻塞insertt1（b，c，d，e）values（20，1，1，51）；不阻塞insertt1（b，c，d，e）values（21，1，1，61）；不阻塞查询使用的是普通索引时，会对满足条件的索引记录都加上锁，同时对这些索引记录对应的聚集索引上的项也加锁。没有使用索引：abcde1111122122431146614688188101012101212116selectfromt1wherec1selectfromt1wherea1阻塞selectfromt1wherea2阻塞selectfromt1wherea3不阻塞selectfromt1wherea7不阻塞查询的时候没有走索引，也只会对满足条件的记录加锁。b、RepeatableRead级别下：查询使用主键：和ReadCommitted级别一样。查询使用唯一索引：和ReadCommitted级别一样。查询使用普通索引：QueryOK，0rowsaffected（0。00sec）mysqlselectfromt1wheree6abcde6614612121162rowsinset（0。00sec）mysqlselectfromt1wherea6阻塞mysqlselectfromt1wherea12阻塞mysqlselectfromt1wherea1不阻塞mysqlselectfromt1wherea2不阻塞mysqlinsertt1（b，c，d，e）values（20，1，1，51）；阻塞mysqlinsertt1（b，c，d，e）values（21，1，1，61）；阻塞总结：REPEATABLEREAD级别可以解决幻读，解决的方式就是加了GAP锁。查询没有使用索引：QueryOK，0rowsaffected（0。00sec）mysqlselectfromt1wherec1abcde11111221224311466146881881010121012121167rowsinset（0。00sec）mysqlselectfromt1wherea1会阻塞mysqlselectfromt1wherea2会阻塞mysqlselectfromt1wherea3不会阻塞mysqlselectfromt1wherea7会阻塞（在ReadCommitted下不会阻塞，跟解决幻读有关系）总结：查询的时候没有走索引，会对表中所有的记录以及间隙加锁。
　　8、表锁：表级别S锁，X锁。在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的在对某个表执行ALTERTABLE、DROPTABLE这些DDL语句时，其他事务对这个表执行SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，或者，某个事务对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，其他事务对这个表执行DDL语句也会发生阻塞。这个过程是通过使用的元数据锁（英文名：MetadataLocks，简称MDL）来实现的，并不是使用的表级别的S锁和X锁LOCKTABLESt1READ：对表t1加表级别的S锁。LOCKTABLESt1WRITE：对表t1加表级别的S锁尽量不用这两种方式去加锁，因为InnoDB的优点就是行锁，所以尽量使用行锁，性能更高。
　　9、
　　IS锁：意向共享锁、IntentionShared
　　Lock。当事务准备在某条记录上加S锁时，需要先在表级别
　　加一个IS锁。
　　IX锁：意向排他锁、IntentionExclusiveLock。当事务准备在某条记录上加X锁时，需要先在表级
　　别加一个IX锁
　　IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以
　　避免用遍历的方式来查看表中有没有上锁的记录
　　10、AUTOINC锁：
　　在执行插入语句时就在表级别加一个AUTOINC锁，然后为每条待插入记录的AUTOINCREMENT
　　修饰的列分配递增的值，在该语句执行结束后，再把AUTOINC锁释放掉。这样一个事务在持有
　　AUTOINC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连
　　续的。
　　采用一个轻量级的锁，在为插入语句生成AUTOINCREMENT修饰的列的值时获取一下这个轻量级
　　锁，然后生成本次插入语句需要用到的AUTOINCREMENT列的值之后，就把该轻量级锁释放掉，
　　并不需要等到整个插入语句执行完才释放锁。系统变量innodbautoinclockmode：innodbautoinclockmode值为0：采用AUTOINC锁。innodbautoinclockmode值为2：采用轻量级锁。当innodbautoinclockmode值为1：当插入记录数不确定是采用AUTOINC锁，当插入记录数确定时采用轻量级锁
　　11、悲观锁：
　　悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至
　　提交了当前事务。
　　12、乐观锁：
　　乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新（回滚）。在数据库中往
　　往添加一个version字段来实现。
　　13、死锁：session1：QueryOK，0rowsaffected（0。00sec）mysqlselectfromt1wherea1第一步abcde111111rowinset（0。00sec）mysqlupdatet1setc2wherea4；第三步开始会阻塞ERROR1213（40001）：Dtryrestartingtransactionsession2：QueryOK，0rowsaffected（0。00sec）mysqldeletefromt1wherea4；第二步QueryOK，1rowaffected（0。00sec）mysqldeletefromtt1wherea1；第四步，阻塞产生死锁，mysql有死锁检测机制，让死锁中断
　　14、避免死锁：
　　以固定的顺序访问表和行
　　大事务拆小，大事务更容易产生死锁
　　在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
　　降低隔离级别（下下签）
　　为表添加合理的索引。