mysql的索引和事务细致解读
一、索引是做什么的?
许多时候，当你的利用程序进行SQL查询速度很慢时，应当想想可否可以建索引。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引运用R-树，而且MEMORY表还支撑hash索引。
索引是一个排序的列表，在这个列表中存储着索引的值和包括这个值的数据所在行的物理地址，在数据十分巨大的时候，索引可以大大加速查询的速度，这是由于运用索引后可以不消扫描全表来定位某行的数据，而是先通过索引表寻到该行数据对应的物理地址然后拜访响应的数据。
二、索引的优缺陷
优势：可以迅速检索，减少I/O次数，加速检索速度；依据索引分组和排序，可以加速分组和排序；
劣势：索引自身也是表，因而会占用存储空间，个别来说，索引表占用的空间的数据表的1.5倍；索引表的保护和新建需要工夫老本，这个老本随着数据量增大而增大；构建索引会落低数据表的修改操纵（删除，增加，修改）的效率，由于在修改数据表的同时还需要修改索引表；
三、索引的分类
常见的索引类型有：主键索引、独一索引、普通索引、全文索引、组合索引
1、主键索引：即主索引，依据主键pk_clolum（length）创立索引，不允许反复，不允许空值；
ALTER TABLE 'table_name' ADD PRIMARY KEY('id')；2、独一索引：用来创立索引的列的值必需是独一的，允许空值
ALTER TABLE 'table_name' ADD UNIQUE('email')；3、普通索引：用表中的普通列构建的索引，没有任何限定
ALTER TABLE 'table_name' ADD INDEX index_name('description')；4、全文索引：用大文本对象的列构建的索引（下一局部会解说）
ALTER TABLE 'table_name' ADD FULLTEXT('content')；5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；遵循“最左前缀”准则，把最常用作为检索或排序的列放在最左，顺次递减，组合索引相当于创立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不克不及运用索引的。
在运用组合索引的时候可能由于列名长度过长而致使索引的key太大，致使效率落低，在允许的状况下，可以只取col1和col2的前几个字符作为索引
ALTER TABLE 'table_name' 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的索引和事务细致解读的细致内容，更多请关注 百分百源码网 其它相干文章！
