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

創(chuàng)新互聯(lián)建站是專業(yè)的林芝網(wǎng)站建設(shè)公司，林芝接單;提供網(wǎng)站設(shè)計制作、成都做網(wǎng)站,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行林芝網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!

用戶權(quán)限管理

創(chuàng)建用戶

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

說明：

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

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

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)驗原則：

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

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

3、初始化數(shù)據(jù)庫的時候刪除沒有密碼的用戶。安裝完數(shù)據(jù)庫的時候會自動創(chuàng)建一些用戶，這些用戶默認(rèn)沒有密碼。

4、為每個用戶設(shè)置滿足密碼復(fù)雜度的密碼。

5、定期清理不需要的用戶?；厥諜?quán)限或者刪除用戶。

  MYSQL權(quán)限一覽：

權(quán)限	權(quán)限級別	權(quán)限說明
CREATE	數(shù)據(jù)庫、表或索引	創(chuàng)建數(shù)據(jù)庫、表或索引權(quán)限
DROP	數(shù)據(jù)庫或表	刪除數(shù)據(jù)庫或表權(quán)限
GRANT OPTION	數(shù)據(jù)庫、表或保存的程序	賦予權(quán)限選項
REFERENCES	數(shù)據(jù)庫或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	刪除數(shù)據(jù)權(quán)限
INDEX	表	索引權(quán)限
INSERT	表	插入權(quán)限
SELECT	表	查詢權(quán)限
UPDATE	表	更新權(quán)限
CREATE VIEW	視圖	創(chuàng)建視圖權(quán)限
SHOW VIEW	視圖	查看視圖權(quán)限
ALTER ROUTINE	存儲過程	更改存儲過程權(quán)限
CREATE ROUTINE	存儲過程	創(chuàng)建存儲過程權(quán)限
EXECUTE	存儲過程	執(zhí)行存儲過程權(quán)限
FILE	服務(wù)器主機(jī)上的文件訪問	文件訪問權(quán)限
CREATE TEMPORARY TABLES	服務(wù)器管理	創(chuàng)建臨時表權(quán)限
LOCK TABLES	服務(wù)器管理	鎖表權(quán)限
CREATE USER	服務(wù)器管理	創(chuàng)建用戶權(quán)限
PROCESS	服務(wù)器管理	查看進(jìn)程權(quán)限
RELOAD	服務(wù)器管理	執(zhí)行flush-hosts,  flush-logs, flush-privileges, flush-status, flush-tables, flush-threads,  refresh, reload等命令的權(quán)限
REPLICATION CLIENT	服務(wù)器管理	復(fù)制權(quán)限
REPLICATION SLAVE	服務(wù)器管理	復(fù)制權(quán)限
SHOW DATABASES	服務(wù)器管理	查看數(shù)據(jù)庫權(quán)限
SHUTDOWN	服務(wù)器管理	關(guān)閉數(shù)據(jù)庫權(quán)限
SUPER	服務(wù)器管理	執(zhí)行kill線程權(quán)限

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

權(quán)限分布	可能的設(shè)置的權(quán)限
表權(quán)限	'Select', 'Insert',  'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列權(quán)限	'Select', 'Insert',  'Update', 'References'
過程權(quán)限	'Execute', 'Alter Routine',  'Grant'

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用戶，@后面接限制的主機(jī)，可以是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ū)鍵有嚴(yán)格要求;分區(qū)表在表變大后,執(zhí)行DDL、SHARDING、單表恢復(fù)等都變得更加困難。因此禁止使用分區(qū)表,并建議業(yè)務(wù)端手動SHARDING。

關(guān)于建表

1、使用INNODB存儲引擎。

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

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

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

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

UNSINGED INT存儲IP地址占用4字節(jié),CHAR(15)則占用15字節(jié)。另外,計算機(jī)處理整數(shù)類型比字符串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址,通過MySQL函數(shù)inet_ntoa和inet_aton來進(jìn)行轉(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、強(qiáng)烈建議使用TINYINT來代替ENUM類型。

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

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

VARBINARY默認(rèn)區(qū)分大小寫,沒有字符集概念,速度快。

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

數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關(guān)系,比如INT(3)默認(rèn)顯示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ù)庫表中存儲明文密碼。

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

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

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

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

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

b)UTF8統(tǒng)一而且通用,不會出現(xiàn)轉(zhuǎn)碼出現(xiàn)亂碼風(fēng)險。

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

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

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

關(guān)于索引

1、禁止冗余索引。

索引是雙刃劍,會增加維護(hù)負(fù)擔(dān),增大IO壓力。(a,b,c)、(a,b),后者為冗余索引。可以利用前綴索引來達(dá)到加速目的,減輕維護(hù)負(fù)擔(dān)。

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

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

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

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

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

覆蓋索引能從索引中獲取需要的所有字段,從而避免回表進(jìn)行二次查找,節(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的列表值進(jìn)行排序后查找,比OR效率更高。

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

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

order by rand()會為表增加一個偽列,然后用rand()函數(shù)為每一行數(shù)據(jù)計算出rand()值,然后基于該行排序, 這通常都會生成磁盤上的臨時表,因此效率非常低。建議先使用rand()函數(shù)獲得隨機(jī)的主鍵值,然后通過主鍵獲取數(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、提交線上建表改表需求,必須詳細(xì)注明涉及到的所有SQL語句(包括INSERT、DELETE、UPDATE),便于DBA進(jìn)行審核和優(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)邏輯與存儲的分離。

看完上述內(nèi)容，你們掌握MySQL操作規(guī)范有哪些的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

名稱欄目：MySQL操作規(guī)范有哪些
當(dāng)前地址：http://www.rwnh.cn/article18/gcgegp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計公司、虛擬主機(jī)、自適應(yīng)網(wǎng)站、品牌網(wǎng)站建設(shè)、建站公司、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)