对MySQL索引、锁及事务的简单分析

一.索引的数据结构

　　1.二叉搜索树实现的索引

　　　　二叉搜索树如下图，它查找元素的时间复杂度为O(logn)

　　　　

　　　　但如果经常出现增删操作，最后导致二叉搜索树变成线性的二叉树，这样它查找元素的时间复杂度就会变成O(n)，如图

　　　　

　　　　虽然可以通过树的旋转来保证该树是一个平衡二叉树（左右子树深度之差的绝对值不大于1），但磁盘的IO次数会变多，并不是最优方案

　　2.B-Tree实现的索引

　　　　B-Tree（B树）是为磁盘等外存储设备设计的一种平衡查找树，它可以让系统高效的找到数据所在的磁盘块。

　　　　为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

　　　　

　　　　

　　　　每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

　　　　模拟查找关键字29的过程：

　　　　　　1.根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

　　　　　　2.比较关键字29在区间（17,35），找到磁盘块1的指针P2。

　　　　　　3.根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

　　　　　　4.比较关键字29在区间（26,30），找到磁盘块3的指针P2。

　　　　　　5.根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

　　　　　　6.在磁盘块8中的关键字列表中找到关键字29。

　　　　分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree（平衡二叉树）缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。但由于树的每个节点能存放的数据是有限的，因此，当数据量大的时候可能会导致B树的深度变大，影响查询效率和磁盘IO次数。

　　3.B+-Tree实现的索引

　　　　B+树是应文件系统所需而产生的一种B树的改进方式（文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据），即非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点中。如下图为一个B+树结构图

　　　　

　　　　对于索引的性能，不仅仅要考虑时间复杂度，还要考虑磁盘的IO次数，因而，B+树比B树有以下优势：

　　　　（1）B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

　　　　（2）B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

　　　　（3）B+树更有利于对数据库的扫描：由于数据存放在叶子节点，所以遍历全部叶子节点即可获取全部数据

　　PS：MySQL的MyISAM、InnoDB引擎默认均使用B+树索引

　　4.Hash实现的索引

　　　　由于Hash实现索引的方式可以直接通过Hash算法定位到特定值的位置，因此有些情况它的效率比B+树还高。

　　　　但Hash实现的索引缺点也很明显：

　　　　　　不能使用范围查询

　　　　　　不能进行排序运算

　　　　　　不能避免表扫描

　　　　　　遇到大量Hash值相等的情况下，效率就会变得低下（不稳定）

二.索引的类别及数据库中索引的相关操作

　　索引可以分为密集索引和稀疏索引两大类

　　　　对于MyISAM引擎，不管是主键索引，唯一索引或普通索引，均属于稀疏索引

　　　　对于InnoDB，如果一个主键被定义，该主键则为密集索引，若没有主键被定义，则该表的第一个唯一非空索引则作为密集索引，若不满足前两个条件，InnoDB内部会生成一个隐藏主键（密集索引）

　　索引的创建方式

CREATE INDEX indexName ON TABLENAME(username(length));

　　　　或创建表的时候指定

CREATE TABLE TABLENAME(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
); 

　　　　或使用ALTER 命令添加

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

　　索引的删除方式

DROP INDEX [indexName] ON TABLENAME; 

　　　　或

ALTER TABLE testalter_tbl DROP INDEX c;

　　创建联合索引及联合索引最左匹配原则

ALTER TABLE projectfile ADD INDEX (ids, names);

　　　　查询中我们能用到的索引的是ids和ids names，而仅仅查询names是不会用到该索引的

三.慢查询

　　可以通过下面两条命令查看慢查询相关信息：

SHOW VARIABLES LIKE '%quer%';#查询相关变量
SHOW STATUS LIKE '%slow_queries%';#查询慢查询的数量

　　例如下图

　　

　　开启慢查询日志：

SET GLOBAL slow_query_log=ON;#开启慢查询日志

　　设置慢查询默认时间：

SET GLOBAL long_query_time=1;#设置慢查询默认时间（需要重新连接才生效，且重启服务后失效，如果需要永久开启慢查询日志和设置慢查询一些属性，可以在配置文件中修改）

　　我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等等，这都可以通过explain命令来查看，例如

　　

