MySQL 索引和事务-海口c网

一：MySQL 索引介绍

1：索引概述

2：索引作用

3：索引的分类

（1）普通索引

（2）唯一索引

（3）主键索引

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

（5）全文索引（FULLTEXT）

4：创建索引的原则依据

5：查看索引

6：删除索引

二：MySQL 事务

一：MySQL 索引介绍

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

1：索引概述

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

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

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

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

使用索引的副作用是需要额外的磁盘空间。对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

2：索引作用

作用类别	具体作用	说明
加速数据检索	快速定位数据	通过索引结构（如B+树）快速找到目标数据，避免逐行扫描
加速数据检索	避免全表扫描	如果没有索引，查询必须扫描整张表，效率极低
提高查询性能	优化 `WHERE` 子句	索引可以加速条件筛选，如 `WHERE id = 100`
	加速 `JOIN` 操作	在多表关联查询时，索引能提高连接字段的匹配速度
	优化 `ORDER BY` 排序	如果排序字段有索引，MySQL 可以直接按索引顺序读取，避免额外排序
	优化 `GROUP BY` 分组	分组操作可以利用索引提高效率
保证数据完整性	唯一性约束（唯一索引）	确保列值不重复，如 `UNIQUE INDEX`
保证数据完整性	主键约束（主键索引）	确保每行数据有唯一标识，且不允许 `NULL`
优化特定操作	覆盖索引（Covering Index）	查询的列全部包含在索引中时，MySQL 可以直接从索引返回数据，无需回表查询
优化特定操作	索引条件下推（Index Condition Pushdown, ICP）	MySQL 5.6+ 支持，将 `WHERE` 条件下推到存储引擎层过滤，减少回表次数
加速表连接	提高多表关联查询效率	外键或连接字段上的索引可以大幅提升 `JOIN` 性能
注意事项	索引会占用存储空间	索引是额外的数据结构，会占用磁盘空间
	影响写入性能（INSERT/UPDATE/DELETE）	数据修改时，索引需要同步更新，可能降低写入速度
	不合理的索引可能无法被优化器使用	索引设计不当（如选择性低的列）可能导致优化器忽略索引，仍然全表扫描

3：索引的分类

从物理存储的角度来划分，索引分为聚簇索引和非聚索引两种，聚索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。

从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。这些索引分类的具体解释如下所示。

（1）普通索引

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

直接创建索引:

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

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

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

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

mysql> ALTER 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>ALTER 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` text 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）组合索引（最左前缀）

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。

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

create table user(

name varchar(9),

age int(3),
sex tinyint(1),
index user(name, age, sex)

);

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

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

若使用的是组合索引 index 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

`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`),

FULLTEXT (content)

);

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

mysql>ALTER TABLE article ADD FULLTEXT index_content (content);

直接创建索引:

mysqI>CREATE FULLTEXT INDEX index_content ON article(content);

4：创建索引的原则依据

数据库建立索引的原则:

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

索引建立的原则:

索引查询是数据库中重要的记录查询方法，要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际生产环境中的一些通用的原则:

在经常用作过滤器的字段上建立索引;
在 SQL语句中经常进行 GROUP BY、ORDER BY 的字段上建立索引;
在不同值较少的字段上不必要建立索引，如性别字段;
对于经常存取的列避免建立索引;
用于联接的列(主健/外健)上建立索引;
在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定;
缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如:含有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描 I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于 DBA 所设计的数据库结构。

5：查看索引

mysql>show index from tablename;

mysql>show keys from tablename;

以 renyuan 表为例，查看renyuan 表的索引内容

字段解析:

Table:表的名称。
Non unique:如果索引不能包括重复词，则为0;如果可以，则为1。
Key name:索引的名称。Seq_in_index:索引中的列序号，从 1开始Column name:列名称。
Collation:列以什么方式存储在索引中。在 MySQL 中，有值'A’(升序)或 NULL(无分类)。
Cardinality:索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk-a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。
如果整列被编入索引，则为 NULL。
Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。
Nu11:如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index type:用过的索引方法(BTREE,FULLTEXT,HASH,RTREE)。
Comment:备注。

6：删除索引

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

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

以 renyuan 表为例，删除renyuan 表的索引。

添加索引

删除索引

二：MySQL 事务

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

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。

一般来说，事务是必须满足4个条件(ACID):原子性(Atomicity，或称不可分割性)、一致性(Consistency)、隔离性(Isolation，又称独立性)持久性(Durability)

特性	描述	关键点
原子性 (Atomicity)	事务中的所有操作要么全部成功，要么全部失败回滚	- 不可分割的工作单位 - 失败时完全回滚 - 没有部分完成的状态
一致性 (Consistency)	事务执行前后，数据库必须保持一致性状态	- 符合所有数据规则和约束 - 保持数据完整性 - 触发所有定义的约束（如外键、唯一性等）
隔离性 (Isolation)	多个并发事务执行时，彼此隔离，防止数据混乱	- 隔离级别： - 读未提交（脏读） - 读提交（避免脏读） - 可重复读（MySQL默认） - 串行化（完全隔离）
持久性 (Durability)	事务一旦提交，对数据的修改是永久性的，即使系统故障也不会丢失	- 通过事务日志（如redo log）实现 - 系统崩溃后可通过日志恢复

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

语句分类	命令语法	作用描述	注意事项
开启事务	`BEGIN;` 或 `START TRANSACTION;`	显式开始一个新事务	在InnoDB引擎中，默认自动提交(auto-commit)需关闭才有效
提交事务	`COMMIT;` 或 `COMMIT WORK;`	永久保存事务中的所有修改	提交后无法回滚
回滚事务	`ROLLBACK;` 或 `ROLLBACK WORK;`	撤销事务中的所有未提交修改	只能回滚未提交的事务
设置保存点	`SAVEPOINT savepoint_name;`	在事务内创建标记点，允许部分回滚	一个事务可创建多个保存点
回滚到保存点	`ROLLBACK TO savepoint_name;`	回滚到指定保存点，保留该点之前的操作	保存点之后的操作会被撤销
删除保存点	`RELEASE SAVEPOINT savepoint_name;`	删除已创建的保存点	删除不存在的保存点会报错
设置隔离级别	`SET TRANSACTION ISOLATION LEVEL level;`	设置事务隔离级别（READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE）	需在事务开始前设置，部分级别可能影响性能