一、存儲引擎

1.1 MySQL體系結(jié)構(gòu)

想象一下，MySQL就像一座精心設(shè)計的四層智慧大樓，每一層都有專門的使命，共同協(xié)作處理你的數(shù)據(jù)請求：

一樓接待大廳：連接層

這里是MySQL的“前臺接待處”，專門負責(zé)：

• 為各種客戶端（你的應(yīng)用程序、命令行工具等）辦理入場手續(xù)
• 進行身份安全檢查（用戶名密碼認證）
• 提供VIP通道（SSL加密連接）
• 管理接待員團隊（線程池），確保不會因為訪客太多而手忙腳亂

二樓智慧中心：服務(wù)層

這里是MySQL的“大腦”，所有智能決策都在這里發(fā)生：

• 翻譯官：把你的SQL語句“翻譯”成MySQL能理解的內(nèi)部指令
• 策略師：思考最優(yōu)執(zhí)行路徑（“應(yīng)該先查哪張表？用哪個索引最快？”）
• 記憶大師：緩存常用查詢結(jié)果，下次相同請求直接“秒回”
• 多功能處理器：內(nèi)置函數(shù)、存儲過程都在這里執(zhí)行

三樓物流倉庫：引擎層

這里是MySQL最靈活的一層，像是個可更換的物流系統(tǒng)：

• 提供多種“物流方案”（存儲引擎）供你選擇：
  • InnoDB：像專業(yè)搬家公司，事務(wù)安全、支持行鎖
  • MyISAM：像快遞站點，查詢速度快但不保證事務(wù)
  • Memory：像臨時貨架，數(shù)據(jù)放內(nèi)存，速度極快
• 每種引擎有自己的“倉儲管理方式”（索引實現(xiàn)、數(shù)據(jù)存儲格式）
• 你可以根據(jù)業(yè)務(wù)需要隨時切換引擎，就像根據(jù)貨物選擇快遞公司

四樓實體倉庫：存儲層

這里是實實在在的“數(shù)據(jù)倉庫”：

• 所有數(shù)據(jù)最終都存放在這里（硬盤文件系統(tǒng)）
• 存儲著各種“貨物清單”：
  • 數(shù)據(jù)本身和索引（你的核心貨物）
  • 操作日志（redolog、undolog，像倉庫的進出貨記錄）
  • 系統(tǒng)日志（錯誤日志、慢查詢?nèi)罩荆駛}庫監(jiān)控記錄）

MySQL的獨特魅力

和其他數(shù)據(jù)庫相比，MySQL有點與眾不同，它的架構(gòu)可以在多種不同場景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲引擎上，插件式的存儲引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲提取分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實際需要選擇合適的存儲引擎。

1.2 存儲引擎介紹

1. 建表時指定存儲引擎

   CREATE TABLE 表名(
            字段1 字段1類型  [ COMMENT 字段1注釋]，
            ...
            字段n 字段n類型  [ COMMENT 字段n注釋]，
   ) ENGINE = INNODB [ COMMENT 表注釋];
   

2. 查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎

   show engines;
   

1.3 存儲引擎特點

1.3.1 InnoDB

1. 介紹
   InnoDB是一種兼顧高可靠性和高性能的通用存儲引擎，MySQL 5.5 之后，InnoDB是默認的

2. 特點

   • DML操作遵循ACID模型，支持事務(wù)；
   • 行級鎖，提高并發(fā)訪問性能；
   • 支持外鍵FOREIGN KEY約束，保證數(shù)據(jù)的完整性和正確性；

3. 文件
   xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會對應(yīng)這樣一個表空間文件，存儲該表的表結(jié)構(gòu)（frm-早期的 、sdi-新版的）、數(shù)據(jù)和索引。

4. 邏輯存儲結(jié)構(gòu)

   

   • 表空間 : InnoDB存儲引擎邏輯結(jié)構(gòu)的最高層，ibd文件其實就是表空間文件，在表空間中可以包含多個Segment段。
   • 段 : 表空間是由各個段組成的， 常見的段有數(shù)據(jù)段、索引段、回滾段等。InnoDB中對于段的管理，都是引擎自身完成，不需要人為對其控制，一個段中包含多個區(qū)。
   • 區(qū) : 區(qū)是表空間的單元結(jié)構(gòu)，每個區(qū)的大小為1M。 默認情況下， InnoDB存儲引擎頁大小為16K， 即一個區(qū)中一共有64個連續(xù)的頁。
   • 頁 : 頁是組成區(qū)的最小單元，頁也是InnoDB 存儲引擎磁盤管理的最小單元，每個頁的大小默認為 16KB。為了保證頁的連續(xù)性，InnoDB 存儲引擎每次從磁盤申請 4-5 個區(qū)。
   • 行 : InnoDB 存儲引擎是面向行的，也就是說數(shù)據(jù)是按行進行存放的，在每一行中除了定義表時所指定的字段以外，還包含兩個隱藏字段(后面會詳細介紹)。

