[04]MySQL索引与事务-优快云博客

1.索引

为了提高查询效率而使用一种数据结构把数据组织起来，例如字典的索引

创建索引

对于非主键
# 查看表索引
show index from 表名
表中的主键默认自动创建索引
create index 索引名 on 表名(字段名);
示例
create index index_student_sn_name on student(sn,name);    # 组合索引
注意

        ①一张表里至少会有一个索引:如果一张表里没有主键,MySQL会为每一行生成一个唯一的字段,并用这个字段当做索引

        ②索引一般创建在频繁查询的列上,且列中的值重复比较少

索引的数据结构

~~①Hash查询的时间复杂度O(1)~~

    ~~MySQL没有使用hash作为索引的数据结构,因为ahsh不支持范围查找~~

~~②二叉搜索树的时间复杂度O(n),MySQL未使用~~

~~③红黑树的时间复杂度log(n),MySQL未使用~~

~~④B树可以降低树高,但仍未使用~~

MySQL中索引使用的B+树的叶子结点相互连接,且是一个双向循环链表

叶子结点中包含了树中所有结点的真实数据

非叶子结点中包含的是主键和叶子结点的引用

在一定的数据量范围内,不论查找到数据是什么,时间效率基本相同

重要的是叶子结点的数据是有序的

N叉搜索树有效的降低了树的高度,从而减少了磁盘IO次数

补充知识

①一个索引包含多个字段就叫组合索引

②索引本身占用磁盘空间

    更新和删除操作同时也会更新索引,建的太多也会有负担

③使用索引的最左原则:

    当使用组合索引(例如sn,name),在查询时where里面必须按顺序写sn和name,不然就没用上索引查询.尽管MySQL会自己优化反过来

④回表查询

    当select还包括除了索引列的其他列,那么会使用id到主键索引中查询完整结果.这个现象叫做回表查询

⑤索引覆盖

    当select里只有索引包含的,就不用回表查其他内容,直接返回结果,这个现象叫做索引覆盖

⑥主键索引中包含当前索引

⑦查找语句之前写explain关键字(查询执行计划)可以知道是否使用了索引

⑧索引失效的情况:最左原则,判断不等(每个都要判断),类型转换(与原类型不符),like'%xxx',索引列运算(改了原来的值),is null/is not null(全表都扫描了)

2.事务

        把一组SQL语句打包运行的机制

提交与回滚

        当其中有语句未执行成功,则会回滚

        当全部执行成功才会提交到数据库.提交之前,别的用户是看不到修改的,只能自己看到.

使用方法
start transaction;
# 输入各种语句;
# 输入各种语句;
# ......
rollback/commit;

# rollback则取消上述所有操作
# commit则会提交修改到数据库
事务的特性(ACID)

①原子性:

        要么全执行,要么都不执行

②一致性:

        数据库从一个一致性状态到另一个一致性状态.即事务执行的前后,要保持正确的结果

③隔离性:

        多个事务执行过程中不能互相干扰

④持久性:

        事务提交就会写入磁盘永久保留,及时数据库服务故障也不影响数据内容

事务的隔离级别

多个事务之间是否可以相互影响?

如果可以相互影响,那么事务的隔离级别比较低,但是性能变高

如果不可相互影响,那么事务的隔离级别比较高,但是性能变低

①read uncommitted:读未提交

②read committed:读已提交

③repeatable:可重复读

④serializable:串行化

上面性能好安全性低,下面性能差安全性高
show variables like 'tx_isolation';    # 查询隔离级别

set @@global.tx_isolation = 'READ-UNCOMMITTED'    # 设置隔离级别.重开才生效
①read uncommitted

事务没有提交的时候,其他用户就能查出来.rollback之后 ,其他用户就查不出来了

这个现象叫"脏读",第一次读到了事务没有提交的数据,当事务回滚之后再次查询,发现没有这条数据.

②read committed

解决了脏读,但可能出现不可重复读的状态

在写数据的时候加一把锁,写锁是一把排它锁,其他的读和写都不能

③repeatable

解决了不可重复读,但可能出现幻读

④serializable

隔离级别

脏读

不可重复读

幻读

read uncommitted

Y

Y

Y

read committed

N

Y

Y

repeatable

N

N

解决了大部分

serializable

N

N

N