日本中文字幕免费高清视频,亚洲国产精品久久精品怡红院,亚洲国产精品久久久久

當前位置：主頁 > 聚焦 > 正文

Mysql高級5-SQL優化

來源：博客園作者：洞察網2023-07-31 06:41:19

一、插入數據優化　　1.1 批量插入

如果有多條數據需要同時插入，不要每次插入一條，然后分多次插入，因為每執行一次插入的操作，都要進行數據庫的連接，多個操作就會連接多次，而一次批量操作只需要連接1次

1.2 手動提交事務

因為Mysql默認每執行一次操作，就會提交一次事務，這樣就會涉及到頻繁的事務的開啟與關閉

start transaction;　　insert into 表名 values(),(),();　　insert into 表名 values(),(),();　　insert into 表名 values(),(),();commit;

1.3 主鍵順序插入

主鍵一般是默認自增的，但是也可以手動增加，這里不建議手動亂序增加，而是使用默認的順序增加，原因會在后面解釋。

(資料圖片僅供參考)

1.4 大批量插入數據

如果一次性需要插入大批量數據，使用insert語句插入性能較低，此時可以使用Mysql數據庫提供的load指令進行插入，

首先在連接數據庫的時候需要加上 --local-infile 參數

mysql --local-infile -u root -p

在使用本地文件加載功能的時候，需要先查看本地加載文件選項是否開啟的

mysql> select @@local_infile;+----------------+| @@local_infile |+----------------+|              0 |+----------------+1 row in set (0.00 sec)

說明1：0表示本地加載文件并未開啟

開啟本地加載文件的語句

mysql> set global local_infile = 1;Query OK, 0 rows affected (0.01 sec)mysql> select @@local_infile;+----------------+| @@local_infile |+----------------+|              1 |+----------------+1 row in set (0.00 sec)

創建一個空表tb_user，其表結構如下

mysql> desc tb_user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | int         | NO   | PRI | NULL    | auto_increment || username | varchar(50) | NO   | UNI | NULL    |                || password | varchar(50) | NO   |     | NULL    |                || name     | varchar(20) | NO   |     | NULL    |                || birthday | date        | YES  |     | NULL    |                || sex      | char(1)     | YES  |     | NULL    |                |+----------+-------------+------+-----+---------+----------------+6 rows in set (0.01 sec)

使用load加載本地文件 "tb_user_data.sql" 內容到新創建的表中,其中tb_user_data.sql中的測試數據如下

houlei@houleideMacBook-Pro Desktop %cat tb_user_data.sql1,a,aa,aaa,2023-07-01,12,b,bb,bbb,2023-07-02,03,c,cc,ccc,2023-07-03,14,d,dd,ddd,2023-07-04,05,e,ee,eee,2023-07-05,16,f,ff,fff,2023-07-06,07,g,gg,ggg,2023-07-07,1houlei@houleideMacBook-Pro Desktop %

使用load加載本地文件 "tb_user_data.sql" 內容到新創建的表中　　

mysql> load data local infile "/Users/houlei/Desktop/tb_user_data.sql" into table tb_user fields terminated by "," lines terminated by "\n";Query OK, 7 rows affected (0.01 sec)Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

說明1： load data local infile 是加載本地文件的意思，

說明2："/Users/houlei/Desktop/tb_user_data.sql"是文件路徑

說明3：into table tb_user 是將文件中的數據，插入到tb_user表中

說明4：fields terminated by "," 是說每個字段之間的數據是使用","分割的

說明5：lines terminated by "\n" 是說每一行之間的數據使用的是‘\n’分割的

說明6：本方法只是舉例，在實際運用大數據量插入時100萬條數據的插入至少要數分鐘，如果使用load方法只需要十幾秒

二、主鍵優化　　2.1 數據組織方式

