轉載請註明，原文地址：

http://www.lgygg.wang/lgyblog/2020/02/12/%e3%80%8amysql%e9%ab%98%e6%95%88%e7%bc%96%e7%a8%8b%e3%80%8b%e8%af%bb%e4%b9%a6%e7%ac%94%e8%ae%b0%e4%b8%80/

《MySQL高效編程》讀書筆記二地址：

http://www.lgygg.wang/lgyblog/2020/02/12/%e3%80%8amysql%e9%ab%98%e6%95%88%e7%bc%96%e7%a8%8b%e3%80%8b%e8%af%bb%e4%b9%a6%e7%ac%94%e8%ae%b0%e4%ba%8c/

簡介

《MySQL高效編程》一書主要是講如何管理操作資料庫的知識，並不是主要講解如何資料庫的書籍。

1.什麼是資料庫？

將數據收集到一起並不能稱為資料庫，只有能利用數據信息功能時才能被成為資料庫庫。

1)什麼是資料庫管理系統？

資料庫只是用來存放數據的倉庫，而對數據進行利用(增刪改查擦操作)的系統，就叫做資料庫管理系統。

2)什麼是資料庫應用程式？

通過資料庫管理系統就可以對資料庫執行所以的操作，例如要對數據進行增刪改查，但是，對於沒有資料庫知識的人來說（普通人根本不懂如何通過SQL語句來進行查詢數據等操作），不懂如何使用資料庫管理系統，所以才會出現資料庫應用程式。例如，如果一個用戶需要查詢關於「二十四節氣」的意思，那麼他可以直接在瀏覽器里打開google，百度等搜尋引擎的主頁，在輸入框輸入「二十四節氣」，點擊搜索，網頁就會查詢出關於「二十四節氣」的相關內容。可以看到整個過程用戶都沒有使用到資料庫專業知識（也就是沒有使用SQL語句來查詢數據）就可以獲取相關信息。

2.資料庫種類

階層型/網絡型資料庫卡片型資料庫關係型資料庫面向對象型資料庫XML型資料庫

3.如何在命令行窗口執行MySQL操作？

這裡只說Window環境下，安裝好MySQL之後，找到MySQL的安裝路徑，我的安裝路徑為C:\Program Files\MySQL ，然後複製裡面的路徑C:\Program Files\MySQL\MySQL Server 8.0\bin，將這個路徑添加到環境變量中，右擊我的電腦，選擇屬性-》高級系統設置-》高級-》環境變量-》系統變量-》編輯path如下，

然後就可以打開命令行窗口，如下圖，輸入命令「mysql -u 用戶名 -p 密碼」，但是具體操作是需要你先輸入「mysql -u 用戶名 -p」，再輸入密碼。如果顯示的內容如下圖，就證明你已經啟動了MySQL的監視器，你可以執行sql語句。

如下是測試執行sql語句顯示資料庫中所以的database

然後要關閉對MySQL的操作，就可以通過exit或者quit來退出

4.創建操作資料庫的帳戶

用戶可以使用命令：create database 資料庫名

來創建資料庫，資料庫創建好之後，可以通過管理者（root）登錄資料庫進行各種操作。但從安全的角度來說，使用管理者（root）權限對所有的資料庫進行操作並不太合適。出於安全著想，需要創建操作資料庫的專用用戶。可以使用語法：

Grant all privileges on 資料庫.* to 用戶名@localhost identified by 密碼

如下例子：

Grant all privileges on home.* to user@localhost identified by 『123456』;

這個例子創建專門操作名為home的資料庫的用戶user,並將密碼設置為「123456」。這樣賦予了從本地連接資料庫的用戶user能對home資料庫中的所用對象進行操作的全部權限（all privileges）。權限是衡量用戶能對資料庫進行什麼樣的操作，具體的有create(創建)，select(查詢)，update(更新),delete(刪除)等權限。[home.*]的意思是可以對home資料庫里所有的表等進行操作，如果[home.customer],就是只能對home資料庫里的customer表進行操作。

5.MySQL主要數據類型

6.修改表的列結構

Alter table命令是用來修改表的列結構的。根據修改類型的種類有，modify,change,add,drop等幾種語法。

修改列的定義：alter table … modify
追加列：alter ttable … add
修改列的名稱和定義：alter table … change
刪除列：alter table … drop

下面舉例子演示。

1)改變列的數據類型

列的數據類型是可以隨時修改的。例如，將定義為varchar類型的列變為可以容納大量字符的text類型。當然必須是可以使用的類型，修改為不能使用的類型的時候是會報錯的。注意，修改的時候，有時候會出現原來的數據變成亂碼的情況，或出現一部分數據丟失的情況。例如，原來能容納100個文字的列，如果將其修改為varchar(50)後，第50個字符之後的內容將消失。因此，如果表中原來有數據時，一般最好在對表結構進行修改前備份一下表。下面就開始演示改變表數據類型。語法：

Alter table 表名 modify 列名 數據類型;

通過命令：

Alter table visitor modify nam varchar(30);

就可以將visitor表中的nam列名的數據類型varchar(20)改為varchar（30）。

2)追加新列

