网站建设怎么上传数据,网站开发技术课程设计总结,中国黄页电话号码查询,深圳企业vi设计公司目录
前言
一、索引语法
1.SQL语法
2.案例演示
二、SQL性能分析
三、慢查询日志
1.开启日志 2.测试样例 四、profile详情
1.开启profile
2.profile测试SQL语句
五、explain详情
1.语法结构 2.执行顺序示例#xff08;id#xff09;
3.执行性能示例(type) 前言 本…目录
前言
一、索引语法
1.SQL语法
2.案例演示
二、SQL性能分析
三、慢查询日志
1.开启日志 2.测试样例 四、profile详情
1.开启profile
2.profile测试SQL语句
五、explain详情
1.语法结构 2.执行顺序示例id
3.执行性能示例(type) 前言 本期就要来去讲解SQL索引的相关语法已经我们之前所用的SQL语句对其进行性能分析和执行效率来去判断如何来选择合适的SQL语句。下面看正文。上一期链接MySQL进阶-----索引的结构与分类-CSDN博客
一、索引语法
1.SQL语法
1.创建索引 CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ; 2. 查看索引
SHOW INDEX FROM table_name ; 3. 删除索引
DROP INDEX index_name ON table_name ;
2.案例演示
先来创建一张表 tb_user并且查询测试数据。 create table tb_user(
id int primary key auto_increment comment 主键,
name varchar(50) not null comment 用户名,
phone varchar(11) not null comment 手机号,
email varchar(100) comment 邮箱,
profession varchar(11) comment 专业,
age tinyint unsigned comment 年龄,
gender char(1) comment 性别 , 1: 男, 2: 女,
status char(1) comment 状态,
createtime datetime comment 创建时间
) comment 系统用户表;INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (吕布, 17799990000, lvbu666163.com, 软件工程, 23, 1,
6, 2001-02-02 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (曹操, 17799990001, caocao666qq.com, 通讯工程, 33,
1, 0, 2001-03-05 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (赵云, 17799990002, 17799990139.com, 英语, 34, 1,
2, 2002-03-02 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (孙悟空, 17799990003, 17799990sina.com, 工程造价, 54,
1, 0, 2001-07-02 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (花木兰, 17799990004, 19980729sina.com, 软件工程, 23,
2, 1, 2001-04-22 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (大乔, 17799990005, daqiao666sina.com, 舞蹈, 22, 2,
0, 2001-02-07 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (露娜, 17799990006, luna_lovesina.com, 应用数学, 24,
2, 0, 2001-02-08 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (程咬金, 17799990007, chengyaojin163.com, 化工, 38,
1, 5, 2001-05-23 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (项羽, 17799990008, xiaoyu666qq.com, 金属材料, 43,
1, 0, 2001-09-18 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (白起, 17799990009, baiqi666sina.com, 机械工程及其自动
化, 27, 1, 2, 2001-08-16 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (韩信, 17799990010, hanxin520163.com, 无机非金属材料工
程, 27, 1, 0, 2001-06-12 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (荆轲, 17799990011, jingke123163.com, 会计, 29, 1,
0, 2001-05-11 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (兰陵王, 17799990012, lanlinwang666126.com, 工程造价,
44, 1, 1, 2001-04-09 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (狂铁, 17799990013, kuangtiesina.com, 应用数学, 43,
1, 2, 2001-04-10 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (貂蝉, 17799990014, 84958948374qq.com, 软件工程, 40,
2, 3, 2001-02-12 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (妲己, 17799990015, 2783238293qq.com, 软件工程, 31,
2, 0, 2001-01-30 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (芈月, 17799990016, xiaomin2001sina.com, 工业经济, 35,
2, 0, 2000-05-03 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (嬴政, 17799990017, 8839434342qq.com, 化工, 38, 1,
1, 2001-08-08 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (狄仁杰, 17799990018, jujiamlm8166163.com, 国际贸易,
30, 1, 0, 2007-03-12 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (安琪拉, 17799990019, jdodm1h126.com, 城市规划, 51,
2, 0, 2001-08-15 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (典韦, 17799990020, ycaunanjian163.com, 城市规划, 52,
1, 2, 2000-04-12 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (廉颇, 17799990021, lianpo321126.com, 土木工程, 19,
1, 3, 2002-07-18 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (后羿, 17799990022, altycj2000139.com, 城市园林, 20,
1, 0, 2002-03-10 00:00:00);
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES (姜子牙, 17799990023, 37483844qq.com, 工程造价, 29,
1, 4, 2003-05-26 00:00:00); 这里我们先去查看这个表的索引
show index from tb_user;这里可以看到索引的名字以及索引的类型是Btree 数据准备好了之后接下来我们就来完成如下需求 A. name字段为姓名字段该字段的值可能会重复为该字段创建索引。 create index index_name on tb_user(name);再次查看 B. phone手机号字段的值是非空且唯一的为该字段创建唯一索引。 create unique index index_phone on tb_user(phone);C. 为profession、age、status创建联合索引。 create index pro_age_sta on tb_user(profession,age,status);二、SQL性能分析
MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 -- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE Com_______; Com_delete: 删除次数 Com_insert: 插入次数 Com_select: 查询次数 Com_update: 更新次数 我们可以在当前数据库再执行几次查询操作然后再次查看执行频次看看 Com_select 参数会不会变化。 通过上述指令我们可以查看到当前数据库到底是以查询为主还是以增删改为主从而为数据库优化提供参考依据。 如果是以增删改为主我们可以考虑不对其进行索引的优化。 如果是以查询为主那么就要考虑对数据库的索引进行优化了。 那么通过查询SQL的执行频次我们就能够知道当前数据库到底是增删改为主还是查询为主。 那假如说是以查询为主我们又该如何定位针对于那些查询语句进行优化呢 次数我们可以借助于慢查询日志。 接下来我们就来介绍一下MySQL中的慢查询日志。 三、慢查询日志
1.开启日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有 SQL语句的日志。 MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。 show variables like slow_query_log;如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2
这里我们先通过vi /etc/my.cnf 指令进入到mysql里面进行对配置文件的编辑 配置完毕之后通过以下指令重新启动MySQL服务器进行测试查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。 systemctl restart mysqld 然后再次查看开关情况慢查询日志就已经打开了。 2.测试样例 A. 执行如下SQL语句 select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
13.35sec B. 检查慢查询日志 最终我们发现在慢查询日志中只会记录执行时间超多我们预设时间2s的SQL执行较快的SQL是不会记录的。 这样通过慢查询日志就可以定位出执行效率比较低的SQL从而有针对性的进行优化。 四、profile详情
1.开启profile
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数能够看到当前MySQL是否支持profile操作 SELECT have_profiling ; 可以看到当前MySQL是支持 profile操作的但是开关是关闭的。可以通过set语句在 session/global级别开启profiling 查看profile是否开启 select profiling;如果显示0就表示未开启那就要去开启 SET profiling 1; 2.profile测试SQL语句 开关已经打开了接下来我们所执行的SQL语句都会被MySQL记录并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句 select * from tb_user;
select * from tb_user where id 1;
select * from tb_user where name 白起; 执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时 -- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id; 查看每一条SQL的耗时情况: 查看指定SQL各个阶段的耗时情况 : 五、explain详情
1.语法结构
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序 语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ; Explain 执行计划中各个字段的含义: 字段 含义 id select查询的序列号表示查询中执行select子句或者是操作表的顺序 (id相同执行顺序从上到下id不同值越大越先执行)。 select_type 表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、 UNIONUNION 中的第二个或者后面的查询语句、 SUBQUERYSELECT/WHERE之后包含了子查询等 type 表示连接类型性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。 possible_key 显示可能应用在这张表上的索引一个或多个。 key 实际使用的索引如果为NULL则没有使用索引。 key_len 表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好 。 rows MySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。 filtered 表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。 2.执行顺序示例id
数据准备
# 创建学生信息表
create table students
(id int auto_incrementprimary key,name varchar(10) null,num varchar(20) null,constraint students_num_uindexunique (num)
);
#创建中间连接表
create table stu_cour
(stu_id int null,cour_id int null,constraint stu_cour_courses_id_fkforeign key (cour_id) references courses (id),constraint stu_cour_students_id_fkforeign key (stu_id) references students (id)
);
# 创建课程表
create table courses
(id int not nullprimary key,c_name varchar(10) null
);# 数据插入
insert into students values (1,韩信,123451),(2,李白,123452),(3,公孙离,123453),(4,司马懿,123454);insert into courses values (1,mysql),(2,java),(3,python);insert into stu_cour values (1,2),(1,3),(2,1),(2,3),(3,3); 关系如图所示
explain语句结果中select查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)。 1id相同案例
explain select s.*,c.* from students s,stu_cour sc,courses c where s.idsc.stu_id and c.idsc.cour_id;2id不同时案例
explain select * from students where id(select stu_id from stu_cour where cour_id(select id from courses where c_namemysql)); 3.执行性能示例(type)
1如果查询全部的话那就是全部扫描结果为all
explain select * from tb_user; 2如果查询指定条件的话但没有建立索引结果如下
explain select * from tb_user where name韩信;3如果查询有建立索引的条件的话结果如下
explain select * from tb_user where id2;以上就是本期的全部内容我们下次见
分享一张壁纸
