MySQL索引语法和索引性能分析

今日目标

理解MySQL索引语法和索引性能分析

今日内容介绍,可能要花费15分钟，希望小伙伴可以耐心看完，然后对大家有帮助

为了保证数据库查询性能，提高应用程序的响应速度，索引成为至关重要的元素。本文将深入研究MySQL索引，包括索引的创建语法、SQL性能分析。我们将探讨索引的方方面面，助力您构建高效运行的数据库系统。

1. MySQL索引的创建语法

MySQL索引的创建语法是建立数据库性能的基础，接下来将详细探讨创建索引的语法

1.1.创建索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

**注意: [] 中内容可写可不写，根据需求填写 **

查看索引

SHOW INDEX FROM table_name(表名) ;

删除索引

DROP INDEX index_name(索引名称) ON table_name(表名) ;

1.2. 案例演示

接下来我们通过对表进行创建索引，进行演示

1.2.1.

数据准备创建tb_user表

-- 创建数据库test
create database if not exists test;
use test;


-- 创建表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  default now() comment  '创建时间'
) comment '系统用户表';

INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('吕布', '18877990000', 'lvbu666@163.com', '软件工程', 23, '1', '6');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('曹操', '18877990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('赵云', '18877990002', '18877990@139.com', '英语', 34, '1', '2');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('孙悟空', '18877990003', '18877990@sina.com', '工程造价', 54, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('花木兰', '18877990004', '19980729@sina.com', '软件工程', 23, '2', '1');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('大乔', '18877990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('露娜', '18877990006', 'luna_love@sina.com', '应用数学', 24, '2', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('程咬金', '18877990007', 'chengyaojin@163.com', '化工', 38, '1', '5');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('项羽', '18877990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('白起', '18877990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('韩信', '18877990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('荆轲', '18877990011', 'jingke123@163.com', '会计', 29, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('兰陵王', '18877990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('狂铁', '18877990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('貂蝉', '18877990014', '84958948374@qq.com', '软件工程', 40, '2', '3');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('妲己', '18877990015', '2783238293@qq.com', '软件工程', 31, '2', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('芈月', '18877990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('嬴政', '18877990017', '8839434342@qq.com', '化工', 38, '1', '1');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('狄仁杰', '18877990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('安琪拉', '18877990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('典韦', '18877990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('廉颇', '18877990021', 'lianpo321@126.com', '土木工程', 19, '1', '3');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('后羿', '18877990022', 'altycj2000@139.com', '城市园林', 20, '1', '0');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status) VALUES ('姜子牙', '18877990023', '37483844@qq.com', '工程造价', 29, '1', '4');
-- 表中数据

select * from tb_user;

1.2.2. 创建tb_user索引

name字段可重复，创建name对应的索引

-- 创建单列索引
create index user_name_index on tb_user(name);

phone，非空，唯一,创建单列唯一索引

create unique index user_phone_index on tb_user(phone);

为profession、age、status创建联合索引

create index user_pro_age_sta_index on tb_user(profession,age,status);

一般一个用户对应一个email,为email创建索引

create index user_email_index on tb_user(email);

5.查看tb_user表中索引

show index from tb_user;

2. SQL性能分析

假设要对SQL进行优化，前提必须是，首先对SQL 进行性能分析，然后才可以进行SQL优化性能分析主要包含以下几方面进行分析：

SQL执行频率
慢查询日志
profile详情
explain(重点)

2.1. SQL执行频率

思考: 为什么需要查看SQL执行频率?

通过SQL执行频率，可以查看当前数据库是查询为主，还是增删改为主，如果是增删改为主，可以不要考虑对索引进行优化，如果是查询为主，可以考虑对数据库所有进行优化

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status可以查看当前数据库INSERT、UPDATE、DELETE、SELECT访问频率

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

主要查看以下内容：

Com_delete：SQL语句删除次数
Com_insert：SQL语句插入次数
Com_select: SQL语句查看次数
Com_update: SQL语句更新次数

思考:假设通过SQL执行频次，发现当前数据库是查询为主，那么该如何确定对查询SQL语句进行优化？

当知道当前数据库是查询数据库为主后，可以通过慢查询日志，发现那些SQL查询语句执行比较慢，然后再具体对其语句进行优化

3. SQL慢查询日志

MySQL慢查询日志默认没有开启,可以通过查看系统变量 slow_query_log

show variables like '%slow_query_log%'

3.1. 开启SQL慢查询日志

开启SQL慢查询日志，主要有两个系统变量进行设置:

slow_query_log=1 : 开启Mysql慢查询日志
long_query_time=2: 设置慢查询日志时间为2S，如果SQL语句执行时间超过2S，就会在慢查询日志中进行记录

3.2. 命令开启慢查询日志

使用如下命令开启慢查询日志

set global slow_query_log =1;
show variables like '%slow_query_log%';

设置慢查询阈值时间

--设置完毕后，重新登陆后起效 （不需要重启服)
set global long_query_time =2;
-- 查看慢查询阈值时间
show variables like '%long_query_time%';  务）

注：使用该命令开启慢查询只对当前数据库生效，MySQL重启失效，如果要配置长期有效，请在my.cnf中进行配置

注意: 设置完毕慢查询阈值时间后，需要重新登陆后起效

3.3. 测试

【步骤一】:表准备

CREATE TABLE tb_sku (
                        `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
                        `sn` varchar(100) NOT NULL COMMENT '商品条码',
                        `name` varchar(200) NOT NULL COMMENT 'SKU名称',
                        `price` int(20) NOT NULL COMMENT '价格（分）',
                        `num` int(10) NOT NULL COMMENT '库存数量',
                        `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
                        `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
                        `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
                        `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
                        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                        `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                        `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
                        `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
                        `spec` varchar(200) DEFAULT NULL COMMENT '规格',
                        `sale_num` int(11) DEFAULT '0' COMMENT '销量',
                        `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
                        `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
                        PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

【步骤二】：导入数据到sku表

数据就保存在我关注我时，发送给大家的网盘中

将多个SQL文件生成一个,打开CMD命令，进入到sql文件目录下，输入一下命令

copy *.sql all_sku.sql

经文件导入到数据库的表中注意:因为有1000w数据，所以需要等待一段时间

【步骤三】：执行sql语句

执行查询 tb_user表

select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec

2.执行查询tb_sku表

select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
10s左右

【步骤四】：检查慢查询日志

查看慢查询日志，地址可以使用如下SQL进行查看

show variables like '%slow_query_log%';

我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL 是不会记录的

4. profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling ;

当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句开启profiling

-- 查看profiling是否开启  0 表示关闭  1表示开启
select @@profiling;

-- 通过set语句开启profiling
SET profiling = 1;

select @@profiling;

4.1.测试

在mysql客户端执行以语句

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

查看sql的执行耗时

-- 查看每一条SQL的耗时基本情况
show profiles;

查看指定query_id的SQL语句各个阶段的耗时情况

show profile for query query_id;

查看指定query_id的SQL语句CPU的使用情况


-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

5, explain执行计划(重点)

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

字段	含义
id	表示执行顺序，id相同从上到下执行，不同值越大越先执行
select_type	示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。

type连接类型说明:

system:一般系统表才会出现
const:常量条件（如主键或唯一索引）访问单个行。通常出现在具有常量条件的查询，例如根据主键或唯一索引进行的等值查询

-- 语句中id表示的是主键
explain select * from   tb_user where id=1;

eq_ref；表示使用唯一索引或主键进行等值连接检索。通常出现在具有关联表的等值连接查询，其中连接条件使用了唯一索引或主键。例如，通过外键连接两个表，或者使用JOIN语句时，连接条件涉及到唯一索引或主键。
ref：表示使用非唯一索引进行等值连接检索。通常出现在使用非唯一索引进行查询的情况，其中每个索引键值可能匹配多行记录。

-- name 是索引
 explain SELECT * FROM tb_user WHERE name= '李四';

range:：表示使用索引进行范围查询。通常出现在使用范围操作符（如BETWEEN、>、<）进行查询时

 explain SELECT * FROM tb_user WHERE id BETWEEN 1 AND 10;

index:示全索引扫描，即遍历整个索引来定位记录，而不是根据索引中的具体值进行查找。通常出现在没有合适的索引可用，或者查询需要遍历大部分或全部索引的情况。
all:表示全表扫描，表示没有使用索引，需要对整个表进行遍历来找到匹配条件的记录。通常出现在没有适用的索引、谓词或查询涉及表的大部分或全部数据的情况。