MySQL操作規范有哪些

操作規范有哪些，相信很多沒(méi)有經(jīng)驗的人對此束手無(wú)策，為此本文總結了問(wèn)題出現的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

用戶(hù)權限管理

創(chuàng )建用戶(hù)

命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password';

說(shuō)明：

Username所創(chuàng )建的用戶(hù)名

host 指定該用戶(hù)在哪個(gè)主機上可以登陸,如果是本地用戶(hù)可用localhost, 如果想讓該用戶(hù)可以從任意遠程主機登陸,可以使用通配符%.

password該用戶(hù)的登陸密碼,密碼可以為空,如果為空則該用戶(hù)可以不需要密碼登陸.

如: CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';

注：創(chuàng )建用戶(hù)時(shí)也可以向user表中添加用戶(hù)信息實(shí)現添加用戶(hù)。

權限分配

MySQL權限原則：

權限控制主要是出于安全因素，因此需要遵循一下幾個(gè)經(jīng)驗原則：

1、只授予能滿(mǎn)足需要的最小權限，防止用戶(hù)越權。如用戶(hù)只是需要查詢(xún)，那就只賦予select權限，不要給用戶(hù)賦予update、insert或者delete權限。

2、創(chuàng )建用戶(hù)的時(shí)候限制用戶(hù)的登錄主機，一般是限制成指定IP或者內網(wǎng)IP段。

3、初始化數據庫的時(shí)候刪除沒(méi)有密碼的用戶(hù)。安裝完數據庫的時(shí)候會(huì )自動(dòng)創(chuàng )建一些用戶(hù)，這些用戶(hù)默認沒(méi)有密碼。

4、為每個(gè)用戶(hù)設置滿(mǎn)足密碼復雜度的密碼。

5、定期清理不需要的用戶(hù)?；厥諜嘞藁蛘邉h除用戶(hù)。

  MYSQL權限一覽：

MYSQL的權限如何分布，就是針對表可以設置什么權限，針對列可以設置什么權限等等，這個(gè)可以從官方文檔中的一個(gè)表來(lái)說(shuō)明：

MySQL權限控制：

1、GRANT命令使用說(shuō)明：

創(chuàng )建一個(gè)只允許從本地登錄的超級用戶(hù)fog，并允許將權限賦予別的用戶(hù)，密碼為：test123

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

GRANT命令說(shuō)明：
ALL PRIVILEGES 是表示所有權限，你也可以使用select、update等權限。

ON 用來(lái)指定權限針對哪些庫和表。

*.* 中前面的*號用來(lái)指定數據庫名，后面的*號用來(lái)指定表名。

TO 表示將權限賦予某個(gè)用戶(hù)。

fog@'localhost'表示fog用戶(hù)，@后面接限制的主機，可以是IP、IP段、域名以及%，%表示任何地方。注意：這里%有的版本不包括本地，以前碰到過(guò)給某個(gè)用戶(hù)設置了%允許任何地方登錄，但是在本地登錄不了，這個(gè)和版本有關(guān)系，遇到這個(gè)問(wèn)題再加一個(gè)localhost的用戶(hù)。

IDENTIFIED BY 指定用戶(hù)的登錄密碼。

WITH GRANT OPTION 這個(gè)選項表示該用戶(hù)可以將自己擁有的權限授權給別人。

注意：經(jīng)常有人在創(chuàng )建操作用戶(hù)的時(shí)候不指定WITH GRANT OPTION選項導致后來(lái)該用戶(hù)不能使用GRANT命令創(chuàng )建用戶(hù)或者給其它用戶(hù)授權。

2、刷新權限

使用這個(gè)命令使權限生效，尤其是你對那些權限表user、db、host等做了update或者delete更新的時(shí)候。以前遇到過(guò)使用grant后權限沒(méi)有更新的情況，只要對權限做了更改就使用FLUSH PRIVILEGES命令來(lái)刷新權限。

mysql> flush privileges;

3、查看權限

查看當前用戶(hù)的權限：

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        |

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

查看某個(gè)用戶(hù)的權限：

mysql> show grants for 'fog'@'%';

4、回收權限

mysql> revoke delete on *.* from 'fog'@'localhost';

5、刪除用戶(hù)

mysql> drop user 'fog'@'localhost';

Query OK, 0 rows affected (0.01 sec)

6、對賬戶(hù)重命名

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用戶(hù)名 -p舊密碼 password 新密碼

3、用update直接編輯user表

mysql> use mysql

mysql> update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

mysql> flush privileges;

SQL開(kāi)發(fā)建議

關(guān)于命名

1、庫名、表名、字段名必須使用小寫(xiě)字母,并采用下劃線(xiàn)分割。

2、庫名、表名、字段名最多64個(gè)字符,盡量不要超過(guò)32個(gè)字符。

