mysql優化技術（二）
（五）常用sql優化
1.預設情況，在使用group by 分組查詢時，會先分組，其後還會預設對組内其他條件進行預設的排序，可能會降低速度。這與在查詢中指定order by col1,
col2類似。
如果查詢中包括group by但使用者想要避免排序結果的消耗，則可以使用order by null禁止排序。
例子：
2.盡量使用左連接配接（或右連接配接）來替代普通多表聯查。因為使用join，mysql不需要在記憶體中建立臨時表。
select * from dept,emp where dept.deptno=emp.deptno; 【普通聯表查詢】
select * from dept left join empon dept.deptno=emp.deptno; 【左外連接配接，效率更高】
3.如果想要在含有or的查詢語句中利用索引，則or之間的每個條件列都必須用到索引，如果沒有索引，則應該考慮增加索引；
select * from 表名 where 條件1=‘t1’ or 條件2=‘t2’;
4.選擇合适的存儲引擎
在開發中，我們經常使用的存儲引擎 myisam / innodb / memory
（1）myisam存儲: 如果表對事務要求不高，同時是以查詢和添加為主的，我們考慮使用myisam存儲引擎,比如bbs中的發帖表，回複表。
（2）innodb存儲（mysql 5.5以上預設）: 對事務要求高，儲存的資料都是重要資料，我們建議使用innodb,比如訂單表，賬号表。
【引申】 myisam 和 innodb的主要差別
①myisam不支援事務；而innodb支援事務；
②myisam批量查詢的速度比innodb快（因為innodb在插入資料時預設會排序）；
③myisam支援全文索引；而innodb不支援；
④myisam是表鎖；而innodb在有索引時，預設為行鎖，無索引時，為行鎖；
⑤myisam 不支援外鍵；innodb支援外鍵；(在php開發中，通常不設定外鍵，通常是在程式中保證資料的一緻)
（3）memory存儲，比如我們資料變化頻繁，不需要入庫（重新開機mysql後，資料會清空），同時又頻繁的查詢和修改，我們考慮使用memory,資料全部在記憶體中，速度極快。
（4）mysql行級鎖、表級鎖、頁級鎖
①表級：引擎 myisam預設。直接鎖定整張表，在你鎖定期間，其它程序無法對該表進行寫操作。如果你是寫鎖。則其它程序則讀也不允許；
②行級：有索引時，引擎 innodb預設（無索引時，為表級鎖）。僅對指定的記錄進行加鎖，這樣其它程序還是可以對同一個表中的其它記錄進行操作；
③頁級：引擎 bdb預設。表級鎖速度快，但沖突多，行級沖突少，但速度慢。是以取了折衷的頁級，一次鎖定相鄰的一組記錄。
各自的特點：
①表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。
②行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。
③頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。
【補充】innodb使用行鎖定，bdb使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在sql語句處理期間，innodb自動獲得行鎖定和bdb獲得頁鎖定，而不是在事務啟動時獲得。
（Ⅰ）mysql表級鎖有兩種模式：
表共享讀鎖（table read lock）和表獨占寫鎖（table write lock）。什麼意思呢，就是說對myisam表進行讀操作時，它不會阻塞其他使用者對同一表的讀請求，但會阻塞
對同一表的寫操作；而對myisam表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作。
myisam表的讀和寫是串行的，即在進行讀操作時不能進行寫操作，反之也是一樣。但在一定條件下myisam表也支援查詢和插入的操作的并發進行，其機制是通過控制一個系統變量（concurrent_insert）來進行的：
①當其值設定為0時，不允許并發插入；
②當其值設定為1時，如果myisam表中沒有空洞（即表中沒有被删除的行），myisam允許在一個程序讀表的同時，另一個程序從表尾插入記錄；
③當其值設定為2時，無論myisam表中有沒有空洞，都允許在表尾并發插入記錄。
myisam鎖排程是如何實作的呢，這也是一個很關鍵的問題。例如，當一個程序請求某個myisam表的讀鎖，同時另一個程序也請求同一表的寫鎖，此時mysql将會如優先處理程序呢？通過研究表明，寫程序将先獲得鎖（即使讀請求先到鎖等待隊列）。但這也造成一個很大的缺陷，即大量的寫操作會造成查詢操作很難獲得讀鎖，進而可能造成永遠阻塞。所幸我們可以通過一些設定來調節myisam的排程行為。我們可通過指定參數，設定set
low_priority_updates=1,使優先級降低。
（Ⅱ）innodb有兩種模式的行鎖：
1）共享鎖：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。
select * from table_name where ......lock in share mode;
2）排他鎖：允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料集的共享讀鎖和 排他寫鎖。
select * from table_name where.....for update;
innodb行鎖是通過給索引項加鎖來實作的，即隻有通過索引條件檢索資料，innodb才使用行級鎖，否則将使用表鎖！
（Ⅲ）插入時，更新性能優化的幾個重要參數：
① bulk_insert_buffer_size 批量插入緩存大小
這個參數是針對myisam存儲引擎來說的.适用于在一次性插入100-1000+條記錄時, 提高效率.預設值是8m.可以針對資料量的大小,翻倍增加；
② concurrent_insert 并發插入
當表沒有空洞(删除過記錄), 在某程序擷取讀鎖的情況下,其他程序可以在表尾部進行插入；
值可以設0不允許并發插入, 1當表沒有空洞時, 執行并發插入, 2不管是否有空洞都執行并發插入（預設是1 針對表的删除頻率來設定）；
③ delay_key_write 針對myisam存儲引擎,延遲更新索引
意思是說,update記錄時,先将資料up到磁盤,但不up索引,将索引存在記憶體裡,當表關閉時,将記憶體索引,寫到磁盤. 值為 0不開啟, 1開啟. 預設開啟.
④ delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延遲插入, 将資料先交給記憶體隊列, 然後慢慢地插入.但是這些配置,不是所有的存儲引擎都支援, 目前來看, 常用的innodb不支援, myisam支援.根據實際情況調大, 一般預設夠用了
5.能用deciaml 的地方，盡量不要用float；
【引申】float、double和decimal的差別:
float:浮點型，占4個位元組，數值範圍為-3.4e38~3.4e38（7個有效位）
double:雙精度實型，占8個位元組，數值範圍-1.7e308~1.7e308（15個有效位）
decimal:數字型，占16個位元組，不存在精度損失，常用于銀行帳目計算。（28個有效位）
decimal(a,b):
a——指定指定小數點左邊和右邊可以存儲的十進制數字的最大個數，最大精度38。
b——指定小數點右邊可以存儲的十進制數字的最大個數。小數位數必須是從 0 到 a之間的值。預設小數位數是 0。
6.對于存儲引擎是myisam的資料庫，如果經常做删除和修改記錄的操作，要定時執行optimize table table_name;功能對表進行碎片整理；
（六）分表技術
1.水準分割（分表）
分表是将一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個檔案，myd資料檔案，myi索引檔案，frm表結構檔案。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。
簡單了解：将一張大表，分割成多個資料類型與大表相同的子表，在通路時，根據事先定義好的規則等到對應的表名，然後去操作；
2.垂直分割（分區）
分區和分表相似，都是按照規則分解表。不同在于分表将大表分解為若幹個獨立的實體表，而分區是将資料分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區後，表面上還是一張表，但資料散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的資料。
簡單了解：是将一張大表的某（幾）列，提取成一張單獨的表。一般情況兩者的關系是一對一；
3.分表的幾種方式
（1）mysql叢集
它并不是分表，但起到了和分表相同的作用。叢集可分擔資料庫的操作次數，将任務分擔到多台資料庫上。叢集可以讀寫分離，減少讀寫壓力。進而提升資料庫性能。
（2）自定義規則分表
大表可以按照業務的規則來分解為多個子表。通常為以下幾種類型，也可自己定義規則。
①range（範圍）：這種模式允許将資料劃分不同範圍。例如可以将一個表通過年份劃分成若幹個分區。
②hash（哈希）：這中模式允許通過對表的一個或多個列的hash key進行計算，最後通過這個hash碼不同數值對應的資料區域進行分區。例如可以建立一個對表主鍵進行分區的表。
③key（鍵值）：上面hash模式的一種延伸，這裡的hash key是mysql系統産生的。
④list（預定義清單）：這種模式允許系統通過預定義的清單的值來對資料進行分割。
⑤composite（複合模式）：以上模式的組合使用
（3）利用merge存儲引擎來實作分表
這裡不做詳解，因為有一個更強大的技術mycat，可以幫我實作各種分庫分表！
（七）mysql參數調優
主要是通過修改mysql的my.ini配置檔案
1.如果是innodb引擎：
innodb_additional_mem_pool_size= 64m
innodb_buffer_pool_size= 1g
2.如果是myisam引擎：
調整key_buffer_size
當然調整參數還是要看狀态，用show status語句可以看到目前狀态，以決定改調整哪些參數。
3.還可以調整最大連接配接數
# connection limit has been reached.
max_connections=1000
（八）讀寫分離
參照《使用spring配置多資料源，實作讀寫分離（ mysql實作主從複制）》一文，