在InnoDB儲存引擎中，表數據都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表(index organized table）IOT

說明1：在索引的B+數中所有的數據保存在葉子節點上，非葉子節點只保存主鍵key的值

說明2：索引中的各個節點都是保存在邏輯結構頁上面的，一頁默認大小16K

2.2 頁分裂

頁可以為空，也可以填充一半，也可以填充100%，每個頁包含了2至N行數據，根據主鍵排列

情況1：主鍵順序插入

說明1：row是行數據，每一頁上可以存放多個行數據。　　　　

情況2：主鍵亂序插入

說明1：當我們想要在插入一個id=50的數據時，會發生頁分裂

說明2：這時會將 1#page頁里面的數據超過 50%的數據，移動到新開辟的 3#page頁中

說明3：然后將 id=50的數據也拼接到 3#page頁中

說明4：這時就會出現一個問題，3#page中的索引比 2#page頁中的索引小，所以還需要將 3#page頁前置，這就叫頁分裂

2.3 頁合并

當刪除一行記錄時，實際上記錄并沒有被物理刪除，只是記錄被標記(flaged)為刪除并且它的空間變得允許被其他記錄聲明使用

當頁中刪除的記錄達到 merge_threshold(默認為頁的50%)，InnoDB 會開始尋找最靠近的頁（前或者后）看看是否可以合并以優化空間使用

說明1：這時在 2#page刪除了13,14,15,16數據后，該頁空余空間超過50%時就會尋找前一頁或者后一頁，是否同樣有不滿足50%,可以合并的

說明2：這時 1#page頁是滿的，不能合并，3#page頁不滿可以合并，所以 3#page頁遷移到 2#page頁中

說明3：這時如果在有數據20插入就可以直接插入到3#page頁上了，這就是頁合并。

2.4 主鍵設計原則滿足業務需求的情況下，盡量減低主鍵的長度。插入數據時，盡量選擇順序插入，選擇使用auto_incerment自增主鍵，盡量不要用uuid作主鍵或者其他自然主鍵如身份證號，因為這個值是無需的，會存在頁分裂情況。三、order by優化　　3.1 Using filesort

通過表的索引或者全表掃描，讀取滿足條件的數據行，然后在排序緩沖區sort buffer 中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫FileSort排序

3.2 Using index

通過有序索引順序掃描直接返回有序數據，這種情況即為using index，不需要額外的排序，操作效率高，即排序的列表字段符合覆蓋索引。

3.3 案例

emp表結構：

mysql> desc emp;+-----------+-------------+------+-----+---------+----------------+| Field     | Type        | Null | Key | Default | Extra          |+-----------+-------------+------+-----+---------+----------------+| id        | int         | NO   | PRI | NULL    | auto_increment || name      | varchar(20) | YES  |     | NULL    |                || age       | int         | YES  |     | NULL    |                || job       | varchar(20) | YES  |     | NULL    |                || salary    | int         | YES  |     | NULL    |                || entrydate | date        | YES  |     | NULL    |                || managerid | int         | YES  |     | NULL    |                || dept_id   | int         | YES  | MUL | NULL    |                |+-----------+-------------+------+-----+---------+----------------+8 rows in set (0.01 sec)

emp表中索引情況

mysql>show index from emp;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || emp   |          1 | fk_dept  |            1 | dept_id     | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

案例1：對查詢結果進行按 salary 和 age 都進行升序排序

mysql> explain select salary,age from emp order by salary, age;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 |Using filesort|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.00 sec)

說明1：Extra 中值為 Using filesort說明是先查出來需要的數據，然后再排序的,效率不高。

說明2：為什么會出現Using filesort呢？因為查詢的這些字段在查詢之前是無須的，索引需要先將數據查詢出來，然后再做排序，這樣才能得到想要的排序好的數據。

案例2：給 salary 和 age 添加一個聯合排序

mysql> create index salary_age_idx onemp(salary,age);Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from emp;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY        |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || emp   |          1 | fk_dept        |            1 | dept_id     | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || emp   |          1 | salary_age_idx |            1 | salary      | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL ||emp   |          1 | salary_age_idx |            2 | age         | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+4 rows in set (0.00 sec)

