1岭粤、開啟MySQL查詢的緩存功能（Query Cache）
通過(guò)show variables like "%query_cache%"查看與查詢緩存相關(guān)的參數(shù)：
mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 1024 |
| query_cache_size | 3145728 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.02 sec)
-
have_query_cache
當(dāng)前版本的MySQL是否支持查詢緩存，該變量是系統(tǒng)只讀變量，無(wú)法修改筹陵。 -
query_cache_type
查詢緩存的類型：0 -– 不啟用查詢緩存; 1 -– 啟用查詢緩存途乃，只要符合查詢緩存的要求顶伞，客戶端的查詢語(yǔ)句和記錄集都會(huì)緩存起來(lái)廉油，共其他客戶端使用。 -
query_cache_size
查詢緩存的大小撩笆，也就是分配多大內(nèi)存給查詢緩存使用捺球。如果設(shè)置為0，則無(wú)論query_cache_type設(shè)置為多少都不起作用夕冲。 -
query_cache_limit
查詢緩存最大能緩存的查詢記錄集氮兵，可以避免一個(gè)大的查詢記錄集占去大量的內(nèi)存區(qū)域，而且往往小查詢記錄集是最有效的緩存記錄集歹鱼，默認(rèn)設(shè)置為1M泣栈，建議修改為16k~1024k之間的值域，不過(guò)最重要的是根據(jù)自己應(yīng)用的實(shí)際情況進(jìn)行分析弥姻、預(yù)估來(lái)設(shè)置南片。 -
query_cache_min_res_unit
查詢緩存分配內(nèi)存的最小單位，要適當(dāng)?shù)卦O(shè)置此參數(shù)庭敦，可以做到為減少內(nèi)存塊的申請(qǐng)和分配次數(shù)疼进，但是設(shè)置過(guò)大可能導(dǎo)致內(nèi)存碎片數(shù)值上升。默認(rèn)值為4K秧廉，建議設(shè)置為1k~16K伞广。 -
query_cache_wlock_invalidate
該參數(shù)主要涉及MyISAM引擎拣帽，若一個(gè)客戶端對(duì)某表加了寫鎖，其他客戶端發(fā)起的查詢請(qǐng)求赔癌，且查詢語(yǔ)句有對(duì)應(yīng)的查詢緩存記錄诞外，是否允許直接讀取查詢緩存的記錄集信息，還是等待寫鎖的釋放灾票。默認(rèn)設(shè)置為0，也即允許茫虽。
但Query Cache有如下規(guī)則刊苍，如果數(shù)據(jù)表被更改，那么和這個(gè)數(shù)據(jù)表相關(guān)的全部Cache全部都會(huì)無(wú)效濒析，并刪除正什。這里“數(shù)據(jù)表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。如果你的數(shù)據(jù)表更新頻繁的話号杏，那么Query Cache將會(huì)成為系統(tǒng)的負(fù)擔(dān)婴氮。如果你的應(yīng)用對(duì)數(shù)據(jù)庫(kù)的更新很少，那么Query Cache將會(huì)作用顯著盾致。
參考：理解MySQL數(shù)據(jù)庫(kù)查詢緩存以及Query Cache主经，看上去很美
使用EXPLAIN 分析SELECT 查詢
在任意的SELECT查詢語(yǔ)句的前面加上EXPLAIN這個(gè)詞，就可以分析MySQL在執(zhí)行該語(yǔ)句時(shí)的具體信息：
# 示例1
mysql> explain select 1\G
*************************** 1. row ***************************
id : 1
select_type : SIMPLE
table : NULL
type : NULL
possible_keys: NULL
key : NULL
key_len : NULL
ref : NULL
rows : NULL
Extra : No tables used
1 rows in set (0.05 sec)
# 示例2
mysql> explain select dept_name from hr_department d left join hr_person p on p.dept_id=d.dept_id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 541 | NULL |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 561 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.05 sec)
# 示例3
mysql> explain select dept_name from hr_department union select dept_name from hr_department_copy;
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
| 1 | PRIMARY | hr_department | ALL | NULL | NULL | NULL | NULL | 541 | NULL |
| 2 | UNION | hr_department_copy | ALL | NULL | NULL | NULL | NULL | 540 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.08 sec)
在EXPLAIN的結(jié)果行是以MySQL實(shí)際執(zhí)行的查詢部分的順序出現(xiàn)的庭惜。
各參數(shù)含義：
-
id
表示在本次查詢語(yǔ)句中該SELECT的執(zhí)行序號(hào)：①id相同罩驻，執(zhí)行順序由上到下；②如果是子查詢护赊，id的序號(hào)會(huì)遞增惠遏，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行骏啰；③id如果相同节吮，可以認(rèn)為是一組，同組內(nèi)從上往下順序執(zhí)行判耕，不同組之間透绩，id值越大，優(yōu)先級(jí)越高祈秕，越先被執(zhí)行渺贤。 -
select_type
表示在本次查詢語(yǔ)句中該SELECT是簡(jiǎn)單類型還是復(fù)雜類型，如果是簡(jiǎn)單類型请毛，則為SIMPLE（不包括子查詢和UNION）志鞍。如果是復(fù)雜類型，則最外層標(biāo)記為PRIMARY方仿，其他部分標(biāo)記如下：-
SUBQUERY：表示不在FROM子句中的子查詢 -
DERIVED：表示在FROM子句中的子查詢 -
UNION：表示在UNION中的第二個(gè)以及隨后的SELECT -
UNION RESULT：表示在UNION的臨時(shí)表檢索結(jié)果的SELECT
除次之外固棚，SUBQUERY和UNION還可以標(biāo)記為DEPENDENT（意味著SELECT依賴與外層查詢中發(fā)現(xiàn)的數(shù)據(jù)）和UNCACHEABLE统翩。
-
-
table
表示該SELECT對(duì)應(yīng)的表，即對(duì)應(yīng)行正在訪問哪個(gè)表此洲。 -
type
訪問類型厂汗，即MySQL如何查找表中的行。從最差到最優(yōu)的順序?yàn)椋?-
ALL：全表掃描呜师，按從第一行到最后一行的順序去查找需要的行娶桦。 -
index：與全表掃描一樣，只是MySQL掃描表時(shí)按照索引次序進(jìn)行而不是行汁汗。 -
range：范圍掃描衷畦，就是一個(gè)有限制的索引掃描，這比全索引掃描要好知牌，因?yàn)椴挥帽闅v全部索引祈争，主要是帶有BETWEEN和WHERE子句里含有>的查詢。 -
ref：索引訪問角寸，也叫索引查找菩混，它返回所有匹配某個(gè)單個(gè)值的行，但有可能會(huì)返回多個(gè)符合條件的行扁藕，因此沮峡，它是查找和掃描的結(jié)合體，只有使用非唯一索引或者唯一索引的非唯一性前綴時(shí)才會(huì)發(fā)生纹磺。ref_or_null是ref的變種帖烘，它表示MySQL必須在初次查找的結(jié)果里進(jìn)行第二次查找以找出NULL條目。 -
eq_ref：使用這種索引查找橄杨，意味著MySQL知道最多只返回一條符合條件的記錄秘症。MySQL使用主鍵或者唯一索引查找時(shí)會(huì)看到該類型。 -
const, system：當(dāng)MySQL能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換為一個(gè)常量時(shí)式矫，就會(huì)使用該訪問類型乡摹。 -
NULL：這種訪問類型意味著MySQL能在優(yōu)化階段分解查詢語(yǔ)句，在執(zhí)行階段甚至用不著再訪問表或者索引采转。例如聪廉，從一個(gè)索引列里選取最小值就可以通過(guò)單獨(dú)查找索引來(lái)完成，不需要再去訪問表故慈。
SQL 性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別板熊，要求是 ref 級(jí)別，如果可以是 consts 最好察绷。
-
-
possible_keys
表示查詢可以使用哪些索引干签，這是基于訪問的列和使用的比較操作符來(lái)判斷的，在后續(xù)優(yōu)化中可能并不會(huì)實(shí)際用到這里列出的索引拆撼。 -
key
表示MySQL實(shí)際上采用了哪個(gè)索引容劳。如果該索引沒有出現(xiàn)在possible_keys中喘沿，那么MySQL可能選擇了一個(gè)覆蓋索引。 -
key_len
表示MySQL在索引里使用的字節(jié)的長(zhǎng)度竭贩。 -
ref
表示之前的表在key列記錄的索引中查找值所用的列或常量蚜印。 -
rows
表示MySQL為了找到所需要的行而讀取的行數(shù)。 -
Extra
這一列包含的是不適合在其他列顯示的額外信息留量，主要如下：-
Using index：表示MySQL將使用覆蓋索引窄赋，以避免訪問表。不要把覆蓋索引和index訪問類型弄混了楼熄。 -
Using where：表示MySQL將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾寝凌，不是所有帶有WHERE子句的查詢都會(huì)顯示Using where。 -
Using temporary：表示MySQL在對(duì)查詢結(jié)果排除時(shí)會(huì)使用一個(gè)臨時(shí)表孝赫。 -
Using filesort：表示MySQL會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按照索引次序從表里讀取行红符。
-
參考：《高性能MySQL》
創(chuàng)建（正確）索引
索引并不一定就是給主鍵或是唯一的字段青柄。如果在你的表中，有某個(gè)字段你總要會(huì)經(jīng)常用來(lái)做搜索预侯，那么致开，請(qǐng)為其建立索引吧。
一些注意點(diǎn)：
①在Join表的時(shí)候使用相當(dāng)類型的例萎馅，并將其索引
SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = xxx
此時(shí)双戳，兩個(gè) state 字段應(yīng)該是被建過(guò)索引的，而且應(yīng)該是相當(dāng)?shù)念愋兔臃迹嗤淖址?br>
②業(yè)務(wù)上具有唯一特性的字段飒货，即使是多個(gè)字段的組合，也必須建成唯一索引峭竣。
③在 varchar 字段上建立索引時(shí)塘辅，必須指定索引長(zhǎng)度，沒必要對(duì)全字段建立索引皆撩，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可扣墩。
④根據(jù)最左原則，不要使用%xxx%或%xxx的形式扛吞，而是xxx%呻惕，只有這個(gè)才會(huì)使用索引（當(dāng)前前提是該字段創(chuàng)建了索引）。
⑤利用覆蓋索引來(lái)進(jìn)行查詢操作滥比，避免回表亚脆。能夠建立索引的種類:主鍵索引、唯一索引守呜、普通索引型酥，而覆蓋索引是一種查詢的一種 效果山憨，用explain的結(jié)果，extra列會(huì)出現(xiàn):using index弥喉。
⑥建組合索引的時(shí)候郁竟，區(qū)分度最高的在最左邊。
參考：阿里巴巴Java開發(fā)手冊(cè)v1.2.0
其他
- 避免 SELECT *的出現(xiàn)
- 盡可能的使用 NOT NULL由境，除非你有一個(gè)很特別的原因去使用 NULL 值棚亩，你應(yīng)該總是讓你的字段保持 NOT NULL。
NULL columns require additional space in the row to record whether their values are NULL.
For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.
- 在某些情況下虏杰，用EXISTS替代IN讥蟆、用NOT EXISTS替代NOT IN：
①select * from A where exists (select * from B where B.id = A.id);
②select * from A where A.id in (select id from B);
①的執(zhí)行可以表述為
對(duì)外表A中所有記錄進(jìn)行循環(huán)，每次循環(huán)中對(duì)內(nèi)表B進(jìn)行查詢（如果當(dāng)前行符合子條件纺阔，則選出）瘸彤，主要使用B中的索引。
- 如果查詢的兩個(gè)表大小相當(dāng)笛钝，那么用in和exists差別不大质况。
- 如果兩個(gè)表中一個(gè)較小，一個(gè)是大表玻靡，則子查詢表大的用exists结榄，
子查詢表小的用in。 - 無(wú)論那個(gè)表大囤捻，用not exists都比not in要快臼朗。
參考：數(shù)據(jù)庫(kù)性能優(yōu)化之SQL語(yǔ)句優(yōu)化、深入研究mysql exists與in的性能及效率
全文思路參考
MYSQL性能優(yōu)化的最佳20+條經(jīng)驗(yàn)
20180226--更新對(duì)explain中id的解釋
