sql的优化

最新推荐文章于 2024-08-14 13:50:42 发布

linkingfei

最新推荐文章于 2024-08-14 13:50:42 发布

阅读量114

点赞数

分类专栏： # mysql

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

SQL执行效率提高了10000000倍

分解关联查询

对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
WHERE tag.tag = 'mysql';
分解成下面这些查询来代替：
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

MySQL优化方法

用EXPLAIN查看SQL执行计划：
EXPLAIN详细使用
select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换
SELECT*语句务必指明字段名称
当只需要一条数据的时候，使用limit 1
分页方式以提高分页的效率：

select id,name from product limit 866613, 20
优化：
select id,name from product where id> 866612 limit 20

*分段查询：

用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。
这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示

避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描

不建议使用%前缀模糊查询

会导致索引失效而进行全表扫描
解决：
使用全文索引
ALTER TABLE dynamic_201606 ADD FULLTEXT INDEX idx_user_name (user_name);
select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表

*MySQL中没有full join，可以用以下方式来解决

select * from A left join B on B.name = A.name where B.name is null union all select * from B;

分页查询方法及其优化

https://mp.weixin.qq.com/s/xqzxJFKIHI4kiG8Q5xNEUg

1、传统方法

直接使用数据库提供的SQL语句：
SELECT * FROM 表名称 LIMIT M,N

2、利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

sql优化

使用join的关联查询代替子查询
索引建立用于join\where\order by的字段上
where子句存在is null\null 不会使用索引
On——where——having
用union all代替union，不进行过滤

查看sql时间

set statistics time on
sql
set statistics time off

查看IO的时间：

set statistics io on
sql
set statistics io off

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。
例如上面的语句B是一个高频查询的语句，我们可以建立(username,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。当然，添加索引是有维护代价的，所以添加时也要权衡一下。

前缀索引

mysql的b+树索引遵循“最左前缀”原则，继续以上面的例子来说明，为了提高语句B的执行速度，我们添加了一个联合索引（username,password）,特别注意这个联合索引的顺序，如果我们颠倒下顺序改成（password,username),这样查询能使用这个索引吗？答案是不能的！这是最左前缀的第一层含义：联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引。