說明1：聯合索引salary_age_idx中 salary 是第一索引字段，age 是第二索引字段

說明2：Collation 中A 代表升序，D 代表降序　　　　

案例3：再次使用 order by 對 salary 和 age 進行升序排序

mysql> explain select salary,age from emp order by salary,age;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select salary,age from emp order by salary;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

說明1：在做升序排列時，無論 order by 后面是組合索引的全部字段，還是只有部分字段，這時 Extra 的值都是Usind index，所以其查詢的結果直接就是排序好的結果

說明2：為什么呢？因為這個時候 salary和age是一個聯合索引，索引在文件中是一個帶順序的b+數結構，所以將這個字段建立一個聯合索引，就意味著使用索引查詢的時候，就已經是帶著順序的數據了，所以這個時候就不需要在將數據從新在排序了，這樣的查詢效率就會更高。

案例4: order by 中的字段順序和索引順序不一致的情況

mysql> explain select salary,age from emp order by age,  salary;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index; Using filesort|+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)

說明1：這個時候order by 是age在前，salary在后，和索引的順序不一致，仍然會觸發索引，使用Using index，但是也會使用Using filesort,所以推薦大家使用正確的索引順序的字段來進行排序

案例5：對salary和age做降序查詢

mysql> explain select salary,age from emp order by salary desc, age desc;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                            |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Backward index scan; Using index|+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+1 row in set, 1 warning (0.00 sec)

mysql> explain select id,salary,age from emp order by salary desc;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                            |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Backward index scan; Using index|+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+1 row in set, 1 warning (0.00 sec)

說明1：無論是對salary和age同時做降序還是對其中一個字段做降序排列，都會出現Backward index scan; Using index，其中 Backward index scan 是反向掃描索引

說明2：這是因為索引中默認的順序是升序的，而做降序排列，就需要反向掃描索引了

案例7：創建一個 salary 和 age 都是降序的索引

create index salary_age_desc_idx on emp(salary desc, age desc);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

查詢目前所有的索引

mysql> show index from emp;+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY             |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || emp   |          1 | fk_dept             |            1 | dept_id     | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || emp   |          1 | salary_age_desc_idx |            1 | salary      | D         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL || emp   |          1 | salary_age_desc_idx |            2 | age         | D         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL || emp   |          1 | salary_age_idx      |            1 | salary      | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || emp   |          1 | salary_age_idx      |            2 | age         | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+6 rows in set (0.01 sec)

說明1：這里 salary_age_desc_idx 就是根據 salary 和 age 做的降序索引，其Collation中的D即降序的意思

案例8：使用salary_age_desc_idx索引然后在使用order by降序查詢

mysql> explain select salary,age from emp use index(salary_age_desc_idx) order by salary desc, age desc;+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_desc_idx | 10      | NULL |    7 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

說明1：這個時候的Extra 中顯示的 Using index，效率就會比較高了

說明2：這是因為salary_age_desc_idx索引的順序就是降序排列的，所以使用該索引做降序排列的時候，就不需要在做反向掃描

說明3：在實際的業務中，我們可以根據自己的查詢需要，創建升序或者降序的索引。

3.4 order by總結根據排序字段建立合適的索引，多字段排序是，也遵循最左前綴法則盡量使用覆蓋索引多字段排序，如果有升序有降序，此時需要注意聯合索引在創建時的規則，也應該有對應的升序和降序如果不可避免的出現filesort,大數據量排序的時候，可以適當增大排序緩沖區的大小，sort_buffer_size（默認256K）四、group by優化

為了測試數據的準確性，這是我先把除了主鍵以外的索引都刪除了，然后根據需要在重新創建

mysql> show index from emp;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+2 rows in set (0.01 sec)

案例1：根據job做聚合查詢

