mysql是關(guān)系型數(shù)據(jù)庫的一種，深入了解mysql內(nèi)部的機制有助于我們寫出高質(zhì)量的sql語句。雖然知道基礎(chǔ)的sql語法也能幫助我們完成平常的工作，但是作為對自己有要求的后端，必須去學習一些內(nèi)部原理，這樣如果在工作過程中碰到數(shù)據(jù)庫的問題，我們也就有排查的思路，然后給出恰當?shù)慕鉀Q方案。

1. 數(shù)據(jù)類型

在建表之初，我們就該考慮各個字段應(yīng)該使用何種數(shù)據(jù)類型。從業(yè)務(wù)角度來看，選擇哪種數(shù)據(jù)類型是無關(guān)緊要的，只要能實現(xiàn)需求。從技術(shù)角度來看，在還沒遇到數(shù)據(jù)庫性能瓶頸之前，選擇哪種數(shù)據(jù)類型也確實看不到什么優(yōu)勢。但是隨著業(yè)務(wù)數(shù)據(jù)激增，以前一點一滴的思考最終會帶來收益。

1.1 整數(shù)類型

如果字段能用整型存儲就盡量用整型。相比于字符類型，整型的計算更快。
盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型。更小的數(shù)據(jù)類型，占用更少的磁盤、內(nèi)存和CPU緩存，比如tinyint相比于int占用更少的空間。但是在ddl語句中int(10)和int(11)并不會實際改變存儲所需的空間，只會影響客戶端顯示的數(shù)據(jù)長度。
如果是非負整數(shù)使用unsingned屬性。unsingned屬性可以使正數(shù)上限提高一倍。

1.2 varchar和char

長度一定的字符串考慮使用char類型，比如32個字符的hash串。varchar是變長類型，會使用1到2個字節(jié)額外記錄數(shù)據(jù)長度。從磁盤角度來說varchar是按需分配空間，并不會存在浪費，但是char類型可能會存在浪費，不過從數(shù)據(jù)更新角度來說，varchar字段長度可能會改變，所以可能會造成頁分裂等問題，導致更多的磁盤io。從內(nèi)存分配角度來說，varchar字段總是會被分配最大長度的空間(ddl語句varchar(n)中的n)，相比于char，可能會造成更多的內(nèi)存浪費。

2.索引

mysql中索引的實現(xiàn)與具體的存儲引擎相關(guān)，本篇文章默認說的是innodb引擎。關(guān)于索引的一些基礎(chǔ)知識這里不再贅述，不了解的朋友可以參看《高性能mysql一書》。

2.1索引創(chuàng)建

創(chuàng)建索引之前可以看下整張表包含哪些索引，是否能滿足需求，如果不能，再考慮是不是可以在原先的索引做改造，比如把單列索引改造成多列索引。索引本身也會占用內(nèi)存磁盤，并且插入數(shù)據(jù)時需要更新索引，所以盡量減少重復的或者無用的索引。
請注意索引列的選擇性，如果你的語句中使用了索引但是該索引列的選擇性不高，數(shù)據(jù)庫引擎可能還是會采用全表掃描的方式進行查詢。可以使用一下式子計算列的選擇性，唯一索引的選擇是1，是性能最好的索引

mysql> select count(distinct(column)) / count(*) from table;

如果是在varchar列創(chuàng)建索引，如果不考慮排序和分組的情景，可以使用列前綴創(chuàng)建索引，并且應(yīng)該選擇足夠長的前綴以確保較高的選擇性，同時又不能太長。下面例子中的n就是前綴的長度，這個值需要根據(jù)實際情況計算得到。

mysql> alter table my_table add key idx_column (column(n));

如果要對一列創(chuàng)建索引，但是這一列的字符串太長，而且c中的前綴索引的方案并不能很好的工作，那么可以使用一些特別的技巧。列是不定長的，可以使用一定的算法得到定長字符串然后作為該列的一個字段存儲，并在計算列創(chuàng)建索引，查詢的時候可以對計算列進行索引?？梢允褂胏rc32等算法，考慮到?jīng)_突問題，查詢的時候除了比較計算列還要對原列進行比較。這是一個平衡內(nèi)存使用和查詢速度的解決方案。

mysql>alter table my_table add key idx_crc_column (crc_column);
mysql>insert into my_table column='xxx', crc_column=crc32('xxx');
mysql>select id from my_table where crc_column=crc32('yyy') and column='yyy'；

3.查詢優(yōu)化

3.1結(jié)果集獲取

