300字范文 > MySQL索引创建删除及事务控制

MySQL索引创建删除及事务控制

时间：2021-02-27 17:38:11

相关推荐

MySQL索引创建删除及事务控制

文章目录

一、索引概述二、索引的优点与缺点三、创建索引的原则依据四、索引的分类与创建4.1 索引的分类4.2 索引的创建4.2.1 普通索引4.2.2 唯一索引4.2.3 全文索引4.2.4 组合索引五、删除索引六、MySQL事务6.1 MySQL事务概述6.2 事务的AICD特点6.3数据不一致产生的结果6.4MySQL事务隔离级别6.5查询和设置事务的隔离级别6.6 事务控制语句6.7 测试事务控制

一、索引概述

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

二、索引的优点与缺点

优点：

使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。主键索引、唯一键索引可以加快表与表之间的连接。在使用分组和排序时，可大大减少分组和排序的时间。

缺点：

索引需要占用额外的磁盘空间，随着数据量的增加而增加在插入和修改数据时要花费更多的时间，因为索引也要随之变动

三、创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。唯一性太差的字段不适合建立索引。更新太频繁地字段不适合创建索引。

四、索引的分类与创建

4.1 索引的分类

主键索引：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引主键索引是一个特殊的唯一索引，不需要单独创建，在指定主键的同时也就创建了主键索引唯一索引：针对唯一性的字段，仅允许出现一次空值组合索引：多字段组合形式的索引组合索引创建的字段顺序是其触发索引的查询顺序全文索引：针对varchar char text 的字段类型普通索引：针对所有字段，没有特殊的需求/规则

4.2 索引的创建

4.2.1 普通索引

语法结构：

创建表时直接定义：create table 表名 (字段1 数据类型…字段n 数据类型,index 索引名(字段名));

修改表结构：alter table 表名 add index 索引名(字段名);

直接创建：create index 索引名 on 表名 (字段名);

mysql> create table userinfo(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));Query OK, 0 rows affected (0.02 sec)mysql> show create table userinfo;+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| userinfo | CREATE TABLE "userinfo" ("id" int(4) NOT NULL,"name" varchar(10) NOT NULL,"cardid" varchar(18) NOT NULL,KEY "id_index" ("id") //普通索引) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql>

4.2.2 唯一索引

语法结构：

创建表时直接定义：create table 表名 (字段1 数据类型…字段n 数据类型,unique 索引名(字段名));

修改表结构：alter table 表名 add unique 索引名(字段名);

直接创建：create unique index 索引名 on 表名 (字段名);

mysql> alter table userinfo add unique cardid_index(cardid);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table userinfo;+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| userinfo | CREATE TABLE "userinfo" ("id" int(4) NOT NULL,"name" varchar(10) NOT NULL,"cardid" varchar(18) NOT NULL,UNIQUE KEY "cardid_index" ("cardid"), //唯一索引KEY "id_index" ("id")) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

4.2.3 全文索引

语法结构：

创建表时直接定义：create table 表名 (字段1 数据类型…字段n 数据类型,fulltext 索引名(字段名));

修改表结构：alter table 表名 add fulltext 索引名(字段名);

直接创建：create fulltext index 索引名 on 表名 (字段名);

mysql> create fulltext index name_index on userinfo(name);Query OK, 0 rows affected, 1 warning (0.04 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show create table userinfo;+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| userinfo | CREATE TABLE "userinfo" ("id" int(4) NOT NULL,"name" varchar(10) NOT NULL,"cardid" varchar(18) NOT NULL,UNIQUE KEY "cardid_index" ("cardid"),KEY "id_index" ("id"),FULLTEXT KEY "name_index" ("name") //全文索引) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

4.2.4 组合索引

语法结构：

创建表时直接定义：create table 表名 (字段1 数据类型…字段n 数据类型,index 索引名(字段名1，字段名2));

修改表结构：alter table 表名 add index 索引名(字段名1,字段名2);

直接创建：create index 索引名 on 表名 (字段名1，字段名2);

mysql> create index id_name_index on userinfo(id,name); //直接创建组合索引Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table userinfo;+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table|+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| userinfo | CREATE TABLE "userinfo" ("id" int(4) NOT NULL,"name" varchar(10) NOT NULL,"cardid" varchar(18) NOT NULL,KEY "id_name_index" ("id","name")) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> drop index id_name_index on userinfo; //删除索引Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table userinfo add index id_name_index(id,name); //修改表结构的方式创建索引Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table userinfo;+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table|+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| userinfo | CREATE TABLE "userinfo" ("id" int(4) NOT NULL,"name" varchar(10) NOT NULL,"cardid" varchar(18) NOT NULL,KEY "id_name_index" ("id","name")) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

五、删除索引

语法结构

直接删除：drop index 索引名 on 表名;

修改表结构的方式删除：alter table 表名 drop index 索引名;

mysql> drop index id_name_index on userinfo;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>

mysql> alter table userinfo drop index id_name_index;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>

六、MySQL事务

6.1 MySQL事务概述

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。事务是通过事务的整体性以保证数据的一致性。

6.2 事务的AICD特点

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这是可靠数据库所应具备的几个特性。

原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。

在事务开始前，数据库中存储的数据处于一致状态。

在正在进行的事务中，数据可能处于不一致的状态。

当事务成功完成时，数据必须再次回到已知的一致状态。

举例说明：银行账户转账，转帐前转账后的数据状态要一致，转帐中数据在交换，可能处于不一致状态。隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

指不管系统是否发生故障，事务处理的结果都是永久的。

一旦事务被提交，事务的效果会被永久地保留在数据库中。

6.3数据不一致产生的结果

1、脏读(读取未提交数据)：脏读是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X,在未提交前,事务A读取了X,而事务B却回滚了,这样事务A就形成了脏读。也就是说,当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。2、不可重复读(前后多次读取，数据内容不一致)：一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的,事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但列内容发生了变化。3、幻读(前后多次读取，数据总量不一致)：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。4、丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

6.4MySQL事务隔离级别

1、read uncommitted：读取尚未提交的数据（不解决脏读）2、read committed：读取已经提交的数据（解决脏读）3、repeatable read：重读读取（解决脏读和不可重复度，MySQL默认级别）4、serializable：串行化（可以解决脏读、不可重复读和虚读——相当于锁表）