标签：MariaDB

1、  创建用户

前置条件：必须拥有globalCREATE USER privilege或者mysql 数据库的INSERT privilege。

语法：

CREATEUSER user_specification

[, user_specification] …

 

user_specification:

user

[

IDENTIFIED BY [PASSWORD] ‘password’

| IDENTIFIED WITH auth_plugin [AS’auth_string’]

]

例：MariaDB>create user ‘user’@’host’ identified by ‘password’;

其中：

user为用户名。

host为主机。可以使用localhost、ip地址或者hostname，使用’%’表示不限制主机。

‘user’@’host’构成一个唯一用户。

password为密码。

 

2、  修改用户

前置条件：alteruser语句需要具备修改账号的权限并且对mysql库有insert权限。

语法：

SETPASSWORD [FOR user] =

{

PASSWORD(‘cleartext password’)

| OLD_PASSWORD(‘cleartext password’)

| ‘encrypted password’

}

例：MariaDB>set password for ‘user’@’host’ = PASSWORD(‘password’);

其中：

‘user’@’host’为需要设置密码的用户，不写for user为设置当前用户的密码，                  PASSWORD及OLD_PASSWORD对后面的明文‘password’进行加密处理，或者直接输入加密后的密码，如’*23AE809DDACAF96AF0FD78ED04B6A265E05AA257’。

 

3、  删除用户

语法：

DROP USER user [, user] …

例：MariaDB >dropuser ‘user’@’host’;

4、  限制账户资源

对单个账户可以设置的资源限制有

1.           每小时查询次数

2.           每小时更新次数

3.           每小时连接次数

4.           同时在线的连接个数

对应：GRANTOPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

例：MariaDB > GRANT ALL ON *.* TO ‘user’@’host’

IDENTIFIEDBY ‘password’

WITHMAX_QUERIES_PER_HOUR 20

MAX_UPDATES_PER_HOUR 10

MAX_CONNECTIONS_PER_HOUR 5

MAX_USER_CONNECTIONS 2;

当账户的限制非0时则会给资源使用计数。Server运行时给每个账户的使用资源计数，如果达到了连接次数限制则下一个连接将会被拒绝。同样地若达到了查询、修改等次数限制则会产生一个error信息。

每个账户各自进行资源计数而不是针对客户端。可以全局重置当前的每小时使用的资源计数，也可以针对指定的账户重置计数：

1.      将所有账户计数器清零使用FLUSH USER_RESOURCES语句。重新加载权限表语句也会清零计数器（FLUSH PRIVILEGES或mysqladmin reload命令）

2.      给特定账户清零计数器使用GRANT USAGE语句指定一个与原来一样的限制次数

计数器清零对于max_user_connections无效，系统重启会将所有的计数器清零。

 

5、  用户管理表user

用户信息存储在系统表mysql.user中，可以通过对user表的增删改查来实现对用户的管理。

User表字段及其含义：

字段名	含义
Host	表示容许该用户连接的客户端限制，取值有：’localhost’、IP地址、域名、’%’；其中%表示不限制
User	用户名
Password	加密后的密码
Select_priv	确定用户是否可以通过SELECT命令
Insert_priv	确定用户是否可以通过INSERT命令插入数据
Update_priv	确定用户是否可以通过UPDATE命令修改现有数据
Delete_priv	确定用户是否可以通过DELETE命令删除现有数据
Create_priv	确定用户是否可以创建新的数据库和表
Drop_priv	确定用户是否可以删除现有数据库和表
Reload_priv	确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表。
Shutdown_priv	确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	确定用户是否可以将已经授予给该用户自己的权限再授予其他用户。例如，如果用户可以插入、选择和删除foo数据库中的信息，并且授予了GRANT权限，则该用户就可以将其任何或全部权限授予系统中的任何其他用户
References_priv	目前只是某些未来功能的占位符；现在没有作用
Index_priv	确定用户是否可以创建和删除表索引
Alter_priv	确定用户是否可以重命名和修改表结构
Show_db_priv	确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库。
Super_priv	确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令。
Create_tmp_table_priv	确定用户是否可以创建临时表
Lock_tables_priv	确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	确定用户是否可以执行存储过程。
Repl_slave_priv	确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
Repl_client_priv	确定用户是否可以确定复制从服务器和主服务器的位置
Create_view_priv	确定用户是否可以创建视图
Show_view_priv	确定用户是否可以查看视图或了解视图如何执行
Create_routine_priv	确定用户是否可以更改或放弃存储过程和函数
Alter_routine_priv	确定用户是否可以修改或删除存储函数及函数
Create_user_priv	确定用户是否可以执行CREATE USER命令
Event_priv	确定用户能否创建、修改和删除事件
Trigger_priv	确定用户能否创建和删除触发器
Create_tablespace_priv	确定用户能否创建表空间
ssl_type
ssl_cipher
x509_issuer
x509_subject
max_questions	每小时可执行的查询命令
max_updates	每小时可执行的更新命令
max_connections	每小时的容许用户的最多连接次数
max_user_connections	每小时容许用户最大连接客户端个数
Plugin
authentication_string	认证字符串
password_expired	密码是否已过期
is_role	是否为角色

