一、插入數(shù)據(jù)優(yōu)化　　1.1 批量插入

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

1.2 手動提交事務(wù)

因為Mysql默認(rèn)每執(zhí)行一次操作，就會提交一次事務(wù)，這樣就會涉及到頻繁的事務(wù)的開啟與關(guān)閉

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

1.3 主鍵順序插入

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

(資料圖片僅供參考)

1.4 大批量插入數(shù)據(jù)

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

首先在連接數(shù)據(jù)庫的時候需要加上 --local-infile 參數(shù)

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)

創(chuàng)建一個空表tb_user，其表結(jié)構(gòu)如下

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" 內(nèi)容到新創(chuàng)建的表中,其中tb_user_data.sql中的測試數(shù)據(jù)如下

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" 內(nèi)容到新創(chuàng)建的表中　　

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 是將文件中的數(shù)據(jù)，插入到tb_user表中

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

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

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

二、主鍵優(yōu)化　　2.1 數(shù)據(jù)組織方式

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

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

說明2：索引中的各個節(jié)點都是保存在邏輯結(jié)構(gòu)頁上面的，一頁默認(rèn)大小16K

2.2 頁分裂

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

情況1：主鍵順序插入

說明1：row是行數(shù)據(jù)，每一頁上可以存放多個行數(shù)據(jù)?！　　　?/p>

情況2：主鍵亂序插入

說明1：當(dāng)我們想要在插入一個id=50的數(shù)據(jù)時，會發(fā)生頁分裂

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

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

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

2.3 頁合并

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

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

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

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

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

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

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

3.2 Using index

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

3.3 案例

emp表結(jié)構(gòu)：

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：對查詢結(jié)果進行按 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說明是先查出來需要的數(shù)據(jù)，然后再排序的,效率不高。

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

案例2：給 salary 和 age 添加一個聯(lián)合排序

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：聯(lián)合索引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，所以其查詢的結(jié)果直接就是排序好的結(jié)果

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

案例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在后，和索引的順序不一致，仍然會觸發(fā)索引，使用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同時做降序還是對其中一個字段做降序排列，都會出現(xiàn)Backward index scan; Using index，其中 Backward index scan 是反向掃描索引

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

案例7：創(chuàng)建一個 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 就是根據(jù) 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：在實際的業(yè)務(wù)中，我們可以根據(jù)自己的查詢需要，創(chuàng)建升序或者降序的索引。

3.4 order by總結(jié)根據(jù)排序字段建立合適的索引，多字段排序是，也遵循最左前綴法則盡量使用覆蓋索引多字段排序，如果有升序有降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則，也應(yīng)該有對應(yīng)的升序和降序如果不可避免的出現(xiàn)filesort,大數(shù)據(jù)量排序的時候，可以適當(dāng)增大排序緩沖區(qū)的大小，sort_buffer_size（默認(rèn)256K）四、group by優(yōu)化

為了測試數(shù)據(jù)的準(zhǔn)確性，這是我先把除了主鍵以外的索引都刪除了，然后根據(jù)需要在重新創(chuàng)建

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：根據(jù)job做聚合查詢

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

我們使用explain查看一下執(zhí)行計劃

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,說明在這次的查詢中創(chuàng)建了一張臨時表，這是無論是空間上還是速度上都會影響到查詢效率的。

這時我們給 job 創(chuàng)建一個索引，再次使用explain查看一下執(zhí)行計劃

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，那是因為沒有一個與之對應(yīng)的聯(lián)合索引。

我們繼續(xù)創(chuàng)建一個 job 和 age 的聯(lián)合索引，然后再看一下 explain 的執(zhí)行計劃

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 || 開發(fā)         |   22 |        2 || 開發(fā)         |   24 |        1 || 董事長       |   43 |        1 || 財務(wù)         |   25 |        1 || 項目經(jīng)理     |   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：當(dāng)我們group by后面的分組字段，存在于某一個聯(lián)合索引中的時候，group by會使用索引查詢，而不會建立臨時表

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

mysql> select job,age from emp where job="開發(fā)" group by age;+--------+------+| job    | age  |+--------+------+| 開發(fā)   |   22 || 開發(fā)   |   24 |+--------+------+2 rows in set (0.01 sec)mysql> explain select job,age from emp where job="開發(fā)" 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：當(dāng)where后面的條件和group by 后面的條件一起組合成連鎖索引，也不會建立臨時表，也會直接走連個查詢索引的。效率同樣比較高

總結(jié)：

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

account_transaction表數(shù)據(jù)量展示

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

說明1：account_transaction總數(shù)據(jù)量有226萬+

案例1：分別采用分頁查詢，第一頁，第1萬頁，200萬頁的數(shù)據(jù)

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萬頁的數(shù)據(jù)分別查詢，發(fā)現(xiàn)隨著查詢數(shù)據(jù)量的增加，查詢的時間也在增加

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

查詢優(yōu)化

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頁以后的數(shù)據(jù)，該查詢僅好事0.51秒，比直接使用limit分頁查詢快了幾倍

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

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

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

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

六、count優(yōu)化　　6.1 count() 原理

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

6.2 count的幾種用法

count(*)：

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

count(主鍵)

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

count(普通字段)：

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

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

count(1）

InnoDB引擎遍歷整張表，但不取值，服務(wù)層對于返回的每一行，放一個數(shù)字“1”進去，直接按行進行累加。

6.3 效率排序

count(*) ≈count(1)>count(id)>count(普通字段)

七、update優(yōu)化　　7.1案例1：根據(jù)索引修改數(shù)據(jù)，僅僅會觸發(fā)行鎖

說明1：因為左邊和右邊都是根據(jù)id修改的不同數(shù)據(jù)，這時id是主鍵索引，所以這里的修改都只會觸發(fā)行鎖，不會影響其他行的修改。

7.2案例2：根據(jù)非索引字段同時修改記錄數(shù)據(jù)

說明1：update的時候，如果條件是索引字段，則只會觸發(fā)行索引

說明2：updae的時候，如果條件是非索引字段，則會觸發(fā)表索引，即在update的時候，整張表處于鎖住的狀態(tài)。

說明3：主需要對update的字段創(chuàng)建一個索引值，就可以在update的時候?qū)⒈礞i降低為行鎖。

7.3 總結(jié)：

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則會從行鎖升級為表鎖。