MySQL索引和事务

一、索引

一、概述

1、索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址
2、索引是表中一列或者若干列值排序的方法

二、作用

1、索引加快对表中记录的查找或排序
2、通过创建唯一(键)性索引，可以保证数据表中每一行数据的唯一性
3、可以加快表与表之间的连接

三、副作用

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

四、创建索引的原则依据

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

五、索引创建

mysql> insert into member values (1,'zs','123','111111','nanjing','this is boy');
mysql> insert into member values (4,'ls','1234','444444','nanjing','this is girl');
mysql> insert into member values (2,'ww','12345','222222','benjing','this is boy');
mysql> insert into member values (5,'zl','123456','555555','nanjing','this is girl');
mysql> insert into member values (3,'qq','1234567','333333','shanghai','this is boy');

mysql> select * from member;
+------+------+---------+--------+----------+--------------+
| id   | name | cardid  | phone  | address  | remark       |
+------+------+---------+--------+----------+--------------+
|    1 | zs   | 123     | 111111 | nanjing  | this is boy  |
|    4 | ls   | 1234    | 444444 | nanjing  | this is girl |
|    2 | ww   | 12345   | 222222 | benjing  | this is boy  |
|    5 | zl   | 123456  | 555555 | nanjing  | this is girl |
|    3 | qq   | 1234567 | 333333 | shanghai | this is boy  |
+------+------+---------+--------+----------+--------------+
5 rows in set (0.00 sec)

普通索引

mysql> create index phone_index on member (phone);#可以在(phone(length)),加上length有利于减小索引文件的大小
mysql> show create table member;

mysql> alter table member add index id_index (id);#修改表创建索引

mysql> create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));#创建表时创建索引

唯一索引

1、唯一索引允许有空值（注意和主键不同）。
2、如果是用组合索引创建，则列值的组合必须唯一。
3、添加唯一键将自动创建唯一索引。

mysql> create unique index name_index on member (name);

mysql> alter table 表名 add unique 字段名_index (字段名); #修改表时创建唯一索引
mysql> create table amd2 (字段名1 数据类型,字段名2 数据类型,unique 字段名_index (字段名)); 创建表时指定

主键索引

1、是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。
2、一个表只能有一个主键，不允许有空值。
3、添加主键将自动创建主键索引。

mysql> create table yy (id int(10) primary key,name varchar(50)); 创建表示指定索引

mysql> alter table yy add primary key(id); #修改方式指定主键索引

组合索引

1、可以是单列上创建的索引，也可以是在多列上创建的索引。
2、从左向右执行。

mysql> create table hh (id int(10),name varchar(50),phone varchar(12),index index_zh (id,name)); #创建表时指定
Query OK, 0 rows affected (0.03 sec)

触发组合索引：
1、查询是指定字段，按创建是从左向右的顺序，指定查询才会触发。

全文索引

1、适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
2、全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
3、每个表只允许有一个全文索引。

mysql> create fulltext index hh_index on hh (phone); #直接创建索引
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table hsh1 add fulltext hsh1_index (phone); #修改方式创建
Query OK, 0 rows affected, 1 warning (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> create table gh (id int(10),name varchar(50),phone varmysql> create table gh (id int(10),name varchar(50),phone varchar(12),fulltext gh_index (phone)); #创建表时指定全索引
Query OK, 0 rows affected (0.08 sec)

六、查看索引

show index from 表名;
show index from 表名\G； #竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;	#竖向显示表索引信息

mysql> mysql> show index from hsh;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----</