索引
索引是一种特殊的文件, 包含着对于数据库里所有数据的引用指针.
可以对表中的一列或多列创建索引, 并指定索引类型, 各类索引有各自的数据结构实现
索引的目的在于 快速定位, 检索数据
索引可以提高 查找 效率, 但会增加 增删改 的开销
索引创建好之后, 每次调用 查询操作 会自走索引
索引的基本操作
创建索引
create index 索引名 on 表名(列名) ;查看索引
show index from 表名 ;删除索引
drop index 索引名 on 表名 ;
创建主键约束, 唯一约束, 外键约束时, 会自动创建对应列的索引
MySQL 索引类型
按照 功能逻辑 区分, MySQL 索引目前有 七类, 不同的索引类型其实就是对不同内容进行限制
主键索引
数据列不允许重复, 不允许为 NULL, 一个表只能有一个主键
普通索引
MySQL 中的普通索引类型, 没有什么限制, 允许在定义索引的列中插入重复值和 NULL 值, 一个表允许创建多个普通索引
唯一索引
索引列的值必须是唯一的, 但是允许 NULL 值 (NULL 值只能有一个)
全文索引
全文索引基于倒排索引, 类似搜索引擎, 主要是为了快速检索大文本数据中的关键字信息 (模糊查询)
前缀索引
常用于 BLOG, TEXT, 或者很长的 VARCHAR, 可限制索引列的长度 (全文索引的简化版)
组合索引
指多个字段上创建的索引, 只有在查询条件中使用了创建索引时的第一个字段, 索引才会被使用 (组合索引遵循最左前缀原则)
空间索引
遵循 OpenGIS 集合数据模型规则, MySQL 5.7之后版本均支持
索引的底层实现
数据库索引的实现有: B 树, B+ 树, Hash
对于 MySQL 的 InnoDB 数据库引擎 来说, 索引是由 B+ 树 来实现的
B 树 : 就是个N叉搜索树
B+ 树 : N叉搜索树, 叶子节点类似链表一样首尾相连
Hash : 通过哈希算法计算键值, 检索时只需要一次哈希算法即可定位到数据的位置 (快)
索引失效
1.当查询中涉及的列或某些字段没有被索引时，索引将不会被使用
2.在索引列上使用函数或表达式时, 索引会失效
3.使用模糊查询时，以%开头就会造成索引失效
4.全表的扫描比索引更快 (eg: 数据量小)
5.使用not, or, <> 等非优化的逻辑运算符，索引会失效. 这是因为其无法充分利用索引。可以使用覆盖索引的方式避免失效情况
6.涉及到隐式类型转换时 (索引列的数据类型与比较的数据类型不一致时)，索引会失效
事务
事务是指逻辑上的一组操作, 组成这组操作的各个单元, 要么全部成功, 要么全部失败 .
MySQL 事务的基本操作
start transaction ;
commit ;
rollback ;
MySQL 事务的特性 – ACID
Atomicity 原子性 : 把多个操作打包到一起, 要么全部执行成功, 要么全部执行失败
Consistency 一致性 : 事务执行前后, 数据库和业务逻辑应该保持一致
Isolation 隔离性 : 多个事务执行之间, 互不影响
Durability 持久性 : 事务产生的修改, 会写入硬盘, 即使事务执行过程中 程序重启 / 主机掉电 … ,待数据库重启后, 事务仍可保证数据的修改是正确的
MySQL 事务执行过程中的问题
脏读
一个事务在执行的过程中读取到了其他事务还没有提交的数据
或者说读到的数据是有问题的 (用户A正在对数据进行修改, 用户B就开始读数据, 等B读取完毕之后, A才修改完毕, 那么此时B使用的就是个错误的数据)
solve : 给写操作加锁
不可重复读
在一个事务中, 多次进行读操作, 前后读取的 数据值不同
solve : 给读操作加锁
幻读
在一个事务中, 多次进行读操作, 前后读取的 数据集不同
solve : 串行化, 彻底放弃并发
MySQL 事务的隔离级别 (隔离性的程度)
事务的隔离级别可通过 修改数据库的配置文件 来调整
隔离程度越高, 事务之间的并发程度越低, 执行效率越慢, 但数据的准确性越高
MySQL 的InnoDB 数据库默认使用的隔离级别是 可重复读 (repeatable read)
