第4章 MySQL 性能調(diào)優(yōu)
4.1 數(shù)據(jù)表設(shè)計(jì)分析
一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)方案對(duì)于數(shù)據(jù)庫(kù)的性能往往會(huì)起到事半功倍的效果。需要考慮數(shù)據(jù)冗余、查詢(xún)和更新的速度、字段的數(shù)據(jù)類(lèi)型是否合理等多方面的內(nèi)容。下面內(nèi)容中對(duì)于表設(shè)計(jì)會(huì)有一些規(guī)范會(huì)被介紹到，但是對(duì)于基于性能的數(shù)據(jù)庫(kù)設(shè)計(jì)，我們并不能完全以規(guī)范化范式理論來(lái)作為唯一的指導(dǎo)。在設(shè)計(jì)過(guò)程中，應(yīng)該從實(shí)際需求出發(fā)，以性能提升為根本目標(biāo)來(lái)展開(kāi)設(shè)計(jì)工作，很多時(shí)候?yàn)榱吮M可能提高性能，我們必須做
反范式設(shè)計(jì)
。 4.1.1 三大范式 在設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)的時(shí)候，一般來(lái)說(shuō)我們都是需要遵從不同的規(guī)范要求來(lái)設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫(kù)，這些不同的規(guī)范要求被稱(chēng)為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫(kù)冗余越小。
范式
可以分為 3 大范式、BC 范式、第四范式和第五范式 ，共六大范式。通常來(lái)說(shuō)滿(mǎn)足三大范式就基本足夠 。但是需要注意的是數(shù)據(jù)庫(kù)的優(yōu)化并非只有結(jié)構(gòu)的設(shè)計(jì)，所以項(xiàng)目的數(shù)據(jù)庫(kù)設(shè)計(jì)并不一定要完全滿(mǎn)足與三大范式，有時(shí)候會(huì)適量的冗余讓 Query 盡量減少 Join。
1. 第一范式
第一范式（1NF）
要求關(guān)系中的屬性必須是原子項(xiàng)，即不可再分的基本類(lèi)型
。集合、數(shù)組以及結(jié)構(gòu)不能作為某一屬性出現(xiàn)，嚴(yán)禁關(guān)系中出現(xiàn) “ 表中有表 ” 的情況。在任何一個(gè)關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中，第一范式是關(guān)系模式的一個(gè)最起碼的要求。不滿(mǎn)足第一范式的數(shù)據(jù)庫(kù)模式不能稱(chēng)為關(guān)系數(shù)據(jù)庫(kù)。 例 - 原始表：
注意：原始表中”工程地址”列還可以細(xì)分為省份，城市等。在國(guó)外，更多的程序把 ” 姓名 ” 列也分成 2 即 ” 姓 ” 和 “ 名 ”。雖然第一范式要求各列要保存原子性，不能再分，但是這種要求和我們的需求是相關(guān)聯(lián)的，上例中 ”工程地址” 就可以不用再分。
2. 第二范式
第二范式（2NF）是在第一范式的基礎(chǔ)建立起來(lái)的，即滿(mǎn)足第二范式就必須要滿(mǎn)足第一范式。第二范式
要求非主鍵字段完全依賴(lài)主鍵
。 所謂完全依賴(lài)是指不能存在僅依賴(lài)主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來(lái)形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對(duì)多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。簡(jiǎn)而言之，第二范式就是在第一范式的基礎(chǔ)上屬性完全依賴(lài)于主鍵。 例 - 原始表中描述了工程信息，員工信息等不符合第二范式的結(jié)構(gòu)，這樣就造成了大量數(shù)據(jù)的重復(fù)。 可以做以下改動(dòng)： （1）工程信息表
（2）員工信息表
3. 第三范式
第三范式（ 3NF ）是第二范式的子集，即滿(mǎn)足第三范式就必須滿(mǎn)足第二范式，其含義為
任何非主屬性不依賴(lài)于其它非主屬性（ 在2NF基礎(chǔ)上消除傳遞依賴(lài) ）
。第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。 在第二范式中，我們對(duì)原始表做了修改，現(xiàn)在可以看看該表是否符合第三范式。從事實(shí)出發(fā)考慮，薪資是根據(jù)人來(lái)定的還是根據(jù)職務(wù)來(lái)定的呢？很明顯是根據(jù)職務(wù)，所以 “ 員工信息表 ” 中 “ 薪資水平 ” 是通過(guò) “職務(wù)” 間接依賴(lài)于主鍵。根據(jù)第三范式要求，我們應(yīng)該移除這種依賴(lài)，可以做如下改動(dòng) ：
通過(guò)對(duì)比我們發(fā)現(xiàn)，表多了、關(guān)系復(fù)雜了、查詢(xún)數(shù)據(jù)變的麻煩了、編程中的難度也提高了，但是各個(gè)表中內(nèi)容更清晰了、重復(fù)的數(shù)據(jù)少了、更新和維護(hù)變的更容易了。 4.1.2 表字段分析 在設(shè)計(jì)數(shù)據(jù)庫(kù)的過(guò)程中，我們要以
“ 夠用原則 ”
選擇合適的類(lèi)型。如果一味的把數(shù)據(jù)字段范圍設(shè)為最大或者默認(rèn)值，會(huì)導(dǎo)致存儲(chǔ)空間大量浪費(fèi)，在數(shù)據(jù)量特別大的情況下，這樣的設(shè)計(jì)將會(huì)造成數(shù)據(jù)庫(kù)空間的嚴(yán)重浪費(fèi)，也會(huì)對(duì)數(shù)據(jù)庫(kù)的執(zhí)行效率造成很大的影響。所以我們?cè)谧鰯?shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候要謹(jǐn)慎選擇，“ 只要最合適，不要最貴 ”。 在選擇不同的類(lèi)型前，需要了解不同類(lèi)型的特點(diǎn)，接下來(lái)以字符串類(lèi)型、數(shù)字類(lèi)型以及時(shí)間類(lèi)型為主說(shuō)明。
1. 字符串類(lèi)型
char 和 varchar 都是用來(lái)存儲(chǔ)字符串類(lèi)型的數(shù)據(jù)，但是他們保存和檢索的方式不一樣。
char 屬于固定長(zhǎng)度的字符類(lèi)型，varchar 屬于可變長(zhǎng)的字符類(lèi)型
。 gbk 字符集：1個(gè)字符，2個(gè)字節(jié)。
utf8 字符集：1個(gè)字符，3個(gè)字節(jié)。
由于
char
是固定長(zhǎng)度的，所以它的處理速度比
varchar
快得多，但是其缺點(diǎn)是浪費(fèi)存儲(chǔ)空間，程序需要對(duì)尾行空格進(jìn)行處理。所以對(duì)那些變化不大并且查詢(xún)速度有較高要求的數(shù)據(jù)，可以考慮使用 char 類(lèi)型來(lái)存儲(chǔ)。盡量少用 text 等大的類(lèi)型，如果感覺(jué)非用不可的話(huà)，則可以考慮分表了。
不同存儲(chǔ)引擎的使用原則
在 MySQL 中，不同的存儲(chǔ)引擎對(duì) char 和 varchar 的使用原則有所不同，如下 ：
MyISAM
存儲(chǔ)引擎： 建議使用固定長(zhǎng)度的數(shù)列代替可變長(zhǎng)度的數(shù)據(jù)列。
InnoDB
存儲(chǔ)引擎： 建議使用 varchar 類(lèi)型，對(duì)于 InnnoDB 數(shù)據(jù)表，內(nèi)部的行存儲(chǔ)格式?jīng)]有區(qū)分固定長(zhǎng)度和可變長(zhǎng)度，因此使用 char 列不一定比可變長(zhǎng)度的 varchar 性能好。
由于 char 平均占用空間多于 varchar ，因此 varchar 來(lái)處理的數(shù)據(jù)行的存儲(chǔ)總量和磁盤(pán) I/O 是比較好的。
另外在
添加字段時(shí)盡量不要使用 null
，如果實(shí)在有字段沒(méi)有值，可以添加默認(rèn)值，例如 0。
2. 數(shù)值類(lèi)型
一般情況下 id 必為主鍵，類(lèi)型為 int / bigint unsigned，單表時(shí)自增步長(zhǎng)為 1。需要注意的是一些表因?yàn)閿?shù)據(jù)量的關(guān)系導(dǎo)致主鍵會(huì)很大，可能會(huì)超出 int 的范圍這個(gè)時(shí)候就比較建議使用 bigint 通常 int 即可。 注意：當(dāng)一個(gè)表數(shù)據(jù)量超過(guò)了 500萬(wàn) 或者單表容量超過(guò) 2GB 的時(shí)候，推薦分庫(kù)分表。這一步操作是需要事先對(duì)于數(shù)據(jù)量在項(xiàng)目上線(xiàn)之后的思考點(diǎn)。
unsigned 屬性標(biāo)識(shí)將數(shù)字類(lèi)型無(wú)符號(hào)化，如果確定沒(méi)有負(fù)數(shù)，可以使用unsigned 類(lèi)型，讓可存儲(chǔ)的上限能擴(kuò)大一倍，間接的使用更小的數(shù)據(jù)類(lèi)型。另外 status、sexuality 相對(duì)來(lái)說(shuō)在系統(tǒng)中我們更愿意使用數(shù)字作為表示，代替實(shí)際該字段所傳單的意思，因?yàn)?char 所占用 的 3 個(gè)字節(jié)，相對(duì)來(lái)說(shuō)比較適合于unsigned tinyint。 浮點(diǎn)數(shù) float 在儲(chǔ)存空間及運(yùn)行效率上要優(yōu)于精度數(shù)值類(lèi)型 decimal，但 float 與 double 會(huì)有舍入錯(cuò)誤而 decimal 則可以提供更加準(zhǔn)確的小數(shù)級(jí)精確運(yùn)算不會(huì)有錯(cuò)誤產(chǎn)生計(jì)算更精確，適用于金融類(lèi)型數(shù)據(jù)的存儲(chǔ)。
實(shí)數(shù)精度要求不高情況，優(yōu)先使用浮點(diǎn)類(lèi)型 float 或 double。如果精度要求高，可考慮用整型轉(zhuǎn)化或直接使用decimal類(lèi)型。
3. 時(shí)間類(lèi)型
默認(rèn)情況下，當(dāng) MySQL 遇到超出范圍的日期或時(shí)間類(lèi)型的值或該類(lèi)型的其他無(wú)效值時(shí)，它會(huì)將該值轉(zhuǎn)換為 "零"。唯一的例外是超出了范圍，TIME 值被裁剪到 TIME 范圍。
（1）DATETIME DATETIME 用于表示 年月日 時(shí)分秒，是 DATE 和 TIME 的組合，并且記錄的年份比較長(zhǎng)久。如果實(shí)際應(yīng)用中有這樣的需求，就可以使用 DATETIME 類(lèi)型。 DATETIME 列可以設(shè)置為多個(gè)，默認(rèn)可為 null，可以手動(dòng)設(shè)置其值。
DATETIME 列不可設(shè)定默認(rèn)值。
DATETIME 列可以變相的設(shè)定默認(rèn)值，比如通過(guò)觸發(fā)器、或者在插入數(shù)據(jù)時(shí)候，將 DATETIME 字段值設(shè)置為 now( )，這樣可以做到了，尤其是后者，在程序開(kāi)發(fā)中常常用到。
（2）TIMESTAMP TIMESTAMP 用于表示年月日時(shí)分秒，但是記錄的年份比較短暫。TIMESTAMP 列必須有默認(rèn)值，默認(rèn)值可以為 “ 0000-00-00 00:00:00 ”，但不能為 null。 TIMESTAMP 和時(shí)區(qū)相關(guān)，更能反映當(dāng)前時(shí)間。當(dāng)插入日期時(shí)，會(huì)先轉(zhuǎn)換為本地時(shí)區(qū)后再存放。當(dāng)查詢(xún)?nèi)掌跁r(shí)，會(huì)將日期轉(zhuǎn)換為本地時(shí)區(qū)后再顯示，所以不同時(shí)區(qū)的人看到的同一時(shí)間是不一樣的。 表中的第一個(gè) TIMESTAMP 列自動(dòng)設(shè)置為系統(tǒng)時(shí)間（CURRENT_TIMESTAMP）。當(dāng)插入或更新一行，但沒(méi)有明確給 TIMESTAMP 列賦值，也會(huì)自動(dòng)設(shè)置為當(dāng)前系統(tǒng)時(shí)間。如果表中有第二個(gè) TIMESTAMP 列，則默認(rèn)值設(shè)置為 0000-00-00 00:00:00。TIMESTAMP 的屬性受 Mysql 版本和服務(wù)器 SQLMode 的影響較大。如果記錄的日期需要讓不同時(shí)區(qū)的人使用，最好使用 TIMESTAMP。 注：一般建表時(shí)候，創(chuàng)建時(shí)間用 DATETIME，更新時(shí)間用 TIMESTAMP。
（3）DATE DATE 用于表示 年月日，如果實(shí)際應(yīng)用值需要保存 年月日 就可以使用 DATE。 （4）TIME TIME 用于表示 時(shí)分秒，如果實(shí)際應(yīng)用值需要保存 時(shí)分秒 就可以使用 TIME。 （5）YEAR YEAR 用于表示 年份，YEAR 有 2 位（最好使用 4 位）和 4 位格式的年（默認(rèn)是 4 位）。如果實(shí)際應(yīng)用只保存年份，那么 用 1 bytes 保存 YEAR 類(lèi)型完全可以。不但能夠節(jié)約存儲(chǔ)空間，還能提高表的操作效率。 如果對(duì)于時(shí)間方面的運(yùn)用要求不高，在考慮性能優(yōu)先的情況下可以考慮使用 int 類(lèi)型來(lái)存儲(chǔ)時(shí)間。
4.1.3 不推薦的數(shù)據(jù)類(lèi)型
1. 二進(jìn)制多媒體數(shù)據(jù)
二進(jìn)制多媒體數(shù)據(jù)將二進(jìn)制多媒體數(shù)據(jù)存放在數(shù)據(jù)庫(kù)中，一個(gè)問(wèn)題是數(shù)據(jù)庫(kù)空間資源耗用非常嚴(yán)重，另一個(gè)問(wèn)題是這些數(shù)據(jù)的存儲(chǔ)很消耗數(shù)據(jù)庫(kù)主機(jī)的 CPU 資源。這種數(shù)據(jù)主要包括圖片，音頻、視頻和其他一些相關(guān)的二進(jìn)制文件。這些數(shù)據(jù)的處理本不是數(shù)據(jù)的優(yōu)勢(shì)，如果我們硬要將他們?nèi)霐?shù)據(jù)庫(kù)，肯定會(huì)造成數(shù)據(jù)庫(kù)的處理資源消耗嚴(yán)重。
2. 流水隊(duì)列數(shù)據(jù)
流水隊(duì)列數(shù)據(jù)我們都知道，數(shù)據(jù)庫(kù)為了保證事務(wù)的安全性（支持事務(wù)的存儲(chǔ)引擎）以及可恢復(fù)性，都是需要記錄所有變更的日志信息的。而流水隊(duì)列數(shù)據(jù)的用途就決定了存放這種數(shù)據(jù)的表中的數(shù)據(jù)會(huì)不斷的被 INSERT、UPDATE 和 DELETE，而每一個(gè)操作都會(huì)生成與之對(duì)應(yīng)的日志信息。 在 MySQL 中，如果是支持事務(wù)的存儲(chǔ)引擎，這個(gè)日志的產(chǎn)生量更是要翻倍。而如果我們通過(guò)一些成熟的第三方隊(duì)列軟件來(lái)實(shí)現(xiàn)這個(gè) Queue 數(shù)據(jù)的處理功能，性能將會(huì)成倍的提升。
3. 超大文本數(shù)據(jù)
超大文本數(shù)據(jù)對(duì)于 5.0.3 之前的 MySQL 版本，VARCHAR 類(lèi)型的數(shù)據(jù)最長(zhǎng)只能存放 255 個(gè)字節(jié)，如果需要存儲(chǔ)更長(zhǎng)的文本數(shù)據(jù)到一個(gè)字段，我們就必須使用 TEXT 類(lèi)型（最大可存放64KB）的字段，甚至是更大的 LONGTEXT 類(lèi)型（最大 4GB ）。 TEXT 類(lèi)型數(shù)據(jù)的處理性能要遠(yuǎn)比 VARCHAR 類(lèi)型數(shù)據(jù)的處理性能低下很多。從 5.0.3 版本開(kāi)始，VARCHAR 類(lèi)型的最大長(zhǎng)度被調(diào)整到 64KB 了，但是當(dāng)實(shí)際數(shù)據(jù)小于 255Bytes 的時(shí)候，實(shí)際存儲(chǔ)空間和實(shí)際的數(shù)據(jù)長(zhǎng)度一樣，可一旦長(zhǎng)度超過(guò) 255Bytes 之后，所占用的存儲(chǔ)空間就是實(shí)際數(shù)據(jù)長(zhǎng)度的兩倍。對(duì)于圖片的存儲(chǔ)，如果說(shuō)是特殊情況可以使用 BLOB，但是通常來(lái)說(shuō)跟推薦使用 varchar 存圖片路徑，而圖片會(huì)放在一個(gè)文件夾中。 4.2 慢查詢(xún)?nèi)罩痉治?MySQL 的慢查詢(xún)?nèi)罩臼?MySQL 提供的一種日志記錄，它用來(lái)記錄在 MySQL 中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò) long_query_time 值的 SQL，則會(huì)被記錄到慢查詢(xún)?nèi)罩局小ong_query_time 的默認(rèn)值為 10，意思是運(yùn)行 10S 以上的語(yǔ)句。 默認(rèn)情況下， Mysql 數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢(xún)?nèi)罩?，需要我們手?dòng)來(lái)設(shè)置這個(gè)參數(shù)。當(dāng)然，如果不是調(diào)優(yōu)需要的話(huà)，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_(kāi)啟慢查詢(xún)?nèi)罩緯?huì)或多或少帶來(lái)一定的性能影響。慢查詢(xún)?nèi)罩局С謱⑷罩居涗泴?xiě)入文件，也支持將日志記錄寫(xiě)入數(shù)據(jù)庫(kù)表。 4.2.1 慢查詢(xún)相關(guān)參數(shù)
1. slow_query_log - 是否開(kāi)啟慢查詢(xún)?nèi)罩?/p>
可以通過(guò) show variables like "%slow_query_log%" 命令查看相關(guān)信息 ： mysql> show variables like "%slow_query_log%"; +---------------------+---------------------------------+ | Variable_name???| Value????????????? | +---------------------+---------------------------------+ | slow_query_log??| OFF?????????????? | | slow_query_log_file | /www/server/data/mysql-slow.log | +---------------------+---------------------------------+ slow_query_log OFF 代表慢查詢(xún)?nèi)罩臼墙玫摹?
2. 慢查詢(xún)?nèi)罩敬鎯?chǔ)路徑
從上面的運(yùn)行結(jié)果可以看到 slow_query_log_file （5.6及以上版本）的值，該值即為 MySQL 數(shù)據(jù)庫(kù)慢查詢(xún)?nèi)罩敬鎯?chǔ)路徑?？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件 host_name_slow.log。如果 MySQL 版本在 5.6 以下版本，則是 log_slow_queries。
3. long_query_time - 慢查詢(xún)閾值
long_query_time 是慢查詢(xún)的超長(zhǎng)時(shí)間，當(dāng)超過(guò)這時(shí)間時(shí)記錄日志?？梢酝ㄟ^(guò) show variables like "long_query_time" 命令查看該信息，默認(rèn)為 10。 mysql> show variables like "long_query_time"; +-----------------+-----------+ | Variable_name?| Value??| +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
4. log_queries_not_using_indexes - 未使用索引記錄
log_queries_not_using_indexes 參數(shù)主要是用來(lái)設(shè)置未使用索引的查詢(xún)也是否記錄到慢查詢(xún)?nèi)罩局?。使?log_queries_not_using_indexes 命令查看，默認(rèn)關(guān)閉 ： mysql> show variables like "log_queries_not_using_indexes"; +-------------------------------+-------+ | Variable_name????????| Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF?| +-------------------------------+-------+
5. log_output - 日志存儲(chǔ)方式
log_output 表示日志的存儲(chǔ)方式，log_output='FILE' 表示將日志存入文件，log_output='TABLE' 表示將日志存入數(shù)據(jù)庫(kù)，這樣日志信息就會(huì)被寫(xiě)入到 mysql.slow_log 表中，默認(rèn)值是 'FILE'。 MySQL 數(shù)據(jù)庫(kù)支持同時(shí)兩種日志存儲(chǔ)方式，配置的時(shí)候以逗號(hào)隔開(kāi)即可，如：log_output='FILE,TABLE'。日志記錄到系統(tǒng)的專(zhuān)用日志表中，要比記錄到文件耗費(fèi)更多的系統(tǒng)資源，因此對(duì)于需要啟用慢查詢(xún)?nèi)罩?，又需要能夠獲得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。 4.2.2 慢查詢(xún)?nèi)罩镜膯?dòng)與設(shè)置 慢查詢(xún)?nèi)罩灸J(rèn)是關(guān)閉的，可以使用 set global slow_query_log = 1 命令來(lái)開(kāi)啟 ： mysql> set global slow_query_log=1; # 啟用慢查詢(xún)，需要加上 global，不然會(huì)報(bào)錯(cuò) 需要注意的是，這種方式只對(duì)當(dāng)前數(shù)據(jù)庫(kù)生效，如果 MySQL 重啟后則會(huì)失效。如果要永久生效，就必須修改配置文件 my.cnf[linux] / my.ini[windows]（其它系統(tǒng)變量也是如此）： slow_query_log = on # 開(kāi)啟日志; slow_query_log_file = /www/server/data/mysql_slow.log # 記錄日志的log文件 long_query_time = 1 # 最長(zhǎng)查詢(xún)的秒數(shù); 注意 ： 以上參數(shù)需要加入到配置文件的 [mysqld] 組中。
慢查詢(xún)?nèi)罩镜拇鎯?chǔ)路徑如果不指定，將默認(rèn)存儲(chǔ)到 MySQL 數(shù)據(jù)庫(kù)的數(shù)據(jù)文件夾下。
慢查詢(xún)?nèi)罩疚募Q(chēng)如果不指定，默認(rèn)文件名為 hostname-slow.log，hostname 是 MySQL 服務(wù)器的主機(jī)名。
long_query_time 默認(rèn)值為 10，單位為 秒，日志只會(huì)記錄大于的部分（等于也不記錄）。
如果慢查詢(xún)?nèi)罩咎崾?‘Permission denied’，可以切換目錄存放日志文件。
4.2.3 慢查詢(xún)使用 修改了慢查詢(xún)?nèi)罩镜拈y值為 1 后，可以使用 sleep 函數(shù)來(lái)測(cè)試慢查詢(xún)?nèi)罩?： mysql> select sleep(5); +----------+ | sleep(5) | +----------+ |????0 | +----------+ 1 row in set (5.01 sec) ? mysql> show status like '%slow%'; +---------------------+-------+ | Variable_name???| Value | +---------------------+-------+ | Slow_launch_threads | 0??| | Slow_queries???| 1??| +---------------------+-------+ 2 rows in set (0.00 sec) sleep 函數(shù)會(huì)在休眠 n 秒后執(zhí)行，通過(guò) show status like '%slow%' 命令可以查看到 Slow_queries 的值為 1，表示一共有一條慢查詢(xún)，詳細(xì)的可以查看對(duì)應(yīng)的慢查詢(xún)?nèi)罩尽?
慢查詢(xún)分析工具
當(dāng)數(shù)據(jù)比較多，慢查詢(xún)?nèi)罩居涗浀臄?shù)據(jù)也比較多，這個(gè)時(shí)候人工的分析明顯是不實(shí)際的。這個(gè)時(shí)候需要借助慢查詢(xún)分析工具 - mysqldumpslow。該工具是慢查詢(xún)自帶的分析慢查詢(xún)工具，一般只要安裝了 mysql，就會(huì)有該工具。 通過(guò) --help 可以查看工具幫助 ： [root@localhost ~]# cd /usr/local/mysql/bin [root@localhost bin]# ./mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] ...... ? 常用選項(xiàng)： -s, 是表示按照何種方式排序: ???c: 訪(fǎng)問(wèn)計(jì)數(shù) ???l: 鎖定時(shí)間 ???r: 返回記錄 ???t: 查詢(xún)時(shí)間 ???al:平均鎖定時(shí)間 ???ar:平均返回記錄數(shù) ???at:平均查詢(xún)時(shí)間 ??-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)； ? ??-g, 后邊可以寫(xiě)一個(gè)正則匹配模式，大小寫(xiě)不敏感的； ? ?例： ? mysqldumpslow -s r -t 10 /www/server/data/mysql-slow.log # 得到返回記錄集最多的10個(gè)SQL。 ???mysqldumpslow -s c -t 10 /www/server/data/mysql-slow.log #得到訪(fǎng)問(wèn)次數(shù)最多的10個(gè)SQL ???mysqldumpslow -s t -t 10 -g “l(fā)eft join” /www/server/data/mysql-slow.log #得到按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句。 ???mysqldumpslow -s r -t 20 /www/server/data/mysql-slow.log | more # 另外建議在使用這些命令時(shí)結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)刷屏的情況。 4.3 索引解析 在介紹數(shù)據(jù)庫(kù)優(yōu)化的資料中，基本都會(huì)提到索引，甚至很多資料第一個(gè)講的就是索引。索引對(duì)于良好的性能非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來(lái)越大的時(shí)候，索引對(duì)性能的影響愈發(fā)重要。 4.3.1 索引簡(jiǎn)介 索引（Index）是
存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)，也就是說(shuō)索引的本質(zhì)是數(shù)據(jù)結(jié)構(gòu)
。在 MySQL 中的索引主要分為 B-Tree 索引、哈希索引、數(shù)據(jù)空間索引(R-Tree)、全文索引等等。我們重點(diǎn)關(guān)注的索引是 B-Tree 索引，如果沒(méi)有特殊說(shuō)明，一般的索引指的都是 B-Tree 索引。 索引種類(lèi)眾多的原因是因?yàn)樗饕窃诖鎯?chǔ)引擎層實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的，因此沒(méi)有統(tǒng)一的索引標(biāo)準(zhǔn)。不同存儲(chǔ)引擎的索引的工作方式并不一樣，也不是所有的存儲(chǔ)引擎都支持所有類(lèi)型的索引。
索引優(yōu)勢(shì)
MySQL 索引的建立對(duì)于 MySQL 的運(yùn)行效率是很重要的，索引可以大大提高 MySQL 的檢索速度。如果說(shuō)沒(méi)有索引的 MySQL 是你的滑板，那么加了索引的 MySQL 就是'柯南'的滑板了。為什么會(huì)這么快呢？你可以想象你看一本有目錄并且有書(shū)簽的書(shū)和一本除只有正文的書(shū)。
索引缺點(diǎn)
使用索引也并不是一定就都是好的，如果索引使用不當(dāng)，反而會(huì)適得其反。創(chuàng)建和維護(hù)索引組要耗費(fèi)時(shí)間，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加。所以在使用索引時(shí)，需要綜合考慮。 4.3.2 創(chuàng)建索引
1. 索引種類(lèi)與創(chuàng)建
索引主要可以分為主鍵索引、唯一索引以及普通索引這幾種，其創(chuàng)建方式分別為 ： 主鍵索引：即 primary key。它是一種特殊的唯一索引，不允許有空值。可以使用 primary key ( 字段 1,字段 2....) 或者是在字段創(chuàng)建完畢之后用 primary key 來(lái)修飾創(chuàng)建。
唯一索引：即用 unique 修飾的列。其特點(diǎn)為索引列的值必須唯一，但允許有空值。在創(chuàng)建完字段之后使用 unique 修飾即可。
普通索引：即 index 或者說(shuō)是 key，通過(guò)這種方式也可以創(chuàng)建兩種 ：
1）單列索引：即索引的列只有一個(gè)，但是一個(gè)表里面可以有多個(gè)單列索引。
2）組合索引：即索引的列有多個(gè)，注意多個(gè)列組成的索引和多個(gè)索引列是不一樣的。
如果對(duì)多列進(jìn)行索引 (組合索引)，列的順序非常重要，MySQL 僅能對(duì)索引最左邊的前綴進(jìn)行有效的查找。例如 ： 創(chuàng)建索引 `idx_c12(c1,c2)`， select * from t1 where c1=1 and c2=2; #能夠使用該索引。 select * from t1 where c1=1; # 也能夠使用該索引。 ? # 注意： select * from t1 where c2=2; # 不能夠使用該索引，因?yàn)闆](méi)有組合索引的引導(dǎo)列，即，要想使用c2列進(jìn)行查找，必需出現(xiàn)c1等于某值。
2. 創(chuàng)建表時(shí)創(chuàng)建索引
mysql> create table t1 ??-> ( ??->?id int primary key, # 創(chuàng)建主鍵 ??->?uid int unique, # 創(chuàng)建唯一鍵 ??->?name varchar(20), ??->?index(name) # 創(chuàng)建普通索引 ??-> ); 創(chuàng)建完畢后，通過(guò) ‘ show create table 表名 ’ 查看表的創(chuàng)建詳情 ： mysql> show create table t1 \G *************************** 1. row *************************** ???Table: t1 Create Table: CREATE TABLE `t1` ( ?`id` int(11) NOT NULL, ?`uid` int(11) DEFAULT NULL, ?`name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uid` (`uid`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 注意：唯一索引和普通索引都有自己的名字，主鍵索引因?yàn)橐粋€(gè)表只允許有一個(gè)主鍵，不需要彼此區(qū)分就不需要了。
3. 表后創(chuàng)建索引
語(yǔ)法： 普通索引：create index 索引名 [索引類(lèi)型] on 表名(列名列表) 唯一索引：create unique index 索引名 [索引類(lèi)型] on 表名(列名列表) # 注意，這種方式無(wú)法創(chuàng)建主鍵索引。 ? # 創(chuàng)建表，但不創(chuàng)建索引 create table t2 ( ?id int, ?uid int, ?mid int, ?tid int ); ? # 創(chuàng)建索引 create unique index idx_uid on t2(uid); # 唯一索引 create index idx_mt on t2(mid,tid); # 創(chuàng)建普通索引，并且是多字段 # 通過(guò) show create table t2 可以查看表的信息。 刪除索引使用 DROP INDEX < 索引名 > ON < 表名 > 即可。
4. 查看索引
通過(guò) “ show create table ” 方式并不能很好的查看索引的相關(guān)信息，比較好的方式是使用 “ show index from ” 表名 查看索引相關(guān)信息。以 t1 表為例 ： mysql> show index from t2 \G; *************************** 1. row *************************** ???Table: t2 # 索引所在表名 ?Non_unique: 0 # 非唯一的索引 ??Key_name: idx_uid # 索引名稱(chēng)，如果是主鍵索引 則是 primary Seq_in_index: 1 # 索引中該列的位置 Column_name: uid # 索引列的名字 ?Collation: A # 列以什么方式存儲(chǔ)在索引中。B+樹(shù)索引是A，使用HEAP引擎，且使用HASH索引，這里會(huì)顯示NULL。 Cardinality: 0 # 索引基數(shù)，因?yàn)闆](méi)有插入數(shù)據(jù) 所以是0 ??Sub_part: NULL # 是不是列的部分索引 ???Packed: NULL # 關(guān)鍵字如何被壓縮 ????Null: YES # 索引列是否含有NULL ?Index_type: BTREE # 索引的類(lèi)型為 BTREE ??Comment: # 注釋 Index_comment: # 索引注釋 *************************** 2. row *************************** ...... 這里重點(diǎn)說(shuō)明 Cardinality，它表示這個(gè)索引有多少個(gè)。一般情況下，這個(gè)數(shù)距離表的行數(shù)越近，我們的索引就越有效，這里由于我們沒(méi)有向表中插入過(guò)數(shù)據(jù)，因此這里為 0。插入幾條數(shù)據(jù)測(cè)試 ： mysql> select * from t2; +------+------+------+------+ | id?| uid | mid | tid | +------+------+------+------+ |??1 |??1 |??1 |??1 | |??2 |??2 |??2 |??2 | |??3 |??3 |??3 |??3 | |??4 |??4 |??4 |??4 | |??5 |??5 |??5 |??5 | +------+------+------+------+ ? # 對(duì)該表再次查看索引信息 mysql> show index from t2 \G; *************************** 1. row *************************** ???Table: t2 ?Non_unique: 0 ??Key_name: idx_uid Seq_in_index: 1 Column_name: uid ?Collation: A Cardinality: 5 ??Sub_part: NULL ???Packed: NULL ????Null: YES ?Index_type: BTREE ??Comment: Index_comment: *************************** 2. row *************************** ...... 此時(shí) Cardinality 的值變成了 5。如果該索引的基數(shù)非常小，我們需要考慮是否去掉或者修改該索引了。 注意：MySQL 存儲(chǔ)的是一個(gè)統(tǒng)計(jì)信息，但是這個(gè)統(tǒng)計(jì)信息并不一定是實(shí)時(shí)的。如果插入了 5 條數(shù)據(jù)，但是結(jié)果少于 5 。此時(shí)我們需要使用 ‘ analyze table 表名稱(chēng) ’ 提醒一下 MySQL 需要重新統(tǒng)計(jì)信息了。
4.2.3 索引設(shè)計(jì)原則 前面有提到索引的缺點(diǎn)，那么什么樣的索引不應(yīng)該使用或者說(shuō)應(yīng)該刪除呢 ？這個(gè)需要取決于 MySQL 對(duì)查詢(xún)的了解，MySQL 會(huì)自動(dòng)優(yōu)化查詢(xún)，接下來(lái)看看如何判定。
1. 應(yīng)該建立的索引
（1）
唯一性索引的值是唯一
的，可以更快速的通過(guò)該索引來(lái)確定某條記錄。 （2）在作為主鍵的列上創(chuàng)建索引，強(qiáng)制該列的唯一性，并組織表中數(shù)據(jù)的排列結(jié)構(gòu)。 （3）經(jīng)常使用的列 ：
在經(jīng)常需要搜索的列上建立索引
，可以加快搜索的速度。
在經(jīng)常使用表連接的列上創(chuàng)建索引
，這些列主要是一些外鍵，可以加快表連接的速度。
在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引
，因?yàn)樗饕呀?jīng)排序，所以其指定的范圍是連續(xù)的。
在經(jīng)常需要排序的列上創(chuàng)建索引
，因?yàn)樗饕呀?jīng)排序，所以查詢(xún)時(shí)可以利用索引的排序，加快排序查詢(xún)。
在經(jīng)常使用 WHERE 子句的列上創(chuàng)建索引
，加快條件的判斷速度。
（5）
盡量使用前綴來(lái)索引
，例如，TEXT 和 BLOG 類(lèi)型的字段，進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間。如果只檢索字段的前面的若干個(gè)字符，這樣可以提高檢索速度。 （6）
盡量使用數(shù)據(jù)量少的索引
，如果索引的值很長(zhǎng)，那么查詢(xún)的速度會(huì)受到影響。例如，CHAR(100) 類(lèi)型比 CHAR(10) 類(lèi)型的字段需要的時(shí)間要多。
2. 應(yīng)該避免的索引
（1）
在查詢(xún)中很少使用或參考的列不應(yīng)該創(chuàng)建索引
。因?yàn)檫@些列很少使用到，所以有無(wú)索引并不能提高查詢(xún)速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度，并增大了空間要求。 （2）
只有很少數(shù)據(jù)值的列不應(yīng)該創(chuàng)建索引
。因?yàn)檫@些列的取值很少，例如人事表的性別列。查詢(xún)結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，增加索引并不能明顯加快檢索速度。 （3）
定義為 TEXT、IMAGE 和 BIT 數(shù)據(jù)類(lèi)型的列不應(yīng)該創(chuàng)建索引
。因?yàn)檫@些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。 （4）
當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引
。因?yàn)楫?dāng)創(chuàng)建索引時(shí)，會(huì)提高檢索性能，降低修改性能，反之一樣。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。 （5）
應(yīng)該限制索引的數(shù)目
，每個(gè)索引都需要占用磁盤(pán)空間，索引越多，需要的磁盤(pán)空間就越大。在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)還可能需要重構(gòu)。因此，索引越多，更新表的時(shí)間就越長(zhǎng)。 （6）
數(shù)據(jù)量小的表最好不要使用索引
，由于數(shù)據(jù)較小，查詢(xún)花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果。 （7）
刪除不再使用或者很少使用的索引
，表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。應(yīng)該定期找出這些索引，將它們刪除，從而減少索引對(duì)更新操作的影響。 使用索引的最終目的是為了使查詢(xún)的速度變快，上面給出的原則是最基本的準(zhǔn)則，但不能只拘泥于上面的準(zhǔn)則。應(yīng)該在學(xué)習(xí)和工作中不斷的實(shí)踐，根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷，選擇最合適的索引方式。 4.3.4 其他索引類(lèi)型 在上面我們主要看到的都是 BTREE 索引，一般的索引也是這個(gè)。除了 BTREE 索引外，我們?cè)俸?jiǎn)單了解一下其他的索引類(lèi)型。
1. hash 索引
hash 索引在 MySQL 中使用并不是很多，所謂 hash 索引，實(shí)際上就是通過(guò)一定的 hash 算法，將必須要索引的鍵值進(jìn)行 hash 運(yùn)算。hash 索引的速度特別快，但是 hash 索引只能用 = 來(lái)進(jìn)行匹配，因?yàn)樗荒苡?hash 碼實(shí)現(xiàn)精確匹配，這也是它的一個(gè)與生俱來(lái)的缺點(diǎn)吧。 MySQL 中的 MyISAM 表并不支持 hash 索引，InnoDB 支持自適應(yīng) hash 索引。也就是說(shuō) InnoDB 會(huì)自動(dòng)監(jiān)測(cè)哪些數(shù)據(jù)比較常用，然后對(duì)它們自動(dòng)建立 hash 索引，這是無(wú)需我們參與并且參與也起不到實(shí)際作用。
2. 全文索引（fulltext）
全文索引，在 MySQL5.6 之前 僅有 myisam 存儲(chǔ)引擎支持，而在 5.6及以上 的版本中開(kāi)始 innodb 支持全文索引。所謂全文索引，是一種通過(guò)建立倒排索引，快速匹配文檔的方式。 MySQL支持三種模式的全文檢索模式： （1）自然語(yǔ)言模式（ IN NATURAL LANGUAGE MODE ），即通過(guò) MATCH AGAINST 傳遞某個(gè)特定的字符串來(lái)進(jìn)行檢索。 （2） 布爾模式（ IN BOOLEAN MODE ），可以為檢索的字符串增加操作符，例如 “ + ” 表示必須包含，“-” 表示不包含，“*” 表示通配符（這種情況， 即使傳遞的字符串較小或出現(xiàn)在停詞中，也不會(huì)被過(guò)濾掉），其他還有很多特殊的布爾操作符，可以通過(guò)如下參數(shù)控制： mysql> show variables like '%ft_boolean_syntax%'; +-------------------+----------------+ | Variable_name??| Value????| +-------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | +-------------------+----------------+ （3）查詢(xún)擴(kuò)展模式（WITH QUERY EXPANSION），這種模式是自然語(yǔ)言模式下的一個(gè)變種，會(huì)執(zhí)行兩次檢索，第一次使用給定的短語(yǔ)進(jìn)行檢索，第二次是結(jié)合第一次相關(guān)性比較高的行進(jìn)行檢索。 目前 MySQL 支持在 char、varchar、text 類(lèi)型的列上定義全文索引。 4.4 Query 語(yǔ)句寫(xiě)法分析 在實(shí)際的項(xiàng)目中，更多的可能就是在使用 Query 語(yǔ)句了，Query 語(yǔ)句對(duì)數(shù)據(jù)庫(kù)性能的影響非常大。 4.4.1 定位 Query 在對(duì) Query 進(jìn)行優(yōu)化時(shí)，并不是說(shuō)對(duì)于所有的 Query 都要優(yōu)化， 這樣工程量太大。例如一個(gè) Query 一周也只用幾次，對(duì)其他的毫無(wú)影響，這樣的就沒(méi)有必要花費(fèi)大的代價(jià)去對(duì)待他。 所以，首先需要找到更需要優(yōu)化的 Query，然后針對(duì)性的進(jìn)行優(yōu)化。那什么樣的 Query 是更需要優(yōu)化呢 ？ 對(duì)于上面這個(gè)問(wèn)題我們需要從對(duì)整個(gè)系統(tǒng)的影響來(lái)考慮。什么 Query 的優(yōu)化能給系統(tǒng)整體帶來(lái)更大的收益，就更需要優(yōu)化。一般來(lái)說(shuō)，高并發(fā)低消耗（相對(duì)）的 Query 對(duì)整個(gè)系統(tǒng)的影響遠(yuǎn)比低并發(fā)高消耗的 Query 大。 例如：一個(gè) query 每小時(shí)執(zhí)行 10000 次，每次消耗 20 IO，另一個(gè) 每小時(shí)執(zhí)行 10 次，每次消耗 20000 IO。
如果想要讓兩者優(yōu)化到相同程度，例如 150000 IO，需要將第一個(gè)的 IO 減少 5 即可，而第二個(gè)需要減少 12.5 才行。
4.4.2 定位優(yōu)化對(duì)象 profiling 找到了需要優(yōu)化的 Query 之后，該怎么優(yōu)化？首先需要明白該 Query 有什么問(wèn)題，并且為什么要優(yōu)化，只有都知道了，才能針對(duì)去做優(yōu)化，而不是覺(jué)得他慢就給他 n 多方式去一個(gè)一個(gè)嘗試。 我們可以通過(guò)系統(tǒng)自帶的 profiling 功能幫我們找出一個(gè) Query 的瓶頸所在。 注意：profiling 是 MySQL5.1 中引入的，如果使用的是之前的版本，只能自己分析 Query 執(zhí)行的各個(gè)步驟了。
1. profiling 設(shè)置
profiling 默認(rèn)是關(guān)閉的，可以通過(guò) select @@profiling; 查看是否開(kāi)啟。例如 ： mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ |?????0 | +-------------+ 把這個(gè)值設(shè)置為 1 即可。示例 ： mysql> set profiling=1; 可以自行檢查。
2. 結(jié)果分析
進(jìn)行一次查詢(xún)后，通過(guò) show profiles; 查看結(jié)果。示例 ： mysql> show profiles; +----------+------------+---------------------+ | Query_ID | Duration?| Query???????| +----------+------------+---------------------+ |????1 | 0.00017825 | select @@profiling?| |????2 | 0.00081250 | select * from users | +----------+------------+---------------------+ 結(jié)果中個(gè)參數(shù)含義如下 ： Query_ID 是查詢(xún)的具體編號(hào)。
Duration 是查詢(xún)的耗時(shí)，它是一個(gè)比較精確的數(shù)字。
Query 是具體的查詢(xún)語(yǔ)句。
具體 query 耗時(shí)分析，通過(guò) show profiles for query n; 即可查看對(duì)應(yīng) query 的具體耗時(shí)。示例 ： mysql> show profile cpu, block io for query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status???????| Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting??????| 0.000051 | 0.000000 |?0.000000 |????NULL |?????NULL | | checking permissions | 0.000005 | 0.000000 |?0.000000 |????NULL |?????NULL | | Opening tables???| 0.001324 | 0.000000 |?0.000000 |????NULL |?????NULL | | init????????| 0.000019 | 0.000000 |?0.000000 |????NULL |?????NULL | | System lock?????| 0.000006 | 0.000000 |?0.000000 |????NULL |?????NULL | | optimizing?????| 0.000002 | 0.000000 |?0.000000 |????NULL |?????NULL | | statistics?????| 0.000008 | 0.000000 |?0.000000 |????NULL |?????NULL | | preparing??????| 0.000006 | 0.000000 |?0.000000 |????NULL |?????NULL | | executing??????| 0.000001 | 0.000000 |?0.000000 |????NULL |?????NULL | | Sending data????| 0.000069 | 0.000000 |?0.000000 |????NULL |?????NULL | | end?????????| 0.000002 | 0.000000 |?0.000000 |????NULL |?????NULL | | query end??????| 0.000005 | 0.000000 |?0.000000 |????NULL |?????NULL | | closing tables???| 0.000005 | 0.000000 |?0.000000 |????NULL |?????NULL | | freeing items????| 0.000075 | 0.000000 |?0.000000 |????NULL |?????NULL | | cleaning up?????| 0.000008 | 0.000000 |?0.000000 |????NULL |?????NULL | +----------------------+----------+----------+------------+--------------+---------------+ 上面的例子中是獲取 CPU 和 Block IO 的消耗，如果需要獲取其他信息可以通過(guò) show profiles *** for query n; 來(lái)查看。Status 所代表的含義則可以通過(guò)翻譯知曉。 在對(duì) Query 進(jìn)行優(yōu)化時(shí)，還需要考慮該 Query 的重要性。
如果 Query 實(shí)現(xiàn)的應(yīng)用系統(tǒng)功能比較重要，即使在某些方面作出一些讓步也是有必要的。比如調(diào)整 schema 設(shè)計(jì)，調(diào)整索引組成。
而如果該 Query 所實(shí)現(xiàn)的是一些并不是太關(guān)鍵的功能，我們需要盡量保證其他更重要的 Query 的性能。這種時(shí)候，即使需要調(diào)整商業(yè)需求，減少功能實(shí)現(xiàn)，也不得不應(yīng)該作出讓步。
4.5 MySQL 性能分析語(yǔ)句
1. 性能問(wèn)題因素與 mysql 性能因素
什么是性能問(wèn)題 ？ 對(duì)于用戶(hù)來(lái)說(shuō)：“又卡了”，“怎么死機(jī)了”，“反應(yīng)太慢了”。 對(duì)于程序員來(lái)說(shuō)：“程序執(zhí)行的時(shí)長(zhǎng)”，“每秒查詢(xún)的次數(shù)”，“內(nèi)存，CPU 的利用率”。 性能優(yōu)化的目的：（合理利用可利用的資源）俗稱(chēng)：摳門(mén)。 吞吐量，越大越好。
延遲，越低越好 同樣的資源下（前提），吞吐量越高越好，響應(yīng)時(shí)間越低越好。通俗的來(lái)說(shuō)就是：多快好省。
性能指標(biāo)參考 ： 執(zhí)行時(shí)間：一段代碼從開(kāi)始運(yùn)行到運(yùn)行結(jié)束所使用的時(shí)間。
CPU 時(shí)間：(算法)函數(shù)或者線(xiàn)程占用 CPU 的時(shí)間。
內(nèi)存分配：程序在運(yùn)行時(shí)占用的內(nèi)存空間。
磁盤(pán)吞吐量：描述 I/O 的使用情況。
網(wǎng)絡(luò)吞吐量：描述網(wǎng)絡(luò)的使用情況。
響應(yīng)時(shí)間：系統(tǒng)對(duì)某用戶(hù)行為或者動(dòng)作做出響應(yīng)的時(shí)間。響應(yīng)時(shí)間越短，性能好。
2. mysql 性能查詢(xún)的命令
（1）show status -- 用來(lái)查看MySQL在運(yùn)行過(guò)程中的屬性狀態(tài) show status; show status like '%變量%'; （2）查看會(huì)話(huà)狀態(tài) -- 查看當(dāng)前會(huì)話(huà)情況 show status like 'Com_%'; -- 查看全局會(huì)話(huà)情況 show global status like 'Com_%'; -- 查看針對(duì)innodb存儲(chǔ)引擎狀態(tài)的統(tǒng)計(jì) show global status like 'Innodb_%'; -- 查看視圖連接MySQL服務(wù)器次數(shù) show global status like 'connections'; -- 查看服務(wù)器工作時(shí)間 show global status like 'uptime'; -- 查看鎖使用情況 show global status like 'innodb_row_lock%'; （3）show processlist processlist 命令的輸出結(jié)果顯示了有哪些線(xiàn)程在運(yùn)行，可以幫助識(shí)別出有問(wèn)題的查詢(xún)語(yǔ)句，兩種方式使用這個(gè)命令。 show processlist; （4）show variables 查看 Mysql 系統(tǒng)變量 show variables; --查看當(dāng)前session級(jí)別的隔離方式 show variables like '%isolation%'; -- 查看isolation幫助 help isolation -- 局部修改 set session transaction isolation level READ COMMITTED; show session variables like'%isolation%'; -- 全局修改 show global variables like '%isolation%'; set global transaction isolation level READ COMMITTED; 4.6 SQL 性能分析 在優(yōu)化 sql 語(yǔ)句時(shí)，主要有以下幾個(gè)步驟 ： 通過(guò) show status 命令了解各種 SQL 的執(zhí)行頻率。
定位執(zhí)行執(zhí)行效率較低的 SQL 語(yǔ)句。
通過(guò) explain 分析低效 SQL 的執(zhí)行計(jì)劃。
確定問(wèn)題并采取相應(yīng)的優(yōu)化措施。
4.6.1 了解執(zhí)行頻率 我們可以通過(guò) show [ session|global ] status 命令可以提供服務(wù)器狀態(tài)信息。 如果使用 session 修飾表示當(dāng)前連接，使用 global 修飾表示自數(shù)據(jù)庫(kù)上次啟動(dòng)至今的統(tǒng)計(jì)結(jié)果，如果不寫(xiě)，默認(rèn)是 session。 我們也可以添加 like 來(lái)幫助我們進(jìn)一步查詢(xún)，例如：show status like ‘Com_%’ Com_select ：表示執(zhí)行 select 操作的次數(shù)，一次查詢(xún)累加 1。
Com_insert：執(zhí)行 insert 操作的次數(shù)，對(duì)于批量插入累加 1。
Com_update:執(zhí)行 update 操作的次數(shù)。
Com_delete:執(zhí)行 delete 操作的次數(shù)。
通過(guò)上面這些參數(shù)，我們很容易的了解到當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還是以查詢(xún)操作為主，以及各種類(lèi)型的 SQL 大致的執(zhí)行比例是多少。 對(duì)于更新操作的計(jì)數(shù)，是對(duì)執(zhí)行次數(shù)的計(jì)數(shù)，不論提交還是回滾都會(huì)進(jìn)行增加。對(duì)于事務(wù)型的應(yīng)用，通過(guò) Com_commit 和 Com_rollback 可以了解事務(wù)提交和回滾的情況，對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù)，可能意味著應(yīng)用編寫(xiě)存在問(wèn)題。 除了上面這些以外，我們還可以查詢(xún)一些參數(shù)來(lái)了解數(shù)據(jù)庫(kù)的基本情況。例如： Connections : 試圖連接 MySQL 服務(wù)器的次數(shù)。
Uptime : 服務(wù)器工作時(shí)間。
Slow_queries : 慢查詢(xún)的次數(shù)。
4.6.2 SQL 分析 前面有提到過(guò)慢查詢(xún)?nèi)罩?，通過(guò)這個(gè)東西我們就可以十分清晰的查看到哪些是執(zhí)行起來(lái)比較慢的，從而定位效率比較低的 sql。 注意慢查詢(xún)?nèi)罩驹诓樵?xún)結(jié)束之后才能記錄，所以在應(yīng)用反應(yīng)執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查詢(xún)慢日志并不能定位問(wèn)題。可以使用 show processlist 命令查看當(dāng)前 mysql 在進(jìn)行的線(xiàn)程，包括線(xiàn)程的狀態(tài)、是否鎖表等等，可以實(shí)時(shí)地查看 SQL 的執(zhí)行情況，同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。
執(zhí)行計(jì)劃
在定位到 sql 之后，需要分析低效 sql 的執(zhí)行計(jì)劃。我們可以通過(guò) explain 或者 desc 命令獲取 MySQL 如何執(zhí)行 select 語(yǔ)句的信息，包括在 select 語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序。 格式 ： explain select 語(yǔ)句 \G 字段說(shuō)明： 1.select_type 表示 select 的類(lèi)型，常見(jiàn)的取值有： ???simpe----簡(jiǎn)單表，即不使用表連接或者子查詢(xún) ????primary---主查詢(xún)，即外層的查詢(xún) ????union------union 中的第二個(gè)或者后面的查詢(xún)語(yǔ)句 ???subquery--子查詢(xún)中的第一個(gè) select ???? 2.type 表示表的連接類(lèi)型，性能由好到差為： ????system------表中僅有一行，即常量表 ????const-------單表中最多只有一個(gè)匹配行，比如 unique index ????eq_ref------在表中只查詢(xún)一條記錄，多表連接使用唯一索引 ????ref------------使用普通索引 ????ref_or_null--條件中包含對(duì) null 的查詢(xún) ????index_merge--索引合并優(yōu)化 ????unique_subquery-in 后面是一個(gè)查詢(xún)主鍵字段的子查詢(xún) ????index_subquery--in 的后面是查詢(xún)非唯一索引字段的子查詢(xún) ????range------------單表查詢(xún)中的范圍查詢(xún) ????index-------------通過(guò)查詢(xún)索引來(lái)得到數(shù)據(jù) ????all-----------------通過(guò)全表掃描來(lái)得到數(shù)據(jù) ???? 3.possible_keys 表示查詢(xún)時(shí)，可能使用的索引。 4.key 表示實(shí)際使用的索引。 5.key_len 表示索引字段的長(zhǎng)度。 6.ref 顯示索引的那一列被使用了。 7.rows 表示掃描行的數(shù)量。 8.extra 表示執(zhí)行情況的說(shuō)明和描述。 示例 ： mysql> explain select name,email from users where name =?'ll' \G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: NULL ?????key: NULL ??key_len: NULL ????ref: NULL ????rows: 19 ??filtered: 10.00 ???Extra: Using where 表示當(dāng)前是一個(gè)對(duì) users 表的簡(jiǎn)單查詢(xún)，沒(méi)有使用任何索引，進(jìn)行了全表掃描，掃描了 19 行，效率很低。對(duì)該表就可以添加索引來(lái)優(yōu)化查詢(xún)。例如 ： mysql> create index idx_name on users(name); -- 給 name 字段創(chuàng)建索引 ? mysql> show index from users; -- 查看表的索引 ... KEY `idx_name` (`name`) ... ? -- 再次執(zhí)行計(jì)劃 mysql> explain select name,email from users where name =?'ll' \G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ref possible_keys: idx_name ?????key: idx_name ??key_len: 802 ????ref: const ????rows: 2 ??filtered: 100.00 ???Extra: NULL 1 row in set, 1 warning (0.00 sec) 這次就會(huì)使用上創(chuàng)建的索引，類(lèi)型為普通索引，掃描行數(shù)則只有 2 行。一般來(lái)說(shuō)，得保證查詢(xún)至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。 需要注意索引會(huì)失效的情況：詳情看索引部分。
4.6.3 優(yōu)化方案 對(duì)于一般人員來(lái)說(shuō)，可能只需要完成一些簡(jiǎn)單實(shí)用的優(yōu)化即可，更復(fù)雜的則會(huì)有專(zhuān)業(yè)的 DBA 完成。簡(jiǎn)單實(shí)用的方法主要有 ： 定期分析表和檢查表。
定期優(yōu)化表。
1. 分析表
MySQL中使用 analyze table 語(yǔ)句來(lái)分析表。其語(yǔ)法格式為 ： analyze [local|no_write_to_binlog] table 表名 1 [,表名 2]..... 該語(yǔ)句用于分析和存儲(chǔ)表的關(guān)鍵字分布，分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息，使得 SQL 能夠生成正確的執(zhí)行計(jì)劃。 如果用戶(hù)感覺(jué)實(shí)際執(zhí)行計(jì)劃并不是預(yù)期的執(zhí)行計(jì)劃，執(zhí)行一次分析表可能會(huì)解決問(wèn)題。 在分析表的過(guò)程中，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)對(duì)表添加一個(gè)只讀鎖，在分析期間只能讀取而不能更新和插入數(shù)據(jù)。 示例 ： mysql> analyze table users; +---------------+---------+----------+-----------------------------+ | Table????| Op???| Msg_type | Msg_text??????????| +---------------+---------+----------+-----------------------------+ | laravel.users | analyze | status?| Table is already up to date | +---------------+---------+----------+-----------------------------+ 說(shuō)明 ： Table：表示表的名稱(chēng)。
Op：表示執(zhí)行的操作 ：
analyze 表示進(jìn)行分析操作。
check 表示進(jìn)行檢查查找。
optimize 表示進(jìn)行優(yōu)化操作。
Msg_type：表示信息類(lèi)型，其顯示的值通常是狀態(tài)、警告、錯(cuò)誤和信息這四者之一。
Msg_text：顯示信息。
對(duì)表的定期分析可以改善性能，且應(yīng)該成為常規(guī)維護(hù)工作的一部分。因?yàn)橥ㄟ^(guò)更新表的索引信息對(duì)表進(jìn)行分析，可改善數(shù)據(jù)庫(kù)性能。
2. 檢查表
MySQL中使用 check table 語(yǔ)句來(lái)檢查表，檢查表是為了檢查一個(gè)或多個(gè)表是否有錯(cuò)誤。其語(yǔ)法格式為 ： check table 表名 1 [,表名 2].... [option]; option 的值可以是 {QUICK | FAST |MEDIUM | EXTENDED |CHANGED}，其執(zhí)行效率會(huì)依次降低。 注意：check table 對(duì) MyISAM 和 InnoDB 表有作用，但是 option 選項(xiàng)只對(duì) MyISAM 類(lèi)型的表有效，對(duì) InnoDB 類(lèi)型的表無(wú)效。CHECK TABLE 語(yǔ)句在執(zhí)行過(guò)程中也會(huì)給表加上只讀鎖。
check table 也可以檢查視圖是否有錯(cuò)誤，比如在視圖定義中被引用的表已經(jīng)不存在。
3. 優(yōu)化表
MySQL 中使用 optimize table 語(yǔ)句來(lái)優(yōu)化表。該語(yǔ)句對(duì) InnoDB 和 MyISAM 類(lèi)型的表都有效。其語(yǔ)法格式為 ： optimize [local | no_write_to_binlog] table 表名 1 [,表名 2].... 如果已經(jīng)刪除了表的一大部分，或者如果已經(jīng)對(duì)含有可變長(zhǎng)度行的表(含有 varchar，blob 或者 text 列的表)進(jìn)行了很多更改，則應(yīng)該使用 optimize table 命令來(lái)進(jìn)行表優(yōu)化。該命令可以將表中的空間碎片進(jìn)行合并，并且可以消除由于刪除或者更新造成的空間浪費(fèi)。 注意：optimize table 語(yǔ)句只能優(yōu)化表中的 VARCHAR、BLOB 或 TEXT 類(lèi)型的字段。optimize table 語(yǔ)句在執(zhí)行過(guò)程中也會(huì)給表加上只讀鎖。因此一定要注意在數(shù)據(jù)庫(kù)不繁忙的時(shí)候執(zhí)行相關(guān)的操作。
4.6.4 優(yōu)化 SQL 對(duì)于 SQL 我們也需要進(jìn)行一些優(yōu)化，常用的 SQL 優(yōu)化有 ： 大批量插入數(shù)據(jù)。
優(yōu)化 insert 語(yǔ)句。
優(yōu)化 group by 語(yǔ)句。
優(yōu)化 order by 語(yǔ)句。
優(yōu)化嵌套查詢(xún)。
優(yōu)化 or 條件。
使用 SQL 提示。
1. 大批量插入數(shù)據(jù)
（1）提高 MyISAM 表的導(dǎo)入效率 當(dāng)用 load 命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度。 對(duì)于 MyISAM 存儲(chǔ)引擎的表，可以通過(guò)以下方式快速的導(dǎo)入大量的數(shù)據(jù)。 alter table t1 disable keys； loading the data alter table t1 enable keys； disable keys 和 enable keys 用來(lái) 打開(kāi)或者關(guān)閉 MyISAM 表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個(gè)非空的 MyISAM 表時(shí)，通過(guò)設(shè)置這兩個(gè)命令，可以提高導(dǎo)入的效率。對(duì)于導(dǎo)入大量數(shù)據(jù)到一個(gè)空的 MyISAM 表，默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的，所以不用進(jìn)行設(shè)置。 注意：以上是對(duì) MyISAM 存儲(chǔ)引擎而言的。
（2）提高 InnoDB 表的導(dǎo)入效率 對(duì)于 InnoDB 可以提高的方面比較多，主要包含以下方向 ：
數(shù)據(jù)的準(zhǔn)備
：因?yàn)?InnoDB 類(lèi)型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列，可以有效的提高導(dǎo)入數(shù)據(jù)的效率。
唯一性校驗(yàn)
：在導(dǎo)入數(shù)據(jù)前執(zhí)行 set unique_checks =0 關(guān)閉唯一性校驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行 set unique_checks=1 來(lái)恢復(fù)唯一性校驗(yàn)，提高效率。
自動(dòng)提交
：如果應(yīng)用使用自動(dòng)提交的方式，建議在導(dǎo)入前執(zhí)行 set autocommit=0 來(lái)關(guān)閉自動(dòng)提交，導(dǎo)入結(jié)束后再執(zhí)行 set autocommit=1 來(lái)打開(kāi)自動(dòng)提交，它也可以提高導(dǎo)入的效率。
2. 優(yōu)化 insert 語(yǔ)句
當(dāng)進(jìn)行數(shù)據(jù) insert 的時(shí)候，可以考慮采用以下幾種優(yōu)化方式 ： （1） 如果同時(shí)從同一客戶(hù)插入很多行，盡量使用多個(gè)值表的 insert 語(yǔ)句，這種方式將大大縮減客戶(hù)端與數(shù)據(jù)庫(kù)之間的連接、關(guān)閉等消耗，使得效率比分開(kāi)執(zhí)行的單個(gè) insert 語(yǔ)句塊。 示例 ： insert into users values(值1，值2..),(值1，值2..),(值1，值2..).. （2）如果從不同客戶(hù)插入很多行，能通過(guò)使用 insert delayed 語(yǔ)句得到更高的速度，delayed 的含義是讓 insert 語(yǔ)句馬上執(zhí)行，其實(shí)數(shù)據(jù)都被存放在內(nèi)存的隊(duì)列中，并沒(méi)有真正寫(xiě)入磁盤(pán)，這比每條數(shù)據(jù)分別插入要快得多。示例 ： insert delayed into users .. 注意：這樣的好處是，提高插入的速度，客戶(hù)端不需要等待太長(zhǎng)時(shí)間。壞處是，不能返回自動(dòng)遞增的ID，以及系統(tǒng)崩潰時(shí)，MySQL還沒(méi)有來(lái)得及插入數(shù)據(jù)的話(huà)，這些數(shù)據(jù)將會(huì)丟失。
（3）將索引文件和數(shù)據(jù)文件分在不同的磁盤(pán)上存放，此處需要使用建表中的選項(xiàng)。 （4）如果進(jìn)行批量插入，可以增加 bulk_insert_buffer_size 變量值的方式來(lái)提高速度，但是，這只能對(duì) MyISAM 表使用。 （5）當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用 load data infile ，這通常比使用很多 insert 語(yǔ)句快 20 倍。
3 . 優(yōu)化 group by 語(yǔ)句
默認(rèn)情況下，MySQL 對(duì)所有 group by c1，c2....的字段進(jìn)行排序，這與在查詢(xún)中指定 order by c1，c2 類(lèi)似，所以是否顯示包含相同列對(duì)性能沒(méi)有什么影響。而如果查詢(xún)包括 group by，但是用戶(hù)想要避免排序結(jié)果的消耗，可以指定 order by null 來(lái)禁止排序。 示例 ： 不使用 order by null mysql> explain select * from users group by id\G *************************** 1. row *************************** ?????id: 1 ?select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: PRIMARY ?????key: NULL ???key_len: NULL ?????ref: NULL ????rows: 19 ??filtered: 100.00 ????Extra: Using filesort 使用 order by null mysql> explain select * from users group by id order by null\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: PRIMARY ?????key: NULL ??key_len: NULL ????ref: NULL ????rows: 19 ??filtered: 100.00 ???Extra: NULL 兩者區(qū)別在于第一個(gè) SQL 語(yǔ)句需要進(jìn)行 filesort，而 filesort 往往非常耗費(fèi)時(shí)間。
4. 優(yōu)化 order by 語(yǔ)句
當(dāng) where 條件和 order by 使用相同的索引， order by 的順序和索引順序相同，并且 order by 的字段都是升序或者都是降序。 這種情況中，MySQL 可以使用一個(gè)索引來(lái)滿(mǎn)足 order by 子句，而不需要額外的排序。 可以使用索引情況 ： select * from users order by key_part1 ,key_part2....; select * from users where key_part = 1 order by key_part1 desc, key_part2 desc; select * from users order by key_part1 desc,key_part2 desc; 無(wú)法使用索引情況 ： order by 的字段混合 asc 和 desc。
select * from users order by key_part1 desc，key_part2 asc;
用于查詢(xún)行的關(guān)鍵字與 order by 中所使用的不同。
select * from users where key2= constant order by key1;
對(duì)不同的關(guān)鍵字使用 order by。
select * from users order by key1，key2;
5. 優(yōu)化嵌套子查詢(xún)
Mysql 的子查詢(xún)可以一次性完成需要多次才能完成的 SQL 操作，同時(shí)也可以避免失誤或者表鎖死，而且寫(xiě)起來(lái)很容易。但是在很多情況下，子查詢(xún)可以被更有效率的 join 替代。 示例 ： 在 user1 表中找到那些在 user2 表中不存信息的所有用戶(hù) ： select * from users where id not in(select user_id from user_infos); 我們可以使用連接來(lái)優(yōu)化它，尤其當(dāng) t2 表中對(duì) id 建有索引的話(huà)， select * from users u left join user_infos uf on u.id = uf.user_id where uf.user_id is null; 在數(shù)據(jù)庫(kù)實(shí)現(xiàn)早期，查詢(xún)優(yōu)化器對(duì)子查詢(xún)一般采用嵌套執(zhí)行的方式，即父查詢(xún)中的每一行，都執(zhí)行一次子查詢(xún)，這樣子查詢(xún)會(huì)執(zhí)行很多次。這種執(zhí)行方式效率低。而對(duì)子查詢(xún)進(jìn)行優(yōu)化，可能帶來(lái)幾個(gè)數(shù)量級(jí)的查詢(xún)效率的提高。子查詢(xún)轉(zhuǎn)變成為連接操作之后，會(huì)得到如下好處 ： 子查詢(xún)不用執(zhí)行很多次。
優(yōu)化器可以根據(jù)統(tǒng)計(jì)信息來(lái)選擇不同的連接方法和不同的連接順序。
子查詢(xún)中的連接條件、過(guò)濾條件分別變成了父查詢(xún)的連接條件、過(guò)濾條件，優(yōu)化器可以對(duì)這些條件進(jìn)行下推，以提高執(zhí)行效率。
6. 優(yōu)化 or
對(duì)于含有 or 的查詢(xún)子句，如果要利用索引，則 or 之間的每個(gè)條件列都必須用到索引，如果沒(méi)有索引，則應(yīng)該考慮增加索引。 沒(méi)有索引 ： mysql> explain select * from users where id=66 or name = "Orrin Eichmann"\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: PRIMARY ?????key: NULL ??key_len: NULL ????ref: NULL ????rows: 100 ??filtered: 10.90 ???Extra: Using where 此時(shí)是全表掃描，效率差。 使用索引 : -- 添加索引 mysql> create index idx_name on users(name); Query OK, 100 rows affected (0.01 sec) ? mysql> explain select * from users where id=66 or name = "Orrin Eichmann"\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: index_merge possible_keys: PRIMARY,idx_name ?????key: PRIMARY,idx_name ??key_len: 8,767 ????ref: NULL ????rows: 2 ??filtered: 100.00 ???Extra: Using union(PRIMARY,idx_name); Using where 此時(shí)則使用了索引 PRIMARY, idx_name。另外還可以看出 MySQL 在處理含有 or 子句的時(shí)候，實(shí)際上是對(duì) or 的各個(gè)字段分別查詢(xún)后的結(jié)果進(jìn)行了 union。
7. SQL 提示
SQL 提示是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，簡(jiǎn)單來(lái)說(shuō)就是在 SQL 語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。常用的有 use index 、ignore index、force index。 （1）use index use index 在查詢(xún)語(yǔ)句中表名的后面，添加 use index 來(lái)提供希望 MySQL 去參考的索引列表，就可 以讓 MySQL 不再考慮其他可用的索引。示例 ： mysql> explain select * from users use index(idx_name) where id = 2\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: NULL ?????key: NULL ??key_len: NULL ????ref: NULL ????rows: 100 ??filtered: 1.00 ???Extra: Using where ? mysql> explain select * from users where id = 2\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: const possible_keys: PRIMARY ?????key: PRIMARY ??key_len: 8 ????ref: const ????rows: 1 ??filtered: 100.00 ???Extra: NULL 但是需要注意使用的是否合理，上例在指定使用的索引情況下效率會(huì)更好。與之相似的則是 force index，它會(huì)讓 MySQL 強(qiáng)制使用指定的索引。 區(qū)別：如果使用 use index，則優(yōu)化器可以使用此索引，但如果優(yōu)化器認(rèn)為更快，則可以使用表掃描。如果使用 force index，那么即使認(rèn)為表掃描更有效，優(yōu)化器也會(huì)使用此索引。只有在沒(méi)有辦法使用索引查找行時(shí)，優(yōu)化器才會(huì)使用表掃描。
（2）ignore index 如果用戶(hù)只是單純地想讓 MySQL 忽略一個(gè)或者多個(gè)索引，則可以使用 ignore index。示例 ： mysql> explain select * from users ignore?index(PRIMARY) where id = 2\G *************************** 1. row *************************** ?????id: 1 select_type: SIMPLE ????table: users ?partitions: NULL ????type: ALL possible_keys: NULL ?????key: NULL ??key_len: NULL ????ref: NULL ????rows: 100 ??filtered: 1.00 ???Extra: Using where 同樣反例，忽略了主鍵索引后，會(huì)掃描全表。 SQL 優(yōu)化問(wèn)題是數(shù)據(jù)庫(kù)性能優(yōu)化最基礎(chǔ)也是最重要的一個(gè)問(wèn)題。很多數(shù)據(jù)庫(kù)性能問(wèn)題都是由不合適的 SQL 語(yǔ)句造成的。 4.7 與elasticsearch結(jié)合 Elastic 是一個(gè)實(shí)時(shí)的分布式搜索分析引擎，它能讓你以前所未有的速度和規(guī)模，去探索你的數(shù)據(jù)。 它被用作全文檢索、結(jié)構(gòu)化搜索、分析以及這三個(gè)功能的組合。 Elastic 的底層是開(kāi)源庫(kù) Lucene。但是，你沒(méi)法直接用 Lucene，必須自己寫(xiě)代碼去調(diào)用它的接口。Elastic 是 Lucene 的封裝，提供了 REST API 的操作接口，開(kāi)箱即用。 4.7.1 安裝運(yùn)行
1 . windows 環(huán)境
（1）JDK 準(zhǔn)備 Elastic 需要 Java 8 環(huán)境。如果你的機(jī)器還沒(méi)安裝 Java，可以下載 java8 ，下載好后注意要保證環(huán)境變量JAVA_HOME 正確設(shè)置（如果系統(tǒng)沒(méi)有幫助設(shè)置），安裝好后，通過(guò) java -version 可以查看版本信息，通過(guò)這個(gè)來(lái)判斷是否安裝成功。 （2）Elasticsearch 安裝 準(zhǔn)備好了 java 環(huán)境之后，準(zhǔn)備 elasticsearch 的安裝。
下載
：首先需要 下載 elasticsearch，可以根據(jù)自己的需要選擇版本，這里是 7.6.1 ，如果不想要最新版本可以點(diǎn)擊下面內(nèi)容選擇需要的版本 ：
解壓運(yùn)行
：下載好需要的版本之后，進(jìn)行解壓，進(jìn)入到解壓目錄下的 bin 目錄。雙擊 elasticsearch.bat 即可運(yùn)行 ：
運(yùn)行后出現(xiàn)一大段內(nèi)容，可以忽略。
查看
：雖然可以忽略一大段令人頭痛的內(nèi)容，但是還是需要測(cè)試是否安裝成功。打開(kāi)瀏覽器輸入 http://127.0.0.1:9200/ ，可以看到以下內(nèi)容則說(shuō)明成功了 ：
不同版本大同小異。 （3）kibana 安裝 Kibana 是一個(gè)開(kāi)放源碼分析和可視化平臺(tái)，如果不需要可以不安裝。主要可以使用 Kibana 搜索、查看并與存儲(chǔ)在 ElasticSearch 索引中的數(shù)據(jù)進(jìn)行交互。它簡(jiǎn)單、基于瀏覽器的界面使您能夠快速創(chuàng)建和共享動(dòng)態(tài)儀表板，實(shí)時(shí)顯示對(duì) Elasticearch 查詢(xún)的更改。 Kibana 的安裝與 elasticsearch 類(lèi)似，如下 ：
下載
：下載 Kibana，需要注意其版本要與 elasticsearch 對(duì)應(yīng)。 同樣，可以在下面選擇不同的版本。
解壓運(yùn)行
：與 elasticsearch 類(lèi)似，接下來(lái)就是進(jìn)入到 bin 目錄雙擊運(yùn)行了 ：
查看
：運(yùn)行時(shí)也是一大段內(nèi)容，忽略不計(jì)，直接進(jìn)入瀏覽器查看。輸入 http://localhost:5601 可以看到 ：
不同版本也有一些不同，不知道的可以翻譯一下需要選擇的內(nèi)容。
2 . CentOS 環(huán)境
elasticsearch 安裝
在上面提供的下載路徑中存在 Linux 版本的。
（1）解壓 elasticsearch-7.6.1-linux-x86_64.tar.gz 到 /usr/local/ 目錄 tar -xvf elasticsearch-7.6.1-linux-x86_64.tar.gz （2）進(jìn)入解壓后的 elasticsearch 目錄新建 data 目錄 mkdir data （3）修改 config/elasticsearch.yml 配置文件 vi config/elasticsearch.yml 取消下列項(xiàng)注釋并修改 ： cluster.name: my-application #集群名稱(chēng) node.name: node-1 #節(jié)點(diǎn)名稱(chēng) #數(shù)據(jù)和日志的存儲(chǔ)目錄 path.data: /usr/local/elasticsearch-7.6.1/data path.logs: /usr/local/elasticsearch-7.6.1/logs #設(shè)置綁定的ip，設(shè)置為0.0.0.0以后就可以讓任何計(jì)算機(jī)節(jié)點(diǎn)訪(fǎng)問(wèn)到了 network.host: 0.0.0.0 http.port: 9200 #端口 #設(shè)置在集群中的所有節(jié)點(diǎn)名稱(chēng)，這個(gè)節(jié)點(diǎn)名稱(chēng)就是之前所修改的，當(dāng)然你也可以采用默認(rèn)的也行，目前 是單機(jī)，放入一個(gè)節(jié)點(diǎn)即可 cluster.initial_master_nodes: ["node-1"] 修改完畢后，保存退出。 （4）啟動(dòng) 進(jìn)入 /bin 目錄執(zhí)行命令。 ./elasticsearch 或者是 ./elasticsearch -d
問(wèn)題 1
：這里會(huì)出現(xiàn)錯(cuò)誤。 Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x00000000c5330000, 986513408, 0) failed; error='Cannot allocate memory' (errno=12) ## There is insufficient memory for the Java Runtime Environment to continue. # Native memory allocation (mmap) failed to map 986513408 bytes for committing reserved memory. # An error report file with more information is saved as: # logs/hs_err_pid22863.log [root@localhost bin]# 原因是 elasticsearch 使用 java 的 jvm 默認(rèn)是使用 1G 的內(nèi)存的，這里我們修改一下內(nèi)存，直接把內(nèi)存改到 256 m。 cd 到 es 目錄修改 ./config/jvm.options ： vi ./config/jvm.options 修改該內(nèi)容 ： -Xms256m -Xmx256m 保存并退出，再次啟動(dòng) es。
問(wèn)題 2
：再次啟動(dòng)會(huì)出現(xiàn)以下錯(cuò)誤。 [2019-06-21T16:20:03,039][WARN ][o.e.b.ElasticsearchUncaughtExceptionHandler] [node-1] uncaught exception in thread [main] org.elasticsearch.bootstrap.StartupException: java.lang.RuntimeException: can not run elasticsearch as root at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:163) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:150) ~ [elasticsearch-7.6.1.jar:7.1.1] atorg.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86) ~[elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:124) ~ [elasticsearch-cli-7.1.1.jar:7.1.1] at org.elasticsearch.cli.Command.main(Command.java:90) ~[elasticsearch-cli- 7.1.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:115) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92) ~ [elasticsearch-7.6.1.jar:7.1.1] Caused by: java.lang.RuntimeException: can not run elasticsearch as root at org.elasticsearch.bootstrap.Bootstrap.initializeNatives(Bootstrap.java:102) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:169) ~[elasticsearch- 7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:325) ~[elasticsearch- 7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:159) ~ [elasticsearch-7.6.1.jar:7.1.1] ... 6 more [root@localhost elasticsearch-7.6.1]# 這是因?yàn)椴荒苁褂?root 用戶(hù)操作，添加一個(gè)其他的用戶(hù)再試試 ： [root@localhost elasticsearch-7.6.1]# useradd elasticsearch [root@localhost elasticsearch-7.6.1]# passwd elasticsearch 更改用戶(hù) elasticsearch 的密碼 。 新的 密碼： 無(wú)效的密碼： 密碼少于 8 個(gè)字符 重新輸入新的 密碼： passwd：所有的身份驗(yàn)證令牌已經(jīng)成功更新。 [root@localhost elasticsearch-7.6.1]# chown -R elasticsearch /usr/local/elasticsearch- 7.6.1 vi 編輯 /etc/security/limits.conf，在末尾加上以下內(nèi)容 ： elasticsearch soft nofile 65536 elasticsearch hard nofile 65536 elasticsearch soft nproc 4096 elasticsearch hard nproc 4096 vi 編輯 vi /etc/security/limits.d/20-nproc.conf，將 * 改為用戶(hù)名（elasticsearch）： # Default limit for number of user's processes to prevent # accidental fork bombs. # See rhbz #432903 for reasoning. ? elasticsearch soft nproc 4096 root soft nproc unlimited vi 編輯 /etc/sysctl.conf，在末尾加上以下內(nèi)容 ： vm.max_map_count = 655360 在 root 用戶(hù)下執(zhí)行 ： [root@localhost ~]# sysctl -p vm.max_map_count = 655360 [root@localhost ~]# 登錄剛才新建的 elasticsearch 用戶(hù)，并啟動(dòng) elasticsearch [root@localhost elasticsearch-7.6.1]# su es [elasticsearch@localhost elasticsearch-7.6.1]$ ./bin/elasticsearch 完畢。 （5）測(cè)試 以上完畢后，在瀏覽器輸入 ip:9200 即可，與 windows 類(lèi)似，只是要改成自己的 ip。
3. kibana 安裝
（1）解壓 在 /usr/local/ 解壓 kibana-7.6.1-linux-x86_64.tar.gz tar -xvf kibana-7.6.1-linux-x86_64.tar.gz （2）啟動(dòng) 進(jìn)入 ./kibana/bin 目錄啟動(dòng) ./kibana Kibana should not be run as root. Use --allow-root to continue -- 不能在root用戶(hù)下啟動(dòng) （3）創(chuàng)建 kibana 用戶(hù) useradd kibana passwd kibana chown -R kibana /usr/local/kibana （4）訪(fǎng)問(wèn) 進(jìn)入 kibana 用戶(hù)后，啟動(dòng) kibana 然后訪(fǎng)問(wèn)即可，效果同上。 4.7.2 基礎(chǔ)使用
1. 基本概念
Elastic 本質(zhì)上是一個(gè)分布式數(shù)據(jù)庫(kù)，允許多臺(tái)服務(wù)器協(xié)同工作，每臺(tái)服務(wù)器可以運(yùn)行多個(gè) Elastic 實(shí)例。單個(gè) Elastic 實(shí)例稱(chēng)為一個(gè)節(jié)點(diǎn)（node）。一組節(jié)點(diǎn)構(gòu)成一個(gè)集群（cluster）。 （1）Index Elastic 會(huì)索引所有字段，經(jīng)過(guò)處理后寫(xiě)入一個(gè)反向索引。查找數(shù)據(jù)的時(shí)候，直接查找該索引。所以，Elastic 數(shù)據(jù)管理的頂層單位就叫做 Index（索引），也就是說(shuō) 索引 即是 單個(gè)數(shù)據(jù)庫(kù)。每個(gè) Index 的名字必須是小寫(xiě)。 通過(guò)下面命令可以查看當(dāng)前節(jié)點(diǎn)的所有 index ： curl -X GET 'http://localhost:9200/_cat/indices?v' （2）Document Index 里面單條的記錄稱(chēng)為 Document（文檔），類(lèi)似于數(shù)據(jù)庫(kù)的 行（row）。許多條 Document 構(gòu)成了一個(gè) Index。Document 使用 JSON 格式表示。如下 ： { ?"user": "admin", ?"title": "超管", ?"desc": "數(shù)據(jù)庫(kù)管理" } 同一個(gè) Index 里面的 Document，不要求有相同的結(jié)構(gòu)（scheme），但是最好保持相同，這樣有利于提高搜索效率。
（3）Type Type 類(lèi)似于數(shù)據(jù)庫(kù)的表（table），但是它是虛擬的，是由 Document（文檔）按照一定邏輯分組得到。不同的 Type 應(yīng)該有相似的結(jié)構(gòu)（schema），例如兩張表的 id 數(shù)據(jù)類(lèi)型應(yīng)該相同。 通過(guò)下面命令可以列出每個(gè) index 包含的 type ： curl 'localhost:9200/_mapping?pretty=true' 以上做對(duì)比為 ：
elasticsearch 是面向文檔的，那么就意味著索引和搜索數(shù)據(jù)的最小單位是文檔，elasticsearch 中，文檔有幾個(gè) 重要屬性 : 自我包含，一篇文檔同時(shí)包含字段和對(duì)應(yīng)的值，也就是同時(shí)包含 key:value！
可以是層次型的，一個(gè)文檔中包含自文檔，復(fù)雜的邏輯實(shí)體就是這么來(lái)的！ {就是一個(gè)json對(duì)象！fastjson 進(jìn)行自動(dòng)轉(zhuǎn)換！}
靈活的結(jié)構(gòu)，文檔不依賴(lài)預(yù)先定義的模式，我們知道關(guān)系型數(shù)據(jù)庫(kù)中，要提前定義字段才能使用，在 elasticsearch 中，對(duì)于字段是非常靈活的，有時(shí)候，我們可以忽略該字段，或者動(dòng)態(tài)的添加一個(gè)新的字段。
倒排索引
說(shuō)到倒排索引則需要和另外一個(gè)對(duì)應(yīng)說(shuō)明 - 正向索引。那么什么是正向索引什么又是倒排索引呢？以一句話(huà)為例：PHP 是最好的語(yǔ)言
正向索引
：正向索引是網(wǎng)頁(yè)與關(guān)鍵詞一一對(duì)應(yīng)的數(shù)據(jù)結(jié)構(gòu)。 使用正向索引時(shí)，其對(duì)應(yīng)的結(jié)果為：
例如以上是兩個(gè)網(wǎng)頁(yè)，當(dāng)我們?nèi)ニ阉鲿r(shí)，會(huì)到各個(gè)網(wǎng)頁(yè)搜索相關(guān)關(guān)鍵字，如果有則該網(wǎng)頁(yè)是符合條件的一個(gè)，沒(méi)有則不屬于。
倒排索引
：倒排索引是相對(duì)正向索引而言的，你也可以將其理解為逆向索引。它是一種關(guān)鍵詞與網(wǎng)頁(yè)一 一對(duì)應(yīng)的數(shù)據(jù)結(jié)構(gòu)。 使用倒排索引時(shí)，期對(duì)應(yīng)結(jié)果為 ：
以上同樣是兩個(gè)網(wǎng)頁(yè)，但是當(dāng)我們?nèi)ニ阉麝P(guān)鍵字的時(shí)候，它并不會(huì)去每個(gè)網(wǎng)頁(yè)搜索關(guān)鍵字，而是通過(guò)關(guān)鍵字找匹配的網(wǎng)頁(yè)，這種情況其效率會(huì)好很多。
2. 基本操作
（1）CURD 基本命令 ： GET _cat/indices：查找所有的索引。 GET movies/_search 查看信息：movies 是索引信息。 took：消耗的時(shí)間。
hits：最外層命中的數(shù)據(jù)總數(shù)，內(nèi)層是具體的數(shù)據(jù)。
value：總共數(shù)據(jù)量。
_source：實(shí)際數(shù)據(jù)本身。
（2）查 GET users/_search：查看 users 索引的所有數(shù)據(jù)。 GET movies/_doc/252：查找 id 為 252 的數(shù)據(jù)。 （3）增 創(chuàng)建一個(gè) users 索引，并且添加數(shù)據(jù) ： POST users/_doc { ?"firstname":"hello", ?"lastname":"world" } 沒(méi)有指定 id ，系統(tǒng)會(huì)自動(dòng)給一個(gè)。 指定 id ： POST users/_doc/1 { ?"firstname":"hello", ?"lastname":"kitty" } 在 _doc 后面加上 /id 的值，如果 id 相同，后面會(huì)覆蓋前面。 （4）create 方式 POST users/_create/1 { ?"firstname":"hello", ?"lastname":"kitty" } 需要注意的是，create 方式插入數(shù)據(jù)，當(dāng) id 存在時(shí)，會(huì)報(bào)錯(cuò)。 （5）批量插入 POST users/_bulk {"index":{}} {"name":"hello","age":"16"} {"index":{"_id":6}} {"name":"hi","age":"17"} 格式 有點(diǎn)怪，但是需要注意 index 部分是可以寫(xiě)主鍵的，如果不寫(xiě)會(huì)默認(rèn)給一個(gè)。 （6）刪 DELETE users：刪除 users 索引 刪除數(shù)據(jù) DELETE users/_doc/Sqdu2ngBMfgPCKya0Kvg _doc 后面接 id。 （7）改 post 方式 POST users/_update/1 { ?"doc": { ??"firstname":"hello", ??"lastname":"uiop" } } put 方式 PUT users/_doc/1 { ?"firstname":"hello", ?"lastname":"qwer" }
3. 類(lèi)型指定
在添加數(shù)據(jù)的時(shí)候，其中的屬性可以指定類(lèi)型。其類(lèi)型主要為： 字符串類(lèi)型
text 、 keyword
數(shù)值類(lèi)型
long, integer, short, byte, double, float, half_float, scaled_float
日期類(lèi)型
date
PUT /索引名/~類(lèi)型名~/文檔id {請(qǐng)求體}
te布爾值類(lèi)型
boolean
二進(jìn)制類(lèi)型
binary
等等
示例 ： PUT userq { ?"mappings": { ??"properties": { ???"name":{ ????"type":"text" ??}, ???"age":{ ????"type":"integer" ??} ?} } } 通過(guò) GET userq 可以查看信息 ： { ?"userq" : { ??"aliases" : { }, ??"mappings" : { ???"properties" : { ????"age" : { ?????"type" : "integer" ???}, ????"name" : { ?????"type" : "text" ???} ??} ?}, ??"settings" : { ???"index" : { ????"creation_date" : "1618583270051", ????"number_of_shards" : "1", ????"number_of_replicas" : "1", ????"uuid" : "eb7Od9UzSUCfvhMEu_E6bg", ????"version" : { ?????"created" : "7060199" ???}, ????"provided_name" : "userq" ??} ?} } }
4. URL
（1）范圍查 查詢(xún)所有包含 2012 的數(shù)據(jù) ： GET movies/_search?q=2012 范查詢(xún)：q 表示參數(shù) question。 （2）指定屬性 查詢(xún) tile 為 2012 的數(shù)據(jù) ： GET movies/_search?q=2012&df=title 等價(jià)于 GET movies/_search?q=title:2012 df 表示默認(rèn)屬性。 （3）多關(guān)鍵字 查找 title 為 hello 或者 world 的數(shù)據(jù) ： 查找 title 為 hello 或者 world 的數(shù)據(jù) GET movies/_search?q=title:hello world ? 如果指定兩個(gè)關(guān)鍵字是整體則可以： GET movies/_search?q=title:"hello world" 此時(shí) 查找 title 就是 hello world 的數(shù)據(jù) 還可以使用 + - 方式 ： GET movies/_search?q=title:(hello world) 括號(hào)中的參數(shù)都可以有 +- 兩個(gè)符號(hào)，+ 代表該參數(shù)有，- 代表該參數(shù)沒(méi)有。 （4）AND 查找 title 為 hello world 的數(shù)據(jù) ： GET movies/_search?q=title:(hello AND world) 注意 AND 不能小寫(xiě)，小寫(xiě)則代表是查找 hello and world 這三個(gè)關(guān)鍵字。 （5）分頁(yè) 從索引位置為 0 的位置開(kāi)始查詢(xún) 6 條數(shù)據(jù) GET movies/_search?q=title:(hello world)&from=0&size=6 （6）范圍查詢(xún) 查詢(xún) year 在 2010 和 2020 質(zhì)檢的數(shù)據(jù) GET movies/_search?q=year:(>=2010 AND <=2020) 另外還可以使用一下方式 : GET movies/_search?q=year:{2010 TO 2020] GET movies/_search?q=year:[2010 TO 2020] 注意: 結(jié)尾必須是閉區(qū)間，也就是 ] 。
基本 Rest 命令說(shuō)明 ：
上面方式前面加上 localhost:9200 并且用對(duì)應(yīng)的 GET 或者 POST 方式訪(fǎng)問(wèn)，即可執(zhí)行對(duì)應(yīng)語(yǔ)句，注意如果是有數(shù)據(jù)，則需要以 json 格式提交。
5. Ik 分詞器
既然對(duì)于一段話(huà)需要根據(jù)關(guān)鍵字來(lái)搜索，那么這些關(guān)鍵字是需要進(jìn)行拆分的。es 也存在一些工具能幫我們做這樣的一些工作。 我們?cè)谒阉鲿r(shí)候會(huì)填寫(xiě)自己需要的信息，搜索引擎則會(huì)把數(shù)據(jù)庫(kù)或者索引庫(kù)中的數(shù)據(jù)進(jìn)行分詞，然后進(jìn)行一個(gè)匹配操作，默認(rèn)的中文分詞是將每個(gè)字看成一個(gè)詞。比如："中國(guó)的花"會(huì)被分為"中"，"國(guó)"，"的"，"花"，這顯然是不符合要求的，所以我們需要借助分詞器來(lái)解決這個(gè)問(wèn)題。如果要使用中文，建議使用 ik 分詞器。
分詞
：即把一段中文或者別的劃分成一個(gè)個(gè)的關(guān)鍵字。
（1）安裝 ik ik 分詞器是 es 的一個(gè)插件，需要我們自己下載安裝。通過(guò)以下命令可以查看 es 安裝的插件： elasticsearch-7.6.1\bin>elasticsearch-plugin list 注意需要在 bin 目錄下，此時(shí)是什么都不存在，接下來(lái)需要下載安裝。 （2）下載 ik 點(diǎn)擊 ik 進(jìn)行下載，然后到 es 的 plugins 目錄下創(chuàng)建 ik 目錄，解壓到該目錄。然后，就沒(méi)然后了，可以再次通過(guò)上述命令查看是否安裝成功。 （3）驗(yàn)證 ik 分詞器安裝完畢后，需要重啟 es 來(lái)使用，否則不會(huì)有效果。ik 提供了兩個(gè)分詞算法：ik_smart 和 ik_max_word，其中 ik_smart 為最少切分，ik_max_word 為最細(xì)粒度劃分。什么意思呢，看兩個(gè)例子。 （4）ik_smart 模式 GET _analyze { "analyzer": "ik_smart", "text": "好好學(xué)習(xí)" } 執(zhí)行后得到的結(jié)果為 ： { ?"tokens" : [ ?{ ???"token" : "好好學(xué)習(xí)", ???"start_offset" : 0, ???"end_offset" : 4, ???"type" : "CN_WORD", ???"position" : 0 ?} ] } 進(jìn)行了一個(gè)完全匹配。 （5）ik_max_word 模式 GET _analyze { "analyzer": "ik_max_word", "text": "好好學(xué)習(xí)" } 執(zhí)行后結(jié)果為 ： { ?"tokens" : [ ?{ ???"token" : "好好學(xué)習(xí)", ???"start_offset" : 0, ???"end_offset" : 4, ???"type" : "CN_WORD", ???"position" : 0 ?}, ?{ ???"token" : "好好學(xué)", ???"start_offset" : 0, ???"end_offset" : 3, ???"type" : "CN_WORD", ???"position" : 1 ?}, ?{ ???"token" : "好好", ???"start_offset" : 0, ???"end_offset" : 2, ???"type" : "CN_WORD", ???"position" : 2 ?}, ?{ ???"token" : "好學(xué)", ???"start_offset" : 1, ???"end_offset" : 3, ???"type" : "CN_WORD", ???"position" : 3 ?}, ?{ ???"token" : "學(xué)習(xí)", ???"start_offset" : 2, ???"end_offset" : 4, ???"type" : "CN_WORD", ???"position" : 4 ?} ] } 結(jié)果會(huì)比上面模式多很多。但是需要注意，并不是說(shuō) ik_smart 就只會(huì)有一個(gè)結(jié)果，只是它的分詞粒度比較小，它認(rèn)為這個(gè)詞只能分成這一個(gè)。
6. 自定義配置
有時(shí)候一些特殊的詞我們自己想要添加到分詞器的字典中去，這時(shí)候我們就可以定義自己的字典文件。步驟如下：
（1）加字典文件
在安裝插件的配置文件目錄下（plugins\ik\config），添加配置文件，這里我們寫(xiě)為 test.dic。
好好
好好學(xué)
好好學(xué)習(xí)
好學(xué)
好學(xué)習(xí)
學(xué)習(xí)
（2）配置字典
配置文件添加好后，需要加入到 IKAnalyzer.cfg.xml 文件中，該文件也在 config 目錄中。
4.7.3 結(jié)合 Laravel
1. 擴(kuò)展包
（1）下載 Elasticsearch 官方提供了 Composer 包，在引入時(shí)需要注意要指定版本，因?yàn)椴煌姹镜?Elasticsearch 的 API 略有不同，我們用的是 7.x，因此需使用 ~7.0 來(lái)指定包版本。 composer require elasticsearch/elasticsearch=7.0 （2）配置 Elasticsearch 的配置很簡(jiǎn)單，我們只需要 Elasticsearch 服務(wù)器的 IP 和端口即可。如下：
config/database.php
[ ????// Elasticsearch 支持多臺(tái)服務(wù)器負(fù)載均衡，因此這里是一個(gè)數(shù)組 ????'hosts' => explode(',', env('ES_HOSTS')), ?] ?> 在 database 配置文件的最外層即可。 .env . . ES_HOSTS=localhost:9200 本地環(huán)境的 Elasticsearch 的 IP 和端口是 localhost:9200，如果端口是 9200 則可以忽略不寫(xiě)。
2. 注冊(cè)綁定
在 laravel 中容器是一個(gè)管理實(shí)例的好地方，這里我們可以借助 laravel 的容器幫助我們管理被我們實(shí)例化的 es 對(duì)象。如下：
app/Providers/AppServiceProvider.php
app->singleton('es', function(){ ??????// 通過(guò)配置文件讀取 es 服務(wù)器列表 ??????$builder = ClientBuilder::create()->setHosts(config('database.elasticsearch.hosts')); ? ??????// 判斷開(kāi)發(fā)環(huán)境:開(kāi)發(fā)環(huán)境 ??????if ($this->app->environment() == 'local') { ????????// 配置日志 ????????$builder->setLogger(app('log')->driver()); ?????} ??????return $builder->build(); ???}); ?} . . 以上就配置完畢。
3. 測(cè)試使用
進(jìn)入 tinker 進(jìn)行測(cè)試： php artisan tinker 查看 es 信息： >>> app('es')->info() # --------- 信息 --------- => [ ??"name" => "DESKTOP-K9LDCLM", ??"cluster_name" => "elasticsearch", ??"cluster_uuid" => "DNQynn_9TLKXFjYmGSzeIw", ??"version" => [ ???"number" => "7.6.1", ???"build_flavor" => "default", ???"build_type" => "zip", ???"build_hash" => "aa751e09be0a5072e8570670309b1f12348f023b", ???"build_date" => "2020-02-29T00:15:25.529771Z", ???"build_snapshot" => false, ???"lucene_version" => "8.4.0", ???"minimum_wire_compatibility_version" => "6.8.0", ???"minimum_index_compatibility_version" => "6.0.0-beta1", ??], ??"tagline" => "You Know, for Search", ?] 如果沒(méi)有信息，可以嘗試添加端口。
4. 讀取數(shù)據(jù)
通過(guò) es 的語(yǔ)法即可獲取到對(duì)應(yīng)信息，例如查詢(xún)可以使用 get 方法。如下 >>> app('es')->get(["index"=>"users","id"=>1]) # --------- 信息 --------- => [ ??"_index" => "users", ??"_type" => "_doc", ??"_id" => "1", ??"_version" => 3, ??"_seq_no" => 6, ??"_primary_term" => 1, get ( ) 方法用于讀取文檔，index 代表要讀取的索引名稱(chēng)， ID 則是指定要讀取的文檔 ID。
