MySQL进阶篇 | 合理的使用索引结构和查询

一、高性能索引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位作为前缀索引,用作订单号的查询,当然这里涉及到一个非常经典的业务场景,订单号机制 。
业务场景:前缀索引一个典型的应用场景就是处理订单号,一个看似很长的订单号,其实包含的信息非常多:
MySQL进阶篇 | 合理的使用索引结构和查询

文章插图