評(píng)論員文章

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

如果有多條數(shù)據(jù)需要同時(shí)插入，不要每次插入一條，然后分多次插入，因?yàn)槊繄?zhí)行一次插入的操作，都要進(jìn)行數(shù)據(jù)庫的連接，多個(gè)操作就會(huì)連接多次，而一次批量操作只需要連接1次

1.2 手動(dòng)提交事務(wù)

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

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

1.3 主鍵順序插入

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

(資料圖片僅供參考)

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

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

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

mysql --local-infile -u root -p

在使用本地文件加載功能的時(shí)候，需要先查看本地加載文件選項(xiàng)是否開啟的

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)建一個(gè)空表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中的測(cè)試數(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 "," 是說每個(gè)字段之間的數(shù)據(jù)是使用","分割的

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

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

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

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

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

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

2.2 頁分裂

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

情況1：主鍵順序插入

說明1：row是行數(shù)據(jù)，每一頁上可以存放多個(gè)行數(shù)據(jù)。　　　　

情況2：主鍵亂序插入

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

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

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

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

2.3 頁合并

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

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

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

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

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

2.4 主鍵設(shè)計(jì)原則滿足業(yè)務(wù)需求的情況下，盡量減低主鍵的長(zhǎng)度。插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用auto_incerment自增主鍵，盡量不要用uuid作主鍵或者其他自然主鍵如身份證號(hào)，因?yàn)檫@個(gè)值是無需的，會(huì)存在頁分裂情況。三、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：對(duì)查詢結(jié)果進(jìn)行按 salary 和 age 都進(jìn)行升序排序

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：為什么會(huì)出現(xiàn)Using filesort呢？因?yàn)椴樵兊倪@些字段在查詢之前是無須的，索引需要先將數(shù)據(jù)查詢出來，然后再做排序，這樣才能得到想要的排序好的數(shù)據(jù)。

案例2：給 salary 和 age 添加一個(gè)聯(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 對(duì) salary 和 age 進(jìn)行升序排序

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：在做升序排列時(shí)，無論 order by 后面是組合索引的全部字段，還是只有部分字段，這時(shí) Extra 的值都是Usind index，所以其查詢的結(jié)果直接就是排序好的結(jié)果

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

案例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：這個(gè)時(shí)候order by 是age在前，salary在后，和索引的順序不一致，仍然會(huì)觸發(fā)索引，使用Using index，但是也會(huì)使用Using filesort,所以推薦大家使用正確的索引順序的字段來進(jìn)行排序

案例5：對(duì)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：無論是對(duì)salary和age同時(shí)做降序還是對(duì)其中一個(gè)字段做降序排列，都會(huì)出現(xiàn)Backward index scan; Using index，其中 Backward index scan 是反向掃描索引

說明2：這是因?yàn)樗饕心J(rèn)的順序是升序的，而做降序排列，就需要反向掃描索引了

案例7：創(chuàng)建一個(gè) 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：這個(gè)時(shí)候的Extra 中顯示的 Using index，效率就會(huì)比較高了

說明2：這是因?yàn)閟alary_age_desc_idx索引的順序就是降序排列的，所以使用該索引做降序排列的時(shí)候，就不需要在做反向掃描

說明3：在實(shí)際的業(yè)務(wù)中，我們可以根據(jù)自己的查詢需要，創(chuàng)建升序或者降序的索引。

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

為了測(cè)試數(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(*) |+--------------+----------+| 董事長(zhǎng)       |        1 || 項(xiàng)目經(jīng)理     |        1 || 開發(fā)         |        3 || 財(cái)務(wù)         |        1 || 出納         |        1 || 人事         |        1 |+--------------+----------+6 rows in set (0.00 sec)

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

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)建了一張臨時(shí)表，這是無論是空間上還是速度上都會(huì)影響到查詢效率的。

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

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：這是查詢中就使用到了索引查詢，而沒有建立臨時(shí)表

這時(shí)我們?cè)趯?duì) job 和 age 同時(shí)做分組查詢

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：這時(shí)Extra字段的值，仍然是Using temporary，那是因?yàn)闆]有一個(gè)與之對(duì)應(yīng)的聯(lián)合索引。

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

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 || 董事長(zhǎng)       |   43 |        1 || 財(cái)務(wù)         |   25 |        1 || 項(xiàng)目經(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后面的分組字段，存在于某一個(gè)聯(lián)合索引中的時(shí)候，group by會(huì)使用索引查詢，而不會(huì)建立臨時(shí)表

案例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 后面的條件一起組合成連鎖索引，也不會(huì)建立臨時(shí)表，也會(huì)直接走連個(gè)查詢索引的。效率同樣比較高

總結(jié)：

在分組操作時(shí)，可以通過索引來提高效率分組操作時(shí)，索引的使用也滿足最左前綴法則五、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：我們對(duì)1頁，1萬頁，200萬頁的數(shù)據(jù)分別查詢，發(fā)現(xiàn)隨著查詢數(shù)據(jù)量的增加，查詢的時(shí)間也在增加

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

查詢優(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官方針對(duì)大數(shù)據(jù)量的分頁查詢給出的方案是，建議使用覆蓋查詢加子查詢形式進(jìn)行優(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做子查詢，這樣效率就會(huì)比直接使用limit速度快很多。

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

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

6.2 count的幾種用法

count(*)：

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

count(主鍵)

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

count(普通字段)：

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

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

count(1）

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

6.3 效率排序

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

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

說明1：因?yàn)樽筮吅陀疫叾际歉鶕?jù)id修改的不同數(shù)據(jù)，這時(shí)id是主鍵索引，所以這里的修改都只會(huì)觸發(fā)行鎖，不會(huì)影響其他行的修改。

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

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

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

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

7.3 總結(jié)：

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