mysql带where的join加索引_MySQL索引分析和优化+JOIN的
分类（转）
join :
左右合併
inner join : 只顥⽰符合修件的資料列 (左右互相⽐對)
left join : 顥⽰符合件的右資料列及左邊不符合件的資料列
(此時右邊的資料會以 NULL 顯⽰)
right join :　顥⽰符合件的左資料列及左邊不符合件的資料列
(此時左邊的資料會以 NULL 顯⽰)
full join : 顥⽰符合件的料列及左邊+右邊不符合件的資料列
(此時缺乏資料的資料列會以 NULL 顯⽰)
cross join :
直接將⼀個資料表的每⼀筆資料列和另⼀個料表的每⼀筆資料列搭配成新的資料列
self-joins : ⾃⼰join ⾃⼰
更多 :
union : 合併多個查尋結果 (上下垂直合併)
subquery : ⼦查尋
index :
索引可以加快查尋速度，以平衡樹結構存放索引資料　．
例如：
當我們要編碼N的記錄時，可從根節點開始往下,
假設有M層則只要M次，但是如果不設索引則要N次
唯⼀索引(⾮叢集索引 : UNIQUE INDEX) :
會依單⼀欄位以順序的⽅式做排序放在前⼀個記錄的後⾯
資料表中的任何索引值都不可以相同.有點像PRIMARY KEY .
復合索引(叢集索引 : COMPOSITE INDEX) :
如果是唯⼀索引⼜是復合索引則多個欄位組合起來的值
不可以重復⽽單⼀欄位則可以重復.
⼆、索引的類型
MySQL提供多種索引類型供選擇：
普通索引
mysql需要安装documentation
這是最基本的索引類型，⽽且它沒有唯⼀性之類的限制。普通索引可以通過以下幾種⽅式創建：
創建索引，例如CREATE INDEX ON tablename
(列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字]
(列的列表);
創建表的時候指定索引，例如CREATE TABLE tablename ( [...], INDEX
[索引的名字] (列的列表) );
唯⼀性索引
這種索引和前⾯的「普通索引」基本相同，但有⼀個區別：索引列的所有值都只能出現⼀次，即必須唯⼀。唯⼀性索引可以⽤以下幾種⽅式創建：
創建索引，例如CREATE UNIQUE INDEX ON tablename
(列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字]
(列的列表);
創建表的時候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE
[索引的名字] (列的列表) );
主鍵
主鍵是⼀種唯⼀性索引，但它必須指定為「PRIMARY
KEY」。如果你曾經⽤過AUTO_INCREMENT類型的列，你可能已經熟悉主鍵之類的概念了。主鍵⼀般在創建表的時候指定，例如「CREATE
TABLE tablename ( [...], PRIMARY KEY (列的列表) );
」。但是，我們也可以通過修改表的⽅式加⼊主鍵，例如「ALTER TABLE
tablename ADD PRIMARY KEY (列的列表);
」。每個表只能有⼀個主鍵。
全⽂索引
MySQL從3.23.23版開始⽀持全⽂索引和全⽂檢索。在MySQL中，全⽂索引的索引類型為FULLTEXT。全⽂索引可以在VARCHAR或者
TEXT類型的列上創建。它可以通過CREATE TABLE命令創建，也可以通過ALTER
TABLE或CREATE INDEX命令創建。對於⼤規模的數據集，通過ALTER
TABLE(或者CREATE
INDEX)命令創建全⽂索引要⽐把記錄插⼊帶有全⽂索引的空表更快。本⽂下⾯的討論不再涉及全⽂索引，要瞭解更多信息，請參⾒MySQL
documentation。
三、單列索引與多列索引
索引可以是單列索引，也可以是多列索引。下⾯我們通過具體的例⼦來說明這兩種索引的區別。假設有這樣⼀個people表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL
AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT
NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY
(peopleid) );
這個數據⽚段中有四個名字為「Mikes」的⼈(其中兩個姓Sullivans，兩個姓McConnells)，有兩個年齡為17歲的⼈，還有⼀個名字與眾不同的Joe
Smith。
這個表的主要⽤途是根據指定的⽤⼾姓、名以及年齡返回相應的peopleid。例如，我們可能需要查姓名為Mike
Sullivan、年齡17歲⽤⼾的peopleid(SQL命令為SELECT peopleid FROM
people WHERE firstname='Mike' AND lastname='Sullivan' AND
age=17;)。由於我們不想讓MySQL每次執⾏查詢就去掃瞄整個表，這裡需要考慮運⽤索引。
⾸先，我們可以考慮在單個列上創建索引，⽐如firstname、lastname或者age列。如果我們創建firstnam
e列的索引(ALTER
TABLE people ADD INDEX firstname
(firstname);)，MySQL將通過這個索引迅速把搜索範圍限制到那些firstname='Mike'；的記錄，然後再在這個「中間結果集」上進⾏其他件的搜索：它⾸先排除那些lastname不等於「Sullivan」的記錄，然後排除那些age不等於17
的記錄。當記錄滿⾜所有搜索件之後，MySQL就返回最終的搜索結果。
由於建⽴了firstname列的索引，與執⾏表的完全掃瞄相⽐，MySQL的效率提⾼了很多，但我們要求MySQL掃瞄的記錄數量仍舊遠遠超過了實際所需要的。雖然我們可以刪除firstname列上的索引，再創建lastname或者age列的索引，但總地看來，不論在哪個列上創建索引搜索效率仍舊相似。
為了提⾼搜索效率，我們需要考慮運⽤多列索引。如果為firstname、lastname和age這三個列創建⼀個多列索引，MySQL只需⼀次檢索就能夠出正確的結果！下⾯是創建這個多列索引的SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age
(firstname,lastname,age);
由於索引⽂件以B-樹格式保存，MySQL能夠⽴即轉到合適的firstname，然後再轉到合適的lastname，最後轉到合適的age。在沒有掃瞄數據⽂件任何⼀個記錄的情況下，MySQL就正確地出了搜索的⽬標記錄！
那麼，如果在firstname、lastname、age這三個列上分別創建單列索引，效果是否和創建⼀個firstname、lastname、
age的多列索引⼀樣呢？答案是否定的，兩者完全不同。當我們執⾏查詢的時候，MySQL只能使⽤⼀個索引。如果你有三個單列的索
引，MySQL會試圖選擇⼀個限制最嚴格的索引。但是，即使是限制最嚴格的單列索引，它的限制能⼒也肯定遠遠低於firstname、lastname、age這三個列上的多列索引。
四、最左前綴
多列索引還有另外⼀個優點，它通過稱為最左前綴(Leftmost
Prefixing)的概念體現出來。繼續考慮前⾯的例⼦，現在我們有⼀個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜索件是以下各種列的組合時，MySQL將使⽤fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
從另⼀⽅⾯理解，它相當於我們創建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。下⾯這些查詢都能夠使⽤這個fname_lname_age索引：
SELECT peopleid FROM people WHERE
firstname='Mike' AND lastname='Sullivan' AND age='17'; SELECT
peopleid FROM people WHERE firstname='Mike' AND
lastname='Sullivan'; SELECT peopleid FROM people WHERE
firstname='Mike'; The following queries cannot use the index at
all: SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT
peopleid FROM people WHERE age='17'; SELECT peopleid FROM people
WHERE lastname='Sullivan' AND age='17';
五、選擇索引列
在性能優化過程中，選擇在哪些列上創建索引是最重要的步驟之⼀。可以考慮使⽤索引的主要有兩種類型的列：在WHERE⼦句中出現的列，在join⼦句中出現的列。請看下⾯這個查詢：
SELECT age ## 不使⽤索引
FROM people
WHERE firstname='Mike' ## 考慮使⽤索引
這個查詢與前⾯的查詢略有不同，但仍屬於簡單查詢。由於age是在SELECT部分被引⽤，MySQL不會⽤它來限制列選擇操作。因此，對於這個查詢來說，創建age列的索引沒有什麼必要。下⾯是⼀個更複雜的例⼦：
SELECT people.age, ##不使⽤索引
town.name ##不使⽤索引
FROM people LEFT JOIN town ON
WHERE firstname='Mike' ##考慮使⽤索引
AND lastname='Sullivan' ##考慮使⽤索引
與前⾯的例⼦⼀樣，由於firstname和lastname出現在WHERE⼦句中，因此這兩個列仍舊有創建索引的必要。除此之外，由於town表的townid列出現在join⼦句中，因此我們需要考慮創建該列的索引。
那麼，我們是否可以簡單地認為應該索引WHERE⼦句和join⼦句中出現的每⼀個列呢？差不多如此，但並不完全。我們還必須考慮到對列進⾏⽐較的操作符類型。MySQL只有對以下操作符才使⽤索引：，>=，BETWEEN，IN，以及某些時候的LIKE。可以在
LIKE操作中使⽤索引的情形是指另⼀個操作數不是以通配符(%或者_)開的情形。例如，
「SELECT peopleid FROM people WHERE firstname
LIKE 'Mich%';」這個查詢將使⽤索引，但「SELECT
peopleid FROM people WHERE firstname LIKE
'%ike';」這個查詢不會使⽤索引。
六、分析索引效率
現在我們已經知道了⼀些如何選擇索引列的知識，但還無法判斷哪⼀個最有效。MySQL提供了⼀個內建的SQL命令幫助我們完成這個任務，這就是
EXPLAIN命令。EXPLAIN命令的⼀般語法是：EXPLAIN
。你可以在MySQL⽂檔到有關該命令的更多說明。下⾯是⼀個例⼦：
EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND
lastname='Sullivan' AND age='17';
這個命令將返回下⾯這種分析結果：
table
type
possible_keys
key
key_len
ref
rows
Extra
people
ref
fname_lname_age
fname_lname_age
102
const,const,const
1
Where used
下⾯我們就來看看這個EXPLAIN分析結果的含義。
table：這是表的名字。
type：連接操作的類型。下⾯是MySQL⽂檔關於ref連接類型的說明：
「對於每⼀種與另⼀個表中記錄的組合，MySQL將從當前的表讀取所有帶有匹配索引值的記錄。如果連接操作只使⽤鍵的最左前綴，或者如果鍵不是
UNIQUE或PRIMARY
KEY類型(換句話說，如果連接操作不能根據鍵值選擇出唯⼀⾏)，則MySQL使⽤ref連接類型。如果連接操作所⽤的鍵只匹配少量的記錄，則ref是⼀種好的連接類型。」
在本例中，由於索引不是UNIQUE類型，ref是我們能夠得到的最好連接類型。
如果EXPLAIN顯⽰連接類型是「ALL」，⽽且你並不想從表裡⾯選擇出⼤多數記錄，那麼MySQL的操作效率將⾮常低，因為它要掃瞄整個表。你可以加⼊更多的索引來解決這個問題。預知更多信息，請參⾒MySQL的⼿冊說明。
possible_keys：
可能可以利⽤的索引的名字。這裡的索引名字是創建索引時指定的索引暱稱；如果索引沒有暱稱，則默認顯⽰的是索引中第⼀個列的名字(在本例中，它是「firstname」)。默認索引名字的含義往往不是很明顯。
Key：
它顯⽰了MySQL實際使⽤的索引的名字。如果它為空(或NULL)，則MySQL不使⽤索引。
key_len：
索引中被使⽤部分的⾧度，以字節計。在本例中，key_len是102，其中firstname占50字節，lastname占50字節，age占2字節。如果MySQL只使⽤索引中的firstname部分，則key_len將是50。
ref：
它顯⽰的是列的名字(或單詞「const」)，MySQL將根據這些列來選擇⾏。在本例中，MySQL根據三個常量選擇⾏。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。