随笔 - 195  文章 - 3  评论 - 36  阅读 - 63万

知识点:Mysql 索引优化实战(3)

 

知识点:Mysql 索引原理完全手册(1)

知识点:Mysql 索引原理完全手册(2)

知识点:Mysql 索引优化实战(3)

知识点:Mysql 数据库索引优化实战(4)

 

索引原理知识回顾

索引的性能分析和优化

通过 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

小结

  • 建议低选择性的列不加索引,如性别,姓名;
  • 选择性高的字段放在前面,常用的字段放在前面;
  • 需要经常排序的字段,可加到索引中,列顺序和最常用的排序一致;
  • 对较长的字符数据类型的字段建索引,优先考虑前缀索引,如 index(url(64))
  • 只创建需要的索引,避免冗余索引,如:index(a,b),index(a)

InnoDB 表主键、索引

  • Innodb 表每一个表都要显式设置主键;
  • 主键越短越好,最好是自增类型;如果不能使用自增,则应考虑构造使用单向递增型主键,禁止使用随机类型值用于主键;
  • 主键最好由一个字段构成,组合主键不允许超过 3 个字段。如果业务需求,则可以创建一个自增字段作为主键,再添加一个唯一索引;
  • 选择作为主键的列必须在插入后不再修改或者极少修改,否则需考虑使用自增列作为主键;
  • 如果一个业务上存在多个 (组) 唯一键,以查询最常用的唯一键作为主键。

over

 

posted on   一只阿木木  阅读(355)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

点击右上角即可分享
微信分享提示