image

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1. mysql系统自带的数据库 ,都为memory 引擎
2. 注意字符集 mysql是c/s架构
+ 服务端 character_set_server、character_set_system
+ 客户端 character_set_client、character_set_connection、character_set_results

3. 权限授权依据 SCHEMA_PRIVILEGES-> TABLE_PRIVILEGES ->COLUMN_PRIVILEGES ->USER_PRIVILEGES

4.实体对象相关重要 INNODB_SYS_COLUMNS 、ENGINES 、PLUGINS 、SCHEMATA
5.管理维护依据相关重要表 GLOBAL_STATUS ,GLOBAL_VARIABLES,SESSION_STATUS,SESSION_VARIABLES、PARTITIONS
、PROCESSLIST、INNODB_BUFFER_POOL_STATS
、INNODB_BUFFER_PAGE*、
6. 表及表空间 TABLES,TABLESPACES,INNODB_SYS_TABLES ,INNODB_SYS_TABLESPACES
7. 优化相关 OPTIMIZER_TRACE 、PROFILING
8. 事务和锁相关 INNODB_LOCKS、INNODB_LOCK_WAITS、INNODB_TRX
1
2
3
4
5
6
7
8
9
10
11
12
13
常用  show [global] status like [%com_%|innodb_%|connections%|uptime%|slow_queries%]
常用 show processlist 当前线程状态、锁状态、
常用 explain sql分析低效sql执行计划
常用 explain extended [sql] show warnings 显示filterd 字段和 warnings中 message信息
explain partitions sql 查看语句访问分区

常用 show profiles ;show profile [cpu io all ] for queryId;

常用 show variables like '%optimizer_trace%';
select * from information_schema.optimizer_trace

//set optimizer_trace='enabled=on'; --- 开启trace
//set end_markers_in_json=on; --- 增加trace中注释

典型mysql体系架构 C/S 架构 (四层架构设计)

image

典型mysql执行流程

image

  • 典型mysql执行流程
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    1. 连接
    1.1 通过mysql客户端服务端通信协议发起连接并发送Query请求,在此连接管理模块设置监听
    1.2 服务端连接线程接收Query请求,check 用户授权
    1.3 check通过,线程管理模块从‘连接池’ 捞一个缓存的空闲连接线程对接客户端Query

    2. 处理
    2.1 check 查询缓存是否开启,若开启,Query 做hash运算,计算key值并check缓存是否命中,命中就进入结果处理返回流程
    2.2 sql语法解析,包括词法解析、语法规则合法性验证并生成对应解析树
    2.3 预处理,解决词法语法不能解决的语义问题、检查权限并生成新的解析树
    2.4 查询优化器,优化器选择最优算法生成sql执行计划
    2.5 check访问控制,包括表、字段访问权限,table-cache及是否获取锁,查询执行引擎根据不同meta信息调用相应存储引擎接口处理,若日志打开并且引擎需要可记录二进制日志,完成sql执行计划
    3. 结果
    3.1 Query 请求完成后,将结果集返回给连接管理模块"监听标识"
    3.2 check 是否需要放入插入缓存,包装返回给mysql客户端结果
    3.3 断开与客户端连接,释放资源

典型索引优化原则

索引原则1
搜索的索引列,不一定是所要选择的列

索引原则2
使用唯一索引,索引基数越大越好,注意数据倾斜导致索引反而使搜索变慢

索引原则3
使用短索引,前缀索引,较小的索引涉及的磁盘io较少,相应的查询更快,且较短的健值,在索引缓存有限情况下能容纳更多健值,节约资源

索引原则4
利用最左前缀匹配原则,根据业务需要合理建立多列组合索引

索引原则5 不过度索引,一般单表不超过5个,太多索引不仅浪费磁盘空间,导致写性能下降,甚至导致mysql 查询优化找不到最好的索引,发生全表扫描,在数据量大或并发情况下严重影响性能

索引原则6 根据引擎合理使用索引 比如myisam、innodb 分别对应的 主键索引与二级索引B-Tree数据结构区别

典型索引业务场景

  1. 匹配全值 精确查找数据,根据需要建立条件索引
  2. 匹配值范围查询,根据索引类型,范围查询适合Btree索引类型
  3. 匹配最左前缀,多列组合索引原则,避免二次回表查询
  4. 匹配列前缀
  5. 列名索引 null 也能使用索引
  6. ICP>=5.6 Index Condition Pushdown 注意Explain Extra:using index condition
  7. % 开始的like查询 不能利用Btree索引 (可以考虑全文索引|考虑二级索引回表查询)
  8. 数据类型隐式转换不会使用索引,(保持类型一致即可)
  9. or 分割的条件,前有索引,后没索引则不会使用索引

查看索引使用情况

  1. information_schema.GLOBAL_STATUS
  2. show status like ‘handler_read%’

    观察
    handler_read_key(太小)/handler_read_rnd_next (很大) 说明索引使用不理想,考虑优化

MRR (Multi-Range Read)

随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段
https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

1
2
3
4
5
6
7
8
9
10
11
多范围读取优化提供了以下好处:

基于索引元组,MRR使数据行能够按顺序访问,而不是按随机顺序访问。
服务器获取满足查询条件的一组索引元组,并按照数据行ID顺序对它们进行排序,并使用排序后的元组按顺序检索数据行。
这使得数据访问效率更高,成本更低。

MRR支持批量处理需要通过索引元组访问数据行的操作的密钥访问请求,
例如范围索引扫描和使用连接属性索引的等连接。
MRR迭代一系列索引范围以获取合格的索引元组。
随着这些结果的积累,它们将用于访问相应的数据行。
在开始读取数据行之前,不需要获取所有索引元组。

ICP( Index_Condition_Pushdown)

1
2
3
4
5
6
1. ICP能减少Server层访问存储引擎的次数和引擎层访问基表的次数
2. 对于InnoDB表,ICP只适用于辅助索引
3. 默认关闭 set optimizer_switch="index_condition_pushdown=on
4. 主要对where的各种条件查询索引优化处理
5. 当 SQL 使用覆盖索引时但只检索部分数据时,ICP 无法使用
6. ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

BAK (Batched Key Access) >=5.6 出现

1
2
3
4
5
6
7
8
9
10
11
原理:

对于多表join语句,当MySQL使用索引访问第二个join表的时候,
使用一个join buffer来收集第一个操作对象生成的相关列值。
BKA构建好key后,批量传给引擎层做索引查找。
key是通过MRR接口提交给引擎的. 这样,MRR使得查询更有效率


BKA主要是指在被join表上有索引可以利用,
那么就在行提交给被join的表之前,
对这些行按照索引字段进行排序,因此减少了随机IO

BNL (Block Nested-Loop ) >=5.1

1
用于当被join的表上无索引