3、庫名、表名、字段名禁止使用MySQL保留字。

當庫名、表名、字段名等屬性含有保留字時(shí),SQL語(yǔ)句必須用反引號引用屬性名稱(chēng),這將使得SQL語(yǔ)句書(shū)寫(xiě)、SHELL腳本中變量的轉義等變得非常復雜。

4、盡量不使用分區表。

分區表對分區鍵有嚴格要求;分區表在表變大后,執行DDL、SHARDING、單表恢復等都變得更加困難。因此禁止使用分區表,并建議業(yè)務(wù)端手動(dòng)SHARDING。

關(guān)于建表

1、使用INNODB存儲引擎。

INNODB引擎是MySQL5.5版本以后的默認引擘,支持事務(wù)、行級鎖,有更好的數據恢復能力、更好的并發(fā)性能,同時(shí)對多核、大內存、SSD等硬件支持更好,支持數據熱備份等,因此INNODB相比MyISAM有明顯優(yōu)勢。

2、建議使用UNSIGNED存儲非負數值。

同樣的字節數,非負存儲的數值范圍更大。如TINYINT有符號為 -128-127,無(wú)符號為0-255。

3、建議使用INT UNSIGNED存儲IPV4。

UNSINGED INT存儲IP地址占用4字節,CHAR(15)則占用15字節。另外,計算機處理整數類(lèi)型比字符串類(lèi)型快。使用INT UNSIGNED而不是CHAR(15)來(lái)存儲IPV4地址,通過(guò)MySQL函數inet_ntoa和inet_aton來(lái)進(jìn)行轉化。IPv6地址目前沒(méi)有轉化函數,需要使用DECIMAL或兩個(gè)BIGINT來(lái)存儲。

例如:

SELECT INET_ATON('209.207.224.40'); 3520061480

SELECT INET_NTOA(3520061480); 209.207.224.40

4、強烈建議使用TINYINT來(lái)代替ENUM類(lèi)型。

ENUM類(lèi)型在需要修改或增加枚舉值時(shí),需要在線(xiàn)DDL,成本較大;ENUM列值如果含有數字類(lèi)型,可能會(huì )引起默認值混淆。

5、使用VARBINARY存儲大小寫(xiě)敏感的變長(cháng)字符串或二進(jìn)制內容。

VARBINARY默認區分大小寫(xiě),沒(méi)有字符集概念,速度快。

6、INT類(lèi)型固定占用4字節存儲,例如INT(4)僅代表顯示字符寬度為4位,不代表存儲長(cháng)度。

數值類(lèi)型括號后面的數字只是表示寬度而跟存儲范圍沒(méi)有關(guān)系,比如INT(3)默認顯示3位,空格補齊,超出時(shí)正常顯示,python、java客戶(hù)端等不具備這個(gè)功能。

7、區分使用DATETIME和TIMESTAMP。存儲年使用YEAR類(lèi)型。存儲日期使用DATE類(lèi)型。 存儲時(shí)間(精確到秒)建議使用TIMESTAMP類(lèi)型。

DATETIME和TIMESTAMP都是精確到秒,優(yōu)先選擇TIMESTAMP,因為T(mén)IMESTAMP只有4個(gè)字節,而DATETIME有8個(gè)字節。同時(shí)TIMESTAMP具有自動(dòng)賦值以及自動(dòng)更新的特性。

注意:在5.5和之前的版本中,如果一個(gè)表中有多個(gè)timestamp列,那么最多只能有一列能具有自動(dòng)更新功能。

8、將大字段、訪(fǎng)問(wèn)頻率低的字段拆分到單獨的表中存儲,分離冷熱數據。

有利于有效利用緩存,防止讀入無(wú)用的冷數據,較少磁盤(pán)IO,同時(shí)保證熱數據常駐內存提高緩存命中率。

9、禁止在數據庫表中存儲明文密碼。

采用加密字符串存儲密碼，并保證密碼不可解密，同時(shí)采用隨機字符串加鹽保證密碼安全。防止數據庫數據被公司內部人員或黑客獲取后，采用字典攻擊等方式暴力破解用戶(hù)密碼。

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

表沒(méi)有主鍵,INNODB會(huì )默認設置隱藏的主鍵列;沒(méi)有主鍵的表在定位數據行的時(shí)候非常困難,也會(huì )降低基于行復制的效率。

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

a)UTF8字符集存儲漢字占用3個(gè)字節,存儲英文字符占用一個(gè)字節。

b)UTF8統一而且通用,不會(huì )出現轉碼出現亂碼風(fēng)險。

c)如果遇到EMOJ等表情符號的存儲需求,可申請使用UTF8MB4字符集。

11、采用合適的分庫分表策略。例如千庫十表、十庫百表等。

