mysql学习总结10 — 优化(sql语句层)-锁与sql优化

mysql学习总结10 — 优化(sql语句层)-锁与sql优化

Write By CS逍遥剑仙 我的主页: www.csxiaoyao.com GitHub: github.com/csxiaoyaojianxian Email: sunjianfeng@csxiaoyao.com QQ: 1724338257

sql语句层的优化主要包括锁的使用、慢查询的定位、limit分页优化

1. 锁机制

1.1 概念

读锁:共享锁S-lock,读操作时添加,所有用户(包括当前用户)只可读不可写

写锁:独占锁/排他锁X-lock,写操作时添加,其他用户不能读写

表级锁:开销小,加锁快,冲突率高,并发低

行级锁:开销大,加锁慢,冲突率低,并发高

myisam只支持表锁,innodb支持表锁和行锁。锁机制消耗性能,容易发生阻塞,拖慢网站速度。

1.2 表锁

# 添加锁,当前用户添加表的锁定后只能操作锁定的表,不能操作未锁定的表
# lock table table_1 read|write, table2 read|write
# 添加读锁
mysql> lock table user read; -- 添加读锁,所有用户只读不写
# 释放锁
mysql> unlock tables;
# 添加写锁
mysql> lock table user write; -- 添加写锁,其他用户不可读
# 释放锁
mysql> unlock tables;

1.3 行锁

# innodb支持行锁,myisam不支持
mysql> alter table user engine innodb;
# begin;
# 执行语句...
# commit;
mysql> begin;
mysql> update user set name="sun" where id=1; -- 只锁定了id=1的行
mysql> commit;

2. 慢查询定位

2.1 临时启动慢查询日志

默认未开启

$ mysqld --safe-mode -slow-query-log
# 慢查询阈值默认10秒,修改为1秒
mysql> show variables like 'long_query_time';
mysql> set long_query_time=1;

2.2 修改配置文件启动慢查询日志

修改配置文件 my.ini

log-slow-queries="/xxx/slow-log"
long_query_time = 1

2.3 精确记录查询时间

(1) 开启profile机制

精确到小数点后8位

mysql> set profiling = 1;

(2) 查询时间

mysql> show profiles;

(3) 不需要分析时关闭profile

mysql> set profiling = 0;

3. limit分页优化

limit offset, N; 当offset非常大时,效率极低,表现为翻页越翻越慢,因为mysql的操作非跳过offset行,单独取N行,而是取offset+N行,舍弃前offset行,返回N行。此外,mysql中获取的行数较多,达到整表记录的一定比例时会默认不使用索引。

预处理:(1) 设置主键索引 (2) 关闭查询缓存

分页算法:

limit (page-1)*length, length;

优化方法:

  1. 业务上解决:不允许翻过100页
  2. 不用offset,用条件查询

通过 where + id>offset + order + limit取代order + limit

因为查询的数据量较少,所以能够使用索引,所以快。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏极客慕白的成长之路

数据库系统概述必背知识点整理

14420
来自专栏java系列博客

pl/sql导入excel到oracle表

24370
来自专栏北京马哥教育

思路决定出路 | 101个MySQL调试和优化技巧

MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧...

29280
来自专栏小灰灰

Mysql之锁与事务

平时的业务中,顶多也就是写写简单的sql,连事务都用的少,对锁这一块的了解就更加欠缺了,之前一个大神分享了下mysql的事务隔离级别,感觉挺有意思的,正好发现一...

468130
来自专栏不想当开发的产品不是好测试

mysql 删表引出的问题

背景 将测试环境的表同步到另外一个数据库服务器中,但有些表里面数据巨大,(其实不同步该表的数据就行,当时没想太多),几千万的数据!! 步骤 1. 既然已经把数据...

32670
来自专栏乐沙弥的世界

Buffer cache 的调整与优化(一)

Buffer Cache是SGA的重要组成部分,主要用于缓存数据块,其大小也直接影响系统的性能。当Buffer Cache过小的时候,将会造成更多的

12830
来自专栏大数据

Python 101:如何从RottenTomatoes爬取数据

今天,我们将研究如何从热门电影网站Rotten Tomatoes爬取数据。你需要在这里注册一个API key。当你拿到key时,记下你的使用限制(如每分钟限制的...

49460
来自专栏idba

死锁案例之十

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

11320
来自专栏数据和云

举一反三:跨平台版本迁移之 XTTS 方案操作指南

作者 | 罗贵林: 云和恩墨技术工程师,具有8年以上的 Oracle 数据库工作经验,曾任职于大型的国家电信、省级财政、省级公安的维护,性能调优等。精通 Ora...

19130
来自专栏文渊之博

探索SQL Server元数据(一)

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如...

15820

扫码关注云+社区

领取腾讯云代金券