1.3.2 MyISAM

1. 介紹
   MyISAM是MySQL早期的默認存儲引擎。

2. 特點

   • 不支持事務(wù)，不支持外鍵
   • 支持表鎖，不支持行鎖
   • 訪問速度快

3. 文件
   xxx.sdi：存儲表結(jié)構(gòu)信息
   xxx.MYD: 存儲數(shù)據(jù)
   xxx.MYI: 存儲索引

1.3.3 Memory

1. 介紹
   Memory引擎的表數(shù)據(jù)時存儲在內(nèi)存中的，由于受到硬件問題、或斷電問題的影響，只能將這些表作為臨時表或緩存使用。
2. 特點
   內(nèi)存存放，hash索引（默認）
3. 文件
   xxx.sdi：存儲表結(jié)構(gòu)信息

1.3.4 區(qū)別及特點

1.4 存儲引擎選擇

• InnoDB: 是Mysql的默認存儲引擎，支持事務(wù)、外鍵。如果應(yīng)用對事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲引擎是比較合適的選擇。
• MyISAM ： 如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個存儲引擎是非常合適的。
• MEMORY：將所有數(shù)據(jù)保存在內(nèi)存中，訪問速度快，通常用于臨時表及緩存。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在內(nèi)存中，而且無法保障數(shù)據(jù)的安全性。

二、索引

2.1 索引概述

2.1.1 介紹

索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

2.1.2 特點

2.2 索引結(jié)構(gòu)

2.2.1 概述

注意： 我們平常所說的索引，如果沒有特別指明，都是指B+樹結(jié)構(gòu)組織的索引。

2.3 索引分類

2.3.1 索引分類

2.3.2 聚集索引&二級索引

在InnoDB存儲引擎中，根據(jù)索引的存儲形式，可分為一下兩種：

聚集索引選取規(guī)則：

• 如果存在主鍵，主鍵索引就是聚集索引。
• 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引
• 如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

聚集索引和二級索引的具體結(jié)構(gòu)如下：

• 聚集索引的葉子節(jié)點下掛的是這一行的數(shù)據(jù)。
• 二級索引的葉子節(jié)點下掛的是該字段值對應(yīng)的主鍵值。

我們可以分析一下，執(zhí)行select * from user where name = 'Arm',具體的查找過程：

1. 由于是根據(jù)name字段進行查詢，到name字段的二級索引中進行匹配查找。但是在二級索引中只能查找到 Arm 對應(yīng)的主鍵10。
2. 由于查詢返回的數(shù)據(jù)是*，所以此時，還需要根據(jù)主鍵值10，到聚集索引中查找10對應(yīng)的記錄，最終找到10對應(yīng)的行rom。
3. 最終拿到這一行的數(shù)據(jù)，直接返回即可。(若直接根據(jù)id查詢，直接通過聚集索引返回數(shù)據(jù))

回表查詢： 這種先到二級索引中查找數(shù)據(jù)，找到主鍵值，然后再到聚集索引中根據(jù)主鍵值，獲取數(shù)據(jù)的方式，就稱之為回表查詢。

2.4 索引語法

1. 創(chuàng)建索引

   CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名 ( index_col_name,... ) ;
   

2. 查看索引

   SHOW INDEX FROM 表名 ;
   

