mysql索引更新耗时_MYSQL 索引 性能优化

最新推荐文章于 2023-11-15 12:28:02 发布
最新推荐文章于 2023-11-15 12:28:02 发布
阅读量981 收藏 1
点赞数
文章标签： mysql索引更新耗时
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_28559803/article/details/113303920
版权
本文探讨了MySQL查询性能优化和索引管理，包括查询瓶颈、EXPLAIN的使用、何时创建索引、索引使用注意事项、索引问题分析及解决策略。讲解了全表扫描、覆盖索引、范围查询的影响，以及如何通过调整查询方式和使用Exist与In来优化性能。此外，还提到了更新操作中的字符串条件和事务对锁的影响，以及并发和线程安全问题。
摘要由CSDN通过智能技术生成

Mysql常见瓶颈

查询语句写的烂、索引失效、关联查询太多join、服务器调优及各个参数设定(缓冲线程数等)

explain用法和结果的含义

explain select t.id,t.loan_order_no,t.loan_amt,t.success_time as loan_success_time from t_ssj_loan_order t where t.state=1 and t.success_time  < '2019-07-16'

and t.success_time  > '2019-07-14' AND t.loan_status =2  and not EXISTS (select 1 from t_ssj_repay_order r where r.state=1 and t.loan_order_no = r.loan_order_no

and r.repay_status=2 and r.repay_type =3) and id > 1 LIMIT 10;

t_ssj_loan_order：

ce2e476626e4bfdc54dadc0731e2211d.png

t_ssj_repay_order：

9e012c1f8125c6a8ab3208d946693829.png

2af86237cc4ad07f05a19263af7952b9.png

id：查询中执行select字句或者操作表的顺序，id相同执行顺序由上至下，id不同 id值越大优先级越高越先被执行

select_type: SIMPLE:简单SELECT(不使用UNION或子查询)  PRIMARY:最外面的SELECT SUBQUERY:子查询中的第一个SELECT DERIVED:导出表的SELECT(FROM子句的子查询) UNION:UNION中的第二个或后面的SELECT语句 UNION RESULT:UNION 的结果

table: 输出的行所引用的表

type：system:表仅有一行(=系统表)。这是const联接类型的一个特例。const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!  eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。range:只检索给定范围的行,使用一个索引来选择行。

index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描

possible_keys：MySQL能使用哪个索引在该表中找到行

key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

key_len：显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL

ref：显示使用哪个列或常数与key一起从表中选择行

rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

Extra：Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

需要建立索引：

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该建立索引 如：customerId、loanOrderNo

3、查询中与其他表关联的字段建立索引 如：customerId、loanOrderNo

4、频繁更新的字段不适合建立索引(不单单更新数据还会更新索引增加了Io)

5、where里面用不到的字段不建立索引

7、查询中的排序字段字段若通过索引去访问将大大提升排序速度(即覆盖索引)

8、查询中统计或者分组字段

不需要建立索引：

1、表记录少

2、数据重复且分布平均的字段 如：status(索引越是唯一查询速度越快 索引越高效)

索引中常见问题分析

现在以随手记相关表为例分析索引在使用中的常见问题：

建立复合索引：KEY `idx_name_idno_ph_card` (`customer_name`,`id_no`,`bind_phone`,`card_no`) USING BTREE(注意索引字段顺序)

索引建立注意点：

1、  复合索引遵循索引顺序，中间不能跳过

n  当查询列和索引列字段相同且排序次序和索引字段次序相同时命中索引(效率最高)，否则文件排序

explain select customer_name,id_no,bind_phone,card_no from t_ssj_sign

ORDER BY customer_name,id_no,bind_phone,card_no;

fc966e54f39c1e3cd8e51e4248124f3e.png

n  当查询列和索引列字段相同且排序次序和索引字段次序不相同时 文件排序

391d158c607a88e23d81f3452d3dac2b.png

n  顺序使用复合索引中的前两个字段，顺序几个有效几个

select * from t_ssj_sign

where customer_name = '张飞'

and  id_no=     'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI';

