3.1.索引语法：
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;
案例演示
:
先来创建一张表
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', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.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_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');
数据准备好了之后，接下来，我们就来完成如下需求：
A. name
字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);
B. phone
手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
create unique index idx_user_phone on tb_user(phone);
C.
为
profession
、
age
、
status
创建联合索引。
create index idx_user_pro_age_sta on tb_user(profession,age,status);
D.
为
email
建立合适的索引来提升查询效率。
create index idx_email on tb_user(email);
完成上述的需求之后，我们再查看
tb_user
表的所有的索引数据。
show index from tb_user;
3.2.SQL性能分析：
SQL
执行频率
MySQL
客户端连接成功后，通过
show [session|global] status
命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的
INSERT
、
UPDATE
、
DELETE
、
SELECT
的访问频次：
show global status like 'Com______';
Com_delete:
删除次数
Com_insert:
插入次数
Com_select:
查询次数
Com_update:
更新次数
我们可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看
Com_select
参数会不会变化。
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据
库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以
查询为主，那么就要考虑对数据库的索引进行优化了。
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据
库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以
查询为主，那么就要考虑对数据库的索引进行优化了。
接下来，我们就来介绍一下
MySQL
中的慢查询日志。
3.3.慢查询日志：
慢查询日志记录了所有执行时间超过指定参数（
long_query_time
，单位：秒，默认
10
秒）的所有
SQL
语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
show variables like 'slow_query_log';如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动
MySQL
服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log
。
systemctl restart mysqld;
然后，再次查看开关情况，慢查询日志就已经打开了。
在慢查询日志中，只会记录执行时间超多我们预设时间（
2s
）的
SQL
，执行较快的
SQL
是不会记录的。
那这样，通过慢查询日志，就可以定位出执行效率比较低的
SQL
，从而有针对性的进行优化。
3.4.profile详情：
show profiles
能够在做
SQL
优化时帮助我们了解时间都耗费到哪里去了。通过
have_profiling
参数，能够看到当前
MySQL
是否支持
profile
操作：
SELECT @@have_profiling ;
可以看到，当前
MySQL
是支持
profile
操作的，但是开关是关闭的。可以通过
set
语句在
session/global
级别开启
profiling
：
SET profiling = 1;
开关已经打开了，接下来，我们所执行的
SQL
语句，都会被
MySQL
记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL
语句：
select * from tb_user;
select * from tb_user where id =1;
select * from tb_user where name = '白起';
select count(*) from tb_user;
执行一系列的业务
SQL
的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;3.5.explain:
EXPLAIN
或者
DESC
命令获取
MySQL
如何执行
SELECT
语句的信息，包括在
SELECT
语句执行过程中表如何连接和连接的顺序。
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
explain select * from tb_user where id =1;
Explain
执行计划中各个字段的含义
:
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
