MySQL 索引、事务与存储引擎

1 MySQL 索引介绍

1.1 索引概述

当数据保存在磁盘类存储介质上时， 它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似千链表，都包含数据部分，以及一个指向下一个节点（ 或数据块）的指针， 不需要连续存储。

记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（ Li near Search ) 的过程，平均需要访问N/2 的数据块， N 是表示所占据的数据块数目。如果这个字段是一个非主键字段（ 也就是说， 不包含唯一的访问入口） ，那么需要在N 个数据块上搜索整个表格空间。

但是对千一个有序字段， 可以运用二分查找( B i na ry Search ），这样只需要访问log2 (N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引， 将创建另外一个数据结构， 包含字段数值以及指向相关记录的指针， 然后对这个索引结构进行排序，允许在该数据上进行二分法排序。
使用索引的副作用是需要额外的磁盘空间。对千My lSAM 引擎而言， 这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大戳的磁盘空间， 这个文件将很快到达底层文件系统所能够支持的大小限制。

1.2 索引作用

在索引列上，除了上面提到的有序查找之外， 数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

例如， 有3 个未索引的表t 1 、t2、t3, 分别只包含列c1 、c2 、c3, 每个表分别含有1000行数据组成， 均为1 ~ 1000 的数值， 查找对应值相等行的查询如下所示。

mysql>SELECT c1 ,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3;

此查询结果应该为1000 行， 每行包含3 个相等的值。在无索引的情况下处理此查询，必须寻找3 个表所有的组合，以便得出与WHERE 子句相配的那些行。而可能的组合数目为10oox10oox1000 （ 十亿） ，显然查询将会非常慢。

如果对每个表进行索引， 就能极大地加速查询进程， 利用索引的查询处理如下。

• 从表t 1 中选择第一行， 查看此行所包含的数据。
• 使用表t2 上的索引， 直接定位t2 中与t 1 的值匹配的行。同理，利用表t3 上的索引，直接定位t3 中与t 1 的值匹配的行。
• 扫描表t 1 的下一行并重复前面的过程，直到遍历t1 中所有的行。

1.3 索引的分类

1. 普通索引

普通索引是最基本的索引， 它没有任何限制， 也是大多数情况下用到的索引。它有以下几种创建方式。

直接创建索引：

mysql>CREATE INDEX index_name ON table_name(column(length));

column 是指定要创建索引的列名。通常可以考虑将查询语句中在J OIN 子句和WHERE 子句里经常出现的列作为索引列。

其中len gt h 是可选项。如果忽略len gth 的值， 则使用整个列的值作为索引。如果指定使用列前的lengt h 个字符来创建索引， 就是使用列的一部分来创建索引， 这样有利千减小索引文件的大小， 节省索引列所占的空间。在某些情况下， 只能对列的前缀进行索引。索引列的长度有一个最大上限255 个字节C MylSAM 和lnnoDB 表的最大上限为1000 个字节） ， 如果索引列的长度超过了这个上限， 就只能用列的前缀进行索引。另外， BLOB 或TEXT 类型的列也必须使用前缀索引。column 和len gt h 的含义， 在下面创建索引的操作语句中意义相同。

修改表结构的方式添加索引：

mysql>AL TER TABLE table_name ADD INDEX index_name (column(length));

创建表结构时， 同时创建索引：

CREATE TABLE `table`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`time` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)

2. 唯一索引

唯一索引与普通索引类似， 不同的就是： 唯一索引的索引列的值必须唯一， 但允许有空值（注意和主键不同）。如果是组合索引， 则列值的组合必须唯一。唯一索引创建方法和普通索引类似。

创建唯一索引：

mysql>CREATE UNIQUE INDEX index_name ON table_name(column(length));

修改表结构的时候添加唯一索引：

mysql>AL TER TABLE table_name ADD UNIQUE index_name (column(length));

创建表的时候同时创建唯一索引：

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` te xt CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY( `id ` ) ,
UNIQUE indexName (title(length))
) ;

3．主键索引

主键索引是一种特殊的唯一索引， 一个表只能有一个主键， 不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(255) NOT NULL,
PRIMARY KEY( `id `)
) ;

4. 组合索引（最左前缀）

在一个use 「用户表中， 有name, age , sex 三个字段， 分别分三次建立了INDEX 普通索引。那么在select* from user where name= 11 AND age= 11 AND sex=” ； 数据查询语句中就会分别检索三条索引， 虽然扫描效率有所提升， 但却还未达到最优。这个时候就需要使用到组合索引（ 即多列索引） ， 如下所示。

create table user(
name varchar(9),
age int(3),
sex tinyint(1),
index user(name, age, sex)
) ;

在M ySQ L 中， 有一个知识点叫最左原则。下面的select 语句的where 条件是依次从左往右执行的。

mysql>select * from user where name=" AND age=" AND sex=";

若使用的是组合索引i ndex user(name, age , sex) 。在查询中，name 、age、sex 的顺序必须如组合索引中一致排序， 否则索引将不会生效，例如：

mysql>select * from user where age=" AND name=" AND sex=";

如果采用“ select* from user where age=" AND name= " AND sex= ";“ 查询方式，这条组合索引将无效化， 所以一般在建立索引时，要先想好相应的查询业务， 尽量避免虽然有索引，但是使用不上的问题。

5. 全文索引(FULLTEXT)

对于较大的数据集，将资料输入一个没有FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT 索引的速度更快。不过切记对千大容噩的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

创建表的全文索引：

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`uue` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`contenf` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY( `id ` ),
FULL TEXT (content)
) ;

修改表结构添加全文索引：

mysql>AL TER TABLE article ADD FULL TEXT index_content(content);

直接创建索引：

mysql>CREATE FULL TEXT INDEX index_content ON article(content);

1.4 创建索引的原则依据

数据库建立索引的原则：

• 确定针对该表的操作是大呈的查询操作还是大盘的增删改操作；
• 尝试建立索引来帮助特定的查询。检查自己的sq l 语句， 为那些频繁在where 子句中出现的字段建立索引；
• 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间， 同时复合索引也占磁盘空间；
• 对千小型的表， 建立索引可能会影响性能；
• 应该避免对具有较少值的字段进行索引；
• 避免选择大型数据类型的列作为索引。

索引建立的原则：

• 索引查询是数据库中重要的记录查询方法， 要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑， 下面给出实际生产环境中的一些通用的原则：在经常用作过滤器的字段上建立索引；
• 在SQ L 语句中经常进行GROUP BY 、ORDER BY 的字段上建立索引；
• 在不同值较少的字段上不必要建立索引，如性别字段；
• 对千经常存取的列避免建立索引；
• 用千联接的列（ 主健／外健）上建立索引；
• 在经常存取的多个列上建立复合索引， 但要注意复合索引的建立顺序要按照使用的频度来确定；
• 缺省情况下建立的是非簇集索引， 但在以下情况下最好考虑簇集索引， 如： 含有限数目（不是很少）唯一的列； 进行大范围的查询； 充分的利用索引可以减少表扫描1/0 的次数， 有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决千DBA 所设计的数据库结构。

1.5 查看索引

MySQ L 数据表索引已经创建好了， 那么如何才能查看刚刚创建的索引？ 或者怎么去查看表内已经存在的索引？ 有以下两种查看当前索引的方式。

mysql>show index from tablename;
mysql>show keys from tablename;

以p laye 「表为例， 查看p la yer 表的索引内容。

mysql>show index from player;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------+---------+--------------+
I Table I Non_unique I Key_name I Seq_in_index I Column_name I Colla ti on I Cardinality I
Sub_part I Packed I Null I lndex_type I Comment I lndex_comment I
+--------+------------+------------ +--------------+-------------+-----------+--- - - ---- - - --+- ---- - - ---+--------+------+------------+---------+
| player |       0 | PRIMARY   |  1 | id             |A           | 9489  |
NULL | NULL |BTREE |         |                       |                        
| player | 1 | index_puid |                 1 | puid       |A           |         9489 I
NULL | NULL |   | BTREE |         |                    |          
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

以上结果中各字段的含义如下：

• Table: 表的名称。
• Non_un iq ue: 如果索引不能包括重复词，则为0 ; 如果可以，则为1 。
• Key_name: 索引的名称。
• Seq_i n_i ndex: 索引中的列序号， 从1 开始。
• Column_name: 列名称。
• Colla ti on: 列以什么方式存储在索引中。在M yS Q L 中， 有值＇A' （ 升序）或NULL （ 无分类）。
• Card i nal ity： 索引中唯一值数目的估计值。通过运行ANALYZE TABLE 或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数， 所以即使对千小型表， 该值也没有必要是精确的。基数越大， 当进行联合时， M yS Q L 使用该索引的机会就越大。
• Sub_pa rt ：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL 。
• Packed: 指示关键字如何被压缩。如果没有被压缩，则为NULL 。
• Null: 如果列含有NULL 则含有YES 。如果没有， 则该列含有NO 。
• lndex_type: 用过的索引方法( BTREE , FULL TEXT, HASH, RTREE ) 。
• Comment: 备注。

1.6 删除索引

索引在创建之后，是会占用一定的磁盘空间的，因此表内如果有不再使用的索引，从数据库性能方面考虑， 最好是删除无用索引。索引的删除有如下两种方法。

DROP I NDEX 索引名ON 表名；
ALTER TABLE 表名DROP INDEX 索引名，

以p layer 表为例，删除p la yer 表的索引。

mysql>drop index index_puid on mapping;
Query OK, 0 rows affected (0.01 sec)
Reco 「ds : 0 Duplicates: 0 Warnings: 0
rnysql>alter table mapping drop index gameid;
Query OK, O rows affected (0.01 sec)
Records: O Duplicates: O Warnings: O

2 MySQL 事务

MySQ L 事务主要用千处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料， 又需要删除和该人员相关的信息，如信箱，
文章等等。这样，这些数据库操作语句就构成一个事务！

• 在MySQ L 中只有使用了lnnodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的SQ L 语句要么全部执行，要么全部不执行。
• 事务用来管理i nse rt ,u pdate , delete 语句。

一般来说， 事务是必须满足4 个条件( ACID ) : 原子性( Atom icity, 或称不可分割性）、一致性(Consistency ) 、隔离性CIsolation, 又称独立性） 、持久性CDurabil ity ) 。

• 原子性： 一个事务( t ransacti on ) 中的所有操作，要么全部完成， 要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误， 会被回滚（ Rollback ) 到事务开始前的状态， 就像这个事务从来没有执行过一样；
• 一致性： 在事务开始之前和事务结束以后， 数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则， 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；
• 隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力， 隔离性可以防止多个事务并发执行时由千交叉执行而导致数据的不一致。事务隔离分为不同级别， 包括读未提交( Read uncomm itted ) 、读提交( read comm i tted ) 、可重复读（ re peatableread ) 和串行化( Serial izable ) ;
• 持久性： 事务处理结束后， 对数据的修改就是永久的， 即便系统故障也不会丢失。

在MySQ L 命令行的默认设置下， 事务都是自动提交的， 即执行S Q L 语句后就会马上执行COMMIT 操作。因此要显式地开启一个事务必须使用命令BEGIN 或STARTTRANSACTION, 或者执行命令SET AUTOCOMMIT=O, 用来禁止使用当前会话的自动提交。

事务控制语句包含：

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

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

( 1 ) 用BEGIN , ROLLBACK, COMMIT 来实现

• BEG I N 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

( 2 ) 直接用SET 来改变MySQL 的自动提交模式

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

mysql>use kgc;
Database changed
mysql>CREATE TABLE kgc_transaction_test(id int(S)) en gi ne= i nnodb;//创建数据表
Query OK, O 「ows affected (0.04 sec)
mysql>select "from kgc_transaction_test;
Empty set (0.01 sec)
mys q l>begi n;//开始事务
Query OK, 0 rows affected (0.00 sec)
mysql>insert into kgc_transaction_test value(1);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into kgc_transaction_test value(2);
Query OK, 1 「ows affected (0.00 sec)
mysql> commit; II提交事务
Query OK, O rows affected (0.01 sec)
mysql>select * from kgc_transaction_test;

+------+
I id     |
+------+

| 1      |

| 2      |

+------+

2 rows in set (0.01 sec)

mys q l>begi n;/／开始事务
Query OK, 0 rows affected (0.00 sec)
mysql>insert into kgc_t ransaction_test values(3);
Query OK, 1 rows affected (0.00 sec)
mys q l>rollback;//回滚
Query OK, O 「ows affected (0.00 sec)
mysql> select* from kgc_transacti on_test;//因为回滚所以数据没有插入

+------+
I id     |
+------+

| 1      |

| 2      |

+------+

2 rows in set (0.01 sec)
mysql>

3 MySQL 存储引擎

在数据库中保存的是一张张有着于丝万缕关系的表， 所以表设计的好坏， 将直接影响着整个数据库。而在设计表的时候， 最关注的一个问题是使用什么存储引擎。

MySQ L 中的数据用各种不同的技术存储在文件（或者内存） 中。这些技术中的每一种都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的、不同的功能和能力。通过选择不同的技术， 能够获得额外的速度或者功能， 从而改善应用的整体性能。

这些不同的技术以及配套的相关功能在MyS Q L 中被称作存储引擎（也称作表类型） 。MySQ L 默认配置了许多不同的存储引擎， 可以预先设置或者在M ySQ L 服务器中启用。选择适用于服务器、数据库和表格的存储引擎，可以在存储信息、检索数据时，提供最大的灵活性。

在MySQ L 客户端中， 使用以下命令可以查看MySQ L 支持的引擎。

mysql>show engines;

1. MylSAM 存储引擎

MylSAM 存储引擎不支持事务， 也不支持外键，特点是访问速度快，对事务完整性没有要求， 以SELECT 、INSERT 为主的应用基本都可以使用这个引擎来创建表。

每个MylSAM 表在磁盘上存储成3 个文件， 其中文件名和表名都相同，但是扩展名分别为：

• frm（存储表定义）
• MYD(MYData, 存储数据）
• MYl(MYlndex, 存储索引）

数据文件和索引文件可以放置在不同的目录，平均分配10 , 获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY 和INDEXDIRECTORY 语句指定，文件路径需要使用绝对路径。

MylSAM 表还支持3 种不同的存储格式：

• 静态（ 固定长度）表
• 动态表
• 压缩表

其中静态表是默认的存储格式。静态表中的字段都是非可变字段， 这样每个记录都是固定长度的， 这种存储方式的优点是存储非常迅速， 容易缓存， 出现故障容易恢复； 缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格， 但是在访问的时候并不会得到这些空格， 这些空格在返回给应用之前已经去掉。同时需要注意： 在某些情况下可能需要返回字段后的空格， 而使用这种格式时后面的空格会被自动处理掉。

动态表包含可变字段， 记录不是固定长度的， 这样存储的优点是占用空间较少， 但是频繁的更新、删除记录会产生碎片， 需要定期执行OP T IMIZE TABLE 语句或myi samchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表由myi samchk 工具创建， 占据非常小的空间， 因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

2. lnnoDB 存储引擎

lnnoDB 是一个健壮的事务型存储引擎， 这种存储引擎已经被很多互联网公司使用， 为用户操作非常大的数据存储提供了一个强大的解决方案。M ySQ L 从5.5.5 版本开始， 默认的存储引擎为lnnoDB 。lnnoDB 存储引擎还引入了行级锁定和外键约束， 在以下场景中使用l nnoDB 存储引擎是最理想的选择：

• 更新密集的表： lnnoDB 存储引擎特别适合处理多重并发的更新请求。
• 事务： lnnoDB 存储引擎是支持事务的标准M yS Q L 存储引擎。
• 自动灾难恢复与其它存储引擎不同， lnnoDB 表能够自动从灾难中恢复。
• 外键约束： My SQ L 支持外键的存储引擎只有lnnoDB 。
• 支持自动增加列AUTO_INCREMENT 属性。

一般来说如果需要事务支待，并且有较高的并发读取频率， lnnoDB 是不错的选择。而MylSAM 和lnnoDB 两种存储引擎的区别主要表现在以下几个方面。

• lnnoDB 支持事务， M y lSAM 不支持，这一点是非常重要的。事务是一种高级的处理方式，如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原， 而MylSAM就不可以。
• MylSAM 适合查询、插入为主的应用， lnnoDB 适合频繁修改以及涉及到安全性较高的应用。
• lnnoDB 支持外键， My lSAM 不支持。
• 从My S Q L5.5.5 以后， lnnoDB 是默认引擎。
• MySQ L 从5.6 版本开始lnnoDB 引擎才支持FULLTEXT 类型的索引。
• lnnoDB 中不保存表的行数，如select count(*) from table 时， lnnoDB 需要扫描一遍整个表来计算有多少行， 但是My lSAM 只要简单的读出保存好的行数即可。需要注意的是，当count(*)语句包含where 条件时My lSAM 也需要扫描整个表。
• 对于自增长的字段， lnnoDB 中必须包含只有该字段的索引，但是在MylSAM 表中可以和其他字段一起建立组合索引。
• 清空整个表时， lnnoDB 是一行一行的删除，效率非常慢。My lSAM 则会重建表。
• lnnoDB 支持行锁（ 某些情况下还是锁整表，如u pda te table set a=1 where user like'%lee%' ;)。

3. 关千My lSAM 与lnnoDB 选择使用

MylSAM 和lnnoDB 是MySQL 数据库提供的两种存储引擎。两者的优劣可谓是各有于秋。lnnoDB会支待一些关系数据库的高级功能，如事务功能和行级锁， MylSAM 不支待。MylSAM 的性能更优， 占用的存储空间少。所以，选择何种存储引擎，视具体应用而定。

( 1 ) 如果应用程序一定要使用事务， 毫无疑问要选择lnnoDB 引擎。但要注意， lnnoDB的行级锁是有条件的。在where 条件没有使用主键时，照样会锁全表。比如DELETE FROMmytable 这样的删除语句。

( 2 ) 如果应用程序对查询性能要求较高， 就要使用My lSAM 了。My lSAM 索引和数据是分开的， 而且其索引是压缩的， 可以更好地利用内存。所以它的查询性能明显优千lnnoDB 。压缩后的索引也能节约一些磁盘空间。

4. 修改默认的存储引擎

修改默认的存储引擎有四种方法， 分别如下。

( 1 ) 通过alter table 修改。

MySQL>alter table user_info engine=MylSAM;

( 2 ) 通过修改my. cnf, 指定默认存储引擎并重启服务。

[root@Mysql /]#vim my.cnf
default-storage-engine= InnoDB

( 3 ) 通过create table 创建表时指定存储引擎。

 MySQL>create table engineTest(id int) engine=MylSAM;

( 4 ) 通过M ys qI_ convert_ table fo rmat 转化存储引擎。

[root@Mysql /]#yum -y install perl-DBI perl-DBD-MySQL
[root@Mysql/]#/usr/local/mysql/bin/mysql_convert_table_format
--user=root --password='123456'--sock=/tmp/mysql.sock auth