首页 > 数据库 >mysql 索引优化 explain,复合索引,联合索引

mysql 索引优化 explain,复合索引,联合索引

时间:2022-11-13 17:25:46浏览次数:64  
标签:11 last explain mysql 索引 base user 2022 time

本节是关于MySQL的复合索引相关的知识,两个或更多个列上的索引被称作复合索引,本文主要介绍了mysql 联合索引生效的条件及失效的条件,感兴趣的可以了解一下:

必须用实例说话:先建立一个普通的用户表

/*
 Navicat MySQL Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80031 (8.0.31)
 Source Host           : localhost:3306
 Source Schema         : suoyin

 Target Server Type    : MySQL
 Target Server Version : 80031 (8.0.31)
 File Encoding         : 65001

 Date: 13/11/2022 16:48:42
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_base
-- ----------------------------
DROP TABLE IF EXISTS `user_base`;
CREATE TABLE `user_base` (
  `user_id` bigint NOT NULL COMMENT '主键,int类型,非自增字段,插入前要先获取最大值',
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名称',
  `client_type` int NOT NULL COMMENT '终端类型,1(delphi旧版)2(delphi云端版)3(electron云端版未来保留)',
  `user_pwd` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户密码',
  `run_status` tinyint(1) NOT NULL COMMENT '运行状态,0(停用)1(正常)',
  `bind_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '绑定的手机号,只允许绑定一个手机号',
  `register_time` datetime NOT NULL COMMENT '注册时间',
  `last_login_time` datetime NOT NULL COMMENT '此用户的最近一次登录时间,方便根据这个字段,统计分析账号的活跃百分比,把长期不活跃的用户删掉,避免占用存储空间,提升查询速度。',
  `last_sync_time` datetime NOT NULL COMMENT '最近一次因登录账号而导致的,自动同步店铺和权限绑定的时间;每间隔3月自动同步一次,各个平台Redis里缓存的数据会自动延长,由于缓存的时间最低1年,所以一年内只要登录,就会自动延长,保证店铺和权限绑定不被清除,同时避免了不活跃用户的绑定数据长期持久化在Redis里的情形。留意:这里仅仅是因登录账号而导致的自动同步的时间,与增删店铺和修改权限导致的同步不同,增删店铺和修改权限导致的同步,不应该更新这个时间。',
  `custom_memo` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户自己自定义备注,比如主账号添加子账号的时候给子账号一个备注,最多500个字符',
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE KEY `ix_user_base_user_name_client_type_pwd` (`user_name`,`client_type`,`user_pwd`) USING BTREE COMMENT '用户名 + 终端类型 + 密码 组成唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户信息基础表';

-- ----------------------------
-- Records of user_base
-- ----------------------------
BEGIN;
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (5143, 'hello', 2, 'F6E932F3C956ECA5E2133808847F6A4B', 1, '13302281999', '2015-03-08 17:03:17', '2022-11-11 17:00:23', '2022-11-11 16:54:44', NULL);
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65883, 'hello:波', 2, 'FE0BE47992ACAB558DCE35D468A5EF68', 1, '13302281999', '2022-03-18 11:06:38', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '这是一个测试信息2222');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65884, 'hello:克', 2, '882F7814CB1565031AA0B3553A4D97F5', 1, '13302281999', '2022-03-18 11:06:56', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65885, 'hello:敏', 2, '05B9B9DB74E22116FB9DA319D558EC54', 1, '13302281999', '2022-03-18 11:21:39', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65886, 'hello:黄', 2, '2E26033B5A26A4F662C8F3A8A7DB58C6', 1, '13302281999', '2022-03-18 11:22:05', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65887, 'hello:丽', 2, '7278D8122C416E697F5175DE575CCE99', 1, '13302281999', '2022-03-18 11:22:32', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65888, 'hello:媛', 2, '8C92853DDA2283D42E286E2132639A82', 1, '13302281999', '2022-03-18 11:24:10', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65889, 'hello:庆', 2, 'ADB57DFAF1CFA6133A27C2864EADA90B', 1, '13302281999', '2022-03-18 11:24:30', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (65942, 'hello:鑫', 2, '47DE96D01DD11D23B4EFDC3685263A02', 1, '13302281999', '2022-03-23 17:03:15', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66208, 'hello:秋', 2, '444295C0BE560F392B54BEEAD1A41B61', 1, '13302281999', '2022-04-25 09:49:19', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66380, 'hello:火', 2, '16AB0C73A5B8FF84C06A3165E63FBD92', 1, '13302281999', '2022-05-13 20:27:37', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '仓库用的子账号');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66386, 'hello:拼', 2, '5289ECD4AB7545889C95C3423EE300CC', 1, '13302281999', '2022-05-14 16:34:33', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66416, 'hello:多', 2, '319E022340BEA1C6D2706171E2E5EB63', 1, '13302281999', '2022-05-18 16:23:34', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66615, 'hello:成', 2, '1BF21C9C19288BFB62F891A7D586A1F5', 1, '13302281999', '2022-06-13 18:01:40', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
INSERT INTO `user_base` (`user_id`, `user_name`, `client_type`, `user_pwd`, `run_status`, `bind_mobile`, `register_time`, `last_login_time`, `last_sync_time`, `custom_memo`) VALUES (66960, 'hello:久', 2, '70260ABB993E25A094135F36CA71890F', 1, '13302281999', '2022-08-11 16:58:00', '2022-11-10 18:16:01', '2022-11-11 11:00:46', '');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 

以上sql文件下载地址:https://files.cnblogs.com/files/del88/user_base_test.sql.zip?t=1668329370

 

对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找;

复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。

    所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

1. 登录的sql语句 select 基本返回列  from user_base where user_name = 'hello' and client_type =2 and user_pwd = 'xxx'

2.判断一个用户名是否存在 select 基本列 from user_base where user_name = 'hello' and client_type = 2 

可见 登录和注册时判断一个用户名是否存在都使用了 user_name + client_type 这2列,且要求用户名 + 终端类型 必须唯一,若只单独给这2列加一个唯一索引,则登录时 又查询了 user_pwd 所以,最好这3列一并组成一个唯一索引,才能发挥最佳效率,以下我们测试一下:

1. 首先只user_name + client_type 组成唯一索引,若登录的话看下查询效能:

 

 

标签:11,last,explain,mysql,索引,base,user,2022,time
From: https://www.cnblogs.com/del88/p/16886339.html

相关文章

  • mysql-最常用SQL语句大全
    DDL(DataDefinitionLanguage)数据定义语言一、操作库 --创建库createdatabasedb1;--创建库是否存在,不存在则创建createdatabaseifnotexists/ɪɡˈzɪsts......
  • MySQL5.7到OceanBase4.0数据迁移
    一、安装MySQL数据库系统版本:CentOS7.5数据库版本:MySQL5.7.38创建目录mkdir-p/mysql/{data,tmp,binlog,logs}chown-Rmysql:mysql/mysql创建用户groupadd-g......
  • MySQL 源码解读之-语法解析(一)
    MySQL源码解读之-语法解析(一)语法分析是编译过程的一个逻辑截断。语法分析的任务是在词法分析的基础上将单词序列组合成各类语法短语,如“程序”,“语句”,“表达式”等等.语......
  • flink开发中整合flinksql、kafka、mysql、hbase等问题与结果记录
    在flink开发中,通常会配合flinksql、kafka、mysql、hbase等一块使用,为避免jar包缺失、冲突,现整理一下。一、版本说明flink:1.13.0kafka:2.11mysql:8.0hbase:2.2.3二、fl......
  • Mysql_DQL操作表_排序查询(重点)
    --查询学生信息,按照年龄升序排列;SELECT*fromstuORDERBYage;--查询学生信息,按照数学成绩降序排列;SELECT*fromstuORDERBYmathdesc;--查询学生信息,按照数......
  • mysql5.7 glibc 绿色安装全系linux支持
    wget-O/tmp/mysql-5.7.38.tar.gzhttps://mirrors.aliyun.com/mysql/MySQL-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz?spm=a2c6h.25603864.0.0.42df63afg9UZlHcu......
  • Mysql InnoDB多版本并发控制MVCC
    参考书籍《mysql是怎样运行的》系列文章目录和关于我一丶为什么需要事务隔离级别mysql是一个客户端/服务断软件,对于同一个服务器来说,可以有多个客户端进行连接,每一个客......
  • 5.numpy数组的索引和切片
    三、ndarray的基本操作  索引和切片1.索引维与列表完全一致多维时同理2.切片维与列表完全一致多维时同理将数据反转,例如[1,2,3]--->[3,2,1]两个::进行切片-------......
  • SpringBoot实现mysql主从复制
    导入依赖<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</v......
  • 解决操作mysql报 1044 - Access denied for user 'root'@'%' to database 'table&#039
    naccat操作数据库时报 1044-Accessdeniedforuser'root'@'%'todatabase'table'说明root用户没有授权,需要授权Mysql8.01.进入mysql容器dockerexec-itmys......