MySQL操作規範總結

標籤：利用   oca   過程   primary   初始化   包括   shell   內容   into   

來源：靜以致遠√團團

 

使用者權限管理建立使用者命令：CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
說明：Username所建立的使用者名稱
host 指定該使用者在哪個主機上可以登陸,如果是本機使用者可用localhost, 如果想讓該使用者可以從任意遠程主機登陸,可以使用萬用字元%.
password該使用者的登陸密碼,密碼可以為空白,如果為空白則該使用者可以不要求輸入密碼登陸伺服器.如: CREATE USER ‘pig‘@‘192.168.1.101_‘ IDENDIFIED BY ‘123456‘;
註：建立使用者時也可以向user表中添加使用者資訊實現添加使用者。
許可權分配MySQL許可權原則：許可權控制主要是出於安全因素，因此需要遵循一下幾個經驗原則：

1. 只授予能滿足需要的最小許可權，防止使用者越權。如使用者只是需要查詢，那就只賦予select許可權，不要給使用者賦予update、insert或者delete許可權。
2. 建立使用者的時候限制使用者的登入主機，一般是限制成指定IP或者內網IP段。
3. 初始化資料庫的時候刪除沒有密碼的使用者。安裝完資料庫的時候會自動建立一些使用者，這些使用者預設沒有密碼。
4. 為每個使用者佈建滿足密碼複雜度的密碼。
5. 定期清理不需要的使用者。回收許可權或者刪除使用者。

