Mysql基础知识小记

Mysql简介

• Mysql 5.7GA，当前最稳定版本

• 最流行的开源关系型数据库

• 支持事务

Mysql体系架构

• 分为Server层和存储引擎层

• 存储引擎层（innodb）：buffer缓存表数据，文件层做持久化存储，还有各种各样的线程用于真正执行读写操作

• 主从复制

SQL优化原理与实践

• 表数据组织结构：索引组织表(Innodb)和堆表（MyIAM/Oracle），区别在于叶子结点是否有数据行：索引组织表叶子结点有完整的数据行，堆表只有指向数据行的索引。

• 二级索引存主键信息，指向聚集索引

• 主键索引物理结构：Tablespace(表空间，一个表的所有索引数据都放在这里) -> Segment（段，主键索引或二级索引的叶子结点在一个段里，非叶子结点在一个段里） -> Extent（区，空间分配单元）
  -> Page（页，IO读取单元） -> Row（行）

• sql执行计划：查数据时的顺序和方法

• explain关键字：type：访问类型，在表中查找数据的方法类型：（从左到右性能逐渐变差）system -> const -> eq_ref -> ref -> range ->index -> all
  key: Mysql在查询中使用的索引，若没有使用索引，显示为NULL
  rows：Mysql根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

• 索引选择性：索引字段组合的唯一值个数，唯一值越多，查询单个组合时扫描的数据量就越少，索引效率就越高
  show index from table_name;

• 索引前缀性：组合索引中，where条件满足索引字段顺序的前缀匹配，就能用上索引。

• 索引优化分析：1. 执行计划中type为all，index的，必须优化
  2. type为range、ref且rows较大的，需要优化
  3. 实际返回的行数和执行计划中rows有数量级差距的，需要优化

• SQL优化实践：1. 加索引，适用于where条件中，有区分度高的字段，但是该字段没有索引
  2.加复合索引，适用于where条件中，单个字段区分度低，但是多个字段联合起来区分度高。
  3. 强制使用高效的索引，用法：from table1 use index index,=，这种方式是在特定的场景和时间点所做的临时优化，用于急救或者调试，禁止线上使用。
  4. 根据业务特征进行SQL改写
  5. dba的一些其他手段（救急用）：根据优化器特性，进行改写；修改Mysql参数；升级数据库。

• SQL使用禁区：1. 在where条件的列名上做函数运算，会导致索引失效
  2. 结果集中包含不需要的字段
  3. 使用%做前缀查询，会导致索引失效，可以放在后缀。
  4. 字符与数字的混用
  5. 大量偏移的limit
  6. 非主流sql写法

• 删除和更新数据：1. 确定执行计划最优（update和delete也可以进行explain）
  2. 每次影响的行数在10000以内，避免事务过大，否则会导致：锁范围过大，行锁冲突概率增加，阻塞其他请求；主从延迟；大事务导致undo占用大量空间。