MySQL近日学习总结

架构

1. MySQL架构可以分为两部分：Server层和存储引擎
2. Server层包括 连接器，查询引擎，解析器，优化器，涵盖了MySQL的大多数核心服务功能，以及所有的内置函数。
3. 存储引擎 负责数据的存储和提取。架构是插件式的，可以根据不同的需求选择不同的存储引擎。最常用的是MyISAM和InnoDB。

索引(Index)

• 定义：是存储引擎用于快速找到记录的一种数据结构(排好序的快速查找数据结构)

• 优点：提高了检索效率，降低IO成本；降低排序成本，降低CPU的消耗

• 缺点：降低表的更新速度(Insert update delete)；索引也是一张表，也会占用空间

• 分类：1. 单值索引 2. 唯一索引(索引列的值必须唯一，可为null) 3. 复合索引

• 基本语法：
  创建：create index [索引名] ON [表名(字段,字段...)] / alter [表名]
  add INDEX ON [表名(字段,字段...)]
  删除：drop index [indexName] on [表名]
  查看：show index from [表名]

• 数据结构：Hash索引 / B+Tree(InnoDB默认索引)

• 什么时候需要建立索引：

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 与其他表关联的字段，外键关系
  4. 排序的字段
  5. 统计或分组字段

• 什么时候不需要建立索引：

  1. 表记录太少
  2. 经常增删的表
  3. 数据重复且分布平均的表

• 什么时候索引失效

  1. 违背最左前缀法则。最左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列
  2. 在索引列上计算、函数、类型转换
  3. 不能使用索引中范围条件右边的列
  4. 使用!= <>
  5. 使用is null，is not null
  6. like以通配符开头(%abc)
  7. 字符串不加单引号
  8. 使用or

• 聚簇索引
  定义：在同一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引来代替。如果没有，会隐式地定义一个主键来作为聚簇索引

• 覆盖索引，回表查询
  参考这篇文章: https://www.cnblogs.com/myseries/p/11265849.html

EXPLAIN指令

• 使用：explain + SQL语句
• 组成:
  id：id相同从上到下，id不同，从id大到id小
  select_type：simple primary subquery derived union union result
  table：显示这一行的数据是关于哪张表的
  type：查询使用了什么类型

  system：表中只有一条记录
  const：通过索引一次就找到了
  eq_ref：唯一性索引扫描。常见于唯一索引扫描
  ref：非唯一性索引扫描，返回某个单独值的所有行
  range：检索给定范围的行
  index：Full Index Scan
  all：Full Table Scan

possible_keys: 可能应用在这张表上的索引
keys: 应用在这张表上的索引
key_len: 索引中使用的字节数
ref：索引的哪一行被使用了
rows：大致估算出所需的记录所需要读取的行数
Extra：包含不合适在其他列但是和重要的信息

• 索引数据结构
  1. B+Tree
     • 和B-Tree有什么不同？
       1. B-Tree每个节点都存放数据，B+Tree只有叶子节点存放数据
       2. B-Tree的叶子节点没有指向右边的指针，B+Tree有，方便范围查询
  2. Hash
     哈希索引能以 O(1) 时间进行查找，但是失去了有序性。无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找。select * from student where id > 3;

锁

• 分类：

1. 按锁的粒度：

   • 表锁

     1. 偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁的粒度大，发生锁冲突的几率大，并发度低
     2. SQL：手动加锁：lock table [表名] read/write
        查询哪些表加了锁：show open tables

   • 行锁：

     1. 偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，发生锁冲突的几率小，并发度高

     2. 行锁是通过索引项来实现的，只有通过索引来检索项才会开启行锁。并且索引不能失效，索引失效的话，会从行锁变成表锁！！！

     3. InnoDB实现了两种类型的行锁

        • 共享锁(读锁)
          允许一个事务去读一行数据。阻止其他事务活动相同数量集的排他锁

        • 排他锁(写锁)
          允许一个事务去更新、读取这一行数据。阻止其他事务获取这个数量集的任何锁(包括共享锁和排他锁)

        • 几种上锁的情况：
          delete、update、insert会自动给涉及到的数据加上排他锁，直接使用select是不上任何锁的，但是select....for update会上排他锁、select...lock in share mode会上共享锁。所以加上排他锁之后不是其他事务就不能查询该列的数据了，可以用select直接查询。

     4. 间隙锁：用范围条件检索数据的时候，并请求共享或者排他锁时，InnoDB会给复合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫间隙(Gap)。InnoDB也会对这个间隙加锁，叫间隙锁。

2. 按对数据的操作类型

• 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁(排他锁)：当前写操作没有完成前，他会阻断其他写锁和读锁