存儲(chǔ)引擎

存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)、建立索引、更新/查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式。存儲(chǔ)引擎是基于表的，而不是基于庫(kù)的，所以存儲(chǔ)引擎也可被稱為表類型。

1. 在創(chuàng)建表時(shí)，指定存儲(chǔ)引擎

create table 表名(
	...
)engine = innodb
#在最后指定

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

show engines;

3. innoDB存儲(chǔ)引擎

innoDB是一種兼顧高可靠性和高性能的通用存儲(chǔ)引擎，在MySQL 5.5之后，innoDB是默認(rèn)的MySQL存儲(chǔ)引擎，在此之前的默認(rèn)引擎是MyISAM。

它具有以下幾個(gè)特點(diǎn)：

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

innoDB引擎的每張表都會(huì)有一個(gè)這樣的表空間文件（xxx.ibd），存儲(chǔ)該表的表結(jié)構(gòu)（frm、sdi）、數(shù)據(jù)和索引。

參數(shù)：innodb_file_per_table 該參數(shù)打開表示每一張表對(duì)應(yīng)一個(gè)表空間文件而不是共享一個(gè)。

4. MyISAM存儲(chǔ)引擎

具有以下幾個(gè)特點(diǎn)：

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

具有三個(gè)文件：

xxx.sdi：存儲(chǔ)表結(jié)構(gòu)信息。

xxx.MYD：存儲(chǔ)數(shù)據(jù)。

xxx.MYI：存儲(chǔ)索引。

5. Memory存儲(chǔ)引擎

Memory引擎的表數(shù)據(jù)是存儲(chǔ)在內(nèi)存中的，由于受到硬件問題、或斷電問題，只能將這些表作為臨時(shí)表或緩存使用。

具有以下幾個(gè)特點(diǎn)：

• 內(nèi)存存放
• hash索引（默認(rèn)）

文件：

xxx.sdi：存儲(chǔ)表結(jié)構(gòu)信息。

6. 區(qū)別

事務(wù)安全、行級(jí)鎖、外鍵。

7. 存儲(chǔ)引擎的選擇

在選擇存儲(chǔ)引擎時(shí)，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲(chǔ)引擎，還可以根據(jù)實(shí)際情況選擇多種存儲(chǔ)引擎進(jìn)行組合。

**innoDB：**是Mysql的默認(rèn)存儲(chǔ)引擎，支持事務(wù)、外鍵。如果應(yīng)用對(duì)事物的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么innoDB存儲(chǔ)引擎是比較合適的選擇。（絕大多數(shù)）

**MyISAM：**如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常合適的。

**MEMORY：**將所有數(shù)據(jù)保存在內(nèi)存中，訪問速度快，通常用于臨時(shí)表及緩存。MEMORY的缺陷就是對(duì)表的大小有限制，太大的表無法緩存在內(nèi)存中，而且無法保障數(shù)據(jù)的安全性。（被redis替代）

索引基礎(chǔ)

索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）

1. 優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

• 提高數(shù)據(jù)檢索的效率，降低了數(shù)據(jù)庫(kù)的I/O成本
• 通過索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗。

缺點(diǎn)：

• 索引列也是需要占空間的。
• 索引大大提高了查詢效率，同時(shí)卻也降低更新表的速度，如對(duì)表進(jìn)行insert、update\delete時(shí)，效率降低。

2. 索引結(jié)構(gòu)

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

3. 索引分類

4. innoDB索引分類

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

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

5. 索引語(yǔ)法

1. 創(chuàng)建索引

create [unique|fulltext] index 索引名 on 表名 (字段1,...);

PS：一個(gè)索引是可以關(guān)聯(lián)多個(gè)字段的。

2.查看索引

show index from 表名;

3.刪除索引

drop index 索引名 on 表名;

SQL優(yōu)化

步驟：

1. 通過慢查詢?nèi)罩緛聿檎倚枰獌?yōu)化的SQL。
2. 通過explain來分析SQL。
3. SQL語(yǔ)句的優(yōu)化原則。

SQL查詢性能下降的原因

查詢性能變低的最基礎(chǔ)的原因，就是訪問的數(shù)據(jù)太多了。

對(duì)于低效的查詢，可以通過下面兩個(gè)步驟分析：

1. 確認(rèn)是否在檢索大量超過需要的數(shù)據(jù)。可能是訪問了很多的行，也有可能是訪問了很多的列。
2. 確認(rèn)MySQL服務(wù)層是否分析大量超過需要的數(shù)據(jù)行。

1. 慢查詢?nèi)罩?/h3>

記錄查詢?cè)捹M(fèi)大量時(shí)間的SQL的日志，就是慢查詢?nèi)罩?/strong>。
long_query_time采數(shù)：該參數(shù)會(huì)設(shè)定一個(gè)閾值，超過該值的SQL，就是慢查詢SQL。

# 查看mysql的環(huán)境變量
show variables like '%query%';

# 設(shè)置慢SQL的時(shí)間及開啟慢SQL功能
set global long_query_time = 10;
set global slow_query_log = on;

