MySQL索引优化

最新推荐文章于 2024-08-25 20:30:00 发布

原创最新推荐文章于 2024-08-25 20:30:00 发布 · 167 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql索引 #索引优化

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文深入探讨了数据库索引优化的策略，包括单表、两表及三表优化技巧，详细解析了如何创建有效索引以提升查询效率，并提供了避免索引失效的实用建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、单表优化

建表：

CREATE TABLE `rc_puchase_catalog` (
  `SEQ_ID` char(30) DEFAULT NULL COMMENT '数据库主键 无实际业务含义',
  `PROVINCE_CODE` char(2) NOT NULL COMMENT '省份',
  `pur_po_number` varchar(30) DEFAULT NULL COMMENT '采购协议号',
  `price_pre` int(8) DEFAULT NULL COMMENT '上一次成本价',
  `rs_id` varchar(30) NOT NULL COMMENT '对应资源中心 rc_resource 表 RS_ID字段',
  `item_number` varchar(30) NOT NULL COMMENT '外部sku编码 字段',
  `price_chengben` int(8) DEFAULT NULL COMMENT '成本价',
  `creation_date` date DEFAULT NULL COMMENT '创建日期',
  `agrrent_id` date DEFAULT NULL COMMENT '协议开始时间',
  `updatime` date DEFAULT NULL COMMENT '程序入库当前时间sysdate',
  `sale_flag` char(1) NOT NULL DEFAULT '0' COMMENT '是否零售机型，0 否 1是',
  `effect_time` date DEFAULT NULL COMMENT '新价格生效日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面执行以下SQL：

EXPLAIN SELECT
	PROVINCE_CODE,
	price_pre,
	rs_id,
	item_number,
	price_chengben,
	sale_flag
FROM
	rc_puchase_catalog
WHERE
	rs_id = 201805284765661
AND sale_flag = '0'
AND PROVINCE_CODE IN('98', '31')

运行结果：没有使用索引、并且出现了文件内排序。

结论：很显然type是ALL，即最坏情况。Extra里还出现Using filesort（文件内排序），也是最坏情况，所以优化是必须的。

开始优化：

创建索引：

（1）查看表已经存在的所有语句：

SHOW INDEX FROM rc_puchase_catalog

（2）如果已经存在索引，删除索引语句：

drop index index_name onrc_purchase_catalog

（3）创建索引，在经常当做查询条件的字段上建立索引（根据业务场景视情况而定）

create index idx_PROVINCECODE_SALEFLAG_RSID on rc_purchase_catalog(PROVINCE_CODE,SALE_FLAG,RS_ID)

使用explain分析以上SQL：

EXPLAIN SELECT
	PROVINCE_CODE,
	price_pre,
	rs_id,
	item_number,
	price_chengben,
	sale_flag
FROM
	rc_puchase_catalog
WHERE
	PROVINCE_CODE  in ('98','31')
AND sale_flag = '0'
AND rs_id = '201805284765661'

运行结果：目前不在是全表扫描，是range可以接受。