3. 刪除索引

   DROP INDEX 索引名 ON 表名 ;
   

   案例演示：

   # 創(chuàng)建系統(tǒng)用戶表
   create table tb_user(
          id int primary key auto_increment comment '主鍵',
          name varchar(50) not null comment '用戶名',
          phone varchar(11) not null comment '手機號',
          email varchar(100) comment '郵箱',
          profession varchar(11) comment '專業(yè)',
          age tinyint unsigned comment '年齡',
          gender char(1) comment '性別 , 1: 男, 2: 女'，
          status char(1) comment '狀態(tài)',
          createtime datetime comment '創(chuàng)建時間'
   ) comment '系統(tǒng)用戶表'；
   
   # 插入數(shù)據(jù)
   insert into tb_user (name, phone, email, profession, age, gender, status, createtime) values 
        ('呂布', '17799990000', 'lvbu666@163.com', '軟件工程', 23, '1', '6', '2001-02-02 00:00:00'), 
        ('曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33, '1', '0', '2001-03-05 00:00:00'), 
        ('趙云', '17799990002', '17799990@139.com', '英語', 34, '1', '2', '2002-03-02 00:00:00'), 
        ('孫悟空', '17799990003', '17799990@sina.com', '工程造價', 54, '1', '0', '2001-07-02 00:00:00'), 
        ('花木蘭', '17799990004', '19980729@sina.com', '軟件工程', 23, '2', '1', '2001-04-22 00:00:00'), 
        ('大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'),
        ('露娜', '17799990006', 'luna_love@sina.com', '應(yīng)用數(shù)學(xué)', 24,
   '2', '0', '2001-02-08 00:00:00')......;
   

   數(shù)據(jù)準(zhǔn)備完畢，完成如下需求：

   1. name字段為姓名字段，該字段的值可能會重復(fù)，為該字段創(chuàng)建索引。

      create index idx_user_name on tb_user(name);
      

   2. phone手機號字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。

      create unqiue index idx_user_phone on tb_user(phone);
      

   3. 為profession、age、status創(chuàng)建聯(lián)合索引。

      create index idx_user_pro_age_sta on tb_user(profession, age, status);
      

   4. 為email建立合適的索引來提升查詢效率。

      create index idx_email on tb_user(email);
      

   我們可以通過show index from tb_user來查看tb_user的所有索引。

2.5 SQL性能分析

2.5.1 SQL執(zhí)行頻率

MySQL 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。通過如下指令，可以查看當(dāng)前數(shù)據(jù)庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

# session 是查看當(dāng)前會話, global 是查詢?nèi)謹?shù)據(jù) 
# Com_delete: 刪除次數(shù), Com_insert: 插入次數(shù)
# Com_select: 查詢次數(shù), Com_update: 更新次數(shù)
show global status like 'Com_______';

通過查詢SQL的執(zhí)行頻次，可以知道當(dāng)前的數(shù)據(jù)庫是以增刪改為主，還是查詢?yōu)橹?，在進一步優(yōu)化。

2.5.2 慢查詢?nèi)罩?/h4>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒，默認10秒）的所有SQL語句的日志。
MySQL的慢查詢?nèi)罩灸J是沒有開啟。我們可以通過show variabies like 'show_query_log'來查看：

如果要配置慢查詢?nèi)罩?，需要在MySQl的配置文件（/etc/my.cnf）中配置如下信息：

# 開啟MySQL慢日志查詢開關(guān)
slow_query_log=1
# 設(shè)置慢日志的時間為2秒，SQL語句執(zhí)行時間超過2秒，就會視為慢查詢，記錄慢查詢?nèi)罩?
long_query_time=2

配置完畢之后，通過systemctl restart mysqld指令重新啟動MySQL服務(wù)器進行測試，查看慢日志文件中記錄的信息/var/lib/mysql/localhost-slow.log。
通過慢查詢?nèi)罩?，就可以定位出?zhí)行效率比較低的SQL，從而有針對性的進行優(yōu)化.

2.5.3 profile詳情

show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費到哪里去了。通過select @@have_profiling;能夠看到當(dāng)前MySQL是否支持profile操作：

可以看到，當(dāng)前MySQL是支持 profile操作的，但是開關(guān)是關(guān)閉的?？梢酝ㄟ^set語句在session/global級別開啟profiling：

set profiling = 1;

若執(zhí)行一系列的業(yè)務(wù)SQL的操作后，通過如下指令查看指令的執(zhí)行耗時：

# 查看每一條SQL的耗時基本情況
show profiles;

# 查看指定query_id的SQL語句各個階段的耗時情況
show profile for query query_id;

# 查看指定query_id的SQL語句CPU的使用情況
show profile cpu for query query_id;

查看每一條SQL的耗時情況:

查看指定SQL各個階段的耗時情況 :

2.5.4 explain詳情

explain 或者 desc 命令獲取MySQl如何執(zhí)行select語句的信息，包括在select語句執(zhí)行過程中表如何連接和連接的順序。

# 直接在select語句之前加上關(guān)鍵字 explain / desc
explain select 字段列表 from 表名 where 條件；

2.6 索引使用

2.6.1 最左前綴法則

如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(后面的字段索引失效)。

在 tb_user 表中，有一個聯(lián)合索引idx_pro_age_sta，這個聯(lián)合索引涉及到三個字段，順序分別為：profession，age，status。
對于最左前綴法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列后面的字段索引將失效。