mysql> select job, count(*) from emp group by job;;+--------------+----------+| job          | count(*) |+--------------+----------+| 董事長       |        1 || 項目經理     |        1 || 開發         |        3 || 財務         |        1 || 出納         |        1 || 人事         |        1 |+--------------+----------+6 rows in set (0.00 sec)

我們使用explain查看一下執行計劃

mysql> explain select job, count(*) from emp group by job;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set, 1 warning (0.00 sec)

說明1：通過Extra字段：Using temporary,說明在這次的查詢中創建了一張臨時表，這是無論是空間上還是速度上都會影響到查詢效率的。

這時我們給 job 創建一個索引，再次使用explain查看一下執行計劃

mysql> create index job_idx onemp(job);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from emp;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || emp   |          1 | job_idx  |            1 | job         | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

mysql> explain select job, count(*) from emp group by job;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | index | job_idx       | job_idx | 83      | NULL |    7 |   100.00 | Using index|+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

說明2：這是查詢中就使用到了索引查詢，而沒有建立臨時表

這時我們在對 job 和 age 同時做分組查詢

mysql> explain select job,age, count(*) from emp group by job,age;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using temporary|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set, 1 warning (0.00 sec)

說明3：這時Extra字段的值，仍然是Using temporary，那是因為沒有一個與之對應的聯合索引。

我們繼續創建一個 job 和 age 的聯合索引，然后再看一下 explain 的執行計劃

mysql> create index job_age_idx onemp(job,age);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from emp;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp   |          0 | PRIMARY     |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || emp   |          1 | job_idx     |            1 | job         | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || emp   |          1 | job_age_idx |            1 | job         | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL || emp   |          1 | job_age_idx |            2 | age         | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL|+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+5 rows in set (0.00 sec)

mysql> select job,age,count(*) from emp group by job,age;+--------------+------+----------+| job          | age  | count(*) |+--------------+------+----------+| 人事         |   27 |        1 || 出納         |   25 |        1 || 開發         |   22 |        2 || 開發         |   24 |        1 || 董事長       |   43 |        1 || 財務         |   25 |        1 || 項目經理     |   38 |        1 |+--------------+------+----------+7 rows in set (0.00 sec)mysql> explain select job,age,count(*) from emp group by job,age;+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | index | job_age_idx   | job_age_idx | 88      | NULL |    7 |   100.00 | Using index|+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

說明4：當我們group by后面的分組字段，存在于某一個聯合索引中的時候，group by會使用索引查詢，而不會建立臨時表

案例2：我們根據job做過濾然后再根據age排序

mysql> select job,age from emp where job="開發" group by age;+--------+------+| job    | age  |+--------+------+| 開發   |   22 || 開發   |   24 |+--------+------+2 rows in set (0.01 sec)mysql> explain select job,age from emp where job="開發" group by age;+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys       | key         | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | ref  | job_idx,job_age_idx | job_age_idx| 83      | const |    3 |   100.00 | Using index|+----+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

說明1：當where后面的條件和group by 后面的條件一起組合成連鎖索引，也不會建立臨時表，也會直接走連個查詢索引的。效率同樣比較高

總結：

在分組操作時，可以通過索引來提高效率分組操作時，索引的使用也滿足最左前綴法則五、limit優化

account_transaction表數據量展示

mysql> select count(*) from account_transaction;+----------+| count(*) |+----------+|  2261942 |+----------+1 row in set (8.40 sec)

說明1：account_transaction總數據量有226萬+

案例1：分別采用分頁查詢，第一頁，第1萬頁，200萬頁的數據

