update_by_query 跨索引执行更新

转载

mob64ca13f96cda 2024-11-06 20:59:37

索引原理知识回顾

索引的性能分析和优化

通过 EXPLAIN 来判断 SQL 的执行计划，发现慢 SQL 或者性能影响业务的 sql

explain [EXTENDED] SELECT...

查看执行计划会有如下信息：

id:1
select_type:simple
table:t
possible_keys:primary
key:primary
key_len:4
ref:const
rows:1
filtered:100.00
extra:using index

关于 key_len 长度计算公式：

varchar(10) 变长字段且允许 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10) 变长字段且不允许 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+2(变长字段)
char(10) 变长字段且允许 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+1(NULL)
char(10) 变长字段且不允许 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)

默认 null，会占用字节，索引长度。也就是说索引 key_len 长度过大，也会影响 SQL 性能。

4.1 索引提高 SQL 效率的方法

利用索引加快查询速度
行记录检索
从索引记录中直接返回结果（联合索引）

min(), max()
order by 
group by 
distinct

如果列定义为 DEFAULT NULL 时，NULL 值也会有索引，存放在索引树的最前端部分。案例 1：

CREATE TABLE `base_assets` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ASSETS1` int(11) DEFAULT '0',
  `ASSETS2` int(10) unsigned DEFAULT NULL,
  `ASSETS5` int(10) unsigned NOT NULL DEFAULT '0',
  `ASSETS3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_STAMP,
  `ASSETS4` varchar(200) NOT NULL DEFAULT,

  PRIMARY KEY (`ID`)
  KEY 'idx_A1' (`ASSETS1`),
  KEY `key_c2` (`ASSETS2`)
) ENGINE=InnoDB AUTO_INCREMENT=512976 DEFAULT CHARSET=utf8;

表说明：

500 万行记录，ASSETS11、ASSETS12、ASSETS15 三个列值完全一样，但定义不一样：
ASSETS1 列定义为 NOT NULL DEFAULT 0，有索引
ASSETS2 列定义为 DEFAULT NULL，有索引
ASSETS5 列定义为 NOT NULL DEFAULT 0，无索引

# 查询
explain select ASSETS1 from base_assets where ASSETS1 = 100000 limit 1;

# 对比

explain select ASSETS5 from base_assets where ASSETS5 = 100000 limit 1;

# 统计类业务：

explain select max(ASSETS2) from base_assets;

# 求平均值，有索引时，扫描索引即可，无需全表扫描（避免回表）

explain select avg(ASSETS1) from base_assets;

4.2 利用索引提高排序效率

# 查询
explain select ASSETS5 from base_assets where ASSETS5 > 100000  order by ASSETS5 limit 100;

# 有索引，可以快速排序完成
explain select ASSETS5 from base_assets where ASSETS1 > 100000  order by ASSETS1 limit 100;

# 读写的列改成c1

explain select ASSETS1 from base_assets where ASSETS1 > 100000  order by ASSETS1 limit 100;

结果可以再次表明不同的执行计划性能差距。（图略）

4.3 NOT NULL 和 DEFAULT NULL 的区别

desc select count(ASSETS1) from base_assets;

desc select count(ASSETS2) from base_assets;

desc select count(ASSETS1) from base_assets where ASSETS1 is null;

desc select count(ASSETS2) from base_assets where ASSETS2 is null;

4.4 利用 index merge - Using union

desc select * form base_assets where ASSETS1 = 2333 or ASSETS2 = 6666

案例 2:

# 测试索引写入效率

create bable base_assets_test (

id int unsigned not null auto_increment,
assets1 int not null default '0',
assets2 int not null default '0',
assets3 int not null default '0',
assets4 int not null default '0',
assets5 timestamp null,
assets6 varchar(200) not null default '',
primary key('id'),
KEY `idx_c2`(`assets2`),
KEY `idx_c3` (`assets3`)
);


# 测试有无索引对比写入效率存储过程

delimiter $$$
create procedure 'insert_test'(in row_num int)
begin 
declare i int default 1;
while i <= row_num do 
insert into base_assets_test(id,assets1,assets2,assets3,assets4,assets5,assets6)
     values(i,floor(rand()_row_num),floor(rand()_row_num),floor(rand()_row_num),
            now(),repeat('wubx',floor(rand()*)20)));

set i = i + 1;
end while;
end $$$

客户端调用：call insert_test (1 000 000);

插入初始化数据：

1	模式	耗时
innodb	无索引	110
innodb	只有主键索引	110
innodb	下全部索引	110
myisam	无任何索引	24
myisam	只有主键索引	27
myisam	全部索引	31