Mysql的索引和事物

一.Mysql的索引介绍

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

1.索引概述

     索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。使用索引的副作用是需要额外的磁盘空间

2.索引的作用

优点

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

3.索引的分类

1.普通索引
2.唯一索引（可以多个)
3.主键索引（只能一个）
4.组合索引（最左索引，从左到右的顺序）
5.全文索引
oracle：B（表示为两个）树索引

创建测试的数据库和表

create database  auth;

use  auth

create  table  users  (id  int(10) , user_name  char(20) , user_pass  char(50));

（1）普通索引

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

1. 直接创建索引语法

create  index  aaa   on  users(use_name(20));

注：index为索引，aaa为索引的名字，user为数据表的名字，20为长度。如果为数字类型的列不能指定其长度，字符串可以

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

alter  table  users  add  index  bbb  (user_name(20));

(2)唯一索引

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

1.创建唯一的索引语法

首先创建一个新的数据表：create  table   t2  (id  int(10) ,  user_name  char(20) , user_pass  char(50));

create  unique  index  bbb  on  t2  (user_name(20));      ///创建唯一的索引语法

2.修改表的时候同时添加唯一的索引语法

alter  table  t2  add  unique  bbb(user_name(20));

(3)主键索引

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

创建主键索引语法，并将表中的id设为主键

create  table  t3  (

id  int（20）name  char（15） primary  key（id）);

(4)组合索引

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。

创建组合索引语法

create table t4 (id int(10),name char(20),age int(2)),index ggg(id,name,age);

注：

select 语句的 where 条件是依次从左往右执行的。

mysql>select * from user where id=''  AND name = '' AND age = '' ;

若使用的是组合索引 index user(id，name, age,)。在查询中，name、age、sex 的顺序必须如组合索引中一致排序，否则索引将不会生效。

（5）全文索引

对于较大的数据集，将资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入到现有的FULLTEXT的速度更快，不过对于大容量的数据表，生成全文索引是一个非常消耗时间、消耗硬盘空间的做法

创建全文索引

1.直接创建索引语法

create  fulltext   index  hhh  on  t5（id）

2.修改表结构添加全文索引语法

alter  table  t5  add  fulltext  hhh(id)

4.创建索引的原则

（1）主键和外键必须有索引
（2）比较小的表，不建议设置全局索引，300行以下
（3）经常用于跨表查询，需要有索引
（4）唯一性比较差的列或表不适合做索引
（5）更新太频繁的列不适合做索引
（6）经常用where指定的条件进行查询，适合创建索引
（7）建立索引的列尽量是小字段
 

5.索引的相关操作

1.查看索引

show  index  from  t1\G；

show  keys  from t2\G;

show  create  table  t3 \G;

2.删除索引

mysql> drop index eee on t3;

mysql> alter table t1 drop index ccc;

二.Mysql事务

1.MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样，这些数据库操作语句就构成一个事务。

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元

适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等

通过事务的整体性以保证数据的一致性

2.具有的ACID特性

原子性
一致性
隔离性
持久性（事物是需要及时提交的，要写入到磁盘）

3.事物的构成
内存（数据缓存区）可以回滚的
服务器宕机，内存就会停电，数据就会丢失
提交即写入磁盘
硬盘（永久存储）不可以回滚的

注意：

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

4.实验环节

mysql> SET AUTOCOMMIT=0;       ///关闭自动提交

mysql>use auth;

mysql>CREATE TABLE mmm( id int(5)) engine=innodb;        //创建数据表

mysql>select * from mmm;

mysql>begin;     //开始事务

mysql>insert into mmm(1);

mysql> insert into mmm(2);

mysql> commit;      //提交事务

mysql>select * from mmm;

mysql>begin;     //开始事务

mysql>insert into mmm values(3);

mysql>rollback;     //回滚

mysql> select * from mmm;     //因为回滚所以数据没有插入