MYSQL許可權一覽：
MYSQL的許可權如何分布，就是針對錶可以設定什麼許可權，針對列可以設定什麼許可權等等，這個可以從官方文檔中的一個表來說明：
MySQL許可權控制1、GRANT命令使用說明：建立一個只允許從本地登入的超級使用者fog，並允許將許可權賦予別的使用者，密碼為：test123
mysql> grant all privileges on *.* to [email protected]‘localhost‘ identified by "test123" with grant option;
GRANT命令說明：
ALL PRIVILEGES 是表示所有許可權，你也可以使用select、update等許可權。
ON 用來指定許可權針對哪些庫和表。
*.* 中前面的*號用來指定資料庫名，後面的*號用來指定表名。
TO 表示將許可權賦予某個使用者。
[email protected]‘localhost‘表示fog使用者，@後面接限制的主機，可以是IP、IP段、網域名稱以及%，%表示任何地方。注意：這裡%有的版本不包括本地，以前碰到過給某個使用者佈建了%允許任何地方登入，但是在本地登入不了，這個和版本有關係，遇到這個問題再加一個localhost的使用者。
IDENTIFIED BY 指定使用者的登入密碼。
WITH GRANT OPTION 這個選項表示該使用者可以將自己擁有的許可權授權給別人。
注意：經常有人在建立操作使用者的時候不指定WITH GRANT OPTION選項導致後來該使用者不能使用GRANT命令建立使用者或者給其它使用者授權。
2、重新整理許可權使用這個命令使許可權生效，尤其是你對那些許可權表user、db、host等做了update或者delete更新的時候。以前遇到過使用grant後許可權沒有更新的情況，只要對許可權做了更改就使用FLUSH PRIVILEGES命令來重新整理許可權。
mysql> flush privileges;
3、查看許可權查看目前使用者的許可權：mysql> show grants;+---------------------------------------------------------------------+| Grants for [email protected]                                           |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION        |+---------------------------------------------------------------------+
查看某個使用者的許可權：mysql> show grants for ‘fog‘@‘%‘;
4、回收許可權mysql> revoke delete on *.* from ‘fog‘@‘localhost‘;
5、刪除使用者mysql> drop user ‘fog‘@‘localhost‘;Query OK, 0 rows affected (0.01 sec)
6、對賬戶重新命名mysql> rename user ‘fog‘@‘%‘ to ‘jim‘@‘%‘;
7、修改密碼
1、用set password命令mysql> SET PASSWORD FOR ‘root‘@‘localhost‘ = PASSWORD(‘123456‘);
2、用mysqladmin[[email protected] ~]# mysqladmin -uroot -p123456 password 1234abcd格式：mysqladmin -u使用者名稱 -p舊碼 password 新密碼
3、用update直接編輯user表mysql> use mysqlmysql> update user set PASSWORD = PASSWORD(‘1234abcd‘) where user = ‘root‘;mysql> flush privileges;
SQL開發建議關於命名1、庫名、表名、欄位名必須使用小寫字母,並採用底線分割。
2、庫名、表名、欄位名最多64個字元,盡量不要超過32個字元。
3、庫名、表名、欄位名禁止使用MySQL保留字。當庫名、表名、欄位名等屬性含有保留字時,SQL語句必須用反引號引用屬性名稱,這將使得SQL語句書寫、SHELL指令碼中變數的轉義等變得非常複雜。
4、盡量不使用分區表。分區表對分區鍵有嚴格要求;分區表在表變大後,執行DDL、SHARDING、單表恢複等都變得更加困難。因此禁止使用分區表,並建議業務端手動SHARDING。
關於建表1、使用INNODB儲存引擎。INNODB引擎是MySQL5.5版本以後的預設引擘,支援事務、行級鎖,有更好的資料恢複能力、更好的並發效能,同時對多核、大記憶體、SSD等硬體支援更好,支援資料熱備份等,因此INNODB相比MyISAM有明顯優勢。
2、建議使用UNSIGNED儲存非負數值。同樣的位元組數,非負儲存的數值範圍更大。如TINYINT有符號為 -128-127,無符號為0-255。
3、建議使用INT UNSIGNED儲存IPV4。UNSINGED INT儲存IP地址佔用4位元組,CHAR(15)則佔用15位元組。另外,電腦處理整數類型比字串類型快。使用INT UNSIGNED而不是CHAR(15)來儲存IPV4地址,通過MySQL函數inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函數,需要使用DECIMAL或兩個BIGINT來儲存。例如:SELECT INET_ATON(‘209.207.224.40‘); 3520061480SELECT INET_NTOA(3520061480); 209.207.224.40
4、強烈建議使用TINYINT來代替ENUM類型。ENUM類型在需要修改或增加枚舉值時,需要線上DDL,成本較大;ENUM列值如果含有數字類型,可能會引起預設值混淆。
5、使用VARBINARY儲存大小寫敏感的變長字串或二進位內容。VARBINARY預設區分大小寫,沒有字元集概念,速度快。
6、INT類型固定佔用4位元組儲存,例如INT(4)僅代表顯示字元寬度為4位,不代表格儲存體長度。
數實值型別括弧後面的數字只是表示寬度而跟儲存範圍沒有關係,比如INT(3)預設顯示3位,空格補齊,超出時正常顯示,python、java用戶端等不具備這個功能。
7、區分使用DATETIME和TIMESTAMP。儲存年使用YEAR類型。儲存日期使用DATE類型。 儲存時間(精確到秒)建議使用TIMESTAMP類型。
DATETIME和TIMESTAMP都是精確到秒,優先選擇TIMESTAMP,因為TIMESTAMP只有4個位元組,而DATETIME有8個位元組。同時TIMESTAMP具有自動賦值以及自動更新的特性。
注意:在5.5和之前的版本中,如果一個表中有多個timestamp列,那麼最多隻能有一列能具有自動更新功能。
8、將大欄位、訪問頻率低的欄位拆分到單獨的表中儲存,分離冷熱資料。
有利於有效利用緩衝,防止讀入無用的冷資料,較少磁碟IO,同時保證熱資料常駐記憶體提高快取命中率。
9、禁止在資料庫表中儲存純文字密碼。採用加密字串儲存密碼，並保證密碼不可解密，同時採用隨機字串加鹽保證密碼安全。防止資料庫資料被公司內部人員或駭客擷取後，採用字典攻擊等方式暴力破解使用者密碼。
表必須有主鍵,推薦使用UNSIGNED自增列作為主鍵。
表沒有主鍵,INNODB會預設設定隱藏的主鍵列;沒有主鍵的表在定位元據行的時候非常困難,也會降低基於行複製的效率。
10、表字元集使用UTF8,必要時可申請使用UTF8MB4字元集。

