阐述常见的一些SQL优化方式

SQL优化

1. 避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像 select * 这种写法应该尽量避免。

2. 分页优化

在数据量比较大、分页深度较深的情况下，可以考虑以下分页优化方案：

• 延迟关联（Deferred Join）：先通过WHERE条件提取出主键，然后再将该表与原数据表进行关联，通过主键ID提取数据行，而不是使用原有的二级索引提取数据行。这样可以减少关联操作的数据量，提高查询效率。
  例如：

SELECT a.* FROM table a, 
 (SELECT id FROM table WHERE type = 2 AND level = 9 ORDER 
BY id ASC LIMIT 190289, 10) b
WHERE a.id = b.id

• 书签方式（Bookmark）：书签方式是根据LIMIT语句中的第一个参数对应的主键值，通过该主键值进行过滤和限制。先找到满足条件的第一个主键值，然后再根据该主键值进行过滤并进行LIMIT操作。
  例如：

SELECT * FROM table WHERE id > 
 (SELECT id FROM table WHERE type = 2 AND level = 9 ORDER 
BY id ASC) LIMIT 190, 10

3. 索引优化

合理地设计和使用索引，是优化慢SQL的利器。
利用覆盖索引InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引
例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

4. 低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

5. 避免使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
例如，把 column<>’aaa’，改成column>’aaa’ or column<’aaa’ ，就可以使用索引了

6.适当使用前缀索引

适当地使用前缀索引，可以降低索引的空间占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的“ @xxx.com ”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

7. 避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

8. 正确使用联合索引

使用联合索引的时候，注意最左匹配原则。