MySQL操作規範（總結）

使用者許可權管理

建立使用者

命令：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許可權一覽：

許可權	許可權級別	許可權說明
CREATE	資料庫、表或索引	建立資料庫、表或索引許可權
DROP	資料庫或表	刪除資料庫或表許可權
GRANT OPTION	資料庫、表或儲存的程式	賦予許可權選項
REFERENCES	資料庫或表
ALTER	表	更改表，比如新增欄位、索引等
DELETE	表	刪除資料許可權
INDEX	表	索引許可權
INSERT	表	插入許可權
SELECT	表	查詢許可權
UPDATE	表	更新許可權
CREATE VIEW	檢視	建立檢視許可權
SHOW VIEW	檢視	檢視檢視許可權
ALTER ROUTINE	儲存過程	更改儲存過程許可權
CREATE ROUTINE	儲存過程	建立儲存過程許可權
EXECUTE	儲存過程	執行儲存過程許可權
FILE	伺服器主機上的檔案訪問	檔案訪問許可權
CREATE TEMPORARY TABLES	伺服器管理	建立臨時表許可權
LOCK TABLES	伺服器管理	鎖表許可權
CREATE USER	伺服器管理	建立使用者許可權
PROCESS	伺服器管理	檢視程式許可權
RELOAD	伺服器管理	執行flush-hosts,flush-logs, flush-privileges,flush-status,flush-tables,flush-threads,refresh,reload等命令的許可權
REPLICATION CLIENT	伺服器管理	複製許可權
REPLICATION SLAVE	伺服器管理	複製許可權
SHOW DATABASES	伺服器管理	檢視資料庫許可權
SHUTDOWN	伺服器管理	關閉資料庫許可權
SUPER	伺服器管理	執行kill執行緒許可權

MySQL的許可權如何分佈，就是針對表可以設定什麼許可權，針對列可以設定什麼許可權等等，這個可以從官方文件中的一個表來說明：

許可權分佈	可能的設定的許可權
表許可權	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列許可權	'Select', 'Insert', 'Update', 'References'
過程許可權	'Execute', 'Alter Routine', 'Grant'

MySQL許可權控制：

1、GRANT命令使用說明：

建立一個只允許從本地登入的超級使用者fog，並允許將許可權賦予別的使用者，密碼為：test123

mysql> grant all privileges on *.* to fog@'localhost' identified by "test123" with grant option;

GRANT命令說明：

ALL PRIVILEGES 是表示所有許可權，你也可以使用select、update等許可權。

ON 用來指定許可權針對哪些庫和表。

*.* 中前面的*號用來指定資料庫名，後面的*號用來指定表名。

TO 表示將許可權賦予某個使用者。

fog@'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 root@localhost                                           |

+---------------------------------------------------------------------+

| 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

[root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd

格式：mysqladmin -u使用者名稱 -p舊密碼 password 新密碼

3）、用update直接編輯user表

mysql> use mysql

mysql> 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'); 3520061480

SELECT 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、禁止在資料庫表中儲存明文密碼。

採用加密字串儲存密碼，並保證密碼不可解密，同時採用隨機字串加鹽保證密碼安全。防止資料庫資料被公司內部人員或駭客獲取後，採用字典攻擊等方式暴力破解使用者密碼。

15.表必須有主鍵,推薦使用UNSIGNED自增列作為主鍵。

表沒有主鍵,INNODB會預設設定隱藏的主鍵列;沒有主鍵的表在定位資料行的時候非常困難,也會降低基於行復制的效率。

10、表字符集使用UTF8,必要時可申請使用UTF8MB4字符集。

a)UTF8字符集儲存漢字佔用3個位元組,儲存英文字元佔用一個位元組。

b)UTF8統一而且通用,不會出現轉碼出現亂碼風險。

c)如果遇到EMOJ等表情符號的儲存需求,可申請使用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),以獲得效能提升。

關於SQL

1、用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 UPDATE

b)REPLACE INTO

c)INSERT IGNORE

d)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資料庫中存放業務邏輯。

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