BATJ解决千万级别数据之MySQL 的 SQL 优化大总结

说实话，当你处理数千万个数据点时，优化 MySQL 确实是一件令人头疼的事情。
当时我刚开始做，SQL执行速度极慢，卡住了。
接下来我慢慢摸索总结出一些道理：
1 ．不要让MySQL扫描整个表 对于数千万个数据点，全表扫描无异于自杀。
您需要向 where 和 orderby 列添加索引。
但添加并不意味着立即生效。
必须使用EXPLAIN+SQL语句来查看执行计划。
如果type字段为ALL，则表示不存在索引。
具体来说，请记住：
不要对where子句中的字段进行空判断。
比如你使用name is null，索引肯定会被毁掉。

!= 和运算符是最烦人的，直接使索引失效。

表达式操作会使索引失效，例如age/2 =2 0，必须改为age=4 0
函数操作也是如此。
DATE(create_time)=... 肯定行不通。
将其更改为 create_time>=... 和 create_time<...
不要将%放在类似的查询前面。
像'%wh'这样的用户是一个完全无用的索引。
需要考虑全文索引
2 、索引需要有针对性。
索引越多越好。
添加太多索引并不好。
但需要补充的是：
联合索引必须是有序的，例如KEYidx_userid_age(userId,age)，搜索时必须从左到右匹配。

使用覆盖率可以避免出现问题。
这意味着所有需要检查的列都直接安装在索引中。
选择是最有问题的。

不要再次创建索引。
例如，(A,B) 和 (A) 是重复的。
只需用 (B,A) 覆盖它们即可。

索引不超过 5 个。
如果太多，插入和更新会变慢。

对于性别这样重复率较高的字段，不要浪费索引资源
3 .更多性感操作
不要对查询使用选择，只需指定所需的列。

查询连接表时，小表领先大表。
例如，使用with子句先处理小表，然后再join。

批量输入数据，一条一条的输入太慢
参加人数不要超过 5 张桌子。
如果表超过三个，则不要使用join。
使用冗余表来代替。

4 工具是关键
MySQL自带的慢查询日志最方便，在my.ini中打开即可
MySQLdumpslow可以分析统计数据，例如通过检查1 0个最常访问的SQL语句
美团的SQLAdvisor不错，开源，直接提供建议
嗯，千万级数据，SQL优化真是个技术活。
你要慢慢适应，慢慢看，慢慢改变。

如何优化大批量数据查询，提升 5000 条主表数据的查询效率？

哎呀，我来说说我遇到的坑吧。
前年我在上海做一个项目，很担心主表里一堆5 000条数据。
支票的速度慢得像蜗牛一样。

当时数据量很小，但是查询效率很低。
我只是在想如何让它更快。
我考虑过拆分工作并尝试并行查询。

我将 5 000 个订单号分成 1 00 个小包，每个包包含 5 0 件商品。
我们雇了 1 00 个男孩，每个人负责检查一个包裹。
检查完后，使用CountDownLatch，让大哥等大家都检查完了再总结。

这个技巧确实有效。
但后来发现是线程太多，数据库连接不够。
例如，MySQL 默认有 1 5 1 个连接。
当我启动 1 00 个线程时，它卡住了。
因此，必须测量线程数，不能与它们混淆。

后来我想到了这个IN查询：当单数太多时，数据库总是要一一匹配，最后扫描全表。
这应该会耗尽数据库。

我将其更改为“加入”。
比如我想查主表和明细表，以前我都是用IN往里面丢5 000个订单号，后来改用JOIN。
这样，数据库可以使用HashJoin或NestedLoop等算法，效率更高。

而且我还必须确保奇数字段有索引，否则 JOIN 将毫无用处。
使用 EXPLAIN 检查执行计划并确保它已建立索引。

最后，我将这两个技巧结合起来。
对于分片，使用JOIN检查每个小兄弟，然后在检查大兄弟后合并它们。
效果确实不错。
之前我要等半天才能查到5 000条数据。
优化之后，一切转眼就解决了。

是的，顺便说一句，如果数据变化不大，也可以添加缓存。
例如，使用Redis来存储经常使用的查询结果。
下次检查一下并获得直接从缓存中读取，避免重新访问数据库。

数据库参数也需要调整。
例如，连接池的大小必须与线程数相匹配。
服务器内存、缓冲池等都需要考虑在内。

总体来说，这两个技巧配合使用，效果确实不错。
之前查了5 000条数据，优化后速度很快。
至少在我的项目中解决了检查超时的问题。

MySQL高效实现模糊查询--28条优化建议

上周我读了一篇关于MySQL优化的文章。
我们来谈谈模糊查询的2 8 个优化技巧。

先说一下。
不要使用 LIKE '%keyword%'。
这将扫描整个表。
效率低。

使用 LIKE '关键字%'。
这样MySQL就可以使用索引了。

或者使用LOCATE功能。
例如，SELECT LOCATE('xbar', foobar)。

WHERE 子句。
不要使用 != o。
这将导致 MySQL 删除索引。

您需要创建索引。
仅在 WHERE 和 SORT BY 列中。

对字段判断为NULL。
最好设置默认值。

WHERE 子句。
不要使用“或”。
分成多个查询。
然后使用联合。

参数问题。
不要在 WHERE 子句中使用参数。
将执行全表扫描。

表达式运算。
使用 WHERE 子句中的字段。
丢弃索引。

函数的运算。
同时删除索引。

索引字段的顺序。
必须与查询条件一致。
尤其是综合指数。

不要问无意义的问题。
例如，SELECT col1 , col2 INTO t FROM t WHERE 1 =0。

使用 CREATE TABLE t(...)。

EXISTS 有时比 IN 更有效。
这取决于具体情况。

索引的有效性。
有很多重复的列数据。
该索引可能没有用。

索引数量。
不是太多。
一般不超过6 个。

簇索引。
不要更新数据列。
消耗资源。

尽可能使用数字字段。
比字体更好的性能。

VARCHAR/NVARCHAR 很好。
存储空间小。

不要使用选择。
仅选择必填字段。

表变量比临时表更好。
但要注意索引的局限性。

不要频繁创建或删除临时表。
消耗资源。

适当使用临时表。
提高效率。

SELECT INTO 比 CREATE TABLE 然后 INSERT 更好。
减少日志量。

临时表。
使用后修剪桌子。
再次删除表。

游标效率较差。
数据超过 1 0,000 行。
考虑重写。

基于集合的解决方案。
比基于光标更好。

光标。
小数据集。
从逐行的角度来看，FAST_FORWARD 可能更好。

设置不计数开/关。
减少客户端消息量。

数据量大。
不要只是回去。
考虑布局。

算了。
就这样。