MySQL經常用到show variables like xxx檢視MySQL服務配置資訊
一.索引是什麼？
1.最多一張表不超過五個索引,查找和排序按照where的順序來
幫助mysql高效擷取資料的資料結構（Index）,(”排好序的快速查找資料結構“)
索引不可能全部存儲在記憶體中，一般以檔案的形式存儲到硬碟上，索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表的記錄，是以索引列也是要占用空間的
2.索引分類：
①單值索引：一個索引隻包含單個列，一個表可以有多個單列索引
②唯一索引：索引列的值必須唯一，但允許有空值（銀行卡号）
③複合索引：一個索引包含多個列
④基本文法：create index indexname on mytable columnname
ALTER TABLE tablename ADD INDEX indexname (columname)
mysql索引結構：BTree索引，Hash索引，full-text全文索引，R-Tree索引
二叉查找樹，每個節點包含索引鍵值和一個指向對應資料記錄實體位址的指針
3.哪些情況要建立索引？
①主鍵自動建立唯一索引
②頻繁作為查詢條件的字段應該建立索引
③查詢中與其他表關聯的字段，外鍵關系建立索引
④頻繁更新的資料不适合建立索引
⑤where語句中用不到的字段就不建立索引，（所建的索引要跟排序一直where後要按索引順序來）
⑥單值索引群組合索引，（在高并發情況下傾向于組合索引）
⑦查詢中排序的字段，排序字段若通過索引去通路提高排序速度
⑧查詢中統計或者分組的字段（分組的話必排序是以也和索引息息相關）
4.哪些情況不适合建立索引？
①表記錄太少了
②經常增删改的表
③有很多重複的内容，沒必要建立索引
二.Mysql常見瓶頸
①CPU：CPU在飽和的時候一般發生在資料裝入記憶體或從磁盤上讀取資料時候
②IO：磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量的時候
③伺服器硬體的性能瓶頸：top,free,iostat和vmstat來檢視系統的性能狀态
1.explain能幹嗎？
表的讀取順序
資料讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優化器查詢
2.JOIN語句優化
①盡可能減少JOIN語句中的NestedLoop的循環總次數，永遠用小次數結果集驅動大的結果集
（書的種類去驅動書的總數），如果用大表驅動小标，要進行很多次IO。
②優先優化NestedLoop的内層循環；
③保證JOIN語句中的驅動表上的JOIN條件字段已經被索引；
④當無法保證被驅動表的JOIN條件被索引且記憶體資源充足的條件下，不要太吝啬JoinBuffer的設定
3.案例分析（索引）
①全值比對我最愛
②最佳左字首法則，查詢從最左列開始并且不跳過索引中的列
③不在索引列上做任何操作（計算，函數，（自動或手動）類型轉換），會導緻索引失效而轉向全表掃描
④存儲引擎不能使用索引中範圍條件右邊的列（between，in，>,範圍之後全失效）
⑤盡量使用覆寫索引（隻通路索引的查詢（索引列和查詢列一直）），減少select *
⑥mysql在使用不等于(!= 或者<>)的時候無法使用索引會導緻全表掃描
⑦is null,is not null 也無法使用索引
⑧like以通配符開頭("%abc…")mysql索引失效會變成全表掃描的操作（百分like加右邊）
怎麼解決like’%字元串%'索引不被使用的方法?
使用覆寫索引解決兩邊的内容，對需要查詢的字段添加一個覆寫索引。
⑨字元串不加單引号導緻索引失效
⑩少用or，用它來連接配接時會索引失效
口訣：
帶頭大哥不能死，中間兄弟不能斷，最佳做字首原則，索引列上無計算
like百分加右邊（ike要麼後面能用要麼隻有前面能用） ，就看like是不是常量開頭
範圍之後全失效 字元串單引号不能缺
groupby基本上都需要進行排序，會有臨時表産生
4.查詢截取分析
①查詢優化:
永遠小表驅動大表，小的資料集驅動大的資料集。
類似于嵌套循環Nested Loop；(exists,in)
order by 關鍵字優化:
提高order by的速度
用了order by時不用 select *,
解決方案：提高sort_buffer_size 和 max_length_for_sort_data
GROUP BY 關鍵字優化:
group by實質是先排序後進行分組，遵照索引建的最佳左字首
當無法使用索引列，增大max_length_for_sort_data 和sort_buffer_size 參數大小
where 高于having,能寫在where 限定的條件就不要去having限定
②慢查詢日志
一般不是調優需要的話，一般不建議啟動該參數
explain + 慢SQL分析
show profile
運維經理 or DBA 進行SQL資料庫伺服器的參數調優
--------------------------------------------總結-------------------------------------
慢查詢的開啟并捕獲
explain + 慢SQL分析
show profile 查詢SQL在MySQL伺服器裡面的執行細節和生命周期情況
SQL資料庫伺服器的參數調優
5.MySQL兩種排序方式：
檔案排序或掃描有序索引排序
MySQL能為排序和查詢使用相同的索引
index idx_abc(a,b,c)，如下案例進行解析
① order by能使用索引最左字首
order by a
order by a,b
order by a,b,c
order by a DESC,b DESC,c DESC 要麼同升要麼同降
② 如果where使用索引的最左字首定義為常量,則order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
③ 不能使用索引進行排序
order by a ASC,b DESC,c DESC /排序不一緻/
where g = const order b,c /丢失a索引/
where a = const order by c
where a = const order by a,d /d不是索引的一部分/
where a in(…) order by b,c /對于排序來說，多個想等條件也是範圍查詢/
三.批量資料腳本
①.先建表
②.設定參數log_bin_trust_function_creators
③.建立函數，保證每條資料都不同
随機産生字元串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
随機産生部門編号
DELIMITER $$
CREATE FUNCTION rand_num( ) RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100 + RAND() * 10);
RETURN i;
END $$
④ 建立存儲過程
建立往emp表中插入資料的存儲過程
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START + i)
, rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
建立往dept表中插入資料的存儲過程
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES ((START + i) ,rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
最後調用存儲過程dept,emp。
CALL insert_dept(100,10);//插入10條記錄，下标從100開始
CALL insert_emp(100001,500000);//插入50W條資料，下标從100001開始
四.show Profiles是什麼？
mysql提供可以分析目前會話中語句執行的資源消耗情況，可以用于SQL的調優的測量，預設是關閉的使用前需要開啟
show variables like 'profiling';
set profiling = on;
檢視一條sql語句的生命周期;
show profiles
show profile cpu,block io for query query_id;
日常開發用profile去檢視一條sql語句的生命周期出現這四個問題sql語句需要修改
converting HEAP to MyISAM 查詢結果太大，記憶體都不夠用了往磁盤上搬了
Creating tmp table 建立臨時表（拷貝資料到臨時表，用完再删除）
Copying to tmp table on disk 把記憶體中臨時表複制到磁盤，危險!!!
locked
五.全局查詢日志（不可以在生産環境下開啟此功能，要在測試環境下開啟）