不要在所有查詢語句中使用select *，也就是不要獲取你不需要的列。額外的字段會帶來額外的內(nèi)存、cpu、磁盤io的消耗。另外，select *會讓優(yōu)化器無法完成覆蓋索引掃描這類優(yōu)化，關(guān)于覆蓋索引掃描的概念一會會提到。
關(guān)注是否返回了多余的結(jié)果集，對并不需要的結(jié)果集，不要去獲取。多余的結(jié)果集會占用帶寬、內(nèi)存。比如當你確定只獲取一列數(shù)據(jù)的時候應(yīng)該使用limit 1限制返回的結(jié)果集，當然如果是唯一索引的查詢就不需要該限制，對于非唯一索引條件實際上還是可能會返回多余的數(shù)據(jù)的，如果是沒有索引的查詢，還會進行全表掃描，所以請注意查詢語句是否返回了多余的結(jié)果集。

3.2 sql優(yōu)化

優(yōu)化count語句。關(guān)于count的優(yōu)化，網(wǎng)上有很多的版本，到底是使用count(column)還是count(*)或者是count(0)。count有兩個作用，一個是統(tǒng)計某個列的數(shù)量，另一個是統(tǒng)計行數(shù)。如果是count某一列的話會判定該列的值是否為NULL。實際上如果優(yōu)化器判定被count的是一個常量如count(*)、count(1)、count(0)等，那么就不會就行NULL判定，只是簡單的獲取行數(shù)。
優(yōu)化回表查詢。innodb主鍵是聚簇索引，其它索引的葉子節(jié)點保存的是主鍵值，而不是行指針，所以利用輔助索引查詢的時候會存在回表的問題。比如某個表有三列，id列(主鍵)、time列(輔助索引)、column列(篩選列)，并且id和time都是單調(diào)遞增的列?，F(xiàn)在需要查詢某個時間段的數(shù)據(jù)，有兩種方案：

mysql> select count(*) from my_table where time between a and b and column='xxx'

mysql> select id from my_table where time>= a limit 1;  // id=x
mysql> select id from my_table where time<= b limit 1; // id=y
mysql> select count(*) from my_table where id between x and y and column='xxx';

在數(shù)據(jù)量不大的時候可能兩種查詢差不多，但是當a與b時間段中的數(shù)據(jù)達十萬級百萬級時，這兩者的性能差別很大。一方面使用輔助索引存在大量的回表查詢，另外一方面，使用id主鍵進行范圍查詢可以一次性順序讀取磁盤數(shù)據(jù)。
使用覆蓋索引。覆蓋索引的概念是如果一個索引包含(或者覆蓋)所有需要查詢的字段的值，我們就稱之為"覆蓋索引"。所以這是一個相對的概念，是相對查詢語句來說的，對于sql1可能是覆蓋索引，對于sql2就可能不是覆蓋索引了。這個概念跟其它的索引概念有所不一樣。我們并沒有利用這個所謂的"覆蓋索引"去"索引"數(shù)據(jù)，而是利用索引其它的特性，比如數(shù)據(jù)量小，能夠順序磁盤io，以及減少內(nèi)存使用量，最終達到快速獲取結(jié)果的目的。

mysql> alter table my_table add key idx_a_b (a, b);
mysql> select a from my_table where b="xxx";

上面這個例子中，select語句并不能使用前綴索引去"索引"數(shù)據(jù)，但是并不妨礙select語句使用idx_a_b索引優(yōu)化查詢，它可以順序掃描這個索引，并對每一行進行篩選。所以可以針對覆蓋查詢這種場景建立復合索引，那么查詢也就變成了覆蓋索引查詢。
優(yōu)化關(guān)聯(lián)查詢。關(guān)聯(lián)查詢join，mysql使用的是嵌套循環(huán)關(guān)聯(lián)，所以應(yīng)該盡量用小表驅(qū)動大表，盡量減少被驅(qū)動表掃描的次數(shù)(也就是小表的行數(shù))，并且被驅(qū)動的表在關(guān)聯(lián)的字段上建立索引，這樣可以加快關(guān)聯(lián)速度。
in和exists的選擇。

mysql> select a.id from a where a.id not in (select a_id from b) limit 100;
mysql> select a.id from a where not exists(select b.a_id from from b where b.a_id=a.id limit 1) limit 100;

上面兩個語句中都有子查詢，但是in是獨立的子查詢，而exist是關(guān)聯(lián)子查詢(跟a表相關(guān))。假設(shè)b表中的a_id字段有索引，考慮a表小(100行)b表大(100W行)的情況。使用in查詢會讀取所有b表所有的數(shù)據(jù)，占用大量內(nèi)存，并且做條件篩選的時候，not in相當于 a.id != n1 and a.id !=n2 and a.id !=n3，此時是不能使用索引優(yōu)化查詢的，所以性能不是很好。使用exists查詢會對a進行全表掃描(100行)，并且會對每一行數(shù)據(jù)進行一次exists子查詢，由于b表有索引，所以查詢很快。所以這種情況下明顯是not exists語句優(yōu)于not in語句的。

