程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL操作規范（總結），mysql操作規范總結

MySQL操作規范（總結），mysql操作規范總結

編輯：MySQL綜合教程

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

權限

權限級別

權限說明

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),以獲得性能提升。