MySQL索引介绍

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

索引概述

当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点(或数据块)的指针，不需要连续存储。
记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索(Linear Search)的过程,平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说，不包含唯一的访问入口)，那么需要在N个数据块上搜索整个表格空间。
但是对于一个有序字段，可以运用二分查找(BinarySearch)，这样只需要访问 log2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。
使用索引的副作用是需要额外的磁盘空间。对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

索引作用

在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表 t1、t2、t3，分别只包含列c1、c2、c3，每个表分别含有 1000 行数据组成，均为 1~1000 的数值，查找对应值相等行的查询如下所示。

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

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

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

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

在此情形下，仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。
利用索引，MySQL 加速了 WHERE 子句满足条件行的搜索，而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。

索引的分类

在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令 MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQL 索引的建立对于 MySQL, 的高效运行是非常重要的。下面介绍几种常见的 MySQL 索引类
型
从物理存储的角度来划分，索引分为聚簇索引和非聚索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了:聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。
从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

普通索引

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

直接创建索引：

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

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

修改表结构时添加索引：

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

唯一索引

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

创建唯一索引:

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

例如
在id上创建唯一索引

在身份证号上创建唯一索引（如果列名是中文，需要用反引号 ` 包裹）

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

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

主键索引

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

组合索引(最左前缀)

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。
在一个 user 用户表中，有 name，age，sex 三个字段，分别分三次建立了INDEX 普通索引。那么在 select *from user where name =’’ AND age =’AND sex=’’;数据查询语句中就会分别检索三条索引，虽然扫描效率有所提升但却还未达到最优。这个时候就需要使用到组合索引(即多列索引)，如下所示

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

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

若使用的是组合索引 indexuser(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’‘;’'查询方式，这条组合索引将无效化，所以一般在建立索引时，要先想好相应的查询业务，尽量避免虽然有索引，但是使用不上的问题。

全文索引(FULLTEXT)

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL5.6 版本以前 FULLTEXT索引仅可用于 MyISAM 表，在 5.6之后 innodb 引
擎也支持FULLTEXT索引;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE或CREATEINDEX 被添加。
对于较大的数据集，将资料输入一个没有FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT 索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

创建表的全文索引；

修改表的全文索引；

直接创建索引；

查看索引

MySQL数据表有两种查看当前索引的方式


字段解析:

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。
Null:如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index type:用过的索引方法(BTREE,FULLTEXT, HASH,RTREE)。
Comment:备注。

删除索引

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

DROP INDEX索引名ON 表名;

ALTER TABLE表名DROP INDEX 索引名;

添加索引


删除索引

MySQL事务

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

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

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

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

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

事务控制语句包含:

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 COMMITTEDREPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法:
(1)用 BEGIN,ROLLBACK,COMMIT 来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

(2)直接用 SET 来改变 MySQL 的自动提交模式
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交