目录
ySQL索引介绍
索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候,索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于图书的目录,可以根据目录中的页码快速找到所需的内容。
索引概述
当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。
记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程星,平均需要访问N/2的数据块,N是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。
索引作用
在索引列上,除了上面提到的有序查找之外,数据库利用名各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
例如,有3个未索引的表tl、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,均为1~1000的数值,查找对应值相等行的查询
如下所示。
mysql>SELECT cl,c2,c3 FROM t1, t2, t3 WHERE c1=c2 ANDc1=c3;
此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然了查询将会非常慢
如果对每个表进行索引,就能极大地加速查询进程,利用索引的查询处理如下。
>从表t1中选择第一行,查看此行所包含的数据。
>使用表t2上的索引,直接定位t2中与tl的值匹配的行。同理,利用表t3上的索引,直接定位 t3中与 t1 的值匹配的行。
>扫描表tl的下一行并重复前面的过程,直到遍历 t1中所有的行。
在此情形下,仍然对表tl执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍
利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。
索引的分类
普通索引
普通索引是最基本的索引,它没有任何限制,也是大多数情况下用到的索引。它有以下几种创建方式。
直接创建索引:
mysql>CREATE INDEX index_name ON table_name(column(length))
column是指定要创建索引的列名。通常可以考虑将查询语句中在JOIN子句和WHERE子句里经常出现的列作为索引列。
其中 length是可选项。如果忽略length的值,则使用整个列的值作为索引。
修改表结构的方式添加索引:
mysql>ALTER TABLE table_name ADD INDEX index_name (columm(Tength));
创建表结构时,同时创建索引:
CREATE TABLE 'table'(
'id` int(11) NOT NULL AUTO_INCREMENT ,
'title' char(255) CHARACTER SET utf8 COLLATE utf8_geneeral_ci NOT NULL
'content'text CHARACTER SET utf8 COLLATE utf8_general_ci NULIi ,
'time' int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)唯一索引
唯一索引与普通索引类似,不同的就是:唯一索引的索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一。唯索引创建方法和普通索引类似。
创建唯一索引:
mysql>CREATE UNIQUE INDEX index_name ON table_name(colummn(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_generalI_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))
)主键索引
主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
CREATE TABLE 'table name'(
'id` int(11) NOT NULL AUTO_INCREMENT,
'title char(255) NOT NULL,
PRIMARY KEY (`id)
):组合索引（最左前缀）
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。在组合索引的创建中,不有两种场景,即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。
在一个user用户表中,有name,age,sex三个字段,分分别分三次建立了INDEX 普通索引。那么在 select*from user where name AND age = AND 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、的顺序必须如组合索引中一致排序,否则索引将不会生效,例如:
mysql>select * from user where name = '' AND age ='' AND sex='';
如果采用"select*fromuserwhereage=" AND name='';AND sex='';"查询方式,这条组合索引将无效化,所以一般在建立索引时,要先想好相应的查询业务,尽量避免虽然有索引,但是使用不上的问题。
全文索引（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_ciNULL ,
time int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT (content)
）；修改表结构添加全文索引：
mysql>ALTER TABLE article ADD FULLTEXT index content (content);直接创建索引：
mysql>CREATE FULLTEXT INDEX index_content ON article(conntent);创建索引的原则依据
数据库建立索引的原则：
>确定针对该表的操作是大量的查询操作还是大量的增删改操作;
>尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引;
>尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时复合索引也占磁盘空间;
>对于小型的表,建立索引可能会影响性能;
>应该避免对具有较少值的字段进行索引;
>避免选择大型数据类型的列作为索引。
索引建立的原则：
索引查询是数据库中重要的记录查询方法,要不要建立索引以以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑,下面给出实际生产环境中的一些通用的原则:
>在经常用作过滤器的字段上建立索引;
>在SQL语句中经常进行GROUP BY、ORDERBY的字段上建立索引
>在不同值较少的字段上不必要建立索引,如性别字段;
>对于经常存取的列避免建立索引;
>用于联接的列(主健/外健)上建立索引;
>在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定;
>缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描1/0的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中,也取决于DBA所设计的数据库结构
查看索引
MySQL数据表索引已经创建好了,那么如何才能查看刚刚创建的索引?或者怎么去查看表内已经存在的索引?有以下两种查看当前索引的方式
mysql>show index from tablename;
mysql>show keys from tablename;
删除索引
索引在创建之后,是会占用一定的磁盘空间的,因此表内如果是有不再使用的索引,从数据库性能方面考虑,最好是删除无用索引。索引的删除有如下两种方法。
DROP INDEX索引名 ON 表名;
ALTER TABLE表名DROPINDEX索引名;
添加索引
mysql> create index name_index on employee(name);
Query OK, O rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table renyuan add index age_index(age);
Query OK, O rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0删除索引
mysql> drop index name_index on employee;
Query OK, O rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table renyuan drop index age_index;
Query OK, O rows affected (0.01 sec)
Records: 0 Duplicates: O Warnings: 0MySQL事务
MySQL事务主要用于处理操作量大，复杂度高的数据。
>在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
>事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
>事务用来管理insert，updata，delete语句
一般来说,事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致(Consistency)、隔离性(Isolatiion,又称独立性)、持久性(Durability)。
>原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样
>一致性:在事务开始之前和事务结束以后,数据库的完整性没有被玻坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作;
>隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed),可重复读(repeatableread)和串行化(Serializable);
>持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行commit操作。
事务控制语句包含：
>BEFTN或START TRANSACTION:显式地开启一个事务；
>COMMIT:也可以使用COMMIT WORK,不过二者是等价的。C0MMIT 会提交事务,并使已对数据库进行的所有修改变为永久性的;
>ROLLBACK:又可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
>SAVEPOINT identifier:SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
>RELEASE SAVEPOINT identifier:删除一个事务的保存,点,当没有指定的保存点时，执行语句会抛出一个异常>ROOLBACK 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=0禁止自动提交
SET AUTOCOMMIT=1开启自动提交