2. 執(zhí)行計(jì)劃

# 要執(zhí)行一個(gè)SQL時(shí)，查詢優(yōu)化器會(huì)基于成本和規(guī)則對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化，從而生成一個(gè)執(zhí)行計(jì)劃；
# 通過查詢計(jì)劃，我們可以看到，查詢走了哪個(gè)索引，查詢的具體方式，多表鏈接的順序等等；
# 執(zhí)行計(jì)劃的語(yǔ)法：
explain SQL語(yǔ)句
# SQL語(yǔ)句可以是insert，update，delete，select等

示例：

id: 在一個(gè)大的查詢中，每一個(gè)select都對(duì)應(yīng)一個(gè)唯一的ID
select_type: select的查詢類型
table: 表名
partitions: 分區(qū)信息
type: 針對(duì)單表的訪問方法
possible_keys: 可能用到的索引
key: 實(shí)際用到的索引
key_len: 實(shí)際使用的索引長(zhǎng)度
ref: 當(dāng)使用索引列等值查詢時(shí)，與索引列進(jìn)行等值匹配的對(duì)象信息
rows: 預(yù)估要讀取的記錄的條數(shù)
filtered: 搜索條件過濾后剩余的百分比
extra: 一些額外的信息

id列

# 查詢的唯一標(biāo)識(shí)
# 一個(gè)查詢語(yǔ)句只有一個(gè)標(biāo)識(shí)；比如簡(jiǎn)單查詢或表連接
# 當(dāng)查詢語(yǔ)句涉及子查詢時(shí)，有兩個(gè)id

select_type

# 查詢類型
simple:簡(jiǎn)單查詢
primary:如果查詢中包含union，union all，子查詢時(shí)，左邊的查詢的select_type就是primary
union:查詢中包含union時(shí)，右邊的查詢的select_type就是primary
union result:選擇使用臨時(shí)表來完成union查詢的去重工作
subquery:子查詢，非關(guān)聯(lián)子查詢，該查詢會(huì)物化，只查詢一次
dependent subquery:關(guān)聯(lián)子查詢，子查詢執(zhí)行多次
derived:from后面跟子查詢，物化表，只執(zhí)行一次

type

#訪問類型
#一共有12個(gè)，有7個(gè)最常用的
#從上到下性能越來越好
性能: system>const>eq_ref>ref>range>index>all
all:全表掃描
    explain select * from emp;
    explain select * from emp where id = 3;
index:當(dāng)可以使用索引覆蓋，但需要掃描全部的索引記錄時(shí)，該表的訪問方法是index
	explain select id from emp;
range:如果使用索引獲取某些單點(diǎn)掃描區(qū)間的記錄
	explain select * from emp where id in (1,4,53,23);
	explain select * from emp where id between 10 and 20;
ref:當(dāng)通過普通的二級(jí)索引與常量進(jìn)行等值匹配時(shí)
	explain select * from emp where name = 'mark';
eq_ref:執(zhí)行連接查詢時(shí)，被驅(qū)動(dòng)的表是通過主鍵或者不允許存儲(chǔ)NULL值的唯一二級(jí)索引列等值匹配時(shí)
const:根據(jù)主鍵或者唯一的二級(jí)索引列與常量行等值匹配時(shí)，就是const
	explain select * from emp where id = 3;
system:表中只有一條記錄，且表引擎使用的存儲(chǔ)引擎的統(tǒng)計(jì)是精確的（例如myisam，memory)

extra

#extra提供了一些額外的信息
using index:使用索引，不需要回表（意思是該二級(jí)索引中字段包括你要查的所有字段）
using where:使用索引，需要回表（意思是用索引定位行，但還必須回表取完整數(shù)據(jù)）
using filesort:排序時(shí)
using temporary:查詢時(shí)可能會(huì)借助臨時(shí)表完成一些功能，例如去重、排序、分組等等

到此這篇關(guān)于MySQL深入之索引、存儲(chǔ)引擎和SQL優(yōu)化的文章就介紹到這了,更多相關(guān)mysql索引、存儲(chǔ)引擎和SQL優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

• MySQL之InnoDB存儲(chǔ)引擎中的索引用法及說明
• mysql中的索引、存儲(chǔ)引擎、事務(wù)、鎖機(jī)制和優(yōu)化詳解
• MySql中的存儲(chǔ)引擎和索引
• MySQL之MyISAM存儲(chǔ)引擎的非聚簇索引詳解
• Mysql?Innodb存儲(chǔ)引擎之索引與算法
• MySql 存儲(chǔ)引擎和索引相關(guān)知識(shí)總結(jié)
• MySQL學(xué)習(xí)（七）：Innodb存儲(chǔ)引擎索引的實(shí)現(xiàn)原理詳解
• 輕松上手MYSQL之SQL優(yōu)化之Explain詳解
• MySQL數(shù)據(jù)庫(kù)的索引原理與慢SQL優(yōu)化的5大原則