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

最新推荐文章于 2024-09-27 20:43:28 发布
最新推荐文章于 2024-09-27 20:43:28 发布
阅读量40 收藏
点赞数
分类专栏： 技术问答 文章标签： mysql 数据库

在这里插入图片描述

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

在这里插入图片描述

SHOW INDEX FROM table_name(表名) ;

在这里插入图片描述

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

在这里插入图片描述

-- 创建数据库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;

在这里插入图片描述

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

在这里插入图片描述

create unique index user_phone_index on tb_user(phone);

在这里插入图片描述

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

在这里插入图片描述

create index user_email_index on tb_user(email);

在这里插入图片描述

show index from tb_user;

在这里插入图片描述

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

在这里插入图片描述

show variables like '%slow_query_log%'

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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='商品表';

在这里插入图片描述
在这里插入图片描述

copy *.sql all_sku.sql

在这里插入图片描述
在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

show variables like '%slow_query_log%';

在这里插入图片描述

SELECT @@have_profiling ;

在这里插入图片描述
在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

show profile for query query_id;

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

explain SELECT * FROM tb_user;

转载：https://mp.weixin.qq.com/s/QMQ_Rfx6DpbzH2_YuKInGw

天空～之城
关注
专栏目录
mybatis 使用mysql索引_MySQL索引语法+使用场景
weixin_36128239的博客
01-27 2017
建表时添加索引建表同时建立单索引CREATE TABLE t_user1(id INT ,userName VARCHAR(20),PASSWORD VARCHAR(20),INDEX (userName) #关键字INDEX);建表同时建立唯一索引(可以是单或多)CREATE TABLE t_user2(id INT ,userName VARCHAR(20),PASSWORD VARCHAR(...
MySQL索引的基本语法
12-14
可以用在 where 条件查找的字段，和order by 排序的字段，有了索引，便可以快速地定位数据所在的物理地址并找出来。 索引的分类 1.普通索引(normal)：没有任何约束，主要用于提高查询效率 2.唯一索引(UNIQUE)：在...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化与性能调优
码农郭大大的博客
06-05 2149
通过慢查询日志获取到慢查询语句后，我们需要对慢查询语句进行分析。一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会会后生成一个执行计划，这个执行计划展示了具体执行的查询方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，因此我们弄清楚执行计划的各个输出项代表的含义，从而可以有针对性的提升查询性能。通过使用EXPLAIN关键字。
MySQL索引创建语法
Pei's Blog
06-26 2462
1. 创建索引 创建普通索引 ALTER TABLE table_name ADD INDEX index_name ( column_name ) ; 创建唯一索引 ALTER TABLE table_name ADD UNIQUE index_name ( column_name ); 创建全文索引 ALTER TABLE table_name ADD FULLTEXT ( c...
【MySQL 系列】MySQL 索引篇
栗筝i的博客
03-14 2918
在 MySQL 中，索引是一种帮助存储引擎快速获取数据的数据结构，形象的说就是索引是数据的目录。它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。
MYSQL之索引语法与使用
指针先生
01-22 1484
如果索引了多列（联合索引），要遵守最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。尽量使用覆盖索引（查询使用了索引，并且要返回的列能在该索引中全部找到），减少select*（要回表查询，使性能下降）using index：查找使用了索引，但需要的数据在索引列能找到，不需要回表查询。用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引豆瓣不会被用到。在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建立联合索引，而非单列索引。
MySQL索引、性能分析、使用法则
weixin_44300779的博客
01-29 1577
一、什么是索引 索引是帮助MySQ高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 优点： 1、提高数据检索的效率，降低数据库的IO成本。 2、通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 缺点： 1、索引列也是要占用空间的。 2、索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降
MySql四大索引介绍以及创建语法
weixin_55076626的博客
05-05 1328
四大索引介绍 1、主键索引： 主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。 2、唯一索引： 索引列的所有值都只能出现一次,即必须唯一,值可以为空。 3、普通索引： 基本的索引类型，值可以为空，没有唯一性的限制。 4、全文索引： 全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。MyISAM支持全文索引，InnoDB在mysql5.6之后也支持了全文索引。 创建语法 1、主键索引 ALTER TA
MySQL索引性能优化
DarzenWong的博客
01-24 2103
MySQL索引，性能优化 01、MySQL：性能优化方案 在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生产的影响也越来越大，此时MySQL数据库的性能问题成为系统应用的瓶颈，因此需要进行MySQL数据库的性能优化。 1.1、性能下降的表现 执行时间长（MySQL处理sql语句的时间） 等待时间长（网络可能会存在一定的延迟，带宽） 1
MySQL8.0索引新特性
时间如瑾 代码如诗
07-09 1390
在MySQL 8.0版本中查看数据表ts1的结构，从结果可以看出，索引已经是降序了。当表中没有显示主键时，表中的第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），在MySQL 5.7版本中查看数据表ts1的结构，从结果可以看出，索引仍然是默认的升序。注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。
MySQL 创建索引(Create Index)的方法和语法结构及例子
12-15
CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON ... HASH | RTREE} 代码如下: — 创建无索引的表格 create table testNoPK ( id int not null, name varchar(10) ); — 创建
【MySQL】数据库表的基本查询——增删查改
最新发布
m0_74755811的博客
09-27 815
在MySQL中，对表进行增删查改操作是数据库管理的基础。增（Insert）操作用于添加新记录，使用`INSERT INTO 表名 (列1 ...) VALUES (值1 ...);`。删（Delete）操作用于移除记录，使用`DELETE FROM 表名 WHERE 条件;`，需注意慎用以防误删。查（Select）操作用于检索数据，基本形式为`SELECT 列名 FROM 表名 WHERE 条件;`，可配合`ORDER BY`进行排序，`LIMIT`限制结果数量。改（Update）操作用于修改现有记录.
Mysql梳理7——分页查询
haozihua的博客
09-22 464
注意：MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。第二个参数“行数”指示返回的记录条数。
MySQL --基本查询(上）
2301_79274600的博客
09-23 1164
语法： 示例： 创建一张学生表 1.1单行数据+全列插入 插入两条记录，value_list 数量必须和指定列数量及顺序一致 由于 主键 或者 唯一键 对应的值已经存在而导致插入失败 可以选择性的进行同步更新操作 语法： 冲突数据被更新– ON DUPLICATE KEY 当发生重复key的时候– 主键 或者 唯一键 没有冲突，则直接插入； – 主键 或者 唯一键 如果冲突，则删除后再插入 语法： 示例： 创建表结构 插入测试数据 – 通常情况下不建议使用 * 进行全列查询 – 1. 查询的列越多，意
hive/impala/mysql几种数据库的sql常用写法和函数说明
biqidaoer的专栏
09-27 121
做大数据开发的时候，会在几种库中来回跳，同一个需求，不同库函数和写法会有出入，在此做汇总沉淀。
MySQL—索引机制详解
m0_74293254的博客
09-27 657
MySQL 官方定义索引为一种帮助数据库高效获取数据的结构，其本质是排好序的快速查找数据结构。数据库除了存储表中的数据，还维护一些额外的满足特定查找算法的数据结构，这些数据结构以某种方式指向实际的数据，从而加快查询的速度。这种额外维护的数据结构就是索引。由于索引是在存储引擎层实现的，不同的存储引擎对索引的实现方式可能不尽相同，因此没有统一的索引标准。索引的使用数据库中的索引可以类比为书籍的目录，它指向实际的数据，帮助快速定位。下图展示了索引的工作原理：在图中，左边是一张数据表，包含两列和七条记录。
Mysql梳理9——多表查询连接分类及实现内、外连接
haozihua的博客
09-23 584
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰 可见。语法说明： 可以使用 ON 子句指定额外的连接条件。满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。: 合并具有同一列的两个以上的表的行,外连接的分类：左连接、右连接、满连接。
MySQL索引设计与优化策略
本文将详细介绍如何设计和使用MySQL索引，包括创建、删除索引的基本语法，以及索引设计的一些原则。 1. **创建索引**: - MySQL支持多种类型的索引，如`UNIQUE`（唯一索引）、`FULLTEXT`（全文索引）和`SPATIAL`...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值