mysql> select a.id from a where a.id in (select a_id from b) limit 100;
mysql> select a.id from a where exists(select b.a_id from from b where b.a_id=a.id limit 1) limit 100;

下面一種情況類似，區(qū)別在于in語句可以利用索引進行查詢了，如果a表大b表小，應(yīng)該使用in語句，如果a表小b表大，應(yīng)該使用exists語句。可能實際的情況更加復雜，也可以考慮把子查詢轉(zhuǎn)換成關(guān)聯(lián)查詢，具體情況具體分析。

上面有的只是給出了一部分例子，其實還有更多可以優(yōu)化的點，弄清楚數(shù)據(jù)庫索引的原理會非常有幫助。

4.數(shù)據(jù)庫配置

4.1 內(nèi)存分配

設(shè)置恰當?shù)膇nnodb_buffer_pool_size。如果大部分表是innodb表，那么innodb緩沖池或許比其它任何東西更需要內(nèi)存。緩沖池緩沖了索引、行的數(shù)據(jù)、插入緩沖、鎖以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)。innodb還借助緩沖池幫助延遲寫入，這樣就可以合并多個寫入操作?？傊?，innodb嚴重依賴緩沖池，必須確保得到了足夠的內(nèi)存。
確定是否需要開啟查詢緩存query_cache_type，開啟查詢緩存有一定的好處，但是也會帶來一些額外的復雜的實現(xiàn)，比如需要確保緩存數(shù)據(jù)和磁盤數(shù)據(jù)一致，這就帶來了很多額外的消耗。如果有專門的緩存服務(wù)器（實際上如果要構(gòu)建高性能網(wǎng)站，那么緩存服務(wù)是必須的），這一配置可以關(guān)閉。
設(shè)置合適的innodb_log_buffer_size。innodb使用日志來減少提交事務(wù)時的開銷。核心原理是將隨機的io轉(zhuǎn)換成順序io，事務(wù)修改的數(shù)據(jù)和索引通常會映射到表空間的隨機位置，所以刷新這些變更到磁盤需要很多隨機io，利用日志記錄事務(wù)并順序?qū)懭胗脖P，一旦日志安全寫到硬盤，事務(wù)就持久化了，即使變更還沒寫到數(shù)據(jù)文件，innodb可以重放日志并且恢復已經(jīng)提交的事務(wù)。如果是大內(nèi)存服務(wù)器，分配32MB~128MB的日志緩沖，可以幫助避免壓力瓶頸。

4.2 IO優(yōu)化

關(guān)于innodb事務(wù)日志的配置，除了緩沖區(qū)的大小以外，還有一個決定何時將日志寫入磁盤的配置innodb_flush_log_at_trx_commit，0表示每秒刷新一次，但是事務(wù)提交時不做任何事，1表示每次事務(wù)提交都刷新到持久化存儲（默認配置），2表示每次提交時把日志緩沖寫到日志文件，但是并不刷新。可能不了解linux系統(tǒng)的同學會有點疑惑2是個什么意思，linux系統(tǒng)存在文件緩存，所以當你寫入日志時內(nèi)核只是把這部分數(shù)據(jù)寫入文件緩存當中，并把該頁設(shè)置成臟頁，后續(xù)何時刷到磁盤由操作系統(tǒng)決定，所以這里要區(qū)分清楚"把日志緩沖寫到日志文件"和"把日志持久化到存儲"是不同的概念。所以如果是設(shè)置2，mysql崩潰了并不會丟失任何事務(wù)，但是如果是服務(wù)器掛了，則還是可能會丟失一部分事務(wù)。1是默認的設(shè)置，可以保證不會丟失任何已經(jīng)提交的事物，除非相關(guān)的系統(tǒng)調(diào)用是一個偽刷新。如果不需要嚴格的持久化，可以將值設(shè)為2或0。

5.回顧

優(yōu)化的方式有很多，但大致都脫離不了cpu、磁盤io、緩存、網(wǎng)絡(luò)、算法的范疇，當然這里指的是單機服務(wù)的優(yōu)化。從頂向下的思考方式可以讓你非常輕松的理解優(yōu)化的點。innodb使用b+tree作為索引加快查詢速度，這與算法相關(guān)，覆蓋索引、事務(wù)日志、緩沖區(qū)等優(yōu)化了磁盤io。優(yōu)化的過程中也要記得二八原則，我們不需要做到十全十美，實際上也做不到，優(yōu)化重要的點可能可以解決大部分問題。另外如果一個優(yōu)化的方案讓你感到很復雜，那么最好不要使用該方案，出了問題解決起來也將非常的棘手。當然還有其它的沒提到的一些點，比如讀寫分離、表分區(qū)等技術(shù)都可以幫助解決一些問題，這一部分請自行了解。