語法:alter table 表名 add 列名 數據類型;

下面例子展示在visitor表里追加新列年齡(old)，

alter table visitor add old int;

還可以指定列的添加位置。

（1）在表的開頭處追加新列

使用first關鍵字。如下,在visitor表的開頭新增old列：

alter table visitor add old int first;

（2）在任意位置追加新列

使用after關鍵字將列追加到任意位置。例如將年齡（old）追加到姓名（nam）列之後：

Alter table visitor add old int after nam;

3)改變列的位置

使用關鍵字modify來修改已經定義列的位置，如下：

如何將visitor表中最好一個列年齡（old）移動到姓名後面，命令如下：

Alter table visitor add old int after nam;

4)改變列名

語法： alter table 表名 change 修改前的列名 修改後的列名 修改後的類型;

對於表中的列生日（birth），現在定義的為datetime類型，即可以保存到時刻[00:00:00]為止的時間，但是一般生日只需要保存年月日即可。所以需要將它改為date類型。通過下面的命令：

Alter table visitor change birth birthday date;

這樣，就將原本的birth列改名為birthday並且將它的數據類型改為date,原本保存的[00:00:00]部分的數據將被刪除。

5)刪除列

語法： alter table 表名 drop 列名;

下面演示刪除列年齡（old）

Alter table visitor drop old;

執行上面的語句，即可刪除old列。

7.複製表

維護資料庫的時候，會碰到需要輸入大量數據到資料庫的情況，這種情況如果人工輸入的話就十分耗時，效率很低，下面介紹3種情況的複製方法。

1)表的列構造+數據的複製

實際上時「從檢索出來的結果中複製構造列和記錄，並創建新表」，是一種連帶數據一起複製表的方法。

語法：create table 新表名 select * from 舊錶名;

下圖是《MySQL高效編程》書中的例子。

如果後面的檢索條件（select）加上對應的檢索條件（where）語句，或者限制記錄的數量（使用limit語句），就只會複製滿足條件的記錄。還需要注意的是，使用這種方法複製可能會發生列屬性被改變的情況。例如，根據MySQL的版本，varchar(20)可能會被改為char(20)。另外，可能會發生不能複製index的有關設定的情況。所以，在複製完成後，一定要使用desc命令來確認表的構造是否正確。如下圖，可以顯示錶student的構造。

2)表的列構造複製

此種方式只為複製表的列結構來創建新表。此種方法不會複製記錄，[auto_incrment],[primary key]等列構造將被複製。

語法：create table 新表名 like 舊錶名;

下圖是《MySQL高效編程》書中的例子。

3)數據的複製

向已經創建好的表中複製數據。

語法：insert inito 表名 select * from 含有數據的表;

下圖是《MySQL高效編程》書中的例子。

8.設置存儲引擎（《MySQL高效編程》第六章）

MySQL有功能可以分為兩部分，外層部分主要完成與客戶端的連接及執行SQL語句的功能。而內層部分就是存儲引擎。它負責接收外層的數據操作指示，完成實際的數據輸入輸出以及文件操作工作。其工作模式如下：

MySQL提供的引擎有：

1)查看當前表使用的引擎

語法：show create table 表名;

下圖是《MySQL高效編程》書中的例子。

2)設置引擎

如上圖，在創建表的時候，可以使用[engine=引擎]來設置想要的引擎。

3)變更引擎

語法：alter table 表名 engine=新引擎;

下圖是《MySQL高效編程》書中的例子。

9.索引的使用

索引的出現是為了提升資料庫的檢索性能。沒有索引的表是如何檢索的？下圖是《MySQL高效編程》書中的例子。下面是展示員工信息表（employee）執行以下select命令的例子。

在默認情況下，表中的記錄是沒有順序的。我們並不知道符合條件的數據保存在表中的什麼位置，這時候，資料庫首先會從第一條記錄開始檢索。例如檢索進行的過程中，找到了一個叫[wang]的人，並不意味著找到了所有叫[wang]的人，因為在資料庫中所[wang]的人可能只有一個，也可能有很多個。因此，要將表中所有記錄都遍歷一遍，也就是所謂的全表掃描（或全件檢索）。全表掃描的效率是很低的。

那麼如何避免全表掃描這樣低效率的搜索呢？使用索引就可以解決。以上面的員工信息表（employee）來說明，如果我們事先為lname_pinyin這個列創建索引，那麼這個索引就會將lname_pingyin（姓氏拼音）的數據與這條記錄的位置信息保存在一個集合里，如下圖，這樣就不需要進行全表掃描，只需要從事先排列好的索引中只抽取符合條件的記錄即可。

1)B數索引

大多數的資料庫中用一種稱為B樹（Balanced Tree,平衡樹）的結構來保存索引。除了B數索引外，還有其他結構來保存索引，如B+樹索引，哈希索引等，注意，這裡保存的是索引，而不是記錄。也就是說以什麼形式來保存索引。B樹結構就是像枝葉擴散開來的樹狀結構，各個節點中保存著複合關鍵字以及指針組成的數組，指針是用來確定數據的位置的，節點就是由這些指針相互關聯起來的。如下圖

