mysql的索引和事务详细解读

一、索引是做什么的?

很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

二、索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

三、索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE &＃39;table_name&＃39; ADD PRIMARY KEY(&＃39;id&＃39;)；

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE &＃39;table_name&＃39; ADD UNIQUE(&＃39;email&＃39;)；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE &＃39;table_name&＃39; ADD INDEX index_name(&＃39;description&＃39;)；

4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE &＃39;table_name&＃39; ADD FULLTEXT(&＃39;content&＃39;)；

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE &＃39;table_name&＃39; ADD INDEX index_name(&＃39;col1&＃39;,&＃39;col2&＃39;,&＃39;col3&＃39;)；

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE &＃39;table_name&＃39; ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4个字符和col2的前3个字符作为索引

四、索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等，

1、哈希索引：

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

2、全文索引：

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
FULLTEXT(my_text));

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER my_table ADD FULLTEXT ft_index(my_text);
CREATE INDEX ft_index ON my_table(my_text);

对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。

在MySQL中，全文索引支队英文有用，目前对中文还不支持。

在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

3、BTree索引和B+Tree索引

BTree索引

BTree是平衡搜索多叉树，设树的度为d（d>1），高度为h，那么BTree要满足以一下条件：

每个叶子结点的高度一样，等于h；

每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；

叶子结点指针都为null；

非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

BTree的结构如下：

MySQL有三种锁的级别：页级、表级、行级。

　　MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；

　　BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

　　InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是

采用行级锁。

MySQL这3种锁的特性可大致归纳如下： 1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。表级锁让多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问（默认加排他表锁）；（共享读锁（Table Read Lock）更新数据时，必须要等到更新完成了，其他线程才能访问（读）这个表。（独占写锁（Table Write Lock））

2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

一般MyISAM引擎的表也支持查询和插入操作的并发进行（原则上数据表有一个读锁时，其它进程无法对此表进行更新操作）

MyISAM引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2：

a、concurrent_insert为0，不允许并发插入。 　　　　
b、concurrent_insert为1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 　　　　
c、concurrent_insert为2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。

我们还可以调整MyISAM读写的优先级别：

　　a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
　　b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
　　c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MyISAM使用的是 flock 类的函数，直接就是对整个文件进行锁定（叫做文件锁定），MyISAM的数据表是按照单个文件存储的，可以针对单个表文件进行锁定；

InnoDB使用的是 fcntl 类的函数，可以对文件中局部数据进行锁定（叫做行锁定），InnoDB是一整个文件，把索引、数据、结构全部保存在 ibdata 文件里，所以必须用行锁定。

事物控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；     
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。
COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；      
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；      
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；     
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；     
ROLLBACK TO identifier；把事务回滚到标记点；     
SET TRANSACTION；用来设置事务的隔离级别。
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务     
ROLLBACK 事务回滚    
COMMIT 事务确认

2、直接用 SET 来改变 My

SQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交     
SET AUTOCOMMIT=1 开启自动提交

注意点

1、如果事务中sql正确运行，后面没有commit，结果是不会更新到数据库的，所以需要手动添加commit。

2、如果事务中部分sql语句出现错误，那么错误语句后面不会执行。而我们可能会认为正确操作会回滚撤销，但是实际上并没有撤销正确的操作，此时如果再无错情况下进行一次commit，之前的正确操作会生效，数据库会进行更新。

以上就是mysql的索引和事务详细解读的详细内容，更多请关注 第一PHP社区 其它相关文章！