MySQL 学习总结 10 - 优化(sql 语句层)- 锁与 sql 优化
Write By CS 逍遥剑仙
我的主页: http://www.csxiaoyao.com/
- GitHub: https://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;
优化方法:
业务上解决: 不允许翻过 100 页
不用 offset, 用条件查询
通过 where + id>offset + order + limit 取代 order + limit
因为查询的数据量较少, 所以能够使用索引, 所以快.
来源: https://www.qcloud.com/developer/article/1352202