mysql> select * from account_transaction limit 1,2;+----+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| id | trade_no           | type   | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |+----+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+|  2 | 156384294742000250 | TOP_UP | CASH   | 2019-07-23 00:49:07.072256 | LOCAL_ACCOUNT |              |  10000 |   10000 |             250 |                12 | 6         |        ||  3 | 156384301875000251 | TOP_UP | CASH   | 2019-07-23 00:50:18.059192 | LOCAL_ACCOUNT |              |  10000 |   10000 |             251 |                12 | 6         |        |+----+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+2 rows in set (0.00 sec)mysql> select * from account_transaction limit 10000,2;+-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| id    | trade_no           | type          | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |+-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| 10054 | 156506391300003827 | CONSUME_LUNCH |        | 2019-08-06 03:58:33.000000 | LOCAL_ACCOUNT |              |    200 |    9800 |            3827 |                 0 | 27        |        || 10055 | 156506391300002816 | CONSUME_LUNCH |        | 2019-08-06 03:58:33.000000 | LOCAL_ACCOUNT |              |    200 |    9800 |            2816 |                 0 | 19        |        |+-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+2 rows in set (0.02 sec)mysql> select * from account_transaction limit 2000000,2;+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| id      | trade_no           | type          | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| 5524352 | 163539315991003043 | CONSUME_LUNCH |        | 2021-10-28 03:52:39.000000 | LOCAL_ACCOUNT |              |    200 |    3800 |            3043 |                 0 | 34        |        || 5524354 | 163539342290003077 | CONSUME_LUNCH |        | 2021-10-28 03:57:02.000000 | LOCAL_ACCOUNT |              |    200 |    1500 |            3077 |                 0 | 19        |        |+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+2 rows in set (2.51 sec)

說明1：我們對1頁，1萬頁，200萬頁的數據分別查詢，發現隨著查詢數據量的增加，查詢的時間也在增加

說明2：當我們查詢limit 2000000,2時，此時需要Mysql排序錢2000002條記錄，但是僅僅需要返回200001-20002的記錄，前2000000條記錄丟棄，查詢排序的代價非常大

查詢優化

mysql> select a.* from account_transaction as a, (select id from account_transaction order by id limit 2000000,2) as at where a.id = at.id;+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| id      | trade_no           | type          | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+| 5524352 | 163539315991003043 | CONSUME_LUNCH |        | 2021-10-28 03:52:39.000000 | LOCAL_ACCOUNT |              |    200 |    3800 |            3043 |                 0 | 34        |        || 5524354 | 163539342290003077 | CONSUME_LUNCH |        | 2021-10-28 03:57:02.000000 | LOCAL_ACCOUNT |              |    200 |    1500 |            3077 |                 0 | 19        |        |+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+2 rows in set (0.51 sec)

說明3：同樣是分頁查詢2000000頁以后的數據，該查詢僅好事0.51秒，比直接使用limit分頁查詢快了幾倍

說明4：Mysql官方針對大數據量的分頁查詢給出的方案是，建議使用覆蓋查詢加子查詢形式進行優化

說明5：該插敘的子查詢：select id from account_transaction order by id limit 2000000,2，首先這是根據id查詢到需要數據的id，本身根據id查找就是比較快的。

mysql> select id from account_transaction order by id limit 2000000,2;+---------+| id      |+---------+| 5524352 || 5524354 |+---------+2 rows in set (0.45 sec)

說明6：將該子查詢的結果當做一張表，與account_trasaction做子查詢，這樣效率就會比直接使用limit速度快很多。

六、count優化　　6.1 count() 原理

是一個聚合函數，對于返回的結果集，一行一行的判斷，如果count函數的參數不為NULL,累計值就+1，否則不加1，最后返回累計值

6.2 count的幾種用法

count(*)：

InnoDB引擎并不會把全部的字段取出來，而是專門做了優化，不取值，服務層直接按行進行累加,mysql 對count(*)做了優化。

count(主鍵)

InnoDB引擎會遍歷整張表，把每一行的主鍵id值都取出來，返回給服務層，服務層那個主鍵后，直接按行進行累加（主鍵不可能為空）

count(普通字段)：

沒有not null 約束：InnoDB引擎會遍歷整張表，把每一行的字段值都取出來，返回給服務層，服務層判斷是否為null，不為null,計數+1.

有not null 約束：InnofDB引擎會遍歷整張表，把每一行的字段值都取出來，返回給服務層，直接按行累加

count(1）