文章目錄

前言
一、SQL常用分析方法
- 1.檢視SQL執行頻率
- 2.定位低效率執行SQL
- 3.使用EXPLAIN分析SQL
- 4.show profile分析SQL
二、避免索引失效
三、SQL優化
- 1.大批量插入資料
- 2.優化order by
- 3.優化group by
- 4.優化or查詢
總結

前言

當面臨慢查詢SQL時，應如何快速定位與解決問題。本篇主要介紹在實際開發過程中如何分析SQL并對SQL進行優化。資料檔案從案例庫sakila下載下傳

一、SQL常用分析方法

1.檢視SQL執行頻率

show [session|global] status

指令可以檢視伺服器狀态資訊。

[session|global]

不填則預設采用

session

下面的指令顯示了目前 session 中所有統計參數的值：

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 15    |
| Com_delete    | 0     |
| Com_insert    | 1017  |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 21    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
mysql> show status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 5     |
| Innodb_rows_inserted | 47313 |
| Innodb_rows_read     | 79    |
| Innodb_rows_updated  | 0     |
+----------------------+-------+

Com_xxx 表示每個 xxx 語句執行的次數，我們通常比較關心的是以下幾個統計參數

參數	含義
Com_select	執行 select 操作的次數，一次查詢隻累加 1。
Com_insert	執行 INSERT 操作的次數，對于批量插入的 INSERT 操作，隻累加一次。
Com_update	執行 UPDATE 操作的次數。
Com_delete	執行 DELETE 操作的次數。
Innodb_rows_read	select 查詢傳回的行數。
Innodb_rows_inserted	執行 INSERT 操作插入的行數。
Innodb_rows_updated	執行 UPDATE 操作更新的行數。
Innodb_rows_deleted	執行 DELETE 操作删除的行數。
Connections	試圖連接配接 MySQL 伺服器的次數。
Uptime	伺服器工作時間。
Slow_queries	慢查詢的次數。

Com_*** : 這些參數對于所有存儲引擎的表操作都會進行累計。

Innodb_*** : 這幾個參數隻是針對InnoDB 存儲引擎的，累加的算法也略有不同。

2.定位低效率執行SQL

可以通過以下兩種方式定位執行效率較低的 SQL 語句。

慢查詢日志：通過慢查詢日志定位那些執行效率較低的 SQL 語句，用–log-slow-queries[=file_name]選項啟動時，mysqld 寫一個包含所有執行時間超過 long_query_time 秒的 SQL 語句的日志檔案。具體參考MySQL日志。
show processlist : 慢查詢日志在查詢結束以後才紀錄，是以在應用反映執行效率出現問題的時候查詢慢查詢日志并不能定位問題，可以使用show processlist指令檢視當MySQL在進行的線程，包括線程的狀态、是否鎖表等，可以實時地檢視 SQL 的執行情況，同時對一些鎖表操作進行優化。

mysql> SHOW processlist;
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db     | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL   | Connect | 38184 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |           | NULL   | Connect | 38184 | Connecting to master                                   | NULL             |
|  6 | root        | localhost | sakila | Query   |     0 | starting                                               | SHOW processlist |
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+

參數	描述
id	使用者登入mysql時，系統配置設定的"connection_id"，可以使用函數connection_id()檢視
User	顯示目前使用者。如果不是root，這個指令就隻顯示使用者權限範圍的sql語句
host	顯示這個語句是從哪個ip的哪個端口上發的，可以用來跟蹤出現問題語句的使用者
db	這個程序目前連接配接的是哪個資料庫
command	顯示目前連接配接的執行的指令，一般取值為休眠（sleep），查詢（query），連接配接(connect)等
time	這個狀态持續的時間，機關是秒
state	顯示使用目前連接配接的sql語句的狀态，很重要的列。state描述的是語句執行中的某一個狀态。一個sql語句，以查詢為例，可能經過Copying to tmp table、sorting result、sending data等狀态才完成
info	顯示這個sql語句，是判斷問題語句的一個重要依據

3.使用EXPLAIN分析SQL

參照EXPLAIN介紹

4.show profile分析SQL

Mysql從5.0.37版本開始增加了對 show profiles 和 show profile 語句的支援。show profiles 能夠在做SQL優化時幫助我們了解時間都耗費到哪裡去了。

檢視是否支援profile

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

預設profiling是關閉的，可以通過set語句在Session級别開啟profiling：

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
-- 開啟
set profiling=1;

下面執行一系列操作，并檢視

profiles

mysql> SHOW TABLES;
mysql> SELECT COUNT(*) FROM actor;
mysql> SELECT * FROM actor;

mysql> SHOW profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00038200 | SELECT @@profiling         |
|        2 | 0.00252900 | SHOW TABLES                |
|        3 | 0.00042675 | SELECT COUNT(*) FROM actor |
|        4 | 0.00088175 | SELECT * FROM actor        |
+----------+------------+----------------------------+

通過

show profile for query query_id

