裸泳的猪

沾沾自喜其实最可悲

0%

mysql基础_索引结构_基础概念

发表于 分类于 ，

mysql的4大特性+4种隔离级别：

4大特性即ACID

  • 1 原子性。事务是一个不可分割的整体，事务开始的操作，要么全部执行，要么全部不执行。
  • 2 隔离性。同一时间，只允许一个事务请求同一组数据。不同的事务彼此之间没有干扰。
  • 3 一致性。事务开始前和结束后，数据库的完整性约束没有被破坏 。
  • 4 稳定性。事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

4种隔离级别

大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。

Read uncommitted 读未提交

就是一个事务可以读取另一个未提交事务的数据。

Read committed 读提交

一个事务要等另一个事务提交后才能读取数据

Repeatable read 重复读

在开始读取数据（事务开启）时，不再允许修改操作

Serializable 序列化

全都顺序执行

除了防止脏读，不可重复读之外，还避免了幻象读(幻读专指新插入的行,原来查不到，再查查到了)

相关系统命令：

1
2
3
4
5
6
7
8
-- 查看当前会话隔离级别
SELECT @@tx_isolation

-- 设置当前会话隔离级别
set session transaction isolation level repeatable read;

-- 查看系统当前隔离级别
select @@global.tx_isolation;

binlog /redo log/undo log

  • binlog我们可以简单理解为：存储着每条变更的SQL语句,以及事务id等，主要用来做主从同步和数据恢复
  • redo log 记载的是物理修改的内容（xxxx页修改了xxx）。是为了持久化而使用的（异步刷盘）
  • undo log主要存储的也是逻辑日志。主要有两个作用：回滚和多版本控制(MVCC)

    mvcc

    MVCC, Multiversion Concurrency Control多版本并发控制。MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作, 因此服务器的开销更低（减少了锁的生产和分配）。

    MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。

    MVCC带来的好处是？

首先我们发现数据库并发场景有三种，分别为：

  • 读-读：不存在任何问题，也不需要并发控制
  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
  • 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制；

也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
所以MVCC可以为数据库解决以下问题：

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
  • 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC的实现原理

它的实现原理主要是依赖记录中的 3个隐式字段(DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID)，undo日志 ，Read View 来实现的。

  • DB_TRX_ID
    • 6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  • DB_ROLL_PTR
    • 7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  • DB_ROW_ID
    • 6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

引擎

mysql常用的引擎有InnoDB，MyISAM，Memory，默认是InnoDB。

InnoDB：磁盘表，支持事务(Redo log 和Undo log和锁)，支持行级锁（默认行级锁），B+Tree索引

ps:优点： 具有良好的ACID特性。适用于高并发，更新操作比较多的表。需要使用事务的表。对自动灾难恢复有要求的表。

缺点：读写效率相对MYISAM比较差。占用的磁盘空间比较大。

MyISAM：磁盘表，不支持事务，不支持外键，支持表级锁，B+Tree索引

ps: 优点：占用空间小，处理速度快（相对InnoDB来说）,强调的是性能

缺点：不支持事务的完整性和并发性

**MEMORY(Heap)**：内存表，不支持事务，表级锁，Hash索引，不支持Blob,Text大类型

ps: 优点：速度要求快的，临时数据

缺点：丢失以后，对项目整体没有或者负面影响不大的时候。

InnoDB详解

二叉树 -> 平衡二叉树 -> B树 -> B+树

在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

聚集索引（也叫聚簇索引) (clustered index)

聚集（clustered）索引，也叫聚簇索引。

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

这种以主键作为 B+ 树索引的键值而构建的 索引，我们称之为聚集索引。InnoDB聚集索引的叶子节点存储行记录。

InnoDB必须要有，且只有一个聚集索引：

表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键：

（1）如果表定义了PK（主键），则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

PS：所以PK查询非常快，直接定位行记录。

非聚集索引（非聚簇索引）

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。（一般我们建的非唯一索引的普通索引就是这种）

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

  1. 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
  2. 非聚集索引在查询的时候可以的话就避免二次查询(回表)，这样性能会大幅提升。

索引建立原则

  1. 最左前缀匹配原则,mysql会从左至右匹配直到遇到范围查询(>、<、between、like)就停止匹配，范围查询
    会导致组合索引半生效。比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，c 可
    以用到索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。where
    范围查询要放在最后 （这不绝对，但可以利用一部分索引）。
  2. 尽量使用区分度较高的列作为索引，区分度的公式为： count(distinct column) / count(*)，用来表示字段不重复的比例，比例越大则代表扫描到的记录数越少，唯一键的区分度是1，而一些状态、性别等字段在大数据量下则无限趋近于0，所以类似性别字段不宜作为区分度的索引列。
  3. 尽量扩展索引列而不是新建索引列（这会增加系统负担）。比如，表中已经有了a列的索引，现在需要加入b列的索引，则需要修改原来的索引即可。
  4. 对于经常存取的列避免建立索引。
  5. 所有的“非”类型的判断不会走索引( not , not in, not like, <>, != ,!>,!< )。
-------------本文结束感谢您的阅读-------------