2.6.2 范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢(>,<)，范圍查詢右側(cè)的列索引失效。
當(dāng)范圍查詢使用>= 或 <= 時，所有的字段都是走索引的，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢。

2.6.3 索引失效情況

2.6.3.1 索引列運算

不要在索引列上進行運算操作， 索引將失效。
例：

# 在tb_user表中，存在idx_phone索引
# 當(dāng)根據(jù)phone字段進行等值匹配查詢時, 索引生效
explain select * from tb_user where phone = '17799990015';

# 當(dāng)根據(jù)phone字段進行函數(shù)運算操作之后，索引失效
explain select * from tb_user where substring(phone,10,2) = '15';

2.6.3.2 字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。

2.6.3.3 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

# 在關(guān)鍵字后面加%，索引可以生效
explain select * from tb_user where profession like '軟件%';

# 在關(guān)鍵字前面加了%，索引將會失效
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

2.6.3.3 or連接條件

用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到。當(dāng)or連接的條件，左右兩側(cè)字段都有索引時，索引才會生效。

# 即使id有索引, age沒有索引, 索引也會失效
explain select * from tb_user where id = 10 or age = 23;

2.6.3.4 數(shù)據(jù)分布影響

如果MySQL評估使用索引比全表更慢，則不使用索引。

當(dāng)MySQL在查詢時，會評估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。 因為索引是用來索引少量數(shù)據(jù)的，如果通過索引查詢返回大批量的數(shù)據(jù)，則還不如走全表掃描來的快，此時索引就會失效。

2.6.4 SQL提示

SQL提示，是優(yōu)化數(shù)據(jù)庫的一個重要手段，就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的。

1. use index ： 建議MySQL使用哪一個索引完成此次查詢（僅僅是建議，mysql內(nèi)部還會再次進行評估）。

   # 在emp表中，根據(jù)軟件工程專業(yè)查找，使用idx_user_pro索引
   explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';
   

2. ignore index ： 忽略指定的索引。

   # 忽略idx_user_pro索引
   explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';
   

3. force index ： 強制使用索引。

   # 強制使用idx_user_pro索引
   explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';
   

2.6.5 覆蓋索引

覆蓋索引是指查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到，減少使用select * 。

接下來，我們來看一組SQL的執(zhí)行計劃，看看執(zhí)行計劃的差別：

explain select id, profession from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

上述這幾條SQL的執(zhí)行結(jié)果為:

因為，在tb_user表中有一個聯(lián)合索引 idx_user_pro_age_sta，該索引關(guān)聯(lián)了三個字段profession、age、status，而這個索引也是一個二級索引，所以葉子節(jié)點下面掛的是這一行的主鍵id。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id、profession、age、status 之中，則直接走二級索引直接返回數(shù)據(jù)了。 如果超出這個范圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個過程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會造成回表查詢（除非是根據(jù)主鍵查詢，此時只會掃描聚集索引）。

2.6.6 前綴索引

當(dāng)字段類型為字符串（varchar，text，longtext等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費大量的磁盤IO， 影響查詢效率。此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

1. 語法

   create index idx_xxxx on table_name(column(n)) ;
   

例：
為tb_user表的email字段，建立長度為5的前綴索引

create index idx_email_5 on tb_user(email(5));

2. 前綴長度
   可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

2.6.7 單例索引與聯(lián)合索引

若存在一個表中，有兩個子段以及這兩個字段分別對應(yīng)的單例索引，我們根據(jù)這兩個字段來查詢，它會走一個字段的索引，再回表查詢。在業(yè)務(wù)場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引，而非單列索引。。

2.7 索引設(shè)計原則

1. 針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
3. 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
4. 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
5. 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率。
6. 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率。
7. 如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢。

三、SQL優(yōu)化

3.1 插入數(shù)據(jù)

3.1.1 insert

如果我們需要一次性往數(shù)據(jù)庫表中插入多條記錄，可以從以下三個方面進行優(yōu)化：

