MySQL索引及事物

目录

一、数据库索引

1.索引概念

2.索引作用

3.创建索引原则

二、索引的分类及创建

1.普通索引

（1）直接创建索引

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

（3）创建表结构时，同时创建索引

2.唯一索引

（1）创建唯一索引语法

（2）修改表结构添加唯一索引 

（3）创建表同时创建索引

3.主键索引

4.组合索引（最左原则）

5.全文索引

三、事物

1.事务的概述    

 2.事务ACID特性

（1）原子性（Atomicity）

（2）一致性（Consistency）

（3）隔离性（Isolation）

（4）持久性（Durability）

---

一、数据库索引

1.索引概念

• 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）
• 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
• 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引是表中一列或者若干列值排序的方法
• 建立索引的目的是加快对表中记录的查找或排序

2.索引作用

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

优点

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
• 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
• 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
• 可以加快表与表之间的连接。
• 在使用分组和排序时，可大大减少分组和排序的时间。

3.创建索引原则

 索引查询是数据库中重要的记录查询方法，要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑。

索引建立的原则：

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

二、索引的分类及创建

1.普通索引

（1）直接创建索引

mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
 
mysql> use auth;
Database changed
 
mysql> create table users(id int(10),user_name char(20),user_pass char(50));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into users values(1,'aaa','pwd123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into users values(2,'bbb','pwd123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into users values(3,'ccc','pwd123');
Query OK, 1 row affected (0.00 sec)
 
mysql> create index xxx on users(user_name(15));    （直接创建索引）
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

 xxx ：索引名称

 users(user_name(15))：users表，user_name表中的列，15索引长度。

查看索引的方式：

mysql> show create table users\G;
或
mysql> show index from users\G;

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

mysql> alter table users add index yyy (user_pass(30));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

备注：
30是定义字符长度，只能比初始值小，不能比初始值大。

（3）创建表结构时，同时创建索引

CREATE TABLE table01 ( 
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), 
INDEX index_table01_title (title(11)) 
);

2.唯一索引

（1）创建唯一索引语法

mysql> create unique index ddd on t1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（2）修改表结构添加唯一索引 

mysql> alter table t1 add unique fff(id);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

（3）创建表同时创建索引

mysql> create table t2 ( id int(10) not null, name char(20), sex char(2),  unique ddd (id) );
Query OK, 0 rows affected (0.00 sec)

3.主键索引

（1）创建主索引语法

mysql> create table t3 (id int(10),name char(20),primary key (id));
Query OK, 0 rows affected (0.01 sec)

（2）

4.组合索引（最左原则）

mysql> create table t4 (id int(10),name char(20),age int(2),index ggg(id,name,age));
Query OK, 0 rows affected (0.02 sec)

组合索引查找方式：

mysql> select * from t4 where id='10' and name='zhansan' and age=16;

注意：
在select语句中where依次从左往右执行。若索引组合是index（id，name，age）。在查询语句中也应该是id，name，age的顺序必须和索引顺序一致，否则无效。

5.全文索引

（1）直接创建索引语法

mysql> create fulltext index hhh on t4 (name);
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

（2）修改表结构添加全文索引

mysql> alter table t1 add fulltext xxx(name);
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

（3）创建表时，建立全文索引

mysql> create table t6 (
     ->  id int(10) not null,
     -> name char(20) not null,
     ->  info varchar (255), 
     -> fulltext index t6(info));
Query OK, 0 rows affected (0.06 sec)

---

查看索引方法：

mysql>show index from t1\G; 
或
mysql>show keys from t2\G; 
或
mysql>show create table t3 \G

索引的删除有如下两种方法：

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

三、事务

1.事务的概述    

         MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样，这些数据库操作语句就构成一个事务。这是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，事务用来管理insert，update，delete语句。

       是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等通过事务的整体性以保证数据的一致性

 2.事务ACID特性

（1）原子性（Atomicity）

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

（2）一致性（Consistency）

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

（3）隔离性（Isolation）

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

（4）持久性（Durability）

• 指不管系统是否发生故障，事务处理的结果都是永久的
• 一旦事务被提交，事务的效果会被永久地保留在数据库中