mysql

  • 引擎:InnoDB【默认】,MyISAM
    • 区别:
      • InnoDB:支持事务,行级锁,MVCC,外键,多种行格式
      • MyISAM:支持全文索引,可以直接拷贝表文件到另一台机器,表行数
      • InnoDB是索引组织表,MyISAN是堆表
        • 堆表:数据插入时的存储位置随机,由内部空闲情况决定
        • 索引表:按照索引的方式存储,数据是有序的
  • 索引:B+Tree索引【默认】,哈希索引,空间索引,全文索引
    • 一般用在:
      • 经常需要查询的列【加快查询速度】
      • 作为主键的列上【强制唯一性,组织表中数据的排列结构】
      • 经常用在连接的列上【主要是外键,可以加快连接的速度】
      • 经常需要范围搜索的列上【索引已经排序,范围时连续的】
      • 经常需要排序的列上
      • 经常使用在where子句中的列上【加快条件判断速度】
    • 不用在:
      • 在查询中很少使用的
      • 只有很少数据值的列
      • 定义为text, image和bit数据类型的列【数据量大/取值很少】
      • 修改性能远远大于检索性能
  • 查询性能优化:
    • SQL语句优化
      • 减少返回的列,
      • 拆分大的语句,
      • 避免全表扫描,
      • 避免null判断,
      • 避免!= <>,
      • 避免or——>or可以改成union all
      • in 和not in不要
      • Like ’%a%’不要,全文检索
      • where表达式操作不要 where num/2=100
      • 避免函数操作
      • 用exists代替in
    • 索引优化
    • 数据库结构优化
      • 范式&反范式
      • 拆分表:分区分割数据,查询时只要在表分区中扫描,避免了全表扫描
        • 垂直拆分:把表分类,分不到不同的数据库上,解决了表与表之间的io竞争
        • 水平拆分:把同一个表按照某种规则分散到不同的库上,解决单表中数据量增长出现的压力
    • 服务器硬件优化
  • 数据库的特性ACID
    • 原子性:事务的操作要么全部完成,要么全部不完成,就是只有开始前和结束后两个状态。如果失败了,就回滚到开始前
    • 一致性:在事务开始前和结束后,数据库的完整性约束没有被破坏
      • 并发可能会导致不一致性:因为破坏了隔离性。解决:并发控制,可串行化调度。并发控制可以通过封锁来实现,但是封锁需要用户自己控制,非常复杂。数据库管理系统通过更轻松的方式处理并发一致性问题。
    • 隔离性:一个事务所做的修改在最终提交以前,其他事物是不可见的
    • 持久性:事务完成后,更改永久保存在数据库中,不会回滚
  • 封锁:行级锁,表级锁
  • 隔离级别:
    • 未提交读
    • 提交读:别的事务看不到这个事务提交前的状态
    • 可重复读:同一个事务中多次读取同样数据的结果是一样的
    • 可串行化:强制事务串行执行
  • MVCC多版本并发控制:(InnoDB引擎)
    • 未提交读不需要,可串行化需要读取的行都加锁,MVCC做不到
  • 范式
    • 1NF:属性不可分
    • 2NF:所有非主属性完全依赖于键码
    • 3NF:非主属性不传递依赖于键码
    • BCNF:所有属性不传递依赖于键码
  • 连接的种类
    • 外连接:
      • 左连接left join:包括左表所有行,右表如果没有对应值就设置值为null
      • 右连接right join:同左
      • 整连接full join:包括左右表所有值
    • 内连接join:只返回符合条件的左右表的列
    • Join on 等价于 cross join where…
    • 直接交叉连接cross join没有where,等于获得笛卡尔积
  • MySQL的复制原理:
    • 步骤:
      1. 主服务器master将改变了数据库的语句记录到二进制日志binary log中
      2. 其他主机slave将master的二进制日志拷贝到它的中继日志relay log中
      3. slave执行中继日志中的语句
    • 限制:在slave上是串行化的,在master上的并行操作不能在slave上并行操作
  • 数据类型:
    • char&varchar:定长&变长
      • Char(N):如果长度不足N,在后面补空格补到N
      • Varchar(N):最多存放N个字符,存同一个字符串消耗的空间是相同的,但是排序的话,200比50消耗更多内存。因为order by是用定长计算长度的。
    • Int(N):N是显示字符长度,不影响内部存储
    • Datatime&timestamp:
      • Datatime:和时区无关,8字节,1001-9999
      • Timestamp:格林威治时间的秒数,4个字节,1970-2038
      • 尽量使用timestamp,因为空间效率更高
  • 日志与事务
    • redo&undo
    • Undo:在事务开始前,把数据备份到undo log
    • redo:记录新数据的备份
    • 回滚:如果redo中没有对应的commit记录,那么就用undo恢复到事务开始前,如果有commit记录,就用redo恢复到完成后
    • 注意:redo(包括undo的修改)一定要比数据页先持久化到磁盘
  • 二进制日志的录入格式:
    • Statement:记录每一条会修改数据的sql
    • Row:不记录sql语句上下文相关信息,仅仅保存哪条记录被修改
    • Mixed level:一般语句修改使用statement,一些statement无法完成主从复制的操作,就使用row
    • 场景:一条sql操作了多行数据,statement更节省空间,row更可靠
  • cpu飙到500%怎么办?
    • 先用top命令观察是不是mysqld占用导致的
    • 再show processlist查看里面跑的session情况,找出消耗高的sql
    • 如果是在数据量太大,先kill掉这些线程,调整后在跑
  • Explain:查看语句执行的详细信息
    • id:执行顺序
    • Type:join类型。all不好,全表查询性能太差const、eq_reg、ref、range、index和ALL【至少达到range】
    • Extra:Using filesort【需要额外的排序操作】/Using temporary【查询有使用临时表】:查询需要优化
    • 尽量用index,key列有值。
    • 当explain无法解决慢SQL的时候,可以用profile来进行更细致的分析,找出sql的具体分析
      • Set profiling=1;
      • Balabala……
      • Show profiles;
      • Show profile for query 1;
      • Set profiling=0;
  • 备份计划
    • mysqldump:
      • 100G以内的库,更加灵活,每天进行全量备份
      • 逻辑备份
      • mysqldump -uroot -pPassword [database name] > [dump file]
      • 备份和恢复速度都慢
    • Xtranbackup
      • 100G以上的库,备份速度更快,一周一个全备
      • 物理备份:直接拷贝表空间文件,同时不断扫描产生的redo日志并保存下来
    • _(′ཀ`」∠)_好难。。不太能理解
Table of Contents