• UTF8字元集儲存漢字佔用3個位元組,儲存英文字元佔用一個位元組。
• UTF8統一而且通用,不會出現轉碼出現亂碼風險。
• 如果遇到EMOJ等Emoji的儲存需求,可申請使用UTF8MB4字元集。

11、採用合適的分庫分表策略。例如千庫十表、十庫百表等。
採用合適的分庫分表策略,有利於業務發展後期快速對資料庫進行水平分割,同時分庫可以有效利用MySQL的多線程複製特性。
關於索引1、禁止冗餘索引。索引是雙刃劍,會增加維護負擔,增大IO壓力。(a,b,c)、(a,b),後者為冗餘索引。可以利用首碼索引來達到加速目的,減輕維護負擔。
2、禁止重複索引。primary key a;uniq index a;重複索引增加維護負擔、佔用磁碟空間,同時沒有任何益處。
3、不在低基數列上建立索引,例如“性別”。大部分情境下,低基數列上建立索引的精確尋找,相對於不建立索引的全表掃描沒有任何優勢,而且增大了IO負擔。
4、合理使用覆蓋索引減少IO,避免排序。覆蓋索引能從索引中擷取需要的所有欄位,從而避免回表進行二次尋找,節省IO。INNODB儲存引擎中, secondary index(非主鍵索引,又稱為輔助索引、二級索引)沒有直接儲存行地址,而是儲存主索引值。如果使用者需要查詢secondary index中所不包含的資料列,則需要先通過secondary index尋找到主索引值,然後再通過主鍵查詢到其他資料列,因此需要查詢兩次。覆蓋索引則可以在一個索引中擷取所有需要的資料,因此效率較高。主鍵查詢是天然的覆蓋索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主鍵,適當時候可以將索引添加為index(uid,email),以獲得效能提升。
關於SQL1、用IN代替OR。SQL語句中IN包含的值不應過多,應少於1000個。
IN是範圍尋找,MySQL內部會對IN的列表值進行排序後尋找,比OR效率更高。
2、用UNION ALL代替UNION。UNION ALL不需要對結果集再進行排序。
3、盡量不使用order by rand()。order by rand()會為表增加一個偽列,然後用rand()函數為每一行資料計算出rand()值,然後基於該行排序, 這通常都會產生磁碟上的暫存資料表,因此效率非常低。建議先使用rand()函數獲得隨機的主索引值,然後通過主鍵擷取資料。
4、建議使用合理的分頁方式以提高分頁效率。
5、SELECT只擷取必要的欄位,盡量少使用SELECT *。
6、SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函數。
語句級複製情境下,引起主從資料不一致;不確定值的函數,產生的SQL語句無法利用QUERY CACHE。
7、減少與資料庫互動次數,盡量採用批量SQL語句。
使用下面的語句來減少和db的互動次數:a)INSERT ... ON DUPLICATE KEY UPDATEb)REPLACE INTOc)INSERT IGNOREd)INSERT INTO VALUES()
8、拆分複雜SQL為多個小SQL,避免大事務。簡單的SQL容易使用到MySQL的QUERY CACHE;減少鎖表時間特別是MyISAM;可以使用多核CPU。
9、對同一個表的多次alter操作必須合并為一次操作。
10、盡量少使用預存程序、觸發器、視圖、自訂函數等。
這些進階特性有效能問題,以及未知BUG較多。商務邏輯放到資料庫會造成資料庫的DDL、SCALE OUT、SHARDING等變得更加困難。
其他1、控制super許可權的應用程式帳號存在。
2、提交線上建表改表需求,必須詳細註明涉及到的所有SQL語句(包括INSERT、DELETE、UPDATE),便於DBA進行審核和最佳化。
並不只是SELECT語句需要用到索引。UPDATE、DELETE都需要先定位到資料才能執行變更。因此需要業務提供所有的SQL語句便於DBA審核。
3、不要在MySQL資料庫中存放商務邏輯。

資料庫是有狀態的服務,變更複雜而且速度慢,如果把商務邏輯放到資料庫中,將會限制業務的快速發展。建議把商務邏輯提前,放到前端或中間邏輯層,而把資料庫作為儲存層,實現邏輯與儲存的分離。

MySQL操作規範總結