mysql sql 优化

Defry

已于 2024-03-23 10:24:57 修改

阅读量412

点赞数 6

文章标签： mysql sql 数据库

于 2024-03-22 18:06:57 首次发布

本文链接：https://blog.csdn.net/Defry/article/details/136940459

版权

本文介绍了如何通过为常用列添加索引、选择性查询、使用LIMIT、合理安排GROUPBY和ORDERBY、以及利用EXPLAIN分析查询计划来提高SQL查询速度。还强调了定期优化索引、避免无效查询条件、数据类型选择和减少全表扫描的策略。

摘要由CSDN通过智能技术生成

使用索引：为常查询的列添加索引可以显著提高查询速度。

CREATE INDEX idx_column ON table_name(column_name);

查询时只查询用到的字段，避免select *

SELECT id, name FROM table_name;

使用LIMIT限制查询结果集大小：

SELECT id, name FROM table_name LIMIT 10;

确保GROUP BY和ORDER BY中的列有合适的索引。
使用EXPLAIN分析查询计划，调整索引和查询语句。

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

定期优化和重建索引：【特别是发生过大批量delete数据的表，通过优化可以释放回收碎片】
OPTIMIZE TABLE table_name;
配合：重新收集下表的统计信息
analyze table table_name;
尽量少使用数据区分度不大的索引字段作为查询条件，如类型只有 1种值，当数据量大的时候会严重降低查询效率
如 where userid=‘123’ and busType=‘view’ userid和view都有索引，当数据量大时busType 只有view这一个值即加不加该条件结果不变，则and busType=‘view’ 会严重降低SQL的查询效率，直接删掉该查询条件即可
SQL时where 条件中的字段不要转换表字段数据类型如time int char ，避免隐式转化导致索引失效【无法走索引】
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算：
应尽量避免在 where 子句中对字段进行表达式操作
select id from t where num/2=100
应改为:
select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作【特别需要注意：日期】
select id from t where substring(name,1,3)=‘abc’–name以abc开头的id
select id from t where datediff(day,createdate,‘2005-11-30’)=0–'2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’
用union all代替union【数据会去重】
小表驱动大表：（大数据量）In（小数据量），（小数据量）exits(大数据量)
优先执行in里面的语句，exists 拿左边的结果匹配exists（）中的数据
循环插入改为批量插入，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。
判断记录是否存在，不再使用count(*)

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}

in中值太多
如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。
能用inner join的地方，尽量少用left join
left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。
如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。
如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。
要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。
控制索引的数量
单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。
能够建联合索引，就别建单个索引，可以删除无用的单个索引。
将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等，在业务表中只需要建几个关键索引即可
选择合理的字段类型
能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。
sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能
应尽量避免在 where 子句中使用!=或<>操作符，根据值对应数据量的不同会导致索引失效
如del_flag有索引，数据del_flag都是为0的记录，当where del_flag !=‘1’ 时就回导致索引失效，从而全表扫描
应尽量避免在 where 子句中对字段进行 null 值判断，有可能会导致索引失效【对应值数据量改变而改变】
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

t.create_time is not null 不走索引【create_time 有索引只有几条为空null】
20. 未命中索引的情况要具体情况具体分析
随着对应值数据量增加，也会导致未命中索引的情况
21. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
22. 避免全like
select id from t where name like ‘%abc%’
23.
24.
25.
26.
27.