MySQL操作規范有哪些

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權限一覽：

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

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數據庫中存放業務邏輯。

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

看完上述內容，你們掌握MySQL操作規范有哪些的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！