数据库

数据库mysql的思维导图，包括数据库的操作、数据表、数据表增删改查、数据的导入导出等内容。

mysql

• 存储引擎
  • InnoDB
    • 默认事务型引擎
    • 支持事务安全表（ACID）
    • 支持行锁
      • 由存储引擎层实现
      • 可以最大程度地支持并发
    • 索引
      • 聚簇索引（主键索引）：聚簇索引的叶子节点中保存的是整行记录
      • 非聚簇索引（非主键索引）：非聚簇索引的叶子节点中保存的是该行记录的主键的值
  • MyISAM
    • MyISAM拥有较高的插入、查询速度
    • 不支持事物和外键
    • 支持表锁，不支持行锁
    • MyISAM是表级锁，不会有并发的行操作
  • MERGE
    • Merge表就是几个相同MyISAM表的聚合器
    • Merge表中并没有数据
    • 对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作
  • MEMORY
    • MEMORY存储引擎将表中的数据存储到内存中
• 索引
  • mysql读取数据的方式
    • 每一次IO读取的数据我们称之为一页
  • 索引的实现方式
    • hash
      • 查询单条快，范围查询慢
      • 哈希索引没办法利用索引完成排序
      • 不支持多列联合索引的最左匹配规则
    • B+tree
      • 平衡的多叉树
      • 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项
      • 当索引为组合索引时遵循最左匹配特性
      • 索引字段要尽量的小
  • 索引的类型
    • 主键索引：值唯一，不允许空值
    • 普通索引：没有什么限制，允许重复值和空值
    • 唯一索引：值唯一，允许空值
    • 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引
    • 空间索引：5.7之后的版本，支持OpenGIS
    • 前缀索引
    • 聚合索引/普通索引
      • 聚集索引的叶子节点存储行记录
        • 聚集索引唯一非空
      • 普通索引的叶子节点存储主键值
        • 普通索引会回表查询
  • 一些定义
    • 命中索引和覆盖索引
      • where后的条件命中索引
      • select后的值覆盖索引，没有覆盖索引的字段会触发回表查询操作
    • 组合索引与索引合并
      • 组合索引的效率要高于索引合并
      • 组合索引：index(name,email)，根据最左原则，匹配where name= 以及where name= and email=；不能覆盖where email=
      • 索引合并：创建两个索引index(name)，index(email)，能覆盖上述三种情况
  • 索引建立与使用原则
    • 1.最左前缀匹配原则
      • 最左前缀匹配：必须按照从左到右的顺序匹配
      • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
    • 2.=和in可以乱序
    • 3.尽量选择区分度高的列作为索引
      • 区分度的公式是count(distinct col)/count(*)
    • 4.索引列不能参与计算，保持列“干净”
      • from_unixtime(create_time) = '2014-05-29' 应该改为create_time = unix_timestamp('2014-05-29')
• sql优化相关
  • 慢查询优化的基本步骤
    • 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
    • where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
    • explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
    • order by limit 形式的sql语句让排序的表优先查
    • 了解业务方使用场景
    • 加索引时参照建索引的几大原则
    • 观察结果，不符合预期继续从0分析
  • 关键字：EXPLAIN
  • 索引失效场景
    • like '%xx'
    • 使用函数
    • or
      • 当or条件中有未建立索引的列才失效
    • 类型不一致
    • 普通索引的不等于不会走索引
      • 如果是主键或索引是整数类型，则还是会走索引
    • 排序条件为索引，则select字段必须也是索引字段，否则无法命中
      • 如果对主键排序，则还是走索引
    • 可能导致引擎放弃使用索引而进行全表扫描
      • 尽量避免在 where 子句中对字段进行 null 值判断
      • 避免在 where 子句中使用!=或<>操作符
      • 避免在 where 子句中使用 or 来连接条件,可以使用union all
      • 避免在 where 子句中对字段进行表达式/函数操作
  • 注意
    • text类型，必须指定长度
    • SELECT *
      • 如果不查询表中所有的列，尽量避免使用 SELECT *，它将以磁盘扫描方式取出单条数据的末尾，而字段方式则会直接取到数据项
      • SELECT * 和 SELECT 所有列，两者差别几乎可忽略。所以查询所有字段（或者大多数字段）的时候，大可select *来操作。如果某些不需要的字段数据量特别大，还是写清楚字段比较好，因为这样可以减少网络传输。
    • COUNT(expr)
      • count(1)或count(列)代替count(*)在mysql中没有差别了
      • COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数，会全表扫描
      • COUNT(*)是SQL92定义的统计行数的标准语法，所以MySQL数据库进行过很多优化
        • MyISAM：做了一个简单的优化，把表的总行数单独记录下来，如果执行count(*)时可以直接返回，前提是不能有where条件
        • InnoDB：MySQL会优先选择最小的非聚簇索引来扫表
