MySql优化

1.sql优化方案

  • 硬件优化 : 服务器的硬件优化

  • 系统配置: mysql的配置优化, 如 最大并发数my.ini, 调整缓存大小等

  • 数据库表结构: 既包括大方向的读写分离,分库分表, 也包括表本身的设计合理化

  • sql及索引: 添加适当索引(index), sql语句也很重要
    主要需要了解的是索引和sql语句的优化.
    降低磁盘IO次数.

2.索引index

  • [ ] 索引就是数据结构, 可以提高mysql的查询效率.

  • B+Tree 自平衡多叉查找树

  1. 1. 每个根节点不存数据,只存索引, 所以度很大,使得树的高度很矮,减少IO次数
  2. 2. 数据全部存放在叶子节点, 叶子不存储指针.
  3. 3. 每个叶子节点通过顺序访问指针连接, 提高了范围查询的效率.
  • Hash
  1. * mysql某些类型的引擎使用hash结构存储索引, 查找单行数据很快,但是
  2. 1. 无法用于排序
  3. 2. 只支持等值查找
  4. 3. 存在hash冲突

  • [ ] Innodb和MyIsam引擎

  • Innodb 聚簇索引

    索引和数据存在同一个文件中.

创建是默认根据主键生成一个B+tree, 如果没有指定主键, 选择第一个唯一索引, 如果都没有, 则会生成一个隐藏的rowId,并以此创建索引树.

主键索引的叶子节点存放数据, 后续索引的叶子节点存放其对应数据的主键, 再通过主键索引到数据.

  • MyIsam 非聚簇索引

    索引和数据分开存放. 叶子节点存放数据地址.

不指定索引就不会创建.

主键索引树, 和后续的索引树的查找效率一样.

  • 索引的分类

  • 普通索引index: 加速查找. create index [索引名] on 表(字段)

  • 唯一索引: 包括主键索引primary key 和 唯一索引unique, 加速查找, 约束字段

  • 联合索引: 多个字段组合成的索引. create index [索引名] on 表(字段a,b,c) 最左原则.

    • ``` 最左匹配原则:
  1. 索引生效顺序是按照创建时的字段顺序.
  2. 必须从左到右,如果左边的字段不存在,那么后续的字段的索引也不会生效. 如a,b,c,如果查询的字段没有a,则b和c的索引都不会生效. 如果没有c, 则a和b的索引会生效.
  3. 如果某个字段使用了范围查询, 那么它本身的索引会生效, 后续的字段索引会失效.
  4. 模糊查询, %在前的时候,该字段索引会失效. ```
  • 全文索引: fulltext, 用于搜索很长的文章的内容时候,效果最好. (我们使用ES去代替了它)

  • [ ] 索引字段的选择

  • 主键自动创建唯一索引: primary

  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段, 即外键关系的字段 应该建立索引
  • 查询中排序的字段 order by 字段
  • 查询中统计或分组的字段 group by 字段

3.sql语句性能优化

  • [ ] 性能分析

  • 慢查询日志

    是mysql提供的一种日志记录, 用来记录在mysql中响应时间超过阈值的语句.

默认情况下是关闭的, 同时默认不记录管理语句和不使用索引进行查询的语句

查看: show varibales like ‘%slow_query_log%’

开启: set globle slow_query_log=1 , 只对当前数据库生效, 重启后会失效.

如果要永久生效, 则要修改配置文件. slow_query_log=1 , slow_query_log_file=地址

  • Explain(执行计划)

    使用explain关键字, 可以模拟优化器执行的sql语句.

使用Explain, 放在sql语句最前面.

对响应结果进行分析和优化.

  • 参数详解
  • id 执行顺序
  • 如果id值相同, 代表优先级一致, 从上到下依次执行.
  • 如果id值不同, 值越大优先级越高, 越先执行.
  • 既有相同也有不同, 按照上述优先级规则依次执行.
  • select_type 查询类型
  • simple 简单select查询, 查询中不包括子查询或union
  • primary 查询中若包含任何复杂的子查询, 最外层查询责备标记成primary
  • subquery 在select或where中包含的子查询
  • derived 在from列表中包含的子查询被标记为derived(衍生) , 把结果放在临时表当 union
  • union result 从union表获取结果select, 两个union合并的结果集在最后
  • type 访问类型

    执行效率: system > const > eq_ref > ref > range > index > all

  • system: 特殊情况, mysql系统表, 通过索引字段只能匹配一个结果.
  • const: 通过索引字段值, 只能匹配一个结果. 如’ 查询条件为 id = ? ‘
  • eq_ref: 唯一性索引扫描, 两个关联查询连接条件为 ‘where a.id = b.id’, 必须是有唯一约束的.
  • ref: 索引扫描. 类似eq_ref, 但条件字段不再唯一.
  • range: 范围索引. 如’ where id > ? ‘

以上类型执行效率都不错, 不需要优化.

  • index: 覆盖索引. 即查询的内容字段是索引字段. 如 ‘ select id from tb ‘
  • all: 没有利用到任何索引, 触发的是全表扫描, 性能最差.

主要优化 all 类型的sql语句.

  • 优化实战

  • [ ] 避免索引失效

  • [x] ```
    — 最左匹配原则.
    — 范围条件右边的索引失效. 如: where a > 10 and b = 5, 如果b字段有索引会失效
    — 不要在索引列上做任何操作. 如: 加减乘除运算, trim()去空格等
    — 使用不等于(!= 或 <>)会导致索引失效. 如: where a != 5
    — is not null 无法使用索引.
    — like以通配符开头(%..)索引失效. %在后,属于范围查询
    — 字符串不加引号索引失效.
    — 使用or连接导致索引失效. or连接前后的字段 必须都有索引,索引才会生效.
    — 必要情况下可以使用覆盖索引.

  • [ ] 排序与分组优化

  • [ ] ```
    order by 语句, 前面的sql语句必须有用到索引, 那么order by 字段的索引(如果有)才能生效.

  • [ ] 大数据量分页优化

  • [ ] ```
    将单条sql查询语句, 优化成子查询.

  • [ ] 小表驱动大表

  • [ ] ```
    简单查询,mysql默认优化小表驱动. 在涉及关联查询的情况下, in 和 exists, 需要注意

  • [ ] max函数优化

  • [ ] ```
    总而言之,就是尽量使用索引字段.

4. MVCC
  1. MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存
  2.     多版本控制: 指的是一种提高并发的技术。
  3.     基于undo_log实现.
  5.     MVCC + 悲观锁
  6.         MVCC解决读写冲突，悲观锁解决写写冲突
  7.     MVCC + 乐观锁
  8.         MVCC解决读写冲突，乐观锁解决写写冲突