MySql

索引

概念

• 排好序的数据结构

数据结构区分

• Hash表
  • 通过hash值定位
• 二叉树
  • key -value 有倾斜的问题
• 红黑树
  • 树高过深的问题
• B树
  • 聚集索引，且叶子结点无链接
• B+树
  • 非聚集索引
  • 叶子结点之间也是顺序排序，有链接

其他

• 聚集索引
  • 任意一个结点都有索引数据，和行数据
• 非聚集索引
  • 数据仅在叶子结点，根节点，父结点仅仅存索引数据
• 二级索引
  • 根据非主键构成的索引，叶子结点存的是索引列值和主键ID
    • 涉及回表
• 组合索引
  • 多个列组成的二级索引
  • 最左原则
  • 索引下推
• 覆盖索引
  • 不是真的索引类型
  • 就是查询的字段在索引树里，不用回表

explain

官网地址

• https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

列信息

• id
  • 执行顺序，越大越优先，相同上面的先执行
• select_type
  • 1：simple
    • 简单查询
  • 2：primary
    • 负责查询的最外层select
  • 3：subquery
    • 衍生查询：from ( * )
  • 4：derived
  • 5：union
• table
  • from 后面的名称
  • 有子查询是，tableg格式： 格式，Id = N ,先执行Id = N的查询
• type
  • 访问类型
  • system
  • const
  • eq_ref
  • range
  • index
  • all
  • 依次效果最慢，调优至 rang or ref
• possible_keys
  • 可能用到的key，具体不一定
• key
  • 实际采用的索引
• key_len
  • 用到的索引长度
  • 计算规则:
    • 1：字符串
    • 2：数字类型
    • 3：时间类型
    • 4：null
• ref列
  • 对应联合查询关联的字段
• rows
  • 可能会扫描多少行，预估值
• extra
  • 额外信息
  • 出现的值
    • using index
    • using where
    • using　index condition
    • using temporary
    • using filesort
      • 效率不行，内存文件排序需要优化
      • max_length_for_sort_data(默认1024字节) 判断是否单路，双路
      • 涉及sort_buffer
        • 存取出的数据的
      • 单路排序
        • 一次性取出所有字段，拿到内存取排序
      • 双路排序
        • 回表的方式，取排序字段跟id,排行再用id回表
    • select table optimized away

最左前缀

• 在联合索引中

Mysql机构

客户端

server层

• binlog文件
  • 二进制文件，基础cud操作
  • 开启bin-log
    • my.conf
      • log-bin=/地址
      • binlog-format=
        • ROW
          • CANNAL基于此方式同步数据
        • statement
        • mixed
  • 查看bin-log
  • 恢复bin-log
    • 全部恢复
      • /usr/local/mysql/bin/mysqlbinlog —no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
    • 制定位置恢复
      • /usr/local/mysql/bin/mysqlbinlog —no-defaults —start-position=”408” —stop-position=”731” /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
    • 制定时间区间恢复
      • /usr/local/mysql/bin/mysqlbinlog —no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 —stop-date= “2018-03-02 12:00:00” —start-date= “2019-03-02 11:55:00”|mysql -uroot -p test(数据库)
• 查询缓存
  • 命令
    • show status like’%Qcache%’; //查看运行的缓存信息
  • 结果
    • Qcache_free_blocks
    • Qcache_free_memory
    • Qcache_hits
    • Qcache_inserts
    • Qcache_lowmem_prunes:
    • Qcache_not_cached
    • Qcache_queries_in_cache
    • Qcache_total_blocks
  • 缓存很鸡肋，8.0后取消了
• 词法分析器
• 优化器
• 执行器

引擎层

• innodb
  • buffer-pool
  • 事务
• myisam
• memory

sql优化

其他

• 1：不要在索引做任何操作，不然不会走索引
• 2：不等于（！=或者<>），not in ，not exists
• 3“:is null,is not null 一般情况下也无法使用索引
• 4：字符串不加单引号索引失效
• 5：少用or或in，用它查询时，mysql不一定使用索引
• 6：like
  • 1：使用覆盖索引，查询字段必须是建立覆盖索引字段
  • 2：如果不能使用覆盖索引则可能需要借助搜索引擎
  • 3：以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
  • 4：like KK% 一般情况都会走索引
  • 5：like会走索引下推
• 7：>: 大于比较也有可能会走，可能不走索引

强制走索引

• force (索引)
• 不代表rows少，时间就少

覆盖索引

• 查询字段，减少回表

索引下推

• 联合索引
  • 5.6后出现
    • 会依次比较联合索引的列

最左法则：

• 跳过就不走索引，必须依次

trace工具用法

• 开启trace
  • set session optimizer_trace=”enabled=on”,end_markers_in_json=on;
  • 详见：https://www.yuque.com/u1038157/wyx/lpmcgs

order by Group by

• 走不走体现在key_len上
• 同样遵守最左原则

分页优化

join 优化

• 关联算法
  • 1：嵌套循环连接 Nested-Loop Join(NLJ) 算法
  • 2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

索引涉及原则

1：代码先上，索引后上

2：联合索引尽量覆盖条件

3：不要在小基数上建索引

4：长字符可以采用前缀索引

5：where 与order by 冲突优先where

6：基于具体的SQLy优化

事务