SQL的生命周期?
1. 应用服务器与数据库服务器建立一个连接
2. 数据库进程拿到请求sql
3. 解析并生成执行计划,执行
4. 读取数据到内存并进行逻辑处理
5. 通过步骤一的连接,发送结果到客户端
6. 关掉连接,释放资源大表数据查询,怎么优化
1. 优化shema、sql语句+索引;
2. 第二加缓存,memcached, redis;
3. 主从复制,读写分离;
4. 垂直拆分,根据你模块的耦合度,将一个大的系统分为多个小的系统,也就是分布式系统;
5. 水平切分,针对数据量大的表,这一步 麻烦, 能考验技术水平,要选择一个合理的sharding key,
为了有好的查询效率,表结构也要改动,
做一定的冗余,应用也要改,sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全
部的表;超大分页怎么处理?
超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age
> 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然
后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in
(select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由
于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select
* from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种, 但是核心思想
都一样,就是减少load的数据.
从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允
许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.
解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.
在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.
【推荐】利用延迟关联或者子查询优化超多分页场景。 说明:MySQL并不是跳过offset
行,而是取offset+N行,然后返回放弃前offset行,返回N行,那当offset特别大的时候,效率就非常的
低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。 正例:先快速定位需要获取
的id段,然后再关联:
SELECT a.* FROM 表1 a,(select id from 表1 where 条件 LIMIT 100000,20) b w here
a.id=b.id
mysql分页
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须
是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返
回记录行的 大数目。初始记录行的偏移量是 0(而不是 1)
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为
-1:
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
如果只给定一个参数,它表示返回 大的记录行数目: mysql> SELECT * FROM table LIMIT 5; //检索前
5 个记录行
换句话说,LIMIT n 等价于 LIMIT 0,n。
1
1
1慢查询日志
用于记录执行时间超过某个临界值的SQL日志,用于快速定位慢查询,为我们的优化做参考。
开启慢查询日志
配置项:slow_query_log 可以使用show variables like ‘slov_query_log’查看是否开启,如果状态值为
OFF,可以使用set GLOBAL slow_query_log = on来开启,它会在datadir下产生一个xxx-slow.log的文
件。
设置临界时间
配置项:long_query_time 查看:show VARIABLES like 'long_query_time',单位秒设置:set
long_query_time=0.5
实操时应该从长时间设置到短的时间,即将 慢的SQL优化掉
查看日志,一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中