mysql優化 | 儲存引擎，建表，索引，sql的優化建議

兩顆西柚發表於2019-02-01
MySql優化儲存引擎索引

個人對於選擇儲存引擎，建表，建索引，sql優化的一些總結，給讀者提供一些參考意見

推薦訪問我的個人網站，排版更好看： chenmingyu.top/mysql-optim…

儲存引擎

mysql中檢視支援的引擎的sql：

show engines; 
複製程式碼

在這裡插入圖片描述

日常工作中使用較多的儲存引擎對比：InnoDB，MyISAM

InnoDB MyISAM
儲存限制 64T 256T
支援事務 yes no
支援索引 yes yes
支援全文索引 no yes
支援資料快取 yes no
支援外來鍵 yes no
支援Hash索引 no no

從MySQL5.6版本開始InnoDB已經支援建立全文索引了

innodb

支援提交、回滾和崩潰恢復能力的事物安全（ACID），支援行鎖，支援外來鍵完整性約束

適合場景

  • 需要事務處理
  • 表資料量大，高併發操作
MyISAM

MyISAM儲存引擎提供了高速檢索和儲存的能力，支援全文索引

適合場景

  • 很多count計算的
  • 查詢非常頻繁的

其餘幾種儲存引擎

MEMORY引擎

資料只儲存在記憶體中，因為是在記憶體中，擁有極高的插入，更新，查詢的效率，但是重啟後資料都會丟失，表級鎖，併發效能低。

MERGE引擎

merge表是一組MyISAM表的組合，所以merge表是沒有資料的，對這個表的操作實際上是操作內部的MyISAM表，將多個MyISAM表合併適合做一些報表之類的操作。

ARCHIVE引擎

僅支援插入和查詢，使用zlib壓縮庫，在記錄被請求的時候實時壓縮，不支援事務，支援行級鎖，適合儲存大量的日誌資料。

個人是推薦Innodb引擎的，公司部門裡也是規定新建表的時候必須使用Innodb引擎，Innodb引擎較MyISAM引擎可以提供更多的功能，不是很實時的查詢場景可以使用快取，近實時的查詢可以使用es，當然了這只是個人看法，針對不同的場景選擇不同的儲存引擎還是很有必要滴。所以在知道不同儲存引擎的特性之後，才可以根據不同業務需求選擇合適的儲存引擎。

建表原則

在建表的時候儘量遵循以下原則

  1. 儘量選擇小的資料型別，資料型別選擇上儘量tinyint(1位元組)>smallint(2位元組)>int(4位元組)>bigint(8位元組)，比如邏輯刪除yn欄位上（1代表可用，0代表）就可以選擇tinyint（1位元組）型別

  2. 儘量保證欄位資料型別長度固定

  3. 儘量避免使用null，使用null的欄位查詢很難優化，影響索引，可以使用0或''代替

  4. 避免寬表，能拆分就拆分，一個表往往跟一個實體域對應，就像設計物件的時候一樣，保持單一原則

  5. 儘量避免使用text和blob，如果非使用不可，將型別為text和blob的欄位在獨立成一張新表，然後使用主鍵對應原表

  6. 禁止使用float或double型別，這個坑超大，float或double存在精度問題，在進行比較或者加減操作的時候會丟失精度導致資料異常，凡是使用float或double型別的時候考慮下可不可使用int或bigint代替。比如金額，以元為單位使用float或double型別的時候，可以考慮以分為單位使用int，bigint型別代替，然後由業務程式碼進行單位的轉換。

  7. 每張表都加上createUser,createTime.updateUser,updateTime欄位

  8. 起名字要規範，包括：庫名，表名，欄位名，索引名

  9. 查詢頻繁使用的欄位記得加索引

  10. 儘量避免使用外來鍵，不用外來鍵約束，效能更高，然後資料的完整性有程式進行管理

  11. 如果表的數量可以預測到非常大，最好在建表的時候，就進行分表，不至於一時間資料量非常大導致效率問題

    未完待補充，，，

索引

