MySQL性能优化(一)索引缺失引起的全表扫描

俊达3年前技术文章1254


索引缺失是引起数据库性能问题的第一大原因。


一个例子

这是一个非常简单的SQL,

SELECT *
FROM template
WHERE templet_id = 2 AND status = 1


执行计划

我们来看一下这个SQL的执行计划



这是一个简单的执行计划,我们需要关注的信息主要包括:

table: 访问的表,这里是template表

type: ALL,表示全表扫描

possible_keys: 该查询可选的索引。这里没有任何索引可以使用

key: 使用的索引。这里没有使用索引

key_len: 使用到的索引长度。

rows: 预估需要访问的数据。


从这里的执行计划可以得到的信息:该查询每次执行都需要扫描3.6万行数据,没有任何索引可以使用。


优化

对于这种场景,我们可以给过滤性高的条件建立索引

alter table template
    add key idx_templateid_status(templet_id, status);


这里的关键是过滤性,如果过滤性不高,那么建了索引也不一定能提升性能。

过滤性

过滤性可以理解为使用给定where条件过滤出来的记录数占总记录数的比例,这个比例越小,则使用索引的效果越好。

可以使用如下的SQL来分析一个字段或多个字段的过滤性:

select 1 / count(distinct c) from tab;

字段的唯一值越多,则上述SQL的查询值约小,过滤性越高。


数据倾斜

在有些业务场景下,存在数据倾斜,也就是字段有的值的数据量特别大。可以通过如下SQL来判断是否存在数据分布不均匀的问题。

Select col, count(*) 
From tab
Group by col 
order by count(*)
desc limit 10


对于存在数据倾斜的场景,则索引是否有效,取决于where条件中的值的过滤性,过滤性高的就可以通过索引来提升查询效率。


组合索引

在本案例中,我们建立了一个组合索引: 

idx_templateid_status(templet_id, status)

组合索引中字段的顺序很重要,需要根据业务的查询场景来设计。关于组合索引中字段顺序的问题,我们后续再来详细分析。

相关文章

MySQL 同步方式

同步方式一、分类同步大致为异步、半同步、增强版同步、全同步;二、详情1.异步复制MySQL 默认的复制策略,Master处理事务过程中,将其写入Binlog就会通知Dump thread线程处理,然后...

MySQL 评估 ALTER TABLE 进度(5.7)

MySQL 评估 ALTER TABLE 进度(5.7)

一、前言问题:大表里执行 ALTER TABLE 的时候,经常会比较忐忑,会面临 “跑又跑不完 Kill 也不敢 Kill” 的窘境。需求:客户在执行 ALTER TABLE 时也会让我们来评估影响的...

sqlserver迁移job步骤

sqlserver迁移job步骤

1)源服务器 sql server 找到sql server 代理,选中作业 2)按F7 弹出作业对象资源管理器,全选中作业 3)右击单出编写job 脚本窗口,将job 创建脚本保存到查询编辑器窗口 ...

MySQL运维实战之备份和恢复(8.9)xtrabackup备份指定表

备份部分表如果实例设置了参数innodb_file_per_table,xtrabackup可以备份部分表。通过--tables,--tables-file,--databases,--databas...

HDP-Yarn开启CPU调度和隔离

HDP-Yarn开启CPU调度和隔离

进入到ambari主界面 点击yarn 点击config CPU Scheduling and Isolation 设置为enable修改高级配置点击ADVANCED搜索需要修改的配yarn.node...

MySQL 有意思的权限报错

前言今天遇到了一个报错,觉得挺有意思的,在此记录下。SELECT command denied to user 'xxx'@'xxx' for table 'xxx'1. 报错原因这里是研发提了一条修...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。