MySQL

MySQL事务，锁，引擎，索引，SQL优化

MySQL

• 事务
  • 特性
    • 原子性
    • 一致性
    • 隔离性
    • 永久性
  • 分类
    • 扁平事务
    • 带有扁平点的扁平事务
    • 链事务
    • 嵌套事务
    • 分布式事务
  • 隔离级别
    • read uncommitted 读未提交
    • read committed 读已提交
      • 可以解决脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据
    • repeatable read 可重复读（ MySQL 默认隔离级别）
      • 可以解决不可重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据
    • serializable 串行化
      • 可解决幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据
• 锁
  • 锁机制
    • 表级锁
      • 开销小，加锁快；不会出现死锁；颗粒度大，发生冲突概率高，并发度低
    • 行级锁
      • 开销大，加锁慢；会出现死锁；颗粒度小，发生冲突概率低，并发度高
    • 页面锁
      • 开销和加锁时间介于表级锁和行级锁之间；会出现死锁；锁定颗粒度介于两者之间，并发度一般
  • 引擎与锁
    • MyISAM 和 MEMORY 支持表锁 BDB 支持页锁 InnoDB 既支持行锁，也支持表锁，默认行锁
• 索引
  • 类型
    • 1 Normal ：普通索引
      • 是最基本的索引，它没有任何限制
    • 2 UNIQUE: 唯一索引
      • 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
    • 3 组合索引:
      • 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
    • 4 Fulltext: 全文检索， MySQL 的全文检索只能用 MyISAM 引擎，并且性能低，不建议使用
  • 方法
    • B+树
      • 特点
        • B+Tree只在最末端叶子节点存数据，叶子节点是以链表的形势互相指向的
      • 为什么用B树作为索引
        • B+Tree的高度是可控的，mysql通常是3到5层
    • Hash: 把索引的值做 hash 运算，并存放到 has 表中，使用较少，一般是 memory 引擎使用 缺点：只适用于精确的值比较，无法使用范围查找，无法使用索引排序，如果大量 hash 值相同，性能较低
  • 创建
    • 1 较频繁的作为查询条件的字段应该创建索引
    • 2 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    • 3 更新频繁的字段不适合作为索引，原因：索引有维护成本
  • 失效
    • 1 有 or 中不是索引的字段
    • 2 复合索引未用左列字段
    • 3 like 以 % 开头
    • 4 需要类型转换
    • 5 where 中索引列有运算
    • 6 where 中索引列使用了函数
    • 7 如果 MySQL 觉得全表扫描更快时（数据少）
  • 引擎与索引
    • MyISAM是非集聚引擎，支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
    • innoDB是集聚引擎，5.6以后才有全文索引;支持事务；它是行级锁;不会保存表的具体行数.
• 引擎
  • InnoDB
  • MyISAM
  • Memory
    • 如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。
• 表结构对性能的影响
  • 冗余数据处理（可以提高系统的整体查询性能三范式）
    • 1 每一列只能有一个值
    • 2 每一行可以被唯一区分
    • 3 不包含其他表的已包含的非关键信息
  • 大表拆小表
    • 1 一般不设计属性过多的表
    • 2 一般不会超过 500 到 1000 万数据的表
    • 3 有大数据的列单独拆为小表
  • 根据需求展示更加合理的表结构
  • 常用属性分离为小表
• MySQL优化
  • 索引优化
    • 1.只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
    • 2.尽量使用短索引，如果可以，应该制定一个前缀长度
    • 3.对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
    • 4.对于有多个列where或者order by子句的，应该建立复合索引
    • 5.对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
    • 6.尽量不要在列上进行运算（函数操作和表达式操作）
    • 7.尽量不要使用not in和<>操作
  • sql语句优化
    • 如何捕获低效sql
      • slow_query_log：这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。 （set @@global.slow_query_log=1）
      • ong_query_time：当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短 （set @@global.slow_query_time=3）
      • log_queries_not_using_indexes：这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快
    • 优化原则
      • 1 查询时，能不要*就不用*，尽量写全字段名
      • 2 大部分情况连接效率远大于子查询
      • 3 多使用explain和profile分析查询语句
      • 4 查看慢查询日志，找出执行时间长的sql语句优化
      • 5 多表连接时，尽量小表驱动大表，即小表 join 大表
      • 6 在千万级分页时使用limit
      • 7 对于经常使用的查询，可以开启缓存
  • 表优化
    • 表的字段尽可能用NOT NULL
    • 字段长度固定的表查询会更快
    • 把数据库的大表按时间或一些标志分成小表
    • 将表拆分
      • 水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
      • 垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
• 主从复制
  • 半同步复制
    • 主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。
    • 优点：利用数据库原生功能，比较简单
    • 缺点：主库的写请求时延会增长，吞吐量会降低
  • 数据库中间件
    • 1所有的读写都走数据库中间件，通常情况下，写请求路由到主库，读请求路由到从库 2记录所有路由到写库的key，在主从同步时间窗口内（假设是500ms），如果有读请求访问中间件，此时有可能从库还是旧数据，就把这个key上的读请求路由到主库。 3在主从同步时间过完后，对应key的读请求继续路由到从库
    • 相关中间件
      • canal
      • otter
    • 优点: 能保证绝对一致
    • 缺点：数据库中间件的成本较高
  • 子主题
    • 写流程： 1 如果key要发生写操作，记录在cache里，并设置“经验主从同步时间”的cache超时时间，例如500ms 2 然后修改主数据库 读流程： 1 先到缓存里查看，对应key有没有相关数据 2 有相关数据，说明缓存命中，这个key刚发生过写操作，此时需要将请求路由到主库读最新的数据。 3 如果缓存没有命中，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离。
    • 优点： 相对数据库中间件，成本较低
    • 缺点： 为了保证“一致性”，引入了一个cache组件，并且读写数据库时都多了缓存操作。