1 前言
参考了部分网上资料整理而成。

2 设计部分
2.1 设计表注意事项
2.1.1 定义字段类型
尽可能精确地定义字段类型，包括类型和长度:如不要以字符类型声明纯数字字段，业务上tinyint够用的情况避免定义为int等。
2.1.2 尽可能使用not  null
null需要更多的代码，更多的检查和特殊的索引逻辑。所以大多数时候应该使用not  null，或者使用一个特殊的值，如0，-1作为默认值。
2.1.3 关于char 和varchar的选择
char类型定长，varchar类型变长。列长度不定，对空间要求高的情况下多使用varchar；列定长，对查询性能高的情况下多用char。
2.1.4 主键与外键
尽可能使用长度短的主键，在主键上无需建单独的索引。
外键会影响插入和更新性能，对于批量可靠数据的插入，建议先屏蔽外键检查。 对于数据量大的表，建议去掉外键，改由应用程序进行数据完整性检查。
2.2 设计索引注意事项
2.2.1 不同表之间的相同属性值的字段需一致：
不同表之间的相同属性值的字段，列类型，类型长度，是否非空，是否默认值，需保持一致，否则无法正确使用索引进行关联对比。
2.2.2 适合建索引的情况：
1、在经常需要搜索的列上，可以加快搜索的速度 。
2、在经常用在连接的列上，可以加快连接的速度 。
3、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的 。
4、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间 。
5、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
2.2.3 不适合建索引的情况：
1、散列度很低的列，如性别。
2、有大量空值的列。
3、很少使用的列（很少作为条件或表连接条件的）。
4、频繁更新的列。
5、在一张表上不要建超过6个索引。

3 开发部分
3.1 不正确的使用索引列，可能会导致不使用索引，进行全表扫描
3.1.1 对索引列进行计算
不建议的写法：
Select col1,col2 from test where index_col/100 > 10;
应写为：
Select col1,col2 from test where index_col > 10*100;
3.1.2 对索引列进行拼接
不建议的写法：
Select col1,col2 from test where concat(first_name,’ ’,last_name = ‘ZHAN SAN’;
应写为：
Select col1,col2 from test where first_name = ‘ZHAN’ and last_name = ‘SAN’;
3.1.3 在索引列上使用is null或is not null
不建议的写法：
Select col1,col2 from test where price is not null;
逻辑上允许的情况下应写为类似如下的sql：
Select col1,col2 from test where price >=0;
3.1.4 在索引列上使用or
不建议的写法：
Select col1,col2 from test where first_name = ‘ZHAN’ or last_name = ‘SAN’;
应写为：
Select col1,col2 from test where first_name = ‘ZHAN’ union Select col1,col2 from test where last_name = ‘SAN’;
3.1.5 对索引列进行类型转换，或隐式的类型转换
不建议的写法：
如定义col1为char型
Select col1,col2 from test where col1 = 3;
应写为：
Select col1,col2 from test where col1 = ‘3’;
3.1.6 尽可能避免索引列在like的首字符使用通配符
不建议的写法：
Select col1,col2 from test where first_name like ‘%HA%’;
逻辑上允许的情况下应写为类似如下的sql：
Select col1,col2 from test where first_name like ‘ZHA%’;
3.1.7 如果索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引
Create index com_index on test (col1,col2,col3);
相当于建立了index(col1,col2,col3)、index(col1,col2)、index(col1) 3个索引。在查询时单独使用col2或col3是不会使用到该索引，必须使用col1才会使用到该索引。
3.2 一条SQL语句只能使用一个表的一个索引
在where条件中多个and的条件中，必须都是一个多列索引的key_part属性而且必须包含key_part1。各自单一索引的话，只使用遍历最少行的那个索引。
3.3 只查询需要的列，避免使用select *
Select * from test;
应写为：
Select col1,col2,col3 from test;
3.4 使用[not] exists 代替 [not] in
select col1,col2 from test where col1 in (select col3 from t2);
应写为：
select col1,col2 from test where exists (select 1 from t2 where test.col1 = t2.col3);
3.5 逻辑上能用union all的时候不要用union
union会对结果进行排序去重，union all则不会。如果已经确定数据不会包括重复行，或者你不在乎是否会出现重复的行时使用union all。
Select col1,col2 from test where col1 >=10 union Select col1,col2 from test where col2 >=10；
逻辑上允许的情况下应写为：
Select col1,col2 from test where col1 >=10 union all Select col1,col2 from test where col2 >=10；
3.6 没有必要时不要使用distinct 和order by
使用distinct 或order by会对结果集进行排序，在结果集较大的情况下会占用大量资源。
3.7 使用case when来避免多次扫描表
Select sum(col1) from test where col2 = 1;
Select sum(col1) from test where col2 = 2;
应写为：
Select sum(case when col2 = 1 then col1 else 0 end) as sum1, sum(case when col2 = 2 then col1 else 0 end) as sum2 from test;
3.8 大数据全表清空时使用truncate代替delete
Delete from test;
应写为：
Truncate table test;
3.9 查询少量记录时使用limit
加上 limit n可以增加性能。MySQL数据库会在找到n条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。