1. 方案一：批量插入數(shù)據(jù)

   insert into tb_user values (1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
   

2. 方案二：手動控制事務(wù)

   start transaction;
   insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
   insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
   insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
   commit;
   

3. 方案三：主鍵順序插入，性能要高于亂序插入

   # 主鍵亂序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
   # 主鍵順序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
   

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

如果一次性需要插入大批量數(shù)據(jù)(比如: 幾百萬的記錄)，使用insert語句插入性能較低，此時可以使用MySQL數(shù)據(jù)庫提供的load指令進行插入。操作如下：

# 客戶端連接服務(wù)端時，加上參數(shù) -–local-infile
mysql –-local-infile -u root -p

# 設(shè)置全局參數(shù)local_infile為1，開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1;

# 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

3.2 主鍵優(yōu)化

主鍵順序插入的性能是要高于亂序插入的原因：

1. 數(shù)據(jù)組織方式
   在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表(index organized table IOT)。行數(shù)據(jù)，都是存儲在聚集索引的葉子節(jié)點上的。
   在InnoDB引擎中，數(shù)據(jù)行是記錄在邏輯結(jié)構(gòu) page 頁中的，而每一個頁的大小是固定的，默認16K。那也就意味著， 一個頁中所存儲的行也是有限的，如果插入的數(shù)據(jù)行row在該頁存儲不小，將會存儲到下一個頁中，頁與頁之間會通過指針連接。

   

2. 頁分裂
   頁可以為空，也可以填充一半，也可以填充100%。每個頁包含了2-N行數(shù)據(jù)(如果一行數(shù)據(jù)過大，會行溢出)，根據(jù)主鍵排列。

   • 主鍵順序插入,將行數(shù)據(jù)插入頁中，也被寫后，申請第二個頁寫入，頁與頁通過指針連接。
   • 主鍵亂序插入，在行數(shù)據(jù)數(shù)目少時，正常插入。若后續(xù)插入數(shù)據(jù)需要插入到兩頁中間時，會申請一個頁用來將兩頁數(shù)據(jù)之間較小頁數(shù)據(jù)分擔(dān)存放一些，再將插入數(shù)據(jù)插入其中。

     

3. 頁合并
   當(dāng)我們對已有數(shù)據(jù)進行刪除時，刪除一行記錄，實際上記錄并沒有被物理刪除，只是記錄被標(biāo)記（flaged）為刪除并且它的空間變得允許被其他記錄聲明使用。當(dāng)我們頁中刪除的記錄達到 MERGE_THRESHOLD（合并頁的閾值，可以自己設(shè)置，在創(chuàng)建表或者創(chuàng)建索引時指定，默認為頁的50%），InnoDB會開始尋找最靠近的頁（前或后）看看是否可以將兩個頁合并以優(yōu)化空間使用。

4. 索引設(shè)計原則

   • 滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度
   • 插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵。
   • 盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號。
   • 業(yè)務(wù)操作時，避免對主鍵的修改。

3.3 order by優(yōu)化

MySQL的排序，有兩種方式：

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

Using index : 通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高。
以上的兩種排序方式，Using index的性能高，Using filesort性能低，我們在優(yōu)化時，盡量要優(yōu)化為 Using index。
出order by優(yōu)化原則:

• 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則。
• 盡量使用覆蓋索引。
• 多字段排序, 一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）。
• 如果不可避免的出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小sort_buffer_size(默認256k)。

3.4 group by優(yōu)化

在分組操作中，我們需要通過以下兩點進行優(yōu)化，以提升性能：

• 在分組操作時，可以通過建立索引來提高效率。
• 分組操作時，索引的使用也是滿足最左前綴法則。

3.5 limit優(yōu)化

在數(shù)據(jù)量比較大時，如果進行l(wèi)imit分頁查詢，在查詢時，越往后，分頁查詢效率越低。因為，當(dāng)在進行分頁查詢時，如果執(zhí)行 limit （2000000,10） ，此時需要MySQL排序前2000010 記錄，僅僅返回 2000000 - 2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。
優(yōu)化思路: 一般分頁查詢時，通過創(chuàng)建 覆蓋索引 能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進行優(yōu)化。

3.6 count優(yōu)化

3.6.1 概述

在之前的測試中，我們發(fā)現(xiàn)，如果數(shù)據(jù)量很大，在執(zhí)行count操作時，是非常耗時的。

• MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count(*) 的時候會直接返回這個數(shù)，效率很高； 但是如果是帶條件的count，MyISAM也慢。
• InnoDB 引擎就麻煩了，它執(zhí)行 count(*) 的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累積計數(shù)。

如果說要大幅度提升InnoDB表的count效率，主要的優(yōu)化思路：自己計數(shù)(可以借助于redis進行,但是如果是帶條件的count又比較麻煩了)。

3.6.2 count用法

按照效率排序的話，count(字段) < count(主鍵 id) < count(1) ≈ count( * )，所以盡量使用 count( * )。

總結(jié)

到此這篇關(guān)于MySQL進階之路索引優(yōu)化與SQL調(diào)優(yōu)的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化與SQL調(diào)優(yōu)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論