索引是為來加速對錶中資料行中的檢索而建立的一種分散的資料結果,是針對表而建立的，它是由資料頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指標,以便加速檢索物理資料，建立索引的目的在於提高查詢效率，innodb的索引都是基於b tree實現的

索引型別

普通索引：最基本的索引，無限制

#方式1
CREATE INDEX idx_username ON sys_user(user_name(32)); 
#方式2
ALTER table sys_user ADD INDEX idx_username(user_name(32))
複製程式碼

主鍵索引：一個表只能有一個主鍵索引，且不能為空

一般建表時同時建立了主鍵索引

CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) DEFAULT NULL,
  `pass_word` varchar(32) DEFAULT NULL,
  `token` varchar(32) DEFAULT NULL,
  `token_expire` int(11) DEFAULT NULL,
  `yn` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=348007 DEFAULT CHARSET=utf8;
複製程式碼

唯一索引：與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一

CREATE UNIQUE INDEX idx_token ON sys_user(token_expire)
複製程式碼

組合索引：指多個欄位上建立的索引，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。使用組合索引時遵循最左字首集合

ALTER TABLE sys_user ADD INDEX idx_un_te (user_name(32),token_expire); 
複製程式碼

全文索引：用來查詢文字中的關鍵字，而不是直接與索引中的值相比較。只有char、varchar，text 列上可以建立全文索引

CREATE FULLTEXT INDEX idx_ ON sys_user(pass_word)
複製程式碼
建立使用索引的原則
  1. 索引的欄位儘量要小，根據索引查詢資料的快慢取決於b tree的高度，當資料量恆定的時候，位元組越少，存的索引的數量就越多，樹的高度就越會越低
  2. 遵循索引的最左匹配原則
  3. 注意使用like的時候儘量不要使用“%a%”，這樣的不走索引，可以使用“a%”，走索引
  4. 不要在索引的列上進行計算，比如 select * from sys_user where token_expire+1 = 10000，這樣的語句 不會走有索引
  5. 什麼樣的欄位建索引，就是那種頻繁在where，group by，order by中出現的列，最好加上索引
索引的缺點

雖然索引的可以提高查詢的效率，但是在進行insert，update，和delete的時候會降低效率，因為在儲存資料的同時也會去儲存索引。

不要在一個表裡建過多的索引，問題跟上面一樣，在運算元據的時候效率降低，而且資料量少的表要看情況建索引，如果建索引跟沒建索引的效果差不多少的情況下就不要建索引了，如果是資料量大的表，就需要建索引去優化查詢效率。

explain分析sql

可以使用explain去分析sql的執行情況，比如

explain select * from sys_user where token_expire = 10000; 
複製程式碼

在這裡插入圖片描述

在阿里的開發手冊中提到過，sql效能優化的標準：至少要達到range，要求ref級別，如果可以是consts最好

說明一下，這裡的級別指的就是上圖的type欄位：

  • consts 是指單表中最多隻有一個匹配行（主鍵或唯一索引）
  • ref 指的是使用普通索引
  • range 是指對索引進行範圍查詢

sql優化

關於sql語句的優化主要是兩方面，一個是在建sql的時候需要注意的問題，另一個就是在發現有慢sql的時候可以根據不同情況進行分析，然後優化sql

優化的建議

  1. 查詢的時候一定要記得使用limit進行限制

  2. 對於結果只需要一條資料的查詢用limit 1進行限制

  3. 使用count(*)來統計行數或者使用count(主鍵)來查詢，使用count(列)的時候，不會統計此列為null的情況

  4. 不要使用select * 來查資料，使用select 需要的列名，這樣的方式去查詢

  5. 使用join連結代替子查詢

  6. 不要使用外來鍵，外來鍵的約束可以放在程式裡解決

  7. 控制一下in操作的集合數量，不要太大了

  8. 針對慢查詢使用explain去分析原因，然後優化sql，讓其儘量走索引

上面說的四個方面就是我目前對於sql優化各個方面的一些總結，希望可以給大家提供一個參考，有問題或者更好意見的可以評論留言，大家一起交流交流

相關文章