MySQL操作規(guī)范總結(jié)

json_huang 2016-12-02

展開全文

用戶權(quán)限管理

創(chuàng)建用戶

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

說明：

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

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

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

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

注：創(chuàng)建用戶時也可以向user表中添加用戶信息實現(xiàn)添加用戶,。

權(quán)限分配

MySQL權(quán)限原則：

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

只授予能滿足需要的最小權(quán)限,，防止用戶越權(quán),。如用戶只是需要查詢，那就只賦予select權(quán)限,，不要給用戶賦予update,、insert或者delete權(quán)限。
創(chuàng)建用戶的時候限制用戶的登錄主機,，一般是限制成指定IP或者內(nèi)網(wǎng)IP段,。
初始化數(shù)據(jù)庫的時候刪除沒有密碼的用戶。安裝完數(shù)據(jù)庫的時候會自動創(chuàng)建一些用戶,，這些用戶默認沒有密碼,。
為每個用戶設(shè)置滿足密碼復(fù)雜度的密碼。
定期清理不需要的用戶,?；厥諜?quán)限或者刪除用戶。

MYSQL權(quán)限一覽：

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

MySQL權(quán)限控制

1,、GRANT命令使用說明：

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

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

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

ON 用來指定權(quán)限針對哪些庫和表,。

*.* 中前面的*號用來指定數(shù)據(jù)庫名,，后面的*號用來指定表名。

TO 表示將權(quán)限賦予某個用戶,。

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

IDENTIFIED BY 指定用戶的登錄密碼,。

WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人,。

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

2,、刷新權(quán)限

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

mysql> flush privileges;

3,、查看權(quán)限

查看當(dāng)前用戶的權(quán)限：

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 |

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

查看某個用戶的權(quán)限：

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

4、回收權(quán)限

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開發(fā)建議

關(guān)于命名

1,、庫名,、表名、字段名必須使用小寫字母,并采用下劃線分割,。

2,、庫名、表名,、字段名最多64個字符,盡量不要超過32個字符,。

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

當(dāng)庫名,、表名,、字段名等屬性含有保留字時,SQL語句必須用反引號引用屬性名稱,這將使得SQL語句書寫、SHELL腳本中變量的轉(zhuǎn)義等變得非常復(fù)雜。

4,、盡量不使用分區(qū)表,。

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

關(guān)于建表

1,、使用INNODB存儲引擎,。

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

2,、建議使用UNSIGNED存儲非負數(shù)值。

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

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

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

例如:

SELECT INET_ATON('209.207.224.40'); 3520061480

SELECT INET_NTOA(3520061480); 209.207.224.40

4、強烈建議使用TINYINT來代替ENUM類型,。

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

5、使用VARBINARY存儲大小寫敏感的變長字符串或二進制內(nèi)容,。VARBINARY默認區(qū)分大小寫,沒有字符集概念,速度快,。

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

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

7,、區(qū)分使用DATETIME和TIMESTAMP,。存儲年使用YEAR類型。存儲日期使用DATE類型,。存儲時間(精確到秒)建議使用TIMESTAMP類型,。

DATETIME和TIMESTAMP都是精確到秒,優(yōu)先選擇TIMESTAMP,因為TIMESTAMP只有4個字節(jié),而DATETIME有8個字節(jié),。同時TIMESTAMP具有自動賦值以及自動更新的特性。

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

8,、將大字段、訪問頻率低的字段拆分到單獨的表中存儲,分離冷熱數(shù)據(jù),。

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

9,、禁止在數(shù)據(jù)庫表中存儲明文密碼,。

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

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

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

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

UTF8字符集存儲漢字占用3個字節(jié),存儲英文字符占用一個字節(jié)。
UTF8統(tǒng)一而且通用,不會出現(xiàn)轉(zhuǎn)碼出現(xiàn)亂碼風(fēng)險,。
如果遇到EMOJ等表情符號的存儲需求,可申請使用UTF8MB4字符集,。

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

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

關(guān)于索引

1,、禁止冗余索引。

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

2、禁止重復(fù)索引,。

primary key a;uniq index a;重復(fù)索引增加維護負擔(dān),、占用磁盤空間,同時沒有任何益處。

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

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

4、合理使用覆蓋索引減少IO,避免排序,。

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

關(guān)于SQL

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

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

2,、用UNION ALL代替UNION,。UNION ALL不需要對結(jié)果集再進行排序。

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

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

4,、建議使用合理的分頁方式以提高分頁效率。

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

6、SQL中避免出現(xiàn)now(),、rand(),、sysdate()、current_user()等不確定結(jié)果的函數(shù),。

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

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

使用下面的語句來減少和db的交互次數(shù):

a)INSERT ... ON DUPLICATE KEY UPDATE

b)REPLACE INTO

c)INSERT IGNORE

d)INSERT INTO VALUES()

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

簡單的SQL容易使用到MySQL的QUERY CACHE;減少鎖表時間特別是MyISAM;可以使用多核CPU,。

9,、對同一個表的多次alter操作必須合并為一次操作。

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

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

其他

1、控制super權(quán)限的應(yīng)用程序賬號存在,。

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

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

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

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

這一次，只為你而來,！