四.MySQL中引擎和锁

　　MyISAM默认用的是表级锁，不支持行级锁，而InnoDB默认用的是行级锁，也支持表级锁。

　　通过以下代码即可查看数据库中引擎使用情况

SHOW ENGINES;

　　

　　1.MyISAM引擎：

　　　　当有某个表中有数据正在被读取的时候，MyISAM会自动给整个表加上一个读锁，当有数据在被增删改的时候，MyISAM会自动给整个表加上一个写锁。当读锁未被释放的时候，写锁会被阻塞，无法对该表进行增删改，直到所有的读锁释放。同理，写锁未被释放时，读取数据的操作会被阻塞，不仅如此，一个写锁未释放，其他写锁也会被阻塞。因此，读锁也被称为共享锁，写锁也称为排他锁/互斥锁。

　　　　手动给表添加读锁或写锁：

LOCK TABLES users READ;
LOCK TABLES users WRITE;

　　　　手动解锁：

UNLOCK TABLES;

　　　　MyISAM适用场景：

　　　　　　频繁执行全表count语句

　　　　　　对数据进行增删改频率不高，查询非常频繁

　　　　　　没有事务的场景

　　2.InnoDB引擎：

　　　　InnoDB支持事务和行级锁，而MySQL默认是提交事务的，对事务添加共享锁，在sql后添加如下代码

LOCK IN SHARE MODE

　　　　InnioDB对某些行加了共享锁，但是可以对其他行加排他锁；同理，对某些行加排他锁后还可以对其他行加共享锁或排他锁，这就是行级锁特性。但行级锁会比表级锁更消耗性能

　　　　InnoDB使用场景：

　　　　　　数据增删改查都相当频繁

　　　　　　可靠性要求比较高要求支持事务的场景

　　3.乐观锁

　　　　总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。

　　　　乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

　　　　关于CAS算法：

　　　　　　1、对于资源竞争较少（线程冲突较轻）的情况，使用synchronized同步锁进行线程阻塞和唤醒切换以及用户态内核态间的切换操作额外浪费消耗cpu资源；而CAS基于硬件实现，不需要进入内核，不需要切换线程，操作自旋几率较少，因此可以获得更高的性能。

　　　　　　2、对于资源竞争严重（线程冲突严重）的情况，CAS自旋的概率会比较大，从而浪费更多的CPU资源，效率低于synchronized。

　　4.悲观锁

　　　　总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。

　　　　悲观锁适用于多写的场景，以上的共享锁和排他锁，它们都属于悲观锁。

五.MySQL中的事务

　　可以通过下列代码查询数据库使用的事务隔离级别

SELECT @@tx_isolation;

　　可以通过下列代码开启事务

START TRANSACTION;

　　可以通过下列代码修改事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;#修改事务隔离级别未读已提交，也可以是其他级别

　　事务的隔离级别：（设置的隔离级别越高越影响数据库性能）

　　　　读未提交

　　　　读已提交

　　　　可重复读

　　　　串行化

　　事务并发访问可能引发的问题：

　　　　更新丢失（一个修改覆盖另一个修改）：例如一个事务A修改了某行数据，而其他事务B也修改了该行数据。MySQL的锁机制可以解决该问题。

　　　　脏读（一个事务读到另一个事务未提交的数据）：例如一个事务A修改了某行数据，但是未提交，而是回滚了，而其他一个事务B获取该事务修改后却回滚的数据，从而产生问题。将事务隔离级别设置在读已提交之上可以解决该问题。

　　　　不可重复度（多次读取同一数据，结果不一致）：例如一个事务A一直读取某行数据，但有其他事务B对该行数据进行了修改，事务A发现读取的数据不一致，从而产生数据不可靠问题。将事务隔离级别设置在可重复读之上可以解决该问题（保证读的数据一致）。

　　　　幻读（读取到不存在的数据，或读取到的数据消失了）：例如一个事务A读取发现数据X不存在，但事务B添加了数据X，事务A发现X凭空产生了（与不可重复读很像，但它强调的是插入和删除数据）。将事务隔离级别设置在串行化可以解决该问题。在快照读读情况下，通过MVCC来避免幻读。在当前读读情况下，通过next-key来避免幻读。