日志

MySQL进阶篇(03):合理的使用索引结构和查询

 来源    2020-08-02    1  

本文源码:GitHub·点这里 || GitEE·点这里

一、高性能索引

1、查询性能问题

在MySQL使用的过程中,所谓的性能问题,在大部分的场景下都是指查询的性能,导致查询缓慢的根本原因是数据量的不断变大,解决查询性能的最常见手段是:针对查询的业务场景,设计合理的索引结构。

2、索引使用原则

索引的使用并不是越多越好,而是针对业务下的查询场景,不断的改进和优化,例如电商系统中用户订单的场景,假设存在如下表结构:

CREATE TABLE `ds_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `ds_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `order_no` varchar(60) NOT NULL COMMENT '订单号',
  `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称',
  `number` int(11) DEFAULT '1' COMMENT '个数',
  `unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '单价',
  `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '总价',
  `order_state` int(2) DEFAULT '1' COMMENT '1待支付,2已支付,3已发货,4已签收',
  `order_remark` varchar(50) DEFAULT NULL COMMENT '订单备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

用户和订单管理表,在电商的业务中很常见,可以通过对该业务分析,看看常用的索引结构:

用户方:

  • 基于用户的查询,多数是基于用户ID(user_id);
  • 基于订单号(order_no),查看物流的信息;

运营方:

  • 基于时间段的流水明细(create_time)或排序;
  • 基于订单状态的筛选(order_state)和统计;
  • 基于产品(product_name)的数据统计分析;

这样一个流程分析走下来,即可以在开发初期,确定哪些结构是查询必须用到的,预先做好索引结构,避免数据量庞大到影响性能时再去考虑使用索引。

有些时候会考虑放弃一些查询条件,例如基于产品名称的数据统计,走定时任务的方式,用来缓解表的查询压力,处理的方式是多样的。

优秀的索引设计,都是建立在对业务数据的理解上,考虑业务数据的查询方式,提高查询效率。

二、索引创建

1、单列索引

单列索引,即索引建立在表的一个字段上,一个表可以有多个单列索引,使用起来相对比较简单:

CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;

主键索引,或者上述的user_id_index都是单列索引。

业务场景:基于用户自己对订单查询,和管理系统,订单和用户的关联查询,所以订单表的user_id需要一个索引。

2、组合索引

组合索引包含两个或两个以上的列,组合索引相比单列索引复杂很多,如何建立组合索引,和业务关联度非常高,在使用组合索引时,还需要考虑查询条件的顺序。

CREATE INDEX state_create_time_index ON `ds_order`(`create_time`,`order_state`);

如上就是组合索引,实际包含的是2个索引 (create_time) (create_time,order_state),这样查询就涉及到最左前缀的原则,必须按照顺序来查询,这里下面详说。

业务场景:首先单说这里组合索引,在业务开发中,常见订单状态的统计,基于统计结果做运营分析,另外就是在运营系统中,基于创建时间段的筛选条件是默认存在的,避免全部数据实时扫描;一些其他的常见查询也都是条件加时间段的查询模式。

3、前缀索引

如果需要加索引的列是很长的字符串,那么索引会变的庞大臃肿,起到的效果可能并不是很明显。这时候可以截取列的前面一部分,创建索引,节省空间,这样可能会出现索引的选择性下降,即基于前缀索引查询出的相似数据可能很多:

ALTER TABLE ds_order ADD KEY (order_no(30)) ;

这里由于订单号太长,所以选择前面30位作为前缀索引,用作订单号的查询,当然这里涉及到一个非常经典的业务场景,订单号机制。

业务场景:前缀索引一个典型的应用场景就是处理订单号,一个看似很长的订单号,其实包含的信息非常多:

  • 时间点:就是订单生成的时间,年月日时分秒;
  • 标识位:即一个唯一的UID,保证订全单号唯一;
  • 埋点一:在很多业务中,在订单号记录产品类目;
  • 埋点二:通常会标识产品属性,例如颜色,口味等;
  • 错位符:防止订单号被分析,会随机一段错位符号;

如此一段分析下来,实际订单号是非常长的,所以需要引入前缀索引机制,前缀索引期望使用的索引长度可以筛选整个列的基数,例如上面的订单号:

  • 大部分业务基于时间节点筛选足够,即索引长度14位;
  • 如果是并发业务,很多时间节点相同,则索引长度是时间点+标识位;

注意:如果业务允许的情况下,一般要求前缀索引的长度有唯一性,例如上面的时间和标示位。

4、其他索引

例如全文索引等,这些用到的场景不多,如果数据庞大,又需要检索等,通常会选择强大的搜索中间件来处理。显式唯一索引,这种也会在程序上做规避,避免不友好的异常被抛出。

三、索引查询

如何创建最优的索引,是一件不容易的事情,同样在查询的时候,是否使用索引也是一件难度极大的事情,经验之谈:多数是性能问题暴露的时候,才会回头审视查询的SQL语句,针对性能问题,做相应的查询优化。

1、单列查询

这里直接查询主键索引,MySQL的主键一般选择自增,所以速度非常快。

EXPLAIN SELECT * FROM ds_order WHERE id=2;
EXPLAIN SELECT * FROM ds_order WHERE id=1+1;
EXPLAIN SELECT * FROM ds_order WHERE id+1=1;

这里,id=2,id=1+1,MySQL都可以自动解析,但是id+1是在索引列上执行运算,直接导致主键索引失效。这里有一个基本策略,如果非要在单列索引上做操作,可以将该逻辑放在程序中,到MySQL层面,SQL语句越干净利落越好。

2、前缀索引查询

前缀索引的查询,可以基于Like对特定长度筛选,或者全订单号查询。

EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%';
EXPLAIN SELECT * FROM ds_order WHERE order_no='20200801131415872362873287162572367';

3、组合索引查询

查询最麻烦的就是组合索引,或者说查询条件组合起来,都使用了索引:

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_state='1';

上述基于组合索引中列的顺序,使用了组合索引:state_create_time_index。

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00';

上述只使用create_time列,也同样使用了索引结构。

EXPLAIN SELECT * FROM ds_order WHERE order_state='1';

上述如果只使用order_state条件,则结果显示全表扫描。

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_no LIKE '20200801%';

上述则基于组合索引的create_time列和单列索引order_no保证查询条件都使用了索引。

通过上面几个查询案例,索引组合索引使用的注意事项如下:

  • 组合索引必须按索引最左列开始查询;
  • 不能跳过组合字段查询,这样无法使用索引;

四、索引其他说明

1、索引的优点

  • 基于注解或唯一索引保证数据库表中数据的唯一性;
  • 索引通过减少扫描表的行数提高查询的效率;

2、索引的缺点

  • 创建索引和维护索引,会耗费空间和实际;
  • 查询以外的操作增删改等,都需要动态维护索引;

3、索引使用总结

索引机制在MySQL中真的非常复杂,非专业的DBA(就是指开发人员),基本要熟练常见的索引结构,待过两年所谓的大厂,每个版本开发涉及的核心表SQL都是有专业DBA验收,复杂的查询都是提交需求,DBA直接输出查询SQL,当然在一般公司是没有DBA,需要开发在开发的过程中不断的思考,逐步优化,这需要对业务数据有一定的敏感度,对核心接口有执行监控,当发现稍微出现耗时情况,就可以不断优化,这个积累是个枯燥和进步的过程。

五、源代码地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base

推荐阅读:MySQL数据库系列

序号 文章标题
01 MySQL基础:经典实用查询案例,总结整理
02 MySQL基础:从五个维度出发,审视表结构设计
03 MySQL基础:系统和自定义函数总结,触发器使用详解
04 MySQL基础:存储过程和视图,用法和特性详解
05 MySQL基础:逻辑架构图解和InnoDB存储引擎详解
06 MySQL基础:事务管理,锁机制案例详解
07 MySQL基础:用户和权限管理,日志体系简介
01 MySQL进阶:基于多个维度,分析服务器性能
02 MySQL进阶:索引体系划分,B-Tree结构说明
mysql 开发进阶篇系列 3 SQL 优化(索引使用方法)
日志一. 本章介绍mysql中的索引的分类,存储,使用方法的介绍 1.  索引的存储分类 MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件, innodb 存储引擎的表的数据和索 ...
1
SQL Server调优系列进阶篇(如何维护数据库索引)
日志前言 上一篇我们研究了如何利用索引在数据库里面调优,简要的介绍了索引的原理,更重要的分析了如何选择索引以及索引的利弊项,有兴趣的可以点击查看. 本篇延续上一篇的内容,继续分析索引这块,侧重索引项的日常 ...
MySQL基础篇(03):系统和自定义函数总结,触发器使用详解
日志本文源码:GitHub·点这里 || GitEE·点这里 一.系统封装函数 MySQL 有很多内置的函数,可以快速解决开发中的一些业务需求,大概包括流程控制函数,数值型函数.字符串型函数.日期时间函数 ...
2
mysql – 即使在大表上使用索引也慢查询
问答我正在执行一个简单的选择查询来从表日志中提取用户名(包含54864行). 检索数据花了大约7.836秒. 我怎样才能加快效果? SELECT username FROM `logs` WHERE lo ...
1
MySQL 千万 级数据量根据(索引)优化 查询 速度
日志一.索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让 ...
1
MySQL进阶5--分组函数 / 分组排序和分组查询 group by(having) /order by
日志MySQL进阶--分组排序和分组查询 group by(having) /order by /* 介绍分组函数 功能:用做统计使用,又称为聚合函数或组函数 1.分类: sum, avg 求和 /平均数 ...
1
MySQL 进阶之索引
日志一,索引前传 在了解数据库索引之前,首先有必要了解一下数据库索引的数据结构基础,那么什么样的数据结构可以作为索引呢? B-tree是最常用的用于索引的数据结构.因为它们是时间复杂度低, 查找.删除.插 ...
mysql 开发进阶篇系列 55 权限与安全(安全事项 )
日志一. 操作系统层面安全 对于数据库来说,安全很重要,本章将从操作系统和数据库两个层面对mysql的安全问题进行了解. 1. 严格控制操作系统账号和权限 在数据库服务器上要严格控制操作系统的账号和权限, ...
1
mysql 开发进阶篇系列 54 权限与安全(账号管理的各种权限操作 下)
日志1. 查看权限 -- 如果host值不是%, 就要加上host值,下面查看bkpuser用户权限(6个权限, 限本地连接) SHOW GRANTS FOR bkpuser@localhost; -- ...
1
mysql 开发进阶篇系列 53 权限与安全(账号管理的各种权限操作 上)
日志一. 概述 在了解前两篇的权限系统介绍后,这篇继续讲账号的管理,这些管理包括账号的创建,权限更改,账号删除等.用户连接数据库的第一步都是从账号创建开始. 1.  创建账号 有两种方法可以用来授权账号: ...
1
mysql 开发进阶篇系列 52 权限与安全(系统四个权限表的粒度控制关系)
日志一.概述 接着上篇的权限介绍,当用户进行连接的时候,权限表的存取过程有以下两个阶段: (1) 先从user表中的host,user, authentication_string 这3个字段中判断连接的 ...
mysql 开发进阶篇系列 50 表的数据导入(load data infile,mysqlimport )
日志一.概述 上篇讲到的表的数据导出(select .. into outfile 或者mysqldump),这篇继续讲表的数据导入,导入也同样有二个方法,分别是load data infile... 和 ...
mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)
日志一.概述 在数据库的日常维护中,表的导入和导出是很频繁的操作,本篇讲解如何使用导入功能,并以案例为演示.某些情况下,需要将表里的数据导出为某些符号分割的纯数据文本,而不是sql语句,比如:(1)用来作 ...
mysql 开发进阶篇系列 48 物理备份与恢复(xtrabackup 的增量备份与恢复,以及备份总结)
日志一.增量备份概述 xtrabackup  和innobackupex  二个工具都支持增量备份,这意味着能复制自上次备份以来更改的数据.可以在每个完整备份之间执行许多增量备份,因此,您可以设置一个备份 ...
1
mysql 开发进阶篇系列 47 物理备份与恢复(xtrabackup 的完全备份恢复,恢复后重启失败总结)
日志一. 完全备份恢复说明 xtrabackup二进制文件有一个xtrabackup --copy-back选项,它将备份复制到服务器的datadir目录下.下面是通过 --target-dir 指定完全 ...
mysql 开发进阶篇系列 46 物理备份与恢复( xtrabackup的 选项说明,增加备份用户,完全备份案例)
日志一. xtrabackup 选项说明   在操作xtrabackup备份与恢复之前,先看下该工具的选项,下面记录了xtrabackup二进制文件的部分命令行选项,后期把常用的选项在补上.点击查看xtr ...
mysql 开发进阶篇系列 45 物理备份与恢复(xtrabackup 安装,用户权限,配置)
日志一. 安装说明 安装XtraBackup 2.4 版本有三种方式:          (1) 存储库安装Percona XtraBackup(推荐)          (2 )下载的rpm或apt包安 ...
mysql 开发进阶篇系列 43 逻辑备份与恢复(mysqldump 的基于时间和位置的不完全恢复)
日志一. 概述          在上篇讲到了逻辑备份,使用mysqldump工具来备份一个库,并使用完全恢复还原了数据库.在结尾也讲到了误操作是不能用完全恢复的.解决办法是:我们需要恢复到误操作之前的状 ...
mysql 开发进阶篇系列 42 逻辑备份与恢复(mysqldump 的完全恢复)
日志一.概述          在作何数据库里,备份与恢复都是非常重要的.好的备份方法和备份策略将会使得数据库中的数据更加高效和安全.对于DBA来说,进行备份或恢复操作时要考虑的因素大概有如下: (1) ...
mysql 开发进阶篇系列 41 mysql日志之慢查询日志
日志一.概述          慢查询日志记录了所有的超过sql语句( 超时参数long_query_time单位 秒),获得表锁定的时间不算作执行时间.慢日志默认写入到参数datadir(数据目录)指定 ...
1