网络搜索概念

1、 避免出现 大小写不统一的情况

错误的写法：

SELECT id From t where name = 'zhangsan';

正确的写法：

SELECT id FROM t WHERE name = 'zhangsan';

在我们看来没有区别，只是大小写不一样而已，但mysql认为是两个不同的SQL，要进行解析，生成两个不同的执行计划。

因此，必须要使相同的查询语句保持一致。

2、 根据字段类型，调整相应的查询条件；

比如 age 类型为 int, 那么

错误的写法：

# 这是字符串的写法，虽然也能查询出数据，但查询效率很低。
SELECT name FROM t WHERE age = '18';

正确的写法：

SELECT name FROM t WHERE age = 18;

3、 避免出现对字段进行NULL的判断，这个会使MySQL放弃索引进行全表扫描。

错误的写法：

SELECT id FROM t WHERE name IS NULL;

这里需要注意的是，在数据库设计的时候要注意不要给数据库留NULL, 尽量使用NOT NULL 进行填充，比如： varchar用""填充。

4、 不要使用 SELECT * FROM 查询，应使用具体的字段查询，尽量不要返回无用的字段。

5、 WHERE 语句优化：

单独把WHERE 条件放在一起；

5.1、相同场景的WHERE条件应该相同，例如：

如果创建的所有是 【name,age】,下面的查询

SELECT id FROM t WHERE name = 'zhangsan' AND age = 18;
SELECT id FROM t WHERE age = 19 AND name = 'zhangsan';

虽然查询结果一样，但第二个查询肯定会造成索引用不到的情况。

5.2、尽量不要在查询中用到OR, 如果一个字段有索引，另一个没有，就会造成全表扫描的。

SELECT id FROM t WHERE num=10 OR name = 'zhangsan';

可以这样写：

SELECT id FROM t WHERE num = 10
UNION ALL 
SELECT id FROM t WHERE name = 'zhangsan';

5.3、尽量不要在WHERE语句中用 != ,<>, 会导致全表扫描

5.4、IN 和 NOT IN 也要注意，否则会导致全表扫描，可以用 EXISTS 代替 IN(当然，前表小于后表时用)

SELECT num FROM a WHERE EXISTS(SELECT 1 FROM b WHERE num=a.num);

5.5、避免在WHERE子句中对字段进行操作，包括表达式，函数等。

错误的写法：

SELECT id FROM t WHERE num/2 = 100;

正确的写法：

SELECT id FROM t WHERE num = 100*2;

6、 避免使用很复杂的SQL, 如果太复杂，查询会变慢，并且过几天你看不懂，别人也看不懂。越简单的SQL被重用的可能性越高。复杂的SQL只要一个字符发生变化，mysql就会重新去解析。

一般我的查询嵌套不会超过两层。

解决办法：临时表 暂存中间结果

优点：

1、简化SQL

2、暂存临时结果，二次利用，避免多次扫描主表，减少锁，减少阻塞，提高并发性能。

缺点： 慎重使用临时表可以极大的提高系统性能(网上说的)，这个目前没有意识到，以后优化的时候可以考虑。

目前我的查询语句中用到了大量的临时表，临时表存储在内存中，查询效率显著提高。

临时表显式删除，先 TERUNCATE table ，然后 DROP table ，这样可以避免系统表的较长时间锁表，释放内存，减少系统开销。

7、对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

8、进行大量INSERT操作时，先禁用索引，待插入完成后在打开，避免插入的时候进行索引排序。

禁用索引：

ALTER TABLE t DISABLE KEYS;

INSERT操作完成后重新打开索引即可：

ALTER TABLE t ENABLE KEYS;

三、索引创建

1、尽量使用联合索引，在常用的字段以及可辨识大的字段上创建索引

2、与WHERE，GROUP BY，ORDER BY 等结合，按顺序创建索引，这就需要相同场景的WHERE条件应该相同，因为聚合索引时最左前缀原则，只有WHERE子句顺序相同的时候才能用到。

3、不要在每个列上创建索引，索引不要超过五个。

4、不要在建立的索引的数据列上进行下列操作:

避免对索引字段进行计算操作

避免在索引字段上使用not，<>，!=

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引列上出现数据类型转换

避免在索引字段上使用函数

避免建立索引的列中使用空值。

5、索引的建立要经过严格的评估。

四、视图也可以使用索引

网上大量的文章讲视图不能用到索引，但业务场景必须要用到视图，就考虑视图也利用索引。

视图的建立：避免使用全部建立视图，应根据业务场景，配合表的索引，创建视图。不关心视图用不用到索引，只要我们创建视图的查询语句能用到索引，就可以了。

这样视图就可以用到索引了，亲测有效。