如何优化SQL性能? 提高SQL查询速度?

数据库之SQL优化

  随着数据的增加, 劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍, 优化SQL查询速度, 提高SQL质量已成为 重中之重*

★ SQL语句基础练习 →

★ SQL实战进阶练习 →

1. 优化方向

1.1 避免全表扫描, 能有效提高查询速度

1. 首先应考虑where 及 order by涉及的列建立索引
2. 应尽量避免在where子句中对字段进行null值判断
3. 应尽量避免在 where 子句中使用 != 或 <> 操作符
4. 慎用 in 和 not in ,用 exists 代替 in ,对于连续的数值，可用 between and 替代
5. 在 where 子句中使用 (参数)¹，也会导致全表扫描
6. 不带任何条件的count会引起全表扫描,且没有业务意义 如: select count(*) from table

1.2 系统资源处理

1. 不要写一些没有意义的查询，如需要生成一个空表结构
2. 任何地方都不要使用 select * from t ，用具体的字段列表代替
3. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
4. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差
5. 建索引需要慎重考虑, 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要
6. 应尽可能的避免更新 (clustered)²索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引
7. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次
8. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
9. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert
10. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定
11. 尽量避免大事务操作，提高系统并发能力
12. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

2. 使用索引

 索引可以理解为一种特殊的目录

2.1 索引分类

聚集索引:（clustered index，也称聚类索引、簇集索引）
非聚集索引:（nonclustered index，也称非聚类索引、非簇集索引）

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续;
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致;
聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

2.2 索引语法

1.创建索引:
   create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , ...);

示例:

 CREATE INDEX paywayid_index ON pay_order_trade (paywayid)

 alter table table_name ADD INDEX [index_name] (index_col_name,...)

2. 组合索引:
  CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

示例：

 ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

3.删除索引 :
  DROP INDEX index_name ON tbl_name;

  alter table table_name drop index index_name;

4.查询索引 :
  show index from table_name;

  show keys from table_name;

  desc table_Name;

2.3 索引建立原则

1. 定义主键的数据列一定要建立索引

2. 定义有外键的数据列一定要建立索引

3. 对于经常查询的数据列最好建立索引

4. 对于需要在指定范围内的快速或频繁查询的数据列

5. 经常用在WHERE子句中的数据列

6. 经常出现在关键字order by、group by、distinct和UNION后面的字段，(建立索引)³

7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引

8. 对于定义为text、image和bit的数据类型的列不要建立索引

9. 对于经常存取的列避免建立索引

10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个

11. 对复合索引，按照字段在查询条件中出现的频度建立索引。
    在复合索引中，记录首先按照第一个字段排序对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此(类推)⁴

12. 尽量的扩展索引，不要新建索引

13. 最左前缀匹配原则
    非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
    在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

2.4 使用联合索引的好处?

1. 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引
2. 效率高。索引列越多，通过索引筛选出的数据越少
3. 覆盖索引

3. 查询优化工具 - explain命令

   语法: explain select … from … [where ...]

1. id：这是SELECT的查询序列号
2. select_type：select_type就是select的类型
3. table：显示这一行的数据是关于哪张表的
4. type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
   结果值从好到坏依次是：
   (保证查询至少达到range级别，最好能达到ref, 否则就可能会出现性能问题)

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

5. possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6. key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7. key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8. ref：显示使用哪个列或常数与key一起从表中选择行。

9. rows：显示MySQL认为它执行查询时必须检查的行数。
        rows算是最直观的指标,一般情况下rows越小, 执行速度越快

10. Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

---

注释:

---

1. 参数: 指SQL变量, @num , 可以防止sql注入, 提高代码质量 , 但也会导致资源的占用 ↩︎

2. clustered : 聚合 ; 指聚合索引 ↩︎

3. 如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用 ↩︎

4. 只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用 ↩︎