经验总结（二）：数据库

数据库

设计简易关系型数据库

• RDBMS
  • 程序实例
    • 存储管理：每个块/页存储多行数据
    • 缓存机制
    • SQL解析
    • 日志管理
    • 权限划分
    • 容灾机制
    • 索引管理
    • 锁管理
  • 存储（文件系统）
• 索引模块
  • 快速查询数据。
  • 主键、唯一键、普通键都可以成为索引。
  • 分类：二叉查找树、B - Tree、B+ - Tree、Hash结构、bitMap索引
    • B+ - tree的磁盘读写代价更低；B+ - tree的查询效率更加稳定；B+ - tree有利于对数据库的扫描
    • Hash索引通过Hash计算便可找到索引但也有响应的缺点：仅仅能满足“=”，“in”，不能使用范围查询；无法排序操作；不能利用部分索引键查询；不能避免表扫描；遇到大量Hash值相等的情况下性能不一定比B树索引高
    • bigMap索引，值固定，锁力度大并不适合高并发。
  • 密集索引和洗漱索引的区别
    • 密集索引文件中的每个索引码值都对应一个索引值
    • 稀疏索引文件只为索引码的某些值建立索引项
  • InnoDB是密集索引，也含有辅助索引，辅助索引存储密集索引的地址。
    • 若一个主键被定义，该主键则作为密集索引
    • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
    • 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
    • 非主键索引存储相关键位和其对应的主键值，包含两次查找。
  • MyISAM是稀疏索引
  • 表文件信息
    • xxx.frm存储表的结构信息
    • xxx.idb存储innodb的数据和索引
    • xxx.MYI存储MyISAM的索引
    • xxx.MYD存储MyISAM的数据
• 定位并优化慢查询sql
  • 根据慢日志定位慢查询sql
    • show variables like '%query%' 查询日志是否打开，设置阈值时间，查看慢日志地址。
    • show status like '%slow_queries%' 查看慢查询的数量
    • set global slow_query_log = on 打开慢查询
    • set global long_query_time= 1; 设置慢查询阈值，需要重新连接客户端才会更新。
    • 以上修改重启服务会被还员，建议直接修改配置文件。
    • 操作表结构的sql不算慢查询，只有dml语句会。
  • 使用explain等工具分析sql
    • id靠前先执行
    • type中all表明是全表扫描进行优化
    • extra出现下面2项意味Mysql不使用索引，效率影响较大，应进行优化
      • Using filesort表示MySQL会对结果使用一个外部索引排序，而不是从表里安索引次序读到相关内容。可能在内存或者磁盘上进行排序。Mysql中无法利用索引完成的排序操作成为“文件排序”
      • Using temporary表示MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by
  • 修改sql或者尽量让sql走索引
    • 在innodb中，对于 select count(id) from T ，如果有稀疏索引，则会走稀疏索引。因为稀疏索引值存储索引，不会存储数据。
• 联合索引
  • 最左匹配原则
    • mysql一直向右匹配直到遇到范围查询（<、>、between、like）就停止匹配。例如：a=1 and b=2 and c > 5 and d = 6 ，如果建立联合索引（a、b、c、d）顺序的索引，d就用不到索引，但是如果建立的联合索引时(a、b、d)，则a,b,d都可以用到，同时a、b、d的顺序可以任意调整。
    • =和in是可以乱序的，例如a=1 and b=2 and c=3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的样式。
• 索引建立的数量
  • 数据量小的表不需要建立索引，建立会增加额外的索引开销。
  • 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
  • 更多的索引意味着也需要更多的空间
  • 索引数量最好在6个之内。
• 锁模块
  • MyISAM默认用的是表级锁，不支持行级锁。
  • InnoDB默认用的是行级锁，也支持表级锁。
  • MyISAM
    • lock tables T_MyISAM read;给MyISAM加读锁。
    • lock tables T_MyISAM read;给MyISAM解除读锁。
    • MyISAM读锁是共享锁，在sql后加 for update 也会变成排他锁。
    • MyISAM写锁是排他锁。
  • InnoDB
    • show variables like 'autocommit' 查看当前自动提交是否打开
    • set variables = 0 ;关闭自动提交，仅关闭当前session
    • begin transaction sql… 也可以开启事物、提交事物
    • 默认的情况下，select不会上锁。
    • select * from T where id = 1 lock in share mode ； 给当前行上共享所锁
    • 当没有走索引时，使用时的表级锁。
  • MyISAM适合的场景
    • 频繁执行全表的count语句。count是表的属性
    • 对数据进行增删改的频率不高，查询非常多的表
    • 没有事物
  • InnoDB适合的场景
    • 增删改查都频繁的
    • 可靠性要求比较高的，支持事物
  • 数据库锁的分类
    • 按粒度划分
      • 表级锁
      • 行级别
      • 页级别（存储页）
    • 按锁级别划分
      • 共享锁
      • 排他锁
    • 按加锁方式
      • 自动锁（自动上的）
      • 显式锁（lock in share mode ， for update）
    • 按操作划分
      • DML锁（数据变更）
      • DDL锁（表结构变更）
    • 按使用方式划分
      • 乐观锁：可以使用版本号来控制。
      • 悲观锁：全程互拆锁
• 数据库事务的四大特性（ACID）
  • 原子性（Atomic）
  • 一致性（Consistency）
  • 隔离型（Isolation）
  • 持久性（Durability）
• 事务隔离级别
  • 更新丢失--mysql所有事务隔离级别在数据库层面上均可以避免
  • 脏读—READ-COMMITTED（已提交读）事务隔离级别以上可避免（不可重复读）
  • select @@tx_isolation; 查看事物隔离级别，默认为REPEATABLE-READ。
  • set session transaction isolation level read uncommitted; （读未提交）设置隔离级别（脏读）
  • set session transaction isolation level repeatable read;（可重复读）不管其他事物是否提交，自己读取的是不变的。但是使用的其实是其他事物已提交的数据。（幻读、快照读）
  • set session transaction isolation level serializable;(可串行化)
• 快照读和当前读
  • 当前读：select … lock in share mode ,select … for update
  • 当前读：update,delete,insert
  • 快照读：不加锁的非阻塞读，select。（事务级别不为serializable）
  • 快照读，读取之后就建立快照，其他事物提交也读取不到最新数据。
• RC、RR级别下的InnoDB的非阻塞读如果实现
  • 数据行里的DB_RRX_ID（最近一次的事物id）、DB_ROLL_PTR（回滚指针指向undo log内的行）、DB_ROW_ID(行号、没有主键或索引用它当作隐藏主键字段)字段
  • undo日志
  • read view
• next-key锁（行锁+gap锁）
  • 行锁
  • Gap锁（在 rc及以下是没有的）
    • 如果where条件全部命中，则不会用Gap锁，只会加记录锁
    • 如果where条件部分命中或全不命中，则会加Gap锁
    • Gap锁会用在非唯一索引或不走索引的当前读中
    • Gap锁的区间大小为（] 开闭区间，要注意非唯一索引。
• 语法注意
  • group by
    • select 子句中的列名必须为分组列或列函数
    • 链表时查询时select子句可以不为分组列
  • Having
    • 通常与group by一起使用
    • where过滤行，having过滤组
    • 出现在同一行sql的顺序：where > group by > having

                                                                                                生活要多点不自量力