采用合適的分庫分表策略,有利于業(yè)務(wù)發(fā)展后期快速對數據庫進(jìn)行水平拆分,同時(shí)分庫可以有效利用MySQL的多線(xiàn)程復制特性。

關(guān)于索引

1、禁止冗余索引。

索引是雙刃劍,會(huì )增加維護負擔,增大IO壓力。(a,b,c)、(a,b),后者為冗余索引?？梢岳们熬Y索引來(lái)達到加速目的,減輕維護負擔。

2、禁止重復索引。

primary key a;uniq index a;重復索引增加維護負擔、占用磁盤(pán)空間,同時(shí)沒(méi)有任何益處。

3、不在低基數列上建立索引,例如“性別”。

大部分場(chǎng)景下,低基數列上建立索引的精確查找,相對于不建立索引的全表掃描沒(méi)有任何優(yōu)勢,而且增大了IO負擔。

4、合理使用覆蓋索引減少I(mǎi)O,避免排序。

覆蓋索引能從索引中獲取需要的所有字段,從而避免回表進(jìn)行二次查找,節省IO。INNODB存儲引擎中, secondary index(非主鍵索引,又稱(chēng)為輔助索引、二級索引)沒(méi)有直接存儲行地址,而是存儲主鍵值。如果用戶(hù)需要查詢(xún)secondary index中所不包含的數據列,則需要先通過(guò)secondary index查找到主鍵值,然后再通過(guò)主鍵查詢(xún)到其他數據列,因此需要查詢(xún)兩次。覆蓋索引則可以在一個(gè)索引中獲取所有需要的數據,因此效率較高。主鍵查詢(xún)是天然的覆蓋索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主鍵,適當時(shí)候可以將索引添加為index(uid,email),以獲得性能提升。

關(guān)于SQL

1、用IN代替OR。SQL語(yǔ)句中IN包含的值不應過(guò)多,應少于1000個(gè)。

IN是范圍查找,MySQL內部會(huì )對IN的列表值進(jìn)行排序后查找,比OR效率更高。

2、用UNION ALL代替UNION。UNION ALL不需要對結果集再進(jìn)行排序。

3、盡量不使用order by rand()。

order by rand()會(huì )為表增加一個(gè)偽列,然后用rand()函數為每一行數據計算出rand()值,然后基于該行排序, 這通常都會(huì )生成磁盤(pán)上的臨時(shí)表,因此效率非常低。建議先使用rand()函數獲得隨機的主鍵值,然后通過(guò)主鍵獲取數據。

4、建議使用合理的分頁(yè)方式以提高分頁(yè)效率。

5、SELECT只獲取必要的字段,盡量少使用SELECT *。

6、SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函數。

語(yǔ)句級復制場(chǎng)景下,引起主從數據不一致;不確定值的函數,產(chǎn)生的SQL語(yǔ)句無(wú)法利用QUERY CACHE。

7、減少與數據庫交互次數,盡量采用批量SQL語(yǔ)句。

使用下面的語(yǔ)句來(lái)減少和db的交互次數:

a)INSERT ... ON DUPLICATE KEY UPDATE

b)REPLACE INTO

c)INSERT IGNORE

d)INSERT INTO VALUES()

8、拆分復雜SQL為多個(gè)小SQL,避免大事務(wù)。

簡(jiǎn)單的SQL容易使用到MySQL的QUERY CACHE;減少鎖表時(shí)間特別是MyISAM;可以使用多核CPU。

9、對同一個(gè)表的多次alter操作必須合并為一次操作。

10、盡量少使用存儲過(guò)程、觸發(fā)器、視圖、自定義函數等。

這些高級特性有性能問(wèn)題,以及未知BUG較多。業(yè)務(wù)邏輯放到數據庫會(huì )造成數據庫的DDL、SCALE OUT、SHARDING等變得更加困難。

其他

1、控制super權限的應用程序賬號存在。

2、提交線(xiàn)上建表改表需求,必須詳細注明涉及到的所有SQL語(yǔ)句(包括INSERT、DELETE、UPDATE),便于DBA進(jìn)行審核和優(yōu)化。

并不只是SELECT語(yǔ)句需要用到索引。UPDATE、DELETE都需要先定位到數據才能執行變更。因此需要業(yè)務(wù)提供所有的SQL語(yǔ)句便于DBA審核。

3、不要在MySQL數據庫中存放業(yè)務(wù)邏輯。

數據庫是有狀態(tài)的服務(wù),變更復雜而且速度慢,如果把業(yè)務(wù)邏輯放到數據庫中,將會(huì )限制業(yè)務(wù)的快速發(fā)展。建議把業(yè)務(wù)邏輯提前,放到前端或中間邏輯層,而把數據庫作為存儲層,實(shí)現邏輯與存儲的分離。