查看SQL执行频率
MySQL 客户端连接成功后,通过show [session|global] status 命令可以提供服务器状态信息。show
[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计结果和
global 级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”
show status like 'Com_______';
针对所有引擎
show status like 'Innodb_rows_%';
针对innoDB引擎
定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句。
慢查询日志 :
- 通过慢查询日志定位那些执行效率较低的 SQL 语句,用–log-slow-queries[=file_name]选项启
动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
show processlist :
- 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询
日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否
锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化
1) id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
2) user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
3) host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
4) db列,显示这个进程目前连接的是哪个数据库
5) command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接 (connect)等
6) time列,显示这个状态持续的时间,单位是秒
7) state列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一 个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态 才可以完成
8) info列,显示这个sql语句,是判断问题语句的一个重要依据
show profile分析SQL
作用:校验出当前SQL语句在执行的整个流程中所占用的时间
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。
通过 have_profiling 参数,能够看到当前MySQL是否支持profile:
默认profiling是关闭的,可以通过set语句在Session级别开启profiling:
set profiling=1; //开启profiling 开关;
例如:
show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
执行完上述命令之后,再执行show profiles 指令, 来查看SQL语句执行的耗时:
通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间:
starting: 开启
checking permissions: 检查权限
Opening tables: 打开表
init: 初始化
system lock: 加锁
optimizing: 优化操作
statistics: 统计状态
preparing: 准备
executing: 执行
sending data: 发送数据
end: 结束
query end: 查询结束
closing tables: 关闭表格
freeing items: 释放
cleaning up: 清除所有
TIP :Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回个客户端。由于 在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整各查询中耗时最长的状态。
explain分析执行计划
作用:通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
环境准备:
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') ;
explain 之 id
id 字段是 select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。
1) id 相同表示加载表的顺序是从上到下。
explain
select
*
from
t_role r,
t_user u,
user_role ur
where r.id = ur.role_id
and u.id = ur.user_id ;
2) id 不同id值越大,优先级越高,越先被执行。
EXPLAIN
SELECT
*
FROM
t_role
WHERE id =
(SELECT
role_id
FROM
user_role
WHERE user_id =
(SELECT
id
FROM
t_user
WHERE username = 'stu1'))
3) 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 ;
TIP :table:< derived2 >为虚拟表 a
explain 之 select_type
个人理解:
PRIMARY: 前提是子查询,查询结果保存在虚拟表中,与其它表相关联
DERIVED:在FROM里出现的子查询
UNION ALL 和 OR 的效果一样,推荐用UNION ALL
UNION 和 UNION ALL的效果一样,但是UNION会对结果进行排序
explain 之 table
展示这一行的数据是关于哪一张表的
explain 之 type
个人理解:
根据id顺序,关联的字段是索引或者主键为eq_ref,否则为ref
结果值从最好到最坏以此是:
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 。
explain 之 key
possible_keys : 显示可能应用在这张表的索引, 一个或多个。
key : 实际使用的索引, 如果为NULL, 则没有使用索引。
key_len : 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前 提下, 长度越短越好 。
explain 之 rows
扫描行的数量。
explain 之 extra
避免索引失效
全值匹配 ,对索引中所有列都指定具体值。
该情况下,索引生效,执行效率高。
explain select * from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京 市’\G;
最左前缀法则
匹配最左前缀法则,走索引:
违法最左前缀法则 , 索引失效:
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
范围查询右边的列,不能使用索引
根据前面的两个字段name , status 查询是走索引的, 但是最后一个条件address 没有用到索引
不要在索引列上进行运算操作, 索引将失效
字符串不加单引号,造成索引失效
由于在查询时,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效
尽量使用覆盖索引,避免select *
尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select * 。
如果查询列,超出索引列,也会降低性能。
TIP :
using index :使用覆盖索引的时候就会出现 using where:在查找使用索引的情况下,需要回表去查询所需的数据
using index condition:查找使用了索引,但是需要回表查询数据
using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表 查询数据
用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
示例,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的
explain select * from tb_seller where name=‘黑马程序员’ or createtime =‘2088-01-01 12:00:00’\G;
以%开头的Like模糊查询,索引失效
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
解决方案 :
通过覆盖索引来解决
如果MySQL评估使用索引比全表更慢,则不使用索引
is NULL , is NOT NULL 有时索引失效
in 走索引, not in 索引失效
单列索引和复合索引
尽量使用复合索引,而少使用单列索引 。
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 :
name
name + status
name + status + address
创建单列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
数据库会选择一个最优的索引(辨识度最高索引)来使用,并不会使用全部索引
强制使用SQL索引
例子