## 出發點 比如，現在有這么一個問題，常見的一個面試題： 有一張users表，數據量在五千萬以上，存在一條查詢語句： `SELECT * FROM users WHERE name LIKE '%明%' AND sex='男' AND age=32 AND created_at BETWEEN 1388505600 AND 1420041600`; `就是說從users表中找出創建時間在14年到15年之間的年齡在32歲，名字中帶有‘明’字的男性用戶` 追溯到數據表的設計，sql查詢語句的調優，并且需要考慮到這種大表的數據插入時候的注意事項,數據分頁及未來數據表管理時可能遇見的問題及解決方案 * * * 本文將會從存儲層，設計層，sql語句層和架構層進行優化： ## 存儲層 1. ### 存儲引擎的選取 * 這里只展開InnoDB和MyIsam(選擇哪個存儲引擎需要判斷當前的業務場景) * #### MyIsam表： * **支持表級鎖，鎖的顆粒度比較大**，所以被鎖定的資源的爭用情況會比其他的鎖定級別會多，會降低并發處理的能力。 解決方法是可以設置`Concurrent Insert(并發插入)` * 數據表的存儲位置：在`mysql安裝目錄/data/數據庫名稱`下，**分為數據表結構，數據表索引，數據表數據三個文件分別進行存儲**。這種索引和數據相分離，是通過物理地址進行關聯的，這種索引結構也叫做‘非聚合型索引’，所以可以選擇直接拷貝對應表的文件進行數據表的備份。在進行數據查詢的時候，`是先查找索引文件中數據對應的記錄地址然后在根據該地址找到對應的數據文件中對應的數據`。因此這種索引的方式也叫做“非聚集的”。 * **支持數據表的壓縮**，犧牲數據表的更新操作，換取快速的查找速度。在CMD中執行`myisamchk.exe -rq 表名`。 使用命令`myisamchk.exe --unpack 表名`進行解壓縮。然后使用`flush table 表名`進行刷新數據表 * **支持全文索引** * **不支持外鍵** * 表的具體行數：保存有表的具體行數。在針對一個140萬的數據表的時候，count(\*)的速度很快：0.0003秒 * **總結**：myisam是mysql最為古老的存儲引擎之一，對于以讀為主的非事務性系統來說，myisam無疑是最優先考慮的對象。但是對于存在一定的并發量的系統，還是不建議使用`Myisam`的，因為目前隨著`Innodb`引擎的成熟，`Myisam`對于高并發時表鎖的消耗太大 * #### InnoDB表 * **支持行級鎖**，帶來的性能方面的鎖好比較大，但是整體的并發性能卻遠遠優于MyIsam表。但是如果使用不當可能會發生死鎖，關于避免死鎖的問題，并沒有深入研究過，大體的發生死鎖的原因就是：由多個并發事務；每個事物都持有了鎖；每個事務為了完成相關的邏輯都需要繼續持有鎖；多個事務之間產生加鎖的循環等待－>最終形成死鎖 * **支持外鍵，支持事務**。 * 在進行select count(\*)的時候，如果數據庫中只存在一個primary key 的時候，**執行count的時候速度會很慢**，一張140萬的數據表，執行的速度是6s多。但是myisam的count時間卻只有0.00幾秒。但是如果在InnoDB表中在添加一個索引的時候，速度就會比較快。詳情看：[](https://segmentfault.com/a/1190000003793230)[https://segmentfault.com/a/11...](https://segmentfault.com/a/1190000003793230) * 數據表內容的存儲位置：.frm文件用來存儲表結構定義相關的元數據，表的索引和數據存放在一起。用戶可以自定義，默認的初始化存儲位置（windows）是在c:/programData/mysql?下。`種數據和索引存儲在一個文件中的索引結構叫做‘聚合型’索引` 2. ### 字段的存儲的類型的類型推薦 * **字段應該優先選擇 數值型進行存儲**，整形數據比起字符型處理的開銷會更小，比如性別，是否這些可以使用enum進行存儲。ip地址，時間等字段也存儲成整形。可以使用數值型和枚舉類型的字段進行不使用字符型進行存儲 * **越小的數據類型越好**。越小的數據類型在硬盤，cpu緩存和內存上的使用空間都更小，處理起來會更快 * **盡量避免使用null**。在創建數據表的時候應該指定列為 NOT NULL ,然后設定默認值 ## 設計層 1. ### 適當的添加索引 * **添加索引可以增快數據查詢的速度，但是對應的在數據的寫入的時候需要去維護索引的數據**，所以在數據的插入和更新等操作時速度回變慢。所以添加索引需要注意在常用的查詢字段上面進行添加 * ?這對于這里的需求來講，創建一個`name,age,created_at`字段上的聯合索引，在后面的查詢條件的排列的地方先查詢`name`字段，然后是`age`字段，然后`created_at`字段，最后在是`sex`字段 * 具體的關于索引的內容可以看[](https://segmentfault.com/a/1190000005985640)[https://segmentfault.com/a/11...](https://segmentfault.com/a/1190000005985640) 2. ### 適時的進行分表和分區 * 分區 * 分區就是將整個的業務模塊分散到不同的服務器上進行存儲，比如說用戶模塊，文章模塊，相冊模塊等分別存在不同的服務器上完成分區 * 水平分表 * 物理分表：可以根據求余的方式或者hash或根據當前月份等方式進行 * 手動創建多個數據表,主要是根據當前記錄的索引值進行判斷該數據所在的位置 * 數據查詢 ~~~shell $id = $_GET['id']; $mod = $id%5; $sql = "SELECT * from goods_$mod WHERE id=$id"; ~~~ * 新增數據　 //在新增數據的時候需要一張臨時表去判斷當前表中的最大id值為多少，選擇對應的數據存儲的數據表 ~~~perl $sql = "INSERT INTO `臨時表` values null"; $new_id = "SELECT mysql_insert_id()"; $mod = $new_id%5; $sql = "INSERT INTO goods_$mod VALUES ($new_id, 內容1, 內容2)"; ~~~ * 邏輯分表（嚴格上來講，是在數據庫的邏輯層進行分區） * **為了保證分區時的查詢效率，必須保證添加的分區字段為主鍵或unique key** * **在新建了分區之后，在查看數據表的存儲文件的結構可以發現，數據表的索引和數據內容已經被單獨拿出去存儲了** * **在訪問分區表時，在where條件中一定要帶上分區列，即使是看似多余的，因為查詢優化器會根據該列鎖定數據所在的分區，不然會對所有數據掃描** * key分區(求余) ~~~pgsql CREATE TABLE test_key( id int not null auto_increment, title varchar(32) not null default '', price decimal(10,2) not null default 0, created_at datetime not null, PRIMARY KEY (id,created_at) ) engine=myisam charset=utf8 partition by key(id) partitions 5; ~~~ 1 * hash分區(求余) ~~~sql CREATE TABLE test_hash( id int not null auto_increment, title varchar(32) not null default '', price decimal(10,2) not null default 0, created_at datetime not null , PRIMARY KEY (id,created_at) ) engine=myisam charset=utf8 partition by hash(month(created_at)) partitions 5; ~~~ * list分區(范圍) ~~~sql CREATE TABLE test_list( id int not null auto_increment, title varchar(32) not null default '', price decimal(10,2) not null default 0, created_at datetime NOT NULL, PRIMARY KEY (id,created_at) ) engine=myisam charset=utf8 partition by list(month(created_at))( partition spring values in (3,4,5), partition summer values in (6,7,8), partition autumn values in (9,10,11), partition winter values in (12,1,2) ); ~~~ * range分區(范圍) ~~~sql CREATE TABLE test_list( id int not null auto_increment, title varchar(32) not null default '', price decimal(10,2) not null default 0, created_at datetime NOT NULL, PRIMARY KEY (id,created_at) ) engine=myisam charset=utf8 partition by range(year(created_at))( partition oldest values less than 1980, partition old values less than 1990, partition middle values less than 2010, partition new values less than 2010 ); ~~~ * 垂直分表 * 對于一張很大的數據表，比如`user`表，`username,password,age`等字段是經常被使用到的字段，可以放在一張表中，表中其他不太常用的字段(不會拿來當作查詢條件的字段)，如`person_info,profile`等可以拿出到一張單獨的表中進行存儲，這樣可以保證主表在數據量很大的時候性能下降不會太嚴重。 ## 架構層 1.配置mysql集群，完成數據的讀寫分離 * ?基本原理：1.master記錄自己改變了的記錄的二進制文件（binlog），在每個事務執行完畢之后，將這些改變記錄在二進制文件中；2.在slave上存在兩個進程：讀取master上的二進制文件到自己的中繼文件中，在中繼文件中讀取更新的事件內容并同步到自己的數據庫中 * 可以使用mysql官方提供的代理層產品完成`MysqlProxy`相關的功能：[](https://segmentfault.com/a/1190000003716617#articleHeader1)[https://segmentfault.com/a/11...](https://segmentfault.com/a/1190000003716617#articleHeader1) ## sql語句層進行優化 * #### 從給定的題目出發 * 字段：在進行數據的查詢的時候，在查詢字段的選擇上，使用`對應的字段`代替`select *`，這樣做對查詢速度不會有明顯的提升，但是可以節省內存 * 條件：在sql語句的條件的書寫的時候，條件的排列順序應該是以字段上的數據差異性較大的列排列在最左端，也就是最能夠區分出更少數據的列優先排列在最左端。比如說在常規的業務邏輯下，`age`字段應該在`sex`?字段的左側 * 分頁：在后臺的數據進行分頁的時候，每頁顯示150條數據，在查看10000頁的數據的時候肯定會很慢，使用`where id > 1500000 limit 150`的寫法代替`limit 10000,150`.這樣可以極大的提高查詢的速度 * 在只查詢一條數據的時候，使用`limit 1`,這樣mysql在進行搜索的時候，找到了一條數據就不會在鄉下進行搜索 * 子查詢：在執行一條資查詢：`select ... from t1 where t1.uid IN (select uid from t2 )`,這條子查詢相當于`select ... from t1 where exists(select 1 from t2 where t2.uid=t1.id)`.這樣一來相當于將兩個結果集中的數據做乘法，相比于連接查詢，速度會很慢 * 連表查詢：將復雜的JOIN查詢語句改寫成針對于單表的sql查詢語句。在JOIN多個表的時候，可能導致更多的鎖定和堵塞 * 注意數據的飲食轉換。比如說查詢的字段的類型為`varchar`,那么在寫where條件的時候，`where name='11'`會比`where name=11`更快，因為傳入的字段類型是`int`,會導致程序進行全表掃描