mysql 获取当前数据库schema

1. 查找需要优化的SQL

    1.1 查看SQL的执行频率

        MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

    我们通常比较关心的是以下几个统计参数：

    

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

    Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

    1.2 定位低效率执行SQL

        两种方法：

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=fifile_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件
• show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

    1.3 explain分析执行计划

        通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

explain select * from tb_item where id = 1 ;

字段	含义
id	select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION （ UNION 中的第二个或者后面的查询语句）、SUBQUERY （子查询中的第一个 SELECT ）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为 ( system ---> const -----> eq_ref ------> ref-------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------>all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
Extra	执行情况的说明和描述

        1.3.1 环境准备

CREATE TABLE `t_role` (

    `id` varchar ( 32 ) NOT NULL ,

    `role_name` varchar ( 255 ) DEFAULT NULL ,

    `role_code` varchar ( 255 ) DEFAULT NULL ,

    `description` varchar ( 255 ) DEFAULT NULL ,

    PRIMARY KEY (`id`),

    UNIQUE KEY `unique_role_name` (`role_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

CREATE TABLE `t_user` (

    `id` varchar ( 32 ) NOT NULL ,

    `username` varchar ( 45 ) NOT NULL ,

    `password` varchar ( 96 ) NOT NULL ,

    `name` varchar ( 45 ) NOT NULL ,

    PRIMARY KEY (`id`),

    UNIQUE KEY `unique_user_username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

CREATE TABLE `user_role` (

    `id` int ( 11 ) NOT NULL auto_increment ,

    `user_id` varchar ( 32 ) DEFAULT NULL ,

    `role_id` varchar ( 32 ) DEFAULT NULL ,

    PRIMARY KEY (`id`),

    KEY `fk_ur_user_id` (`user_id`),

    KEY `fk_ur_role_id` (`role_id`),

    CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON

    DELETE NO ACTION ON UPDATE NO ACTION,

    CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON

    DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

insert into `t_user` (`id`, `username`, `password`, `name`) values ( '1' , 'super' , '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe' , ' 超级管理员' );

insert into `t_user` (`id`, `username`, `password`, `name`) values ( '2' , 'admin' , '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe' , ' 系统管理员' );

insert into `t_user` (`id`, `username`, `password`, `name`) values ( '3' , 'itcast' , '$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui' , 'test02' );

insert into `t_user` (`id`, `username`, `password`, `name`) values ( '4' , 'stu1' , '$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa' , ' 学 生1' );

insert into `t_user` (`id`, `username`, `password`, `name`) values ( '5' , 'stu2' , '$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm' , ' 学 生2' );

insert into `t_user` (`id`, `username`, `password`, `name`)  values ( '6' , 't1' , '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe' , ' 老师 1' );

 

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ( '5' , ' 学生' , 'student' , ' 学生 ' );

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ( '7' , ' 老师' , 'teacher' , ' 老师 ' );

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ( '8' , ' 教学管理员' , 'teachmanager' , ' 教学管理员 ' );

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ( '9' , ' 管理员' , 'admin' , ' 管理员 ' );

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ( '10' , ' 超级管理员' , 'super' , ' 超级管理员 ' );

INSERT INTO user_role(id,user_id,role_id) VALUES ( NULL , '1' , '5' ),( NULL , '1' , '7' ), ( NULL , '2' , '8' ),( NULL , '3' , '9' ),( NULL , '4' , '8' ),( NULL , '5' , '10' ) ;

        1.3.2 explain 之 id

id 字段是 select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。 id 有相同，也有不同，同时存在。 id 相同的可以认为是一组，从上往下顺序执行；在所有的组中， id 的值越 大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , ( SELECT * FROM user_role ur WHERE ur.`user_id` = '2' ) a WHERE r .id = a .role_id ;

常见的取值含义，如下表所示：

select_type	含义
SIMPLE	简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DERIVED	在 FROM 列表中包含的子查询，被标记为 DERIVED （衍生） MYSQL 会递归执行这些子查询，把结果放在临时表中
UNION	若第二个 SELECT 出现在 UNION 之后，则标记为 UNION ； 若 UNION 包含在 FROM 子句的子查询中，外层SELECT 将被标记为 ： DERIVED
UNION  RESULT	从 UNION 表获取结果的 SELECT

        1.3.4 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为:

type	含义
NULL	MySQL 不访问任何表，索引，直接返回结
system	表只有一行记录 ( 等于系统表 ) ，这是 const 类型的特例，一般不会出现
const	表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where 列表中， MySQL 就能将该查询转换为一个常亮。 const 于将 "主键 " 或 " 唯一 " 索引的所有部分与常量值进行比较
eq_ref	类似 ref ，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL 的区别为 index 类型只是遍历了索引树， 通常比 ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 

system > const > eq_ref > ref > range > index > ALL

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到 ref.

        1.3.5 explain 之 key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。

key ： 实际使用的索引， 如果为 NULL ， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好

        1.3.6 explain 之 rows

            语句执行扫描的数据库行数，越少越好；

其他的额外的执行计划信息

extra	含义
using  fifilesort	说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为“文件排序 ”, 效率低。
using  temporary	使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index	表示相应的 select 操作使用了覆盖索引， 避免访问表的数据行， 效率不错

    1.4 show profile 分析SQL

Mysql从5.0.37版本开始增加了对 show profifiles 和 show profifile 语句的支持。show profifiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

select @@have_profiling;    --  查看是否支持profile；

set profiling=1;     --开启profiling 开关；

开启开关，执行业务SQL后，再执行show profile指令，来查看SQL语句执行的耗时：

再通过show profifile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后， MySQL 支持进一步选择 all 、 cpu 、 block io 、 context switch 、 page faults 等明细类型类查看MySQL 在使用什么资源上耗费了过高的时间。例如，选择查看 CPU 的耗费时间 ：

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的 cpu
CPU_system	系统占有的 cpu

    1.5 trace 分析优化器执行计划

        MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。打开trace ，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace= "enabled=on" ,end_markers_in_json= on ;     --打开trace ，设置格式为 JSON；

 

SET  optimizer_trace_max_mem_size= 1000000 ;    -- 最大能够使用的内存大小；

 

-- 执行SQL语句；

 

select * from information_schema .optimizer_trace \G;    -- 查看执行计划

2.1 SQL 优化方向

    2.1.1 对表建立合例的索引；

SET UNIQUE_CHECKS=0；关闭事务的自动提交SET AUTOCOMMIT=0；使用多个值表的insert语句；对于 InnoDB 类型的表，确保插入的主键值升序；将SQL编写为文件保存，使用load data local infile '文件路径名称' into table ·插入数据的表名· fields terminated by '数据列分隔符' lines terminated by '数据行分隔符'；

    2.1.3 优化order by 语句：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort；当实在无法避免FileSort排序时，对于Filesort ， MySQL 有两种排序算法：

• 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffffer 中排序，如果sort buffffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作；
• 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。可以适当提高 sort_buffffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

    2.1.4 优化 group by : 实际执行中group by 默认包含了一个order by ,当查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序；

    2.1.5 优化子查询语句： 连接 (Join) 查询之所以更有效率一些 ，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的 查询工作；

    2.1.6 优化 or 语句：对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引 ， 而且不能使用到复合索 引； 如果没有索引，则应该考虑增加索引。

    2.1.7 优化 limit 查询：

•         1）在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容；
•         2）该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

    2.1.8 使用 SQL 提示：

•         1）USE INDEX (索引名)，在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引；
•         2）IGNORE INDEX(索引名)，如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 提示 ；
•         3）FORCE INDEX(索引名)，强制MySQL使用一个特定的索引，可在查询中使用 force index 作为提示。