Mysql基础 --- 索引+事务

1、索引的概念

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。

如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种：BTREE、HASH。也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。我们现在只需要知道索引的作用，功能是什么就行。

2、索引分类

key: 普通索引
unique key: 唯一索引
primary key: 主键索引
fulltext: 全文索引（对于中文存储没什么用，需要借助第三方）
多列索引
冗余索引

2.1 unique key 和 key的使用

建表

create table t16 (
    name char(10),
    email char(20),
    key name(name),
    unique key email(email)
)engine myisam charset utf8;

这里name字段使用key作为普通索引 eamil字段作为唯一索引

插入数据

insert into t16 
values
('lisi', 'lisi@qq.com');

如果再次插入上面的数据，就会报错，因为unique key不能重复

insert into t16 
values 
('lisi', 'lisi@qq.com');

2.2 primary key的使用

例如在2.1表的基础上增加id字段

create table t17 (
    id int,
    name char(10),
    email char(20),
    primary key (id),
    key name(name),
    unique key email(email)
)engine myisam charset utf8;

2.3 索引长度

建立索引时，可以索引列的前一部分内容，比如，只索引前10个字符

建表

create table t18 (
    name char(10),
    email char(20),
    unique key email(email(10))
)engine myisam charset utf8;

这里只取eamil的前十个字符作为索引

2.4 多列索引

就是把2列或者多列的值看成整体，建立索引,例如

create table t19 (
    xing char(2),
    ming char(10),
    key xm(xing, ming)
)engine myisam charset utf8;

这里的key xm(xing, ming)就建立了多列索引查看索引

show index from t19 \G;

其中key_name代表索引名，column_name代表生效的列名

insert into t19 values ('朱', '元璋');

需要注意的是，注意以下两个句子的对比

select * from t19 where xing="朱" and ming="元璋";

这条查询语句会用到索引

select * from t19 where ming="元璋";

这条语句索引是不会发生作用的请看下图

explain select * from t19 where ming='元璋' \G;

记住索引的原则就是左前缀，比如朱开头，在左边，就能有索引，元璋是右边的，所以无法根据右半部分无法建立索引，因为可能还有叫李元璋，张元璋，根本不知道左边是谁，但是根据左边能推出右边是谁。

3、索引的操作

3.1 查看索引

show index from 表名或create table 表名也能看到

3.2 删除索引

alter table 表名 drop index 索引名
或者
drop index 索引名 on 表名

3.3 添加索引

alter 表名 add[index/unique]索引名(列名)

添加主键索引
alter 表名 add primary key(列名)

删除主键索引

alter table 表名 drop primairy key

4、事务

4.1 事务的定义

Transaction

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)

一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

4.2 事务的特征

原子性：确保事务内的所有操作都成功完成，否则事务将被中止在故障点，以前的操作将回滚到以前的状态。
一致性：对于数据库的修改是一致的。
隔离性：事务是彼此独立的，不互相影响
持久性：确保提交事务后，事务产生的结果可以永久存在。

4.3 关于事务的一些术语

开启事务：Start Transaction
事务结束：End Transaction
提交事务：Commit Transaction
回滚事务：Rollback Transactio

4.4 实例详解事务的特征

建立1个表，用来观察事务的特征

# 表1 注意mysql引擎是innodb，myisam不支持事务
create table account(
    id int,
    name char(10),
    money int
)engine innodb charset utf8;

表1插入数据

insert into account values 
(1, 'zhangsan', 5000);
insert into account values 
(2, 'lisi', 5000);

4.5 实例理解事务的原子性和隔离性

# 开启事务
start transaction;
# 给lisi加500
update account set money=money+500 where id=2;

此时，我们再开一个命令行窗口，查看account账户情况

注意这里account并没有给lisi加上那500 接着执行命令

# 给zhangsan减去500
update account set money=money-500 where id=1;
# 提交事务
commit;

然后我们查看account表的数据

这既是隔离性，事务里面的状态并不会实时影响到这张表的数据，事务只显示事务前和事务提交成功后的状态。

原子性就是，如果我们事务中其中一项执行失败，则回滚到初始状态

# 开启事务
start transaction;
# 给lisi加500
update account set money=money+500 where id=2;
# 此时我们想结束这个事务，并且状态还原
rollback；

一致性指的是，前后的数据改变必须一致。比如给zhangsan+500,肯定是从lisi那里扣的500也就是-500,，所以必须数据的改变要一致