語句可以檢視到該SQL執行過程中每個線程的狀态和消耗的時間：

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000096 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000029 |
| init                 | 0.000037 |
| System lock          | 0.000018 |
| optimizing           | 0.000008 |
| statistics           | 0.000025 |
| preparing            | 0.000023 |
| executing            | 0.000005 |
| Sending data         | 0.000544 |
| end                  | 0.000010 |
| query end            | 0.000014 |
| closing tables       | 0.000013 |
| freeing items        | 0.000024 |
| cleaning up          | 0.000023 |
+----------------------+----------+

Sending data 狀态表示MySQL線程開始通路資料行并把結果傳回給用戶端，而不僅僅是傳回個用戶端。由于在Sending data狀态下，MySQL線程往往需要做大量的磁盤讀取操作，是以經常是整各查詢中耗時最長的狀态。

二、避免索引失效

比對全值

對索引中的所有列都有等值比對

--其中 rental_date為複合索引(rental_date,inventory_id,customer_id)
mysql> SHOW INDEX FROM rental;
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rental |          0 | PRIMARY             |            1 | rental_id    | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            1 | rental_date  | A         |       15815 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            2 | inventory_id | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            3 | customer_id  | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_inventory_id |            1 | inventory_id | A         |        4580 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_customer_id  |            1 | customer_id  | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_staff_id     |            1 | staff_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-24 22:53:30' AND  inventory_id=367 AND customer_id=130\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

從執行計劃key中可以看出優化器選擇了複合索引

rental_date

對索引範圍查詢

mysql> EXPLAIN SELECT * FROM rental WHERE customer_id>=373 AND customer_id<400\G;
mysql> EXPLAIN SELECT * FROM rental WHERE customer_id>=373 AND customer_id<400\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

類型type為range說明優化器選擇範圍查詢，優化器選擇了idx_fk_customer_id索引。Extra為

Using index condition

表示需要根據索引回表查詢資料。

比對最左字首

如果索引了多列（複合索引），要遵守最左字首法則。指的是查詢從索引的最左前列開始，并且不跳過索引中的列。

走全部索引，key_len=12

mysql> EXPLAIN SELECT * FROM payment WHERE payment_date='2005-05-25 11:30:37' AND  amount='2.99' AND last_update='2006-02-15 22:12:30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 12
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

如果符合最左法則，但是出現跳躍某一列，隻有最左列索引生效,key_len=5

ALTER TABLE payment ADD INDEX idx_payment_date(payment_date,amount,last_update);
mysql> EXPLAIN SELECT * FROM payment WHERE payment_date='2005-05-25 11:30:37' AND last_update='2006-02-15 22:12:30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

違反最左字首法則，索引失效：

-- 索引失效
mysql> EXPLAIN SELECT * FROM payment WHERE amount='2.99' AND last_update='2006-02-15 22:12:30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

僅對索引列進行查詢

當查詢的列都在索引的字段時，查詢效率更高；避免SELECT *

mysql> EXPLAIN SELECT payment_date  FROM payment WHERE payment_date='2005-05-25 11:30:37' AND last_update='2006-02-15 22:12:30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

對比上一例中select * ,本次隻查詢payment_date，Extra的值發生了變化

using index ：使用覆寫索引的時候就會出現

using where：在查找使用索引的情況下，需要回表去查詢所需的資料

using index condition：查找使用了索引，但是需要回表查詢資料

using index ; using where：查找使用了索引，但是需要的資料都在索引列中能找到，是以不需要回表查詢資料

不要在索引上計算，索引會失效

mysql> EXPLAIN SELECT * FROM customer WHERE last_name='SMITH'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_last_name
          key: idx_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN  SELECT * FROM customer WHERE substring(last_name,1,4) ='JOHN'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

or查詢，前面列有索引後面類無索引，那麼涉及到的索引都失效

mysql> EXPLAIN SELECT * FROM actor WHERE last_name='WAHLBERG' OR first_name='NICK'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 19.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

以%開頭的Like模糊查詢，索引失效

-- 索引失效
mysql> EXPLAIN SELECT * FROM actor WHERE last_name like '%WAHLBERG'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
-- 索引生效
mysql> EXPLAIN SELECT * FROM actor WHERE last_name like 'WAHLBERG%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: range
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

這種情況，查詢索引字段可避免

mysql> EXPLAIN SELECT actor_id,last_name FROM actor WHERE last_name like '%WAHLBERG%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_actor_last_name
      key_len: 182
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

三、SQL優化

1.大批量插入資料

對于 InnoDB 類型的表，有以下幾種方式可以提高導入的效率：

主鍵順序插入
關閉唯一性檢驗
手動送出事務

主鍵順序插入

InnoDB類型的表是按照主鍵的順序儲存的，是以将導入的資料按照主鍵的順序排列，可以有效的提高導入資料的效率。如果InnoDB表沒有主鍵，那麼系統會自動預設建立一個内部列作為主鍵，是以如果可以給表建立一個主鍵，将可以利用這點，來提高導入資料的效率。

