目录
一：MYSQL索引介绍
索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候,索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到
行数据对应的物理地址然后访问相应的数据。索引的作用类似于图书的目录,可以根据目录中的页码快速找到所需的内容。
1.索引概念
（1. 当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样 保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。
（2. 记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程,平均需要访问 N/2的数据块,N是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。
（3. 但是对于一个有序字段,可以运用二分查找(Binary Search),这样只需要访问 1og2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。
（4. 索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构,包含字段数值以及指向相关记录的指针,然后对这个索引结构进行排序,允许在该数据上进行二分法排序。
（5. 使用索引的副作用是需要额外的磁盘空间。对于 MyISAM 引擎而言,这些索引是被统一保存一张表中的。如果很多字段都建立了索引,那么会占用大量的磁盘空间,这个文件将很快到达底层件系统所能够支持的大小限制。
2.索引作用
在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
此查询结果应该为 1000行,每行包含 3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE 子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿)显然查询将会非常慢。
如果对每个表进行索引,就能极大地加速查询进程,利用索引的查询处理如下
（1.从表 t1 中选择第一行,查看此行所包含的数据。
（2.使用表 t2 上的索引,直接定位 t2中与t1的值匹配的行。同理,利用表 t3上的索引,直接定位t3 中与t1 的值匹配的行。
(3.扫描表 t1 的下一行并重复前面的过程,直到遍历t1 中所有的行。
在此情形下,仍然对表 t1执行了一个完全扫描,但能够在表 t2和 t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。
利用索引,MySQL 加速了 WHERE 子句满足条件行的搜索,而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。
3.索引的分类
在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQL 索引的建立对于MySQL的高效运行是非常重要的。下面介绍几种常见的 MySQL 索引类
(1.从物理存储的角度来划分,索引分为聚簇索引和非聚簇索引两种,聚簇索是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,非聚簇索引对于单行的检索更快。
（2.从逻辑的角度来划分,索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。这些索引分类的具体解释如下所示。
3.1普通索引
普通索引是最基本的索引,它没有任何限制,也是大多数情况下用到的索引。它有以下几种创建式。
直接创建索引:
column 是指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和WHERE 子句里经常出现的列作为索引列。
其中 length 是可选项。如果忽略 length 的值,则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引,就是使用列的一部分来创建索引,这样有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下,只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM和 InnoDB 表的最大上限为1000个字节),如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外,BLOB或TEXT 类型的列也必须使用前缀索引。column和 length 的含义在下面创建索引的操作语句中意义相同。
修改表结构添加索引：
创建表结构 同时创建索引
查看一下t1 
3.2唯一索引
唯一索引与普通索引类似,不同的就是:唯一索引的索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一。唯一索引创建方法和普通索引类似。
创建唯一索引:
修改表街斗添加唯一索引
创建表同时创建唯一索引
3.3组合索引（最左前缀）
平时用的 SQL 查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL 的效率,就要考虑建立组合索引。在组合索引的创建中,有两种场景,即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。
在一个user 用户表中,有 name,age,sex 三个字段,分别分三次建立了INDEX 普通索引。那么在 select * from user where name =''AND age=AND sex='’;数据查询语句中就会分别检索三条索引,虽然扫描效率有所提升,但却还未达到最优。这个时候就需要使用到组合索引(即多列索引),如下所示。
如果采用“select * from user where age=''AND name='‘AND sex =’’;”查询方式,这条组合索引将无效化,所以一般在建立索引时,要先想好相应的查询业务,尽量避免虽然有索引,但是使用不上的问题。
3.4全文索引
MySQL 从 3.23.23版开始支持全文索引和全文检索。在MySQL5.6 版本以前FULLTEXT索引仅可用于MyISAM表,在5.6之后innodb 引擎也支持 FULLTEXT 索引;他们可以从 CHAR、VARCHAR 或 TEXT 列中作为CREATE TABLE 语句的一部分被创建,或是随后使用 ALTER TABLECREATEINDEX 被添加。
对于较大的数据集,将资料输入一个没有 FULLTEXT 索引的表中,然后创建索引,其速度比把资料输入现有 FULLTEXT 索引的速度更快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。
创建表全文索引
4.2 创建索引原则依据
数据库建立索引原则
确定针对该表的操作是大量的查询操作还是大量的增删改操作; 尝试建立索引来帮助特定的查询。检查自己的 sql 语句,为那些频繁在where 子句中出现的字段建立索引; 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时复合索引也占磁盘空间;
对于小型的表,建立索引可能会影响性能; 应该避免对具有较少值的字段进行索引; 避免选择大型数据类型的列作为索引。
索引建立原则
索引查询是数据库中重要的记录查询方法,要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑,下面给出实际生产环境中的一些通用的原则:
在经常用作过滤器的字段上建立索引; 在 SQL 语句中经常进行 GROUP BY、ORDER BY 的字段上建立索引; 在不同值较少的字段上不必要建立索引,如性别字段; 对于经常存取的列避免建立索引; 用于联接的列(主健/外健)上建立索引；
在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定; 缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描 I/0 的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中,也取决于 DBA 所设计的数据库结构。
4.3查看索引
MySQL 数据表索引已经创建好了,那么如何才能查看刚刚创建的索引?或者怎么去查看表内已经存在的索引?有以下两种查看当前索引的方式。
字段解析:
1.Table:表的名称。
2.Non_unique:如果索引不能包括重复词,则为0;如果可以,则为1。
3.Key_name:索引的名称。
4.Seq_in_index:索引中的列序号,从1开始。
5.Column_name:列名称。
6.Collation:列以什么方式存储在索引中。在 MySQL中,有值‘A’(升序)或 NULL(无分类)。
7.Cardinality:索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk -a 可以新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数大,当进行联合时,MySQL 使用该索引的机会就越大。
8.Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
9.Packed:指示关键字如何被压缩。如果没有被压缩,则为 NULL。10.Null:如果列含有 NULL,则含有 YES。如果没有,则该列含有 NO。
11.Index_type:用过的索引方法(BTREE,FULLTEXT, HASH, RTREE)。12.Comment:备注。
4.4删除索引
索引在创建之后,是会占用一定的磁盘空间的,因此表内如果有不再使用的索引,从数据库性能方面考虑,最好是删除无用索引。索引的删除有如下两种方法。
二 MYSQL事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,要删除一个人员,即需要删除人员的基本资料,又需要删除和该人员相关的信息,如信箱,文章等等。这样,这些数据库操作语句就构成一个事务!
在MySQL 中只有使用了Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性,保证成批的SQL 语句要么全部执行,要么全部不执行。 事务用来管理 insert,update,delete 语句。
一般来说,事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)
原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样; 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作; 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务离分为不同级别,包括读未提交(Read uncommitted)、读提交(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 COMMITTED 、REPEATABLE READ 和SERIALIZABLE.
MYSQL 事务处理主要有两种方法:
(1)用 BEGIN,ROLLBACK,COMMIT 来实现
> BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认(2)直接用 SET 来改变 MySQL 的自动提交模式
SET AUTOCOMMIT=O 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