2503ac526a31b5d107883131b981e349.png

n  非顺序使用复合索引中的字段(使用了三个却只命中两个)，顺序几个有效几个

select * from t_ssj_sign

where customer_name = '张飞'

and id_no= 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'

and card_no = '6222081510001755903';

71d5786e1e65aaaa885b9b0d3f014d65.png

2、索引上列上不能做任何操作(计算、函数、类型转换)会导致索引失效转而全表扫面

explain select * from t_ssj_loan_order t where left(t.loan_order_no,3) ='181';

ccaf246b575cfab266662fdc447cf7a6.png

3、范围右边的索引会失效

explain select * from t_ssj_sign

where customer_name = '张飞'

and id_no> 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'

and bind_phone='13243211234'

9fdb10bbdd201c5b1ed1290d76e2d8d4.png

4、尽量使用覆盖索引(索引列和查询列一致)减少select*(不论出于覆盖索引考虑还是性能考虑)

n  查询列和索引列不相同时

select * from t_ssj_sign

where customer_name = '张飞'

and id_no= 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'

and bind_phone='13243211234'

and card_no = '6222081510001755903';

928b8748cafacfb9180eca2e0c48c99b.png

n  查询列和索引列相同时 直接访问索引(效率最高，比使用*效率高)

select customer_name,id_no,bind_phone,card_no from t_ssj_sign where customer_name = '张飞'

and  id_no='bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'

and bind_phone='13243211234'

and card_no = '6222081510001755903';

3f70bad45e77e9a904271a97bb802eb9.png

5、！= 和 <> 无法使用索引会导致全表扫描

select * from t_ssj_loan_order t where t.loan_order_no !='18122657915176190131';

8247c03da4cfda5758e6036cbab3c144.png

6、is null is not null 也无法使用索引

explain select * from t_ssj_loan_order t where t.ssj_order is NOT NULL;

3d494e91a405a8cd80d13814e074b6f8.png

7、like以通配符开头的索引会导致全表扫描 (没有利用索引)

explain select * from t_ssj_loan_order t where t.loan_order_no like '%18122657915176190131%';

8ee412b133170e0193459abf3a04a882.png

如果业务逻辑允许可改为：

explain select * from t_ssj_loan_order t where t.loan_order_no like '18122657915176190131%';

25f8b69c2c2dfefd1685d6bc080bc788.png

也可以采用覆盖索引的方式，如果查询出的字段不满足业务可以把这个业务拆分两次执行。

8、字符串不加单引号索引失效

explain select * from t_ssj_loan_order t where t.loan_order_no =18122657915176190131;

e44f87bb4a887a9c82ce455a51a9059b.png

查询优化：小表驱动大表(连接耗时)

Exist:左边数据少，in：右边数据少

explain select * FROM t_ssj_repay_order t where t.loan_order_no  in(select loan_order_no from t_ssj_loan_order o where o.create_time>'2019-01-01');

explain select * FROM t_ssj_repay_order t where EXISTS (select 1 from t_ssj_loan_order o where o.loan_order_no=t.loan_order_no and o.create_time>'2019-01-01');

对数据进行更新时如果字符串条件未带单引号或双引号时会导致锁全表(mysql底层做了类型转换)

登陆mysql: .\mysql.exe -u root –p

事务手动提交：set autocommit=0;

use test;

创建测试表：

CREATE TABLE `t_cust` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

`cid` bigint(20) NOT NULL COMMENT '客户ID',

`no` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '客户编号',

PRIMARY KEY (`id`),

KEY `idx_cid` (`cid`),

