04-Mysql 索引，事务

MySQL 索引介绍

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引作用

数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

索引:排了序的列表，对数据进行快速的查询

主键：（主键索引）

输入输出（IO）

1.3：索引的分类

1.普通索引（）

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引

CREATE INDEX index_name ON table_name(column(length));
例：
mysql> create index aaa on users(user_name(20));

（2）修改表结构的方式添加索引语法

mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));

例：
mysql> drop index aaa on users;
mysql> alter table users add index aaa (user_pass(45));

2.唯一索引（可以多个）：

唯一索引与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。

（1）创建唯一索引语法
mysql>CREATE UNIQUE INDEX index_name ON table_name(column(length)); 
（2）修改表结构的时候添加唯一索引语法
mysql>ALTER TABLE table_name ADD UNIQUE index_name (column(length)); 

（3）创建表的时候同时创建唯一索引
CREATE TABLE table02 ( 
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_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
UNIQUE index_table02_title (title(11)) 
);

3.主键索引（只能一个）

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

（例如）创建表t2，并将表中的id设置为主键
CREATE TABLE t2 
( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) NOT NULL , 
PRIMARY KEY (id) 
);

mysql>show creata table t2\G

4.主合索引（最左）

create table t3
( 
name varchar(9), 
age int(3), 
sex tinyint(1), 
index eee(name, age, sex) 
);


注意：
select 语句的 where 条件是依次从左往右执行的。 
mysql>select * from user where name = '' AND age = '' AND sex = ''; 
若使用的是组合索引 index user(name, age, sex)。在查询中，name、age、sex 的顺序必须如组合索引中一致排序，否则索引将不会生效。

5.全文索引

1）创建表的全文索引语法： 
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_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
FULLTEXT (content) 
)ENGINE=MyISAM;


（2）修改表结构添加全文索引语法： 
 mysql>alter table article add fulltext index_content(content);
（3）直接创建索引语法：
mysql>create fulltext index index_content on article(content);

oracle：B树索引

删除：drop index ggg on t4

1.5：查看索引

mysql>show index from t1\G;

mysql>show keys from t2\G;

mysql>show create table t3 \G

1.6：删除索引

DROP INDEX 索引名 ON 表名; 
ALTER TABLE 表名 DROP INDEX 索引名;

注意事项：

（1）主键和外键要有索引

（2）300行以下，没有必要创建索引

（3）经常用于跨表查询的，需要有索引

（4）唯一性比较差的列或表不适合建立索引

（5）更新太频繁的列不适合建立索引

（6）经常用where指定的条件进行查询的，适合创建索引

（7）建立索引的列尽量是小字段

MySQL 事务

事务：

一系列sql语句的合集，这些语句要么全部执行，要么不执行

原子性

• 事务是一个完整的操作，事务的各元素是不可分的
• 事务中的所有元素必须作为一个整体提交或回滚
• 如果事务中的任何元素失败，则整个事务将失败

一致性

• 当事务完成时，数据必须处于一致状态
• 在事务开始前，数据库中存储的数据处于一致状态
• 在正在进行的事务中，数据可能处于不一致的状态
• 当事务成功完成时，数据必须再次回到已知的一致状态

隔离性

• 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
• 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性（提交）

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

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

内存（数据缓冲区）（可以回滚）

提交意味着写入磁盘

硬盘（永久存储）（不可以回滚的）

实例代码

mysql> SET AUTOCOMMIT=0;

mysql>use auth; 
mysql>CREATE TABLE kgc_transaction_test( id int(5)) engine=innodb;
mysql>select * from kgc_transaction_test; 

mysql>begin;     //开始事务 
mysql>insert into kgc_transaction_test value(1); 
mysql> insert into kgc_transaction_test value(2); 
mysql> commit;      //提交事务 
mysql>select * from kgc_transaction_test;

mysql>begin;     //开始事务 
mysql>insert into kgc_transaction_test values(3); 
mysql>rollback;     //回滚 
mysql> select * from kgc_transaction_test;     //因为回滚所以数据没有插入