• 一些底层晦涩的内容
  • latch锁
    • 在内存中保护list的内存锁结构
  • 自适应哈希索引
    • InnoDB 通过在内存中构造哈希索引，对使用 = 和 IN 运算符 加速查找的表进行优化
    • 自动为经常访问的索引页构建一个哈希索引
• 事务
  • 事务的特性：原子性、一致性、隔离性和持久性 (ACID)
    • redo log(重做日志) 保证事务的持久性
    • undo log(回滚日志) 来保证事务的原子性
    • 通过 锁机制、MVCC 等手段来保证事务的隔离性
  • 隔离级别
    • 读未提交
    • 读已提交
    • 可重复读
      • innodb引擎默认隔离级别
    • 序列化
  • 不同隔离级别会发生的问题
    • 脏读
      • 一个事务读到了另一个事务还未提交的数据
      • 读已提交级别就能避免
    • 幻读
      • 指的是在一个事务A中执行了一个当前读操作，而另外一个事务B在事务A的影响区间内insert了一条记录，这时事务A再执行一个当前读操作时，出现了幻行
    • 不可重复读
      • 一个事务范围内多次查询返回不同的数据值
• 锁
  • 死锁
    • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置
    • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
    • 减少死锁的主要方向，就是控制访问相同资源的并发事务量。
  • 共享/排他锁
    • 共享锁（读锁/S锁）
      • 加S锁只能读不能修改，同时其他事务也只能再加S锁
      • lock in share mode
    • 排他锁（写锁/X锁）
      • 加X锁可以对数据进行修改，其他事务不能再加任何锁（但是可以进行普通查询，快照读），直至X锁被释放
      • for update
      • 锁的粒度来划分
        • 表锁
          • 意向锁
            • 意向锁是一个表级别的锁，为了解决行锁与表锁的矛盾，意向锁仅仅表明意向，意向锁之间相互兼容
            • 意向锁是InnoDB自动加的，不需要用户干预
            • 类别
              • 意向共享锁(intention shared lock, IS)
              • 意向排它锁(intention exclusive lock, IX)
          • 自增锁(Auto-inc Locks)
            • 专门针对事务插入AUTO_INCREMENT类型的列
        • 行锁
          • 记录锁(Record Locks)
            • record lock锁住的永远是索引，而非记录本身
            • 当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁
          • Next-Key Locks
            • Next-Key Locks是行锁和gap锁的组合
            • innodb默认的锁就是Next-Key locks
            • 当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock
          • Gap lock（间隙锁）
            • 在索引记录之间的间隙中加锁
            • 间隙锁之间不互斥
            • 插入意向锁(Insert Intention Locks)
              • 它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此
              • 间隙锁的一种
              • gap lock 会阻塞 insert intention lock
              • insert intention lock 相互不会阻塞
          • 数据库隔离级别为RR的时候才有效
    • 普通 select 语句默认不加锁，而CUD操作默认加排他锁
  • MDL全称为metadata lock，即元数据锁
    • 表级锁
    • 用于解决或者保证DDL操作与DML操作之间的一致性
    • MDL 不需要显式使用，在访问一个表的时候会被自动加上
    • 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