B樹一個顯著的特點就是從根節點到各個葉子節點的距離都是相等的。除此之外，我們還要明白什麼叫做聚簇索引和非聚簇索引。聚簇索引是對磁碟上實際數據重新組織以按指定的一個或多個列的值排序的算法。特點是存儲數據的順序和索引順序一致。一般情況下主鍵會默認創建聚簇索引，且一張表只允許存在一個聚簇索引。在《資料庫原理》一書中是這麼解釋聚簇索引和非聚簇索引的區別的：聚簇索引的葉子節點就是數據節點，而非聚簇索引的葉子節點仍然是索引節點，只不過有指向對應數據塊的指針。所以，《MySQL高效編程》書中下圖例子展示的是非聚簇索引的查詢過程，其實也很容易辨別出來依據lname_pinyin列創建出來的不是聚簇索引，因為這個lname_pinyin是可能重複的，而定義中也可以看出，一張表只有一個聚簇索引，且一般是主鍵，很明顯lname_pinyin不可能是主鍵。

例子中，資料庫首先將檢索條件[fang]與根節點的各個值進行比較，發現沒有符合條件的，根據某個條件（假設這裡是根據lname_pinyin列裡邊的開頭字母，按26個英文字母順利來進行節點分支的），找到[fang]在[ai]和[peng]之間，下一步就開始檢索以[ai]開頭的分支節點，將節點的值與[fang]進行比較，但是也沒有找到[fang]，但是縮小了範圍，確定了[fang]在[cao]和[he]開頭的節點之間，隨後去查找以[cao]開頭的節點，在第二為位置找到了[fang]，取出對應指針，就能找到對應的記錄。

2)索引的創建

語法：create [unique] index 索引名 on 表名(列名，…);

3)顯示錶的索引信息

語法：show index from 表名;

下圖來自《MySQL高效編程》一書的例子。

從上圖中顯示的索引信息代表的意思如下圖：

4)叢生索引

在「顯示錶的索引信息」的例子中，明明只創建了一個索引，為什麼會出現兩個索引信息。這是因為在創建員工信息表（employee）的時候，伴隨主鍵的定義而創建的特殊索引，稱之為叢生索引。通常的索引只會在葉子節點保存指向實際表的指針，而叢生索引的葉子節點保存的是實際數據。下圖也是來自《MySQL高效編程》

5)索引的分析

使用explain命令來分析索引的檢索效率。不是說索引創建了就一定會使查詢速度變快，不同的索引會造成不同的查詢速度，因此我們需要分析索引的檢索效率，選擇合適的索引。

語法：explain 調查對象 select 語句;

這是《MySQL高效編程》的例子，首先，將之前給員工信息表（employee）創建的索引刪除，查看檢索lname_pingyin列的時候索引的使用情況。

下圖展示的使explain命令輸出各個參數表示的含義

然後，下圖使對lname_pingyin創建索引後，查看它的檢索情況如下：

將這個結果與沒有對lname_pingyin創建索引之前的檢索結果進行對比，發現對lname_pingyin創建索引後進行檢索，發現rows從原來的9變成了2，也就是說遍歷次數從原來的9遍變成了2遍，這就大大的提高了檢索效率。相反，如果遍歷的次數和創建索引前變化不大，則說明創建索引時選擇的列名不合理，需要重新選擇合適的列作為索引。

6)索引起效果的場合

在某些情況下，即使你創建了索引，這些索引也不一定會起作用。下面舉幾個場景來提醒我們規範的使用索引。

（1）避免進行後方一致/部分一致檢索

當使用like進行模糊檢索的時候，只有在進行前方一致的檢索才能使用上索引。而後方一致/部分一致檢索的場合下是使用不上索引的。例如下面兩個查詢語句，是不會調用索引的

Select * from employee where lname_pinyin like 『%w%』;
Select * from employee where lname_pinyin like 『%w』;

（2）避免使用 is not null和<>比較運算符

下面兩個語句也不會調用索引

Select * from employee where lname_pinyin is not null;
Select * from employee where lname_pinyin <> 『wang』;

（3）避免對列使用運算/函數

對索引列使用了函數或者進行了某些運算的情況，也是不能使用索引的。如下例子是無法使用索引的

Select * from employee where year(birth) =』1980』;

可以想辦法將上面的條件中的函數或一種方式，就能使用索引了，如下：

Select * from employee where birth >=』1980-01-01』 and birth <=』1980-12-31』;

（4）避免複合索引的第一列沒有包含在where條件語句中

假設針對員工信息表（employee）創建的複合索引如下：

Create index idx_pingyin on employee(lname_pinyin,fname_pinyin);

針對這個索引，如果單獨檢索lname_pinyin列，或者同時檢索lname_pinyin和fname_pinyin列時，該索引是會被調用的，如下：

Select * from employee where lname_pinyin =『wang』 and fname_pinyin =『xiao』;
Select * from employee where lname_pinyin =『wang』;

而下面的檢索語句是無法使用該複合索引的

Select * from employee where lname_pinyin =『wang』 or fname_pinyin =『xiao』;
Select * from employee where lname_pinyin =『xiao;