KEY `idx_no` (`no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='客户表';

录入测试数据：

insert into t_cust values(1,11,'001'),(2,22,'002'),(3,33,'003');

两个窗口：默认行锁 分别更新两条记录(字符串字段字符串格式)没有锁表

update t_cust set cid=1 where no='001';

update t_cust set cid=2 where no='002';

b4454650222a892dd12bfac0d6e7b600.png

两个窗口：默认行锁 分别更新两条记录(字符串字段非字符串格式)锁表

update t_cust set cid=222 where no=002;

update t_cust set cid=111 where no='001';

537661fafbac3f5f72fca7e74b3e698a.png

Show open tables;

3854dfb0a677e0cca1cb6cbe62245eab.png

show OPEN TABLES;

mysql日志相关分析

查询是否锁表：show OPEN TABLES where In_use > 0;

查询到相对应的进程：show processlist;

查看正在锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

慢查询日志：show variables like ‘%slow_query_log%’

fab72cfc7610a393de3f619252b9bf3a.png

set global slow_query_log=1 开启了慢查询日志只对当前数据库生效

ecd83fff57bb31452fd7c6a7f6f26abb.png

也可以修改my.cnf  假如运行时间大于long_query_time会被记录下来

1fae688c72a5719d855068276cdacc36.png

Select sleep(5)

b8b6793f12eb87adb6b2a757a031d1cc.png

cd966b3ef88a61d4cb0b9dcb5d72d828.png

c0313ceebc04fa3b78710db41664f11f.png

日志分析工具：Mysqldumpslow:mysqldumpslow –s –r –t 10 /var /lib/mysql/.log 返回记录数最多的十条记录

还可以借助Show profiles:mysql 执行明细

show variables like '%profiling%';

777851ddf605ee5b944f21bfcb63fc03.png

b4777a22487f744a19815ec8fcd9ac58.png

Show profiles:mysql 执行明细 set profiling=on

5f89a84edf127784d31ddcc4f3d21b0d.png

show profile cpu,block io for query 10;

0c459e510e863e6cb9507556a7ad1979.png

瓶颈：cpu运算，io

线上坑之并发、线程安全问题

线程安全性问题满足以下三个条件：多线程 多个线程共享一个资源 对资源进行非原子性操作(如a++读写操作)

单服务器场景

对于多个线程共享且修改一个资源时发生的并发问题我们一般通过Synchronize,threadLocal,select for update,数据库锁等处理并发安全性问题。Synchronize在获取锁时会强制从主内存中读取对应资源的最新值到工作内存中，在释放锁时前会强制更新主内存中的对应值。这样子就保证了共享资源的可见性。ThreadLocal则通过对于每个线程维护其单独的变量副本避免线程安全性问题。

1、jvm执行程序的时候进行指令重排，避免一些非原子性操作。

private volatile int count=0;

public static void main(String[] args) {

final ThreadTest test = new ThreadTest();

for (int i =0 ; i<1000 ; i++){

new Thread(new Runnable() {

@Override

public void run() {

try {

Thread.sleep(100);

} catch (InterruptedException e) {

e.printStackTrace();

}

test.addNum();

}

}).start();

}

try {

Thread.sleep(1000);

} catch (InterruptedException e) {

e.printStackTrace();

}

System.out.println(">>>>>>>>>>>count="+ test.count);

}

public void addNum(){

count++;

}

分布式场景

我们一般通过分布式锁机制，通过redis获取分布式锁。利用redis.SETNX命令。把要锁定资源的某个标识作为key，并设置全局唯一的值作为redis的key的值，如果SETNX key value返回1那么我们认为获取锁成功，否则认为获取锁失败。但是使用SETNX可能会存在如下问题：

a如果对于所加的锁没有设置有效时间，某个服务成功获取了某个分布式锁，此时该服务崩溃，那么该分布式锁将因锁持有者崩溃而无法释放。所以一般加锁后会紧接着设置锁的有效时间。

b极端场景设置key有效期异常则可以修改获取锁的方法同时根据业务合理设置过期时间：

public boolean getLock(String key, int value) {

try {

if (cacheClient.setnx(key,value) == 1) {

cacheClient.expire(key, timeOut);

return value;

}

if (cacheClient.ttl(key) < 0) {

cacheClient.expire(key, timeOut);

}

} catch (Exception e) {

return false;

}

}

c这里设置的超时时间时还有一个可能存在的问题，假如我超过超时时间都还没有完成业务逻辑的情况下，key会过期，其他线程有可能会获取到锁。这样一来的话，第一个线程还没执行完业务逻辑，第二个线程进来了也会出现线程安全问题。这个除了合理设置超时时间外还可以通过redisson实现：

Config config = new Config();

config.useClusterServers()

.addNodeAddress("redis://address1")

.addNodeAddress("redis://address2")

.addNodeAddress("redis://address3");

RedissonClient redisson = Redisson.create(config);

RLock lock = redisson.getLock("anyLock");

lock.lock();

lock.unlock();

redisson中有一个watchdog的概念，翻译过来就是看门狗，它会在你获取锁之后，每隔10秒帮你把key的超时时间设为30s。这样的话，就算一直持有锁也不会出现key过期了，其他线程获取到锁的问题了。

总结：关键处进行事务控制，减少非原子性操作

事实求是
关注
mysql sqlnocache_Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE
weixin_29358053的博客
01-19 523
场景产品中有一张图片表pics，数据量将近100万条，有一条相关的查询语句，由于执行频次较高，想针对此语句进行优化表结构很简单，主要字段：user_id 用户IDpicname 图片名称smallimg 小图名称一个用户会有多条图片记录，现在有一个根据user_id建立的索引：uid，查询语句也很简单：取得某用户的图片集合：select picname, smallimg from pics wh...
mysql 索引 倒序_使用倒序索引提升ORDER BY DESC性能
weixin_29688227的博客
02-18 1359
使用倒序索引(INDEX DESC)，可以大幅提升带有order by desc子句的SQL语句性能。一、场景1、表名：test_t，有一字段名为object_id2、总数据量：580000行，segment_size：72MB3、Where条件(Owner=’SYS’ and Object_id>50000)的行数：32472行4、SQL语句：select * from test_t wh...
mysql索引更新耗时_数据库-Titan索引更新花费的时间太长
weixin_39631344的博客
02-28 196
即使在空数据库上,在Titan 1.0中创建索引也要花费几分钟.时间似乎很准确,这表明有不必要的延迟.我的问题是：如何缩短或消除Titan重新编制索引所需的时间？从概念上讲,由于无需进行任何工作,因此时间应最短,当然不应为4分钟.(注：我之前曾指出过一个解决方案,该解决方案只是让Titan等待整个延迟而不会超时.这是错误的解决方案-我想完全消除延迟.)我用来从头开始设置数据库的代码是：graph ...
MySQL索引统计信息为什么不更新
老叶茶馆
05-22 805
本文转自问题最近看到一个问题：一个大表有大约2000万行的数据，但其统计数据还是1500万，且收集的时间为去年。我们知道，一般来讲，表数据更改超过10%会重新收集统计数据，统计数据在非dive（eq_range_index_dive_limit参数相关）的情况下就会使用统计数据作为优化器判断执行计划的标准。何时进行统计数据收集这里只看默认配置下的持久化(PERSISTEN...
mysql索引更新耗时_为什么mysql 走同样的索引耗时差别却巨大?
weixin_42310891的博客
01-20 134
耗时查询：mysql> select * from channel_details where channel like "%" and type=29 and `year`>=2017 limit 10;+---------+------+-------+---------------+-------------+---------+------+-----+------------...
mysql在线更新工具简介（处理索引多的表更新慢的问题）
jsczxy2
12-20 183
【MySQL】online ddl 工具之pt-online-schema-change 下面是些介绍文章，记录一下以备后用： http://www.oschina.net/p/percona-toolkit http://blog.csdn.net/attilax/article/details/24144353 http://www.dataguru.cn/article-3460...
mysql 索引 变慢_为什么我加索引之后，速度变慢了
weixin_30980893的博客
01-20 1453
疑问，如题具体是这样的，有一张表，数据有236W行，SELECT COUNT(*) FROM zt_action；随后进行无索引搜索read这个字段的值SELECT * FROM zt_action WHERE `read`='0'161W数据用时11s执行EXPLAIN SELECT * FROM zt_action WHERE `read`='0'加索引CREATE INDEX `read` ...
Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE
12-15
总的来说，通过创建覆盖索引和理解并有效利用MySQL的缓存机制，我们可以显著提升查询性能，减少不必要的I/O操作，从而优化数据库的响应时间和资源利用率。在实践中，性能优化是一个持续的过程，需要不断分析查询行为...
Mysql性能优化案例 - 覆盖索引分享
09-10
总结来说，通过合理设计和应用覆盖索引，我们可以有效地优化MySQL查询性能，特别是在那些频繁执行且涉及大量数据的查询中。在实际工作中，应结合`EXPLAIN`分析，理解查询执行计划，以此为依据进行索引调整，实现...
MySql索引优化
12-14
MySQL索引优化是数据库管理中的关键环节，它直接影响着数据查询效率和系统整体性能。创建索引的主要目的是为了加速数据的检索，特别是对于大型数据库，这一点尤为重要。索引能够确保数据的唯一性，使得数据库表中的...
update会锁表吗？
南客先生的博客
10-20 1225
update锁表锁行问题
【实践、技术】解决一起索引字段经常更新造成的索引效率低下的问题
cfy_zybing的专栏
03-07 1228
        操作系统： 数据库版本：     数据库机器读盘厉害，由于没有完整的检测手段以及历史累计数据，因此只能用简单的方式先进行检查：   检查消耗磁盘多的SQL语句：   从V$SQLAREA中查询最占用资源的查询 select b.username username         ,a.disk_reads reads         ,a.exe...
Mysql 提升索引效率优化的八种方法
最新发布
it知识分享 free for nothing..
11-15 2701
Mysql 提升索引效率优化的八种方法
千万数据怎么加索引_MySQL索引那些事
weixin_39684228的博客
11-28 2060
大家有没有遇到过慢查询的情况，执行一条SQL需要几秒，甚至十几、几十秒的时间，这时候DBA就会建议你去把查询的 SQL 优化一下，怎么优化？你能想到的就是加索引吧？为什么加索引就查的快了？这就要从索引的本质以及他的底层原理说起。索引是什么那索引到底是什么呢?你是不是还停留在大学学 『数据库原理 』时老师讲的“索引就像字典的目录”这样的概念？老师讲的没错，但没有深入去讲。其实索引就是一种用于快速查找...
为什么索引可以让查询变快？终于有人说清楚了！
m0_71777195的博客
12-09 1003
人类存储信息的发展历程大致经历如下：由于是个人凭着自己理解总结的，因此可能不一定精确，但是毋庸置疑的是，在当代，各大公司机构部门的数据都是维护在数据库当中的。数据库作为数据存储介质发展的最新产物，必然是具有许多优点的，其中一个很大的优点就是存储在数据库中的数据访问速度非常快。数据库访问速度快的一个很重要的原因就在于索引index的作用。也就是这篇文章的主要想介绍的内容，为什么索引可以让数据库查询变快？
mysql索引添加缓慢_mysql 中 创建索引很慢，怎么解决
weixin_30806145的博客
01-18 7114
引用如题，我现在 有一张表。。。里面的数据大概就是 800w 条左右，当然以后也可能会更多，这个表会频繁的更新！我现在的处理是：每次更新 都会先truncate 这张表(因为里面的数据 已经不需要了)，然后我会 drop index ！这样之后我会去 insert 数据 。。。。(这些数据量 有可能过千万。。级别的) ，之后就是为数据建立索引！建立索引的过程很慢。。。800w 数据 现在执行了 半...
【索引 index】是个好东西——大幅加快MySQL update更新速度的方法
热门推荐
sscssz的博客
12-14 2万+
去年这个时候，也是做数据库操作 其中，是对主键为字符串的大概几万条的数据进行更新，就已经很慢了 语句类似于这样： UPDATE persondata SET age=age+1 where name = '伏地魔'; 因为要全库去找，字符串比较，速度很慢，大概一秒钟几条，如果数据一行很长的话，可能会更慢 忽然想起来，是不是可以建个索引 然后就试着建了个索引，当点save的那
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值