mysql结构
应用层
连接处理、用户鉴权、安全管理
服务层
MySQL Management Server & utilities(系统管理)、SQL Interface(SQL 接口)、SQL Parser(SQL 解析器)、Optimizer (查询优化器)、Caches & buffers(缓存)
存储引擎层
存储引擎、物理文件
查找慢SQL的方式
方式一
慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句。
#该参数用来控制慢查询日志是否开启, 可取值: 1 和 0 , 1 代表开启, 0 #代表关闭
slow_query_log=1
#该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
#该选项用来配置查询的时间限制, 超过这个时间将认为值慢查询, 将需要#进行日志记录, 默认10s
long_query_time=10
方式二
show processlist : 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询 日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否 锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。
Time:显示这个状态持续的时间,单位是秒
State:显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态 Killed 、 Locked 、sending data、 Sending to client等
Info:显示这个sql语句
explain执行计划
explain
explain 之 type
explain加上format=json选项后可以查看成本信息
explain加上show warnings可以查看被改写后的语句
例如:explain format=json select * from table_name;
show warnings
索引结构
SQL优化
- 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。 - 范围查询右边的列,不能使用索引
- 不要在索引列上进行运算操作, 索引将失效。
- 字符串不加单引号,造成索引失效。
- 尽量使用覆盖索引,避免select *
- 用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到,使用 union 替换 or
- 以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。通过覆盖索引来解决 - 优化order by
排序的方式:
第一种是通过对返回数据进行排序,也就是通常说的 filesort 排序
第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index
优化目标:尽量减少额外的排序,通过索引直接返回有序数据。where 条件 和Order by 使用相同的索引,并且Order By 的顺序和索引顺序相同, 并且Order by 的字段都是升序,或者都是 降序。否则肯定需要额外的操作,这样就会出现FileSort - 优化group by
如果查询包含 group by 但是用户想要避免排序结果的消耗, 则可以执行order by null 禁止排序 - 优化分页
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。