阅读 295

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,,所以必须数据的改变要一致

关注下面的标签,发现更多相似文章
评论