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

一、索引概述

索引是对数据库表中一列或多列的值进行排序的一种结构。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 索引名(字段名));