CREATE TABLE `tb_user_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
`birthday` datetime DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`qq` varchar(32) DEFAULT NULL,
`status` varchar(32) NOT NULL COMMENT '使用者狀态',
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

資料導入

-- sql1.log資料主鍵有序，時間為29.11sec
mysql> load  data local  infile '/usr/local/mysql/temp/sql1.log' into table tb_user_1 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (29.11 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000
mysql> SELECT COUNT(*) FROM tb_user_1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.27 sec)
-- sql2.log為無序資料，時間1min19.81s
mysql> load  data local  infile '/usr/local/mysql/temp/sql2.log' into table tb_user_2 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (1 min 19.81 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000
mysql> select count(id) from tb_user_2;
+-----------+
| count(id) |
+-----------+
|   1000000 |
+-----------+
1 row in set (0.24 sec)

關閉唯一性檢驗

在導入資料前執行

SET UNIQUE_CHECKS=0

，關閉唯一性校驗，在導入結束後執行

SET UNIQUE_CHECKS=1

，恢複唯一性校驗，可以提高導入的效率(筆者在測試時基本沒有變化)

mysql> SET UNIQUE_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> load  data local  infile '/usr/local/mysql/temp/sql1.log' into table tb_user_1 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (30.42 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000
mysql> SET UNIQUE_CHECKS=1;
Query OK, 0 rows affected (0.02 sec)

手動送出事務

如果應用使用自動送出的方式，建議在導入前執行

SET AUTOCOMMIT=0

，關閉自動送出，導入結束後再執行

SET AUTOCOMMIT=1

，打開自動送出，也可以提高導入的效率。

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> load  data local  infile '/usr/local/mysql/temp/sql1.log' into table tb_user_1 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (28.27 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000
mysql> COMMIT;
mysql> SELECT COUNT(id) FROM tb_user_1;
+-----------+
| COUNT(id) |
+-----------+
|   1000000 |
+-----------+

2.優化order by

CREATE TABLE `emp` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);

有兩種排序方式：

第一種是通過對傳回資料進行排序，也就是通常說的 filesort 排序，所有不是通過索引直接傳回排序結果的排序都叫 FileSort 排序
第二種通過有序索引順序掃描直接傳回有序資料，這種情況即為 using index，不需要額外排序，操作效率高。

mysql> EXPLAIN SELECT * FROM emp ORDER BY age DESC\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
No query specified

mysql> EXPLAIN SELECT id,age FROM emp ORDER BY age DESC\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_emp_age_salary
      key_len: 9
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

了解了MySQL的排序方式，隻要通過以下方法優化：

盡量減少額外的排序，通過索引直接傳回有序資料。
where 條件和Order by 使用相同的索引，并且Order By 的順序和索引順序相同
Order by 的字段都是升序，或者都是降序。

mysql> EXPLAIN SELECT id,age FROM emp ORDER BY age,salary\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_emp_age_salary
      key_len: 9
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,age FROM emp ORDER BY salary,age\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_emp_age_salary
      key_len: 9
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,age FROM emp ORDER BY age DESC,salary ASC\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_emp_age_salary
      key_len: 9
          ref: NULL
         rows: 12
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

Filesort 的優化

通過建立合适的索引，能夠減少 Filesort 的出現，但是在某些情況下，條件限制不能讓Filesort消失，那就需要加快 Filesort的排序操作。對于Filesort ， MySQL 有兩種排序算法：

1）兩次掃描算法：MySQL4.1 之前，使用該方式排序。首先根據條件取出排序字段和行指針資訊，然後在排序區sort buffer 中排序，如果sort buffer不夠，則在臨時表 temporary table 中存儲排序結果。完成排序之後，再根據行指針回表讀取記錄，該操作可能會導緻大量随機I/O操作。

2）一次掃描算法：一次性取出滿足條件的所有字段，然後在排序區 sort buffer 中排序後直接輸出結果集。排序時記憶體開銷較大，但是排序效率比兩次掃描算法要高。

MySQL 通過比較系統變量 max_length_for_sort_data 的大小和Query語句取出的字段總大小，來判定是否那種排序算法，如果max_length_for_sort_data 更大，那麼使用第二種優化之後的算法；否則使用第一種。可以适當提高 sort_buffer_size 和 max_length_for_sort_data 系統變量，來增大排序區的大小，提高排序的效率。

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set (0.03 sec)

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

3.優化group by

預設情況下，MySQL對所有

GROUP BY col1,col2..

的字段進行排序。如果查詢包括

GROUP BY

但使用者想要避免排序結果的消耗，則可以指定

ORDER BY NULL

禁止排序。

mysql> EXPLAIN SELECT payment_date,sum(amount) from payment GROUP BY payment_date\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT payment_date,sum(amount) from payment GROUP BY payment_date ORDER BY NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

4.優化or查詢

對于包含OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須用到索引，而且不能使用到複合索引；如果沒有索引，則應該考慮增加索引。

總結

通過分析sql找到問題所在，最後對索引，常用sql語句進行優化。

SQL分析與優化前言一、SQL常用分析方法二、避免索引失效三、SQL優化總結

文章目錄

前言

一、SQL常用分析方法

1.檢視SQL執行頻率

2.定位低效率執行SQL

3.使用EXPLAIN分析SQL

4.show profile分析SQL

二、避免索引失效

三、SQL優化

1.大批量插入資料

2.優化order by

3.優化group by

4.優化or查詢

總結

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案