mysql的一些优化小技巧

MySQL的一些优化小技巧

​ 摘要：对于我们敲代码的程序员来说，操作数据库写sql是我们的一项基本功，那这项基本功够扎实吗？先看看这些优化小技巧你都知道吗？

1.尽量避免子查询

例：

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'chackca');

原因：由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

2. 用IN来替换OR

• 低效查询：SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
• 高效查询：SELECT * FROM t WHERE id IN (10,20,30);

理由：MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。

注意：如果数值较多，产生的消耗也是比较大的。

解决办法：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

3. 将多次插入换成批量Insert插入

INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
—>
INSERT INTO t(id, name) VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');

4. 用具体的字段代替 select *

原因：

​ SELECT * 会增加很多不必要的消耗（cpu、io、内存、网络带宽）；减少了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。当存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。

覆盖索引：

​ 如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作

5. 区分in和exists

select * from 表A where id in (select id from 表B)

上面的语句相当于：

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

原因：

​ 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

6. 尽量使用数字型字段

原因：

​ 若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。