Please enable JavaScript.

Coggle requires JavaScript to display documents.

高性能MySQL (Schema与数据类型优化 p146 (VARCHAR (适合：字符串列的最大长度远大于平均长度；列的更新很少，所以碎片不是问题…

- - - - 支持更大范围1001-9999，精度为秒
    - - 自1970年1月1日以来的秒数
      - 只使用4字节存储空间，:!: 只能到2038年
  - - - 用UNHEX()转换为整数存储，检索时用HEX()转回十六进制
  - - - MySQL存储引擎API在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，过多的列造成列转行操作代价很大
    - - “实体-属性-值”的设计模式很糟糕，需要许多self-join，容易超出限制或影响性能
    - - 修改ENUM需要ALTER TABLE，在大表上性能很差
  - - - 更新操作比反范式快
      - 重复冗余数据少，可以更好的放在内存中，执行更快
    - - 需要更多join，影响查询性能，且使索引失效
    - - 冗余数据以减少join
  - - - 用ALTER TABLE命令要求的新的表结构创建一个和原表无关的表，拷贝源表的值，然后通过重命名和删除源表完成交换
- - - - 客户端发送查询给服务器
        
        服务器查看缓存(用查询语句的哈希值进行匹配)，结果存在就直接返回
        
        未缓存则进行SQL解析／验证，生成解析树
        
        预处理验证／鉴权解析树
        
        优化器优化查询并比较所有可能的执行计划的代价，选择最优的执行计划，并为每个查询需要的表创建一个handler实例，供优化器获取表／索引的相关信息
        
        MySQL服务器层根据执行计划调用存储引擎的handler API来执行查询
        
        将结果写入缓存并返回客户端
    - - 半双工，不能同时双方互相发送数据
      - 客户端必须完整接受所有数据再结束，不能中途截断，因此要LIMIT限制查询行数
      - MySQL只有在数据全部发送给客户端后才能释放该查询所占用的资源，缓存可以使资源尽早释放
    - - Sleep
        
        该线程在等待客户端发送新的请求
      - Query
        
        线程在执行查询或将结果发送给客户端
      - Locked
        
        线程在等待表锁（InnoDB的行锁不会在这里的线程level反映）
      - Analyzing and statistics
        
        线程在收集存储引擎的统计信息并生成查询的执行计划
      - Copying to tmp table [on disk]
        
        线程在执行查询并将结果集复制到一个临时表中，on disk表示在将内存临时表写入磁盘
      - Sorting result
        
        线程在对结果排序
      - Sending data
        
        多种可能，在多个状态之间传送数据，或者在生成结果集，或向客户端返回数据
  - - - 直接对解析树分析优化，只需要优化一次
    - - 根据查询的context优化，每次查询都需要重新评估
    - - 重新定义join表的顺序
      - 将OUTER JOIN转化成INNER JOIN
      - 使用等价变换规则，合并和减少一些比较
      - 优化COUNT(), MIN(), MAX()
        
        EXPLAIN 显示Select tables optimized away表示优化器从执行计划中移除了该表，并以一个常数取而代之
      - 预估并转化为常数表达式
      - 覆盖索引扫描
      - 子查询优化
      - 提前终止查询，比如LIMIT或发现不成立的条件时立刻返回空结果
      - 等值传播
        
        如果两个列的值通过等式join，MySQL能够把其中一个列的WHERE条件传递到另一个列上
        
        在IN(...)的值特别多时，会复制到join的各个表中，影响性能
      - 列表IN()的比较
        
        MySQL排序IN()中的值，然后二分查找
    - - MySQL中，限制条件如LIMIT必须应用到UNION的每个子查询中，
        否则子查询将不受限制，将大量数据存入临时表，联合时才做LIMIT操作
      - 使用UNION ALL，否则MySQL给临时表加上DISTINCT进行去重操作，代价很高
    - - HIGH／LOW PRIORITY
        
        :!:不要在行锁的InnoDB中使用
      - DELAYED
        
        对INSERT和REPLACE有效，缓冲出入行并稍后空闲时批量写入
      - STRAIGHT_JOIN
        
        用在SELECT之后或任何两个join表名字之间。固定join的顺序
      - SQL_SMALL/BIG_RESULT
        
        SQL_SMALL_RESULT提示结果集小到可以在内存放，SQL_BIG_RESULT建议结果集大，放到磁盘临时表做排序
      - SQL_BUFFER_RESULT
        
        将查询结果集放入一个临时表，并尽快释放表锁
      - SQL_CACHE／SQL_NO_CACHE
        
        告诉MySQL是否该缓存结果集
      - SQL_CALC_FOUND_ROWS
        
        计算除去LIMIT后该查询会返回的结果数
      - FOR UPDATE／LOCK IN SHARE MODE
        
        对SELECT的结果中的行加锁。:!:可能造成锁争用，尽量避免使用
      - USE／IGNORE／FORCE INDEX
        
        提示优化器使用或不用哪些索引来查询
    - - COUNT(*)性能最好，忽略所有的列直接统计行数。只需要估计值时可以用EXPLAIN代替
    - - 确保ON或者USING子句中的列上有索引。在创建索引时就要考虑到join的顺序，只需要在join的右手端的表上建立索引
      - 确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，否则MySQL优化器无法利用索引
    - - 缓存完整的SELECT查询结果
      - 当查询涉及的任何表发生变化，和该表相关的所有缓存都失效
      - 最好默认关闭缓存或限制缓存大小，因为缓存是影响服务器扩展性的因素，可能成为整个服务器的资源竞争单点，在多核服务器上还可能造成僵死
      - 如果查询包含任何用户自定义函数／子查询／存储过程／用户变量／临时表／系统表／包含列级别权限的表，都不会被缓存
      - :!:如果查询缓存很大或碎片很多，会带来很大系统消耗。查看缓存的操作加排他锁
      - MVCC导致只能在当前事务提交之后才能更新缓存，长时事务会大大降低缓存命中率
      - :!:缓存使用很大内存空间时，缓存失效操作可能造成系统僵死一会，因为所有操作都要等待一个全局锁
      - Qcache_hits / Qcache_inserts 的比值大于3:1时认为缓存是有效果的，最好达到10:1。否则可以考虑禁用缓存
      - 如果表上有任何的锁，锁住期间任何查询都无法被缓存
  - - - 对每一个表的每一个事件最多只能定义一个TRIGGER
      - 只支持基于行的TRIGGER，当变更大量数据行时性能低下
- - - - 所有值按顺序存储，每一个叶子page到根的距离相同
      - 支持的查询类型
        
        全值匹配
        
        匹配最左前缀
        
        匹配列前缀
        
        匹配范围值
        
        先全值匹配，再范围匹配
        
        只访问索引，不访问数据行
      - 必须从索引的最左列开始查询，否则无法使用索引
      - 不能跳过索引中的任何一列，除非用IN(所有选项)来满足
      - 若某一列使用范围查询，其之右的列无法使用索引优化查找
      - 有序存储数据，因此可以用于ORDER BY和GROUP BY操作
    - - 基于hashtable实现，只支持精确匹配索引所有列的查询
      - MySQL只有Memory存储引擎显式支持hash索引
      - 为每一行中被索引的全部列计算一个hash值，存储在索引中（不存储字段值），因此必须使用所有索引列匹配
      - :star:适合用于数据仓库中星型schema的查找表（大量join）
      - adaptive hash index
        
        InnoDB发现某些B-Tree索引值被频繁使用时，为其在内存中再构建一个hash索引
    - - 空间索引，无须前缀查询（MySQL的支持并不完善，谨慎使用）
    - - InnoDB在同一结构中保存B-Tree和数据行
      - 数据行实际存放在leaf page中，聚簇表示数据行和相邻的键值紧凑存储在一起
      - 一个表只能有一个聚簇索引
      - 只能用primary key建立聚簇索引
      - 优点
        
        可以把相关数据保存在一起，最大限度提高IO密集型应用的性能
        
        数据访问更快
        
        使用覆盖索引扫描的查询可以直接使用页节点中的主键值
      - 插入速度严重依赖于插入顺序，按照主键顺序插入是加载数据到InnoDB表中最快的方法，
        否则最好加载完成后OPTIMIZE TABLE重新组织一下表（去除随机插入新数据造成的页分裂造成的碎片）
        :!:顺序插入在高并发插入时可能造成大量间隙锁竞争，影响性能，AUTO INCREMENT锁机制也可能带来性能瓶颈
      - 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
      - 可能导致全表扫描变慢，尤其是当行比较稀疏，或者由于页分裂导致的数据不连续存储
      - 二级索引只保存主键值，还要再去聚簇索引中查找对应的行
    - - 索引包含（覆盖）所有需要查询的字段的数据，无需再去查找数据行
      - 优点
        
        极大减少数据访问量，减少磁盘IO
      - :!:MyISAM在内存中只缓存索引，数据依赖操作系统缓存，因此会导致大量系统调用，严重影响性能
      - 查询被覆盖索引满足时，EXPLAIN的Extra列会显示“Using index”
      - deferred join p208
        
        延迟对列的访问，用子查询在join之前利用覆盖索引
  - - - MySQL可以自动OR／AND多个索引来优化（EXPLAIN的Extra项显示该优化，:!:当出现该情况时最好审视表，索引的设计问题）
      - 当MySQL对多个索引AND时 - 通常意味着需要一个包含多列的索引，而不是多个独立的单列索引
        当MySQL对多个索引OR时 - 通常耗费大量CPU内存资源在算法的缓存，排序和合并操作上，尤其当索引选择性不好时
        *优化器不会把这些成本计算到查询成本中，使其难以被监控到
    - - :star:在避免随机IO，保证排序的前提下，将选择性最高的放到最前列
- - - - 一个数据行被分开存储在多个地方的多个片段中
      - InnoDB不会发生行碎片，会移动短小的行并重写到一个片段中
    - - 逻辑上顺序的页或行在磁盘上不是顺序存储，导致全表扫描和聚簇索引扫描不能利用顺序IO，有很大性能影响
    - - 数据页中有大量空余空间，导致服务器读取大量不需要的数据，浪费性能
- - - - 保留索引块的第一个值（perform），其他值仅保存相对区别（performance -> 7，ance）。但倒序扫描更慢了
- - - - mysqlbinlog - MySQL官方提供的查看binlog内容的工具，可以读取远程服务器的binlog，也可以制定开始位置／时间。每个at代表一个事件
      - SHOW BINLOG EVENTS IN ‘log_name’ FROM position LIMIT offset, row_count
    - - ROW（默认）
        
        仅保存记录被修改细节，不记录当时的context
        
        优点：可以保证任何类型的修改都会被记录，加快回放效率
        
        缺点：所有改动都被记录，日志量更大（5.6版本支持参数binlog_row_image=minimal可以只记录改动的列，大大减少日志量）
      - Statement
        
        记录每一条修改数据的SQL statement及其context
        
        优点：避免记录每一行数据的变化，大大减少日志量，节约IO提高效率
        
        缺点：必须记录context以确保回放正确性。主从复制时存在部分function及stored procedure结果不一致
      - MiXED
        
        同时使用ROW和Statement格式。（如今ROW格式已经被优化，一般来说ROW就够用了）
  - - - master将数据变更记录到binlog
        
        slave连接master，请求指定binlog文件的指定位置之后的日志数据
        
        master收到slave的请求，负责复制的IO进程会读取并传输相应日志数据及其metadata
        
        slave的IO进程收到数据后追加到自己的relay-log中（append-only），保存当前binlog文件名和offset以备下次请求
        
        slave的MySQL进程检测到relay-log更新的内容，解析并执行
      - 通过show slave status查看复制进度
        
        看Relay_Master_Log_File 和 Maser_Log_File 是否一样，不一样则落后较多，需要从master获取binlog status判断差距
        
        再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件
        
        可以使用pt_heartbeat工具来监控主备复制的延迟
    - - :question:
- - - - connect to DB system via JDBC etc.
    - - interpret SQL request from client and then forward to database server
      - analysing SQL protocol, routing, filtering, CQRS, joining results, sorting, partitioning, etc.
    - - load balancing in between client and dbproxy
      - cost one more connection layer, instead store list of dbproxy in client, and let client do load balancing
    - - Master-Slave DB cluster
      - Master has strong ACID consistency, and replicate updates to Slaves via binlog. Slaves have eventually consistency.
    - - maintain dbgroup details like splitting rules for master elections
      - HA via multiple Master-standby
    - - daemon process on each db server
      - for monitoring, failover, install, upgrade db etc.
  - - - 事务模型
        
        Entity Group
        
        Idea: usually a tx only update a single Entity Group
        
        strong ACID consistency within the same Entity Group, eventually consistency across multiple Entity Groups. Avoiding distributed transactions
        
        multiple rows in multiple tables that belong to the same entity (primary key, tx, particular user, etc), stored in the same physical DB server
      - ACID 强一致
        
        Atomic
        
        原子操作，全部成功或全部取消，不能只做一部分
        
        Consistent
        
        :question: 用户查询可以得到确定性的一致的结果
        
        Isolated
        
        一个事务做的修改在最终提交之前对其他事务是不可见的
        
        SET TRANSACTION ISOLATION LEVEL 命令可以设置隔离级别
        
        Durable
        
        一旦事务提交，修改要持久保存，即使系统崩溃重启后也不丢失
      - 隔离级别
        
        READ UNCOMMITTED
        
        脏读，一个事务可以读取另一事务尚未commit的修改
        
        READ COMMITTED
        
        一个事务只能读取另一事务已经提交的修改，但同一事务中的同一查询多次运行可能读取到不同数据，因为其他事务在两次查询间隔修改了数据（幻读）
        
        REPEATABLE READ
        
        保证同一事务中同一查询多次执行读取到的数据相同，通过Multi-Version Concurrency Control保证事务只能看到它创建时的数据快照
        
        SERIALIZABLE
        
        任何并行事务的执行结果必须和它们串行执行的结果一致，可能导致大量锁开销和锁争用
    - - 第二层：大多数核心服务功能，查询解析／分析／优化／缓存，以及内置函数（时间，日期，数学，加密等），所有跨存储引擎功能：Stored Procedures，triggers，views...
      - 第三层：存储引擎，负责数据等存储和提取，存储引擎API封装底层存储引擎的不同实现，提供统一的访问接口
      - 第一层：网络连接处理／鉴权／安全处理等
    - - 每个客户端连接在server中对应一个线程（线程池化），建立连接时先认证身份，再验证该用户的操作权限
    - - 将查询语句解析成解析树并进行优化，包括重写查询，决定表的读取顺序，选择合适的索引
      - 如果SELECT查询的结果以及存在缓存里，直接返回缓存结果
      - :!:MySQL不能并行执行查询
    - - 存储引擎 1）先将改动追加到磁盘上的append-only日志，2）再在内存中执行修改，3）在后台将内存修改写入磁盘，4）成功写入磁盘后清空日志中相应的记录
- - - - 锁定整个表，开销最小
    - - 最大程度支持并发处理，开销最大
      - InnoDB，XtraDB引擎使用
    - - InnoDB检测到死锁的循环依赖时，回滚持有行级排他锁最少的事务
    - - 隐式锁。InnoDB采用，在事务执行过程中，随时可以锁定，锁只有在执行COMMIT或ROLLBACK时才会所有锁同时释放。
    - - SELECT ... LOCK IN SHARE MODE ／ FOR UPDATE