索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的。
成都網(wǎng)站建設(shè)公司更懂你！成都創(chuàng)新互聯(lián)只做搜索引擎喜歡的網(wǎng)站！成都網(wǎng)站制作前臺采用搜索引擎認可的DIV+CSS架構(gòu),全站HTML靜態(tài),H5網(wǎng)站設(shè)計+CSS3網(wǎng)站,提供：網(wǎng)站建設(shè)，微信開發(fā)，小程序開發(fā)，電子商務(wù)商城網(wǎng)站建設(shè)，成都App制作，國際域名空間，服務(wù)器租售，網(wǎng)站代托管運營，微信公眾號代托管運營。
1.索引的優(yōu)點
假設(shè)你擁有三個未索引的表t1、t2和t3，每個表都分別包含數(shù)據(jù)列i1、i2和i3，并且每個表都包含了1000條數(shù)據(jù)行，其序號從1到1000。查找某些值匹配的數(shù)據(jù)行組合的查詢可能如下所示：
SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2, t3 WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;
這個查詢的結(jié)果應(yīng)該是1000行，每個數(shù)據(jù)行包含三個相等的值。如果在沒有索引的情況下處理這個查詢，那么如果我們不對這些表進行全部地掃描，我們是沒有辦法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄?？赡艿慕M合的數(shù)量是1000 x 1000 x 1000（10億?。?，它是匹配記錄的數(shù)量的一百萬倍。這就浪費了大量的工作。這個例子顯示，如果沒有使用索引，隨著表的記錄不斷增長，處理這些表的聯(lián)結(jié)所花費的時間增長得更快，導致性能很差。我們可以通過索引這些數(shù)據(jù)表來顯著地提高速度，因為索引讓查詢采用如下所示的方式來處理：
1．選擇表t1中的第一行并查看該數(shù)據(jù)行的值。
2．使用表t2上的索引，直接定位到與t1的值匹配的數(shù)據(jù)行。類似地，使用表t3上的索引，直接定位到與表t2的值匹配的數(shù)據(jù)行。
3．處理表t1的下一行并重復(fù)前面的過程。執(zhí)行這樣的操作直到t1中的所有數(shù)據(jù)行都被檢查過。
在這種情況下，我們?nèi)匀粚Ρ韙1執(zhí)行了完整的掃描，但是我們可以在t2和t3上執(zhí)行索引查找，從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運行上面的查詢會快一百萬倍。當然這個例子是為了得出結(jié)論來人為建立的。然而，它解決的問題卻是現(xiàn)實的，給沒有索引的表添加索引通常會獲得驚人的性能提高。
-
2.索引的代價
首先，索引加快了檢索的速度，但是減慢了插入和刪除的速度，同時還減慢了更新被索引的數(shù)據(jù)列中的值的速度。也就是說，索引減慢了大多數(shù)涉及寫操作的速度。發(fā)生這種現(xiàn)象的原因在于寫入一條記錄的時候不但需要寫入數(shù)據(jù)行，還需要改變所有的索引。數(shù)據(jù)表帶有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的”高效率載入數(shù)據(jù)”部分中，我們將更細致地了解這些現(xiàn)象并找出處理方法。
其次，索引會花費磁盤空間，多個索引相應(yīng)地花費更多的磁盤空間。這可能導致更快地到達數(shù)據(jù)表的大小限制：
· 對于MyISAM表，頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達到最大限制。
· 對于BDB表，它把數(shù)據(jù)和索引值一起存儲在同一個文件中，添加索引引起這種表更快地達到最大文件限制。
· 在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池，因此添加索引會更快地耗盡表空間中的存儲。但是，與MyISAM和BDB表使用的文件不同，InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制，因為我們可以把它配置成使用多個文件。只要有額外的磁盤空間，你就可以通過添加新組件來擴展表空間。
使用單獨表空間的InnoDB表與BDB表受到的約束是一樣的，因為它的數(shù)據(jù)和索引值都存儲在單個文件中。
這些要素的實際含義是：如果你不需要使用特殊的索引幫助查詢執(zhí)行得更快，就不要建立索引。
3.選擇索引
假設(shè)你已經(jīng)知道了建立索引的語法，但是語法不會告訴你數(shù)據(jù)表應(yīng)該如何索引。這要求我們考慮數(shù)據(jù)表的使用方式。這一部分指導你如何識別出用于索引的備選數(shù)據(jù)列，以及如何最好地建立索引：
用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話說，用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。僅僅出現(xiàn)在SELECT關(guān)鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列：
SELECT col_a - 不是備選列 FROM tbl1 LEFT JOIN tbl2 ON tbl1.col_b = tbl2.col_c - 備選列 WHERE col_d = expr; - 備選列
當然，顯示的數(shù)據(jù)列與WHERE子句中使用的數(shù)據(jù)列也可能相同。我們的觀點是輸出列表中的數(shù)據(jù)列本質(zhì)上不是用于索引的很好的備選列。
Join子句或WHERE子句中類似col1 = col2形式的表達式中的數(shù)據(jù)列都是特別好的索引備選列。前面顯示的查詢中的col_b和col_c就是這樣的例子。如果MySQL能夠利用聯(lián)結(jié)列來優(yōu)化查詢，它一定會通過減少整表掃描來大幅度減少潛在的表－行組合。
考慮數(shù)據(jù)列的基數(shù)（cardinality）。基數(shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。例如，某個數(shù)據(jù)列包含值1、3、7、4、7、3，那么它的基數(shù)就是4。索引的基數(shù)相對于數(shù)據(jù)表行數(shù)較高（也就是說，列中包含很多不同的值，重復(fù)的值很少）的時候，它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡，索引會很快地分辨數(shù)據(jù)行。如果某個數(shù)據(jù)列用于記錄性別（只有”M”和”F”兩種值），那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數(shù)據(jù)行。在這些情況下，最好根本不要使用索引，因為查詢優(yōu)化器發(fā)現(xiàn)某個值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是”30%”?，F(xiàn)在查詢優(yōu)化器更加復(fù)雜，把其它一些因素也考慮進去了，因此這個百分比并不是MySQL決定選擇使用掃描還是索引的唯一因素。
索引較短的值。盡可能地使用較小的數(shù)據(jù)類型。例如，如果MEDIUMINT足夠保存你需要存儲的值，就不要使用BIGINT數(shù)據(jù)列。如果你的值不會長于25個字符，就不要使用CHAR(100)。較小的值通過幾個方面改善了索引的處理速度：
· 較短的值可以更快地進行比較，因此索引的查找速度更快了。
· 較小的值導致較小的索引，需要更少的磁盤I/O。
· 使用較短的鍵值的時候，鍵緩存中的索引塊（block）可以保存更多的鍵值。MySQL可以在內(nèi)存中一次保持更多的鍵，在不需要從磁盤讀取額外的索引塊的情況下，提高鍵值定位的可能性。
對于InnoDB和BDB等使用聚簇索引（clustered index）的存儲引擎來說，保持主鍵（primary key）短小的優(yōu)勢更突出。聚簇索引中數(shù)據(jù)行和主鍵值存儲在一起（聚簇在一起）。其它的索引都是次級索引；它們存儲主鍵值和次級索引值。次級索引屈從主鍵值，它們被用于定位數(shù)據(jù)行。這暗示主鍵值都被復(fù)制到每個次級索引中，因此如果主鍵值很長，每個次級索引就需要更多的額外空間。
索引字符串值的前綴（prefixe）。如果你需要索引一個字符串數(shù)據(jù)列，那么最好在任何適當?shù)那闆r下都應(yīng)該指定前綴長度。例如，如果有CHAR(200)數(shù)據(jù)列，如果前面10個或20個字符都不同，就不要索引整個數(shù)據(jù)列。索引前面10個或20個字符會節(jié)省大量的空間，并且可能使你的查詢速度更快。通過索引較短的值，你可以獲得那些與比較速度和磁盤I/O節(jié)省相關(guān)的好處。當然你也需要利用常識。僅僅索引某個數(shù)據(jù)列的第一個字符串可能用處不大，因為如果這樣操作，那么在索引中不會有太多的唯一值。
你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT數(shù)據(jù)列的前綴。
使用最左（leftmost）前綴。建立多列復(fù)合索引的時候，你實際上建立了MySQL可以使用的多個索引。復(fù)合索引可以作為多個索引使用，因為索引中最左邊的列集合都可以用于匹配數(shù)據(jù)行。這種列集合被稱為”最左前綴”（它與索引某個列的前綴不同，那種索引把某個列的前面幾個字符作為索引值）。
假設(shè)你在表的state、city和zip數(shù)據(jù)列上建立了復(fù)合索引。索引中的數(shù)據(jù)行按照state/city/zip次序排列，因此它們也會自動地按照state/city和state次序排列。這意味著，即使你在查詢中只指定了state值，或者指定state和city值，MySQL也可以使用這個索引。因此，這個索引可以被用于搜索如下所示的數(shù)據(jù)列組合：
state, city, zip state, city state
MySQL不能利用這個索引來搜索沒有包含在最左前綴的內(nèi)容。例如，如果你按照city或zip來搜索，就不會使用到這個索引。如果你搜索給定的state和具體的ZIP代碼（索引的1和3列），該索引也是不能用于這種組合值的，盡管MySQL可以利用索引來查找匹配的state從而縮小搜索的范圍。
不要過多地索引。不要認為”索引越多，性能越高”，不要對每個數(shù)據(jù)列都進行索引。我們在前面提到過，每個額外的索引都會花費更多的磁盤空間，并降低寫操作的性能。當你修改表的內(nèi)容的時候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就沒有必要減小表的修改操作的速度。此外，為檢索操作生成執(zhí)行計劃的時候，MySQL會考慮索引。建立額外的索引會給查詢優(yōu)化器增加更多的工作量。如果索引太多，有可能（未必）出現(xiàn)MySQL選擇最優(yōu)索引失敗的情況。維護自己必須的索引可以幫助查詢優(yōu)化器來避免這類錯誤。
如果你考慮給已經(jīng)索引過的表添加索引，那么就要考慮你將增加的索引是否是已有的多列索引的最左前綴。如果是這樣的，不用增加索引，因為已經(jīng)有了（例如，如果你在state、city和zip上建立了索引，那么沒有必要再增加state的索引）。
讓索引類型與你所執(zhí)行的比較的類型相匹配。在你建立索引的時候，大多數(shù)存儲引擎會選擇它們將使用的索引實現(xiàn)。例如，InnoDB通常使用B樹索引。MySQL也使用B樹索引，它只在三維數(shù)據(jù)類型上使用R樹索引。但是，MEMORY存儲引擎支持散列索引和B樹索引，并允許你選擇使用哪種索引。為了選擇索引類型，需要考慮在索引數(shù)據(jù)列上將執(zhí)行的比較操作類型：
· 對于散列（hash）索引，會在每個數(shù)據(jù)列值上應(yīng)用散列函數(shù)。生成的結(jié)果散列值存儲在索引中，并用于執(zhí)行查詢。散列函數(shù)實現(xiàn)的算法類似于為不同的輸入值生成不同的散列值。使用散列值的好處是散列值比原始值的比較效率更高。散列索引用于執(zhí)行=或=操作等精確匹配的時候速度非?？?。但是對于查詢一個值的范圍效果就非常差了：
id 30 weight BETWEEN 100 AND 150
· B樹索引可以用于高效率地執(zhí)行精確的或者基于范圍（使用操作、=、=、=、、、!=和BETWEEN）的比較。B樹索引也可以用于LIKE模式匹配，前提是該模式以文字串而不是通配符開頭。
如果你使用的MEMORY數(shù)據(jù)表只進行精確值查詢，散列索引是很好的選擇。這是MEMORY表使用的默認的索引類型，因此你不需要特意指定。如果你希望在MEMORY表上執(zhí)行基于范圍的比較，應(yīng)該使用B樹索引。為了指定這種索引類型，需要給索引定義添加USING BTREE。例如：
CREATE TABLE lookup ( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING BTREE (id) ) ENGINE = MEMORY;
如果你希望執(zhí)行的語句的類型允許，單個MEMORY表可以同時擁有散列索引和B樹索引，即使在同一個數(shù)據(jù)列上。
有些類型的比較不能使用索引。如果你只是通過把值傳遞到函數(shù)（例如STRCMP()）中來執(zhí)行比較操作，那么對它進行索引就沒有價值。服務(wù)器必須計算出每個數(shù)據(jù)行的函數(shù)值，它會排除數(shù)據(jù)列上索引的使用。
使用慢查詢（slow-query）日志來識別執(zhí)行情況較差的查詢。這個日志可以幫助你找出從索引中受益的查詢。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具來統(tǒng)計它的內(nèi)容。如果某個給定的查詢多次出現(xiàn)在”慢查詢”日志中，這就是一個線索，某個查詢可能沒有優(yōu)化編寫。你可以重新編寫它，使它運行得更快。你要記住，在評估”慢查詢”日志的時候，”慢”是根據(jù)實際時間測定的，在負載較大的服務(wù)器上”慢查詢”日志中出現(xiàn)的查詢會多一些。
*4.建索引的幾大原則*
4.1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a = 1 and b = 2 and c 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。
4.2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
4.3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
4.4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp('2014-05-29');
4.5.盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。
詳細說明可以百度搜我的博客: mysql foreign外鍵詳細使用方法和使用事項_2018_lcf
---------下面是使用方法
使用外鍵的前提：
1. 表儲存引擎必須是innodb，否則創(chuàng)建的外鍵無約束效果。
2. 外鍵的列類型必須與父表的主鍵類型完全一致。
3. 外鍵的名字不能重復(fù)(一般使用。
建外鍵表兩種方式(創(chuàng)建表時關(guān)聯(lián)/添加外鍵)
1) 第一種方式創(chuàng)建表時加外鍵使用實例:
mysql create table A( name char(12), id int(8) ,index(id))engine=innodb; //先建立A表,
mysql create table B( //建立B表,同時做外鍵
- id int(9),
- money int(9),
- index(id),
- foreign key(id) references A (id) //這個是必加項,foreign key(B表要關(guān)聯(lián)的字段),references A表名 (對應(yīng)字段)
- on delete cascade on update cascade //這里是可選項的,只加一項或都不加都可以的,看需求.
- )engine=innodb; //這個必須是innodb類型,并且和A表的一致
第二方式在已有的表上做和A表關(guān)聯(lián)的外鍵(最好是新建好沒有記錄的,不然會因為記錄對不上而創(chuàng)建不成功)
mysql create table C( //這里先創(chuàng)建一個空表C
- id int(7),
- money int(5),
- index(id)
- )engine=innodb;
mysql alter table Cadd constraint abc //在C表上添加和表A關(guān)聯(lián)的外鍵,constraint 外鍵名(自己任意取)
- foreign key(id) references A(id) //和創(chuàng)建時一樣的輸入
- on delete cascade on update cascade; //一樣是可選項,這行不寫也可以通過.
注: @以上的cascade是上面介強的四種模式之一,是可以替換成其它模式的,如寫成on update set null
@還可以同時做兩個外鍵,如寫成foreign key(id,money) references A(id,money) 即可
@兩張表關(guān)聯(lián)字段名可以取不一樣名字,但類型必須一致
create database food_210;
use food_210
create table people
(
people_id int(11) not null primary key 自動增長,
people_name char(20) not null,
people_sex tinyint(1) not null default 1
);
use food_210
create table food
(
food_id int(11) not null primary key 自動增長,
food_name char(20) not null,
food_price char(10) not null
food_time int not null (0:早餐，1:午飯，2:晚飯)
);
use food_210
create table link
(
link_id int(11) primary key 自動增長
people_id int(11) int not null ,
food_id int(11) not null,
NewsTime timestamp DEFAULT now()
);
查詢
select * from link left join people on people.people_id = link.link_id left join food on food.food_id = link.link_id where food.food_time = 0
where 后面可以跟你想要查詢的條件
但是你要注意的是where 表名+字段名 = 值
大概就是這樣的，我沒有測試
一、SQL速成
以下是一些重要的SQL快速參考，有關(guān)SQL的語法和在標準SQL上增加的特性，請查詢MySQL手冊。
1．創(chuàng)建表
表是數(shù)據(jù)庫的最基本元素之一，表與表之間可以相互獨立，也可以相互關(guān)聯(lián)。創(chuàng)建表的基本語法如下：
create table table_name
(column_name datatype {identity |null|not null},
…)
其中參數(shù)table_name和column_name必須滿足用戶數(shù)據(jù)庫中的識別器(identifier)的要求，參數(shù)datatype是一個標準的SQL類型或由用戶數(shù)據(jù)庫提供的類型。用戶要使用non-null從句為各字段輸入數(shù)據(jù)。
create table還有一些其他選項，如創(chuàng)建臨時表和使用select子句從其他的表中讀取某些字段組成新表等。還有，在創(chuàng)建表是可用PRIMARY KEY、KEY、INDEX等標識符設(shè)定某些字段為主鍵或索引等。
書寫上要注意：
在一對圓括號里的列出完整的字段清單。
字段名間用逗號隔開。
字段名間的逗號后要加一個空格。
最后一個字段名后不用逗號。
所有的SQL陳述都以分號";"結(jié)束。
例：
mysql CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
2．創(chuàng)建索引
索引用于對數(shù)據(jù)庫的查詢。一般數(shù)據(jù)庫建有多種索引方案，每種方案都精于某一特定的查詢類。索引可以加速對數(shù)據(jù)庫的查詢過程。創(chuàng)建索引的基本語法如下：
create index index_name
on table_name (col_name[(length)],... )
例：
mysql CREATE INDEX part_of_name ON customer (name(10));
3．改變表結(jié)構(gòu)
在數(shù)據(jù)庫的使用過程中，有時需要改變它的表結(jié)構(gòu)，包括改變字段名，甚至改變不同數(shù)據(jù)庫字段間的關(guān)系?？梢詫崿F(xiàn)上述改變的命令是alter，其基本語法如下：
alter table table_name alter_spec [, alter_spec ...]
例：
mysql ALTER TABLE t1 CHANGE a b INTEGER;
4．刪除數(shù)據(jù)對象
很多數(shù)據(jù)庫是動態(tài)使用的，有時可能需要刪除某個表或索引。大多數(shù)數(shù)據(jù)庫對象可以下面的命令刪除：
drop object_name
mysql DROP TABLE tb1;
5．執(zhí)行查詢
查詢是使用最多的SQL命令。查詢數(shù)據(jù)庫需要憑借結(jié)構(gòu)、索引和字段類型等因素。大多數(shù)數(shù)據(jù)庫含有一個優(yōu)化器(optimizer)，把用戶的查詢語句轉(zhuǎn)換成可選的形式，以提高查詢效率。
值得注意的是MySQL不支持SQL92標準的嵌套的where子句，即它只支持一個where子句。其基本語法如下：
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ]
其中where從句是定義選擇標準的地方，where_definition可以有不同的格式，但都遵循下面的形式：
字段名操作表達式
字段名操作字段名
在第一種形式下，標準把字段的值與表達式進行比較；在第二種形式下，把兩個字段的值進行比較。根據(jù)所比較的數(shù)據(jù)類型，search_condition中的操作可能選以下幾種：
= 檢查是否相等
！= 檢查是否不等
(或=) 檢查左邊值是否大于(或大于等于)右邊值
(或=) 檢查左邊值是否小于(或小于等于)右邊值 [not] between 檢查左邊值是否在某個范圍內(nèi) [not] in 檢查左邊是否某個特定集的成員 [not] like 檢查左邊是否為右邊的子串
is [not] null 檢查左邊是否為空值
在這里，可以用通配符_代表任何一個字符，%代表任何字符串。使用關(guān)鍵字、和可以生成復(fù)雜的詞，它們運行檢查時使用布爾表達式的多重標準集。
例：
mysql select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql select college, region, seed from tournament
ORDER BY region, seed;
mysql select col_name from tbl_name WHERE col_name 0;
重慶分公司
重慶分公司
