1200字范文 > MySQL实战——表索引创建与优化

MySQL实战——表索引创建与优化

时间：2019-04-30 20:52:55

相关推荐

MySQL实战——表索引创建与优化

前言MySQL表、索引优化实战优化前 - 表、索引结构表优化思路非聚簇索引优化思路组合索引优化思路认识key_len依据最左匹配选择只使用一列也能走联合索引依据最左匹配选择使用前面两列使用联合索引所有列查询范围查询对组合索引的影响将查询范围扩大直接导致全表扫描优化后 - 表、索引结构优化后组合索引全字段查询优化后组合索引无法满足最左匹配下SQL写法优化后扩大查询范围依然能走索引总结

前言

就目前中国市场，作为每一个开发人员都需要去会的技能MySQL，大家工作中都会需要建表、建索引，有的同学可能要说了：“这玩意谁不会建呢？太简单了！”。若是如此那真是太好了，我带团队也有几年了前前后后经历了一些人，超过85%的人建表、建索引真的只是停留在会创建。创建出来的表、索引在我看来也是问题一大堆。

所以，咱们今天就来好好聊聊如何创建一张漂亮的表，同时给他上一个高效的、有逼格的索引。

MySQL表、索引优化实战

咱们简单拿一个订单信息表来做基础优化(假设没有别的业务影响)，当然实际业务上会比这种案例场景复杂一点，希望大家可以用这次优化思路，持续延伸、扩展。同时也希望大家可以先看看下面的表结构自己判断下哪些地方是可以优化的，对比一下下面的优化思路。

优化前 - 表、索引结构

-- 用户订单表CREATE TABLE `user_order` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id自增长',`order_number` varchar(32) DEFAULT NULL COMMENT '订单编号',`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',`origin` varchar(64) DEFAULT 'google play' COMMENT '订单来源(运营市场分析)',`type` int(2) DEFAULT '0' COMMENT '订单类型,0-普通订单;1-xxxxx;2-xxxxxx',`source_id` varchar(32) DEFAULT NULL COMMENT '下单设备类型: Android, H5，IOS',`amount` bigint(20) DEFAULT NULL COMMENT '申请金额',`promotion_code` varchar(32) DEFAULT NULL COMMENT '优惠码',`state` varchar(32) DEFAULT NULL COMMENT '状态',`applied_at` datetime DEFAULT NULL COMMENT '申请时间',`cancelled_at` datetime DEFAULT NULL COMMENT '取消时间',`closed_at` datetime DEFAULT NULL COMMENT '关闭时间',`created_at` datetime NOT NULL COMMENT '创建时间',`updated_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`) USING BTREE,KEY `idx_on_user_id` (`user_id`) USING BTREE,KEY `idx_on_order_number` (`order_number`) USING BTREE,KEY `idx_on_applied_at_and_source_id_and_state` (`applied_at`,`source_id`,`state`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='用户订单表';

表优化思路

id：id 主键字段，自增长，带主键索引这个字段没有问题，希望大家建任何MySQL的表都能加上一个自增长的主键ID，且这个主键ID不要用UUID、不要不创建主键。order_number：varchar(32) DEFAULT NULL 首先我们一个系统生成订单号一般长度都是固定的，不可能一会长度5、一会长度30。所以这里建议根据实际长度使用char(len)类型。因为varchar是一个可变长字段，MySQL需要用额外的两个字节去保存长度。使用char类型对存储空间、查询效率均有提升。其次这个order_number，在业务逻辑上应该是不可能为NULL的，所以我们应该对order_number 设置NOT NULL，第一保证的业务的正确性，第二节省了一个字节，因为如果一个字段允许为NULL，MySQL需要额外的一个字节去标记该字段是否为NULL。user_id：bigint(20) DEFAULT NULL 同理根据业务可确定该字段需要设置NOT NULLorigin：varchar(64) DEFAULT ‘google play’ 既然都设置默认值了逻辑上也不可能为NULL了所以需要设置NOT NULL运营的渠道统计分析、这个字段长度要更具实际场景考虑64是否太长了？如业务允许可以用关联表管理渠道Name，这里直接写入渠道的ID即可，因为大多数渠道应该都是重复的，这个字段建索引的概率也不大，用int类型ID，节省存储空间、同时查询效率提升。因为内存中数值类型的判断等于的效率远高于字符类型。type：int(2) DEFAULT ‘0’ 字段名使用到了MySQL的关键字type，建议改为order_type既然都设置默认值了逻辑上也不可能为NULL了所以需要设置NOT NULLsource_id：varchar(32) DEFAULT NULL 根据描述这个字段完全可以用int类型1、2、3代替Android、H5、IOS如果实在不想使用int代替，也可以修改字段长度，因为实际场景中（Android、H5、IOS）这三个字段的长度完全是已知的所以可以改成char类型。逻辑上也不可能为NULL了所以需要设置NOT NULLamount：bigint(20) DEFAULT NULL 这个字段类型要不要替换成decimal咱们就不聊了，也许人家就是整数的，这个根据实际业务确定逻辑上金额需要设置一个默认 0，然后设置NOT NULLpromotion_code：varchar(32) DEFAULT NULL 这个字段没啥好说的，根据实际业务来吧state：varchar(32) DEFAULT NULL 逻辑上状态也不可能为NULL，需要设置一个默认的初始状态,同时需要设置NOT NULL也可以考虑替换为int类型的码表代替。（自行判断）applied_at： datetime DEFAULT NULL 逻辑上这个时间应该不可能为 NULL ，所以需要设置NOT NULLcancelled_at：datetime DEFAULT NULL 这个根据实际业务来，没啥好优化的closed_at：datetime DEFAULT NULL 这个根据实际业务来，没啥好优化的created_at：datetime NOT NULL 这个字段没啥问题，建议大家的数据库表都加上创建时间字段updated_at：datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 这个字段没啥问题，建议大家的数据库表都加上修改时间字段，且自动根据当前时间更新

非聚簇索引优化思路

KEYidx_on_user_id(user_id) USING BTREE user_id索引，没啥问题，正常的业务中一定有根据user_id查询的需求 KEYidx_on_order_number(order_number) USING BTREE order_number索引，逻辑上订单号是不能重复的，所以这里的索引应该修改为唯一索引，数据安全性、查询效率均有提升 KEYidx_on_applied_at_and_source_id_and_state(applied_at,source_id,state) USING BTREE 到了这次分享的关键点了，这个联合索引最左侧的是下单时间（applied_at），而这个字段查询多半是会使用范围查询 “>”、"<" 之类的，那问题就来了，如果我把这三个字段都用到查询条件中，可能会出现的场景就是联合索引没有使用全，因为applied_at的 “>”、"<" 查询，导致索引只使用了applied_at字段，并没有去使用后面的source_id和state，所以我的优化思路是: KEYidx_on_source_id_and_state_and_applied_at(applied_at,source_id,state) USING BTREE。

当然这样优化我想大家可能有很多疑问，那么接下来的篇幅中将会针对如上疑问进行一一讲解

组合索引优化思路

注：以下案例无特殊说明均使用优化前的user_order表的字段和索引

认识key_len

key_len：数值提示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，user_order的联合索引 idx_on_applied_at_and_source_id_and_state 由 applied_at 和 source_id 以及 state 三个列组成，如果在索引中所有列都用到了则 key_len = 三个字段的长度总和，如只用到了applied_at 则 key_len = datetime类型在数据库中所占用的字节数。

key_len Demo 如下 ↓↓↓

依据最左匹配选择只使用一列也能走联合索引

EXPLAIN SELECT * from user_order WHERE applied_at = '-10-31' ;

依据最左匹配选择使用前面两列

EXPLAIN SELECT * from user_order WHERE applied_at = '-10-31' AND source_id = 'android';

上面这个长度可能有同学会问了为什么是 105 呢？已知 source_id 是 varchar(32) 且允许字段为NULL，使用的是utf8编码，一个中文在索引中占用字节数为3 ，(32 * 3 ) + 2(varchar类型需要存储长度) + 1（是否为null标识） = 99。
已知第一个字段 applied_at 索引长度为 5 ， 5 + 99 = 104

使用联合索引所有列查询

EXPLAIN SELECT * from user_order WHERE applied_at = '-10-31' AND source_id = 'android' and state in('canceled','closed');

范围查询对组合索引的影响

-- 将等于修改为大于EXPLAIN SELECT * from user_order WHERE applied_at > '-10-31' AND source_id = 'android';EXPLAIN SELECT * from user_order WHERE applied_at > '-10-31' AND source_id = 'android' and state in('canceled','closed');

根据如上案例得知，如果我们使用 “>”、"<" 范围查询时有可能导致组合索引无法完全生效。
当然这里如果将> ‘-10-31’修改为>= ‘-11-01’是可能会去走后续索引的，但是也并不是一定的。

将查询范围扩大直接导致全表扫描

EXPLAIN SELECT * from user_order WHERE applied_at > '-06-31' AND source_id = 'android' and state in('canceled');

如上SQL 直接不走索引选择了全表扫描，可是canceled这种状态，在业务数据中一般是比较少的，如果可以走索引再加上时间我们依然可以过滤掉95%以上的数据，但结果是优化器选择了全表扫描。

接下来我们按照上面对表和索引进行优化然后看看执行情况。

优化后 - 表、索引结构

CREATE TABLE `user_order` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id自增长',`order_number` char(32) NOT NULL COMMENT '订单编号',`user_id` bigint(20) NOT NULL COMMENT '用户id',`origin` varchar(32) NOT NULL DEFAULT 'google play' COMMENT '订单来源(运营市场分析)',`order_type` int(2) NOT NULL DEFAULT '0' COMMENT '订单类型,0-普通订单;1-xxxxx;2-xxxxxx',`source_id` char(8) NOT NULL COMMENT '下单设备类型: Android, H5，IOS',`amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '申请金额',`promotion_code` varchar(32) DEFAULT NULL COMMENT '优惠码',`state` varchar(20) NOT NULL DEFAULT 'init' COMMENT '状态',`applied_at` datetime NOT NULL COMMENT '申请时间',`cancelled_at` datetime DEFAULT NULL COMMENT '取消时间',`closed_at` datetime DEFAULT NULL COMMENT '关闭时间',`created_at` datetime NOT NULL COMMENT '创建时间',`updated_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`) USING BTREE,UNIQUE KEY `idx_on_order_number` (`order_number`) USING BTREE,KEY `idx_on_user_id` (`user_id`) USING BTREE,KEY `idx_on_applied_at_and_source_id_and_state` (`source_id`,`state`,`applied_at`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='借款信息表';