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