【数据库笔记】MySQL必知必会：chapter 27-30 全球化和本地化 | 安全管理 | 数据库维护 | 改善性能

---

chapter 27 全球化和本地化

27.1 字符集和校对顺序

数据库表被用来存储和检索数据。

不同的语言和字符集需要以不同的方式存储和检索。因此， MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。

重要术语

• 字符集：字母和符号的集合；
• 编码：某个字符集成员的内部表示；
• 校对：规定字符如何比较的指令。

在MySQL的正常数据库活动（SELECT、 INSERT等）中，不需要操心太多的东西。

使用何种字符集和校对的决定在服务器、数据库和表级进行。

27.2 使用字符集和校对顺序

MySQL支持众多的字符集。

为查看所支持的 字符集完整列表：

show character set;

此语句显示：所有可用的字符集、每个字符集的描述和默认校对

为了查看所支持 校对的完整列表：

show collation;

此语句显示所有可用的校对，以及它们适用的字符集。可以看到有的字符集具有不止一种校对。例如， latin1对不同的欧洲语言有几种校对，而且许多校对出现两次，一次区分大小写（由_cs表示），一次不区分大小写（由_ci表示）。

通常系统管理在安装时定义一个默认的字符集和校对，也可以在创建数据库时，指定默认的字符集和校对。

为了确定所用的字符集和校对：

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation%';

实际上，字符集很少是服务器范围（甚至数据库范围）的设置。

不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。

为了给表指定字符集和校对，可使用带子句的CREATE TABLE：

CREATE TABLE mytable(columnn1 INT, columnn2 VARCHAR(10)) 
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序

这个例子中指定了CHARACTER SET和COLLATE。一般， MySQL如下确定使用什么样的字符集和校对：

• 如果指定CHARACTER SET和COLLATE两者，则使用这些值。
• 如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）。
• 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

MySQL还允许对每个列设置字符集和校对：

CREATE TABLE mytable 
( 
	column1 INT, 
	column2 VARCHAR ( 10 ), 
	column3 VARCHAR ( 10 ) CHARACTER SET latin1 COLLATE latin1_general_ci
 ) 
 	DEFAULT CHARACTER SET hebrew 
  	COLLATE hebrew_general_ci;

这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE。

如前所述，校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。

如果你需要用与创建表时不同的校对顺序，排序特定的SELECT语句，可以在SELECT语句自身中进行：

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
--这里的`COLLATE`设置是列级别设置，直接对lastname, firstname起作用。

此SELECT使用COLLATE指定一个备用的校对顺序。
上述SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术。这显然将会影响到结果排序的次序。

select的其他COLLATE子句
除了在order by的子句中使用以外，COLLATE还可以用于group by、having、聚集函数、别名等。

优先级顺序： SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。
也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于MySQL的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在MySQL的查询中也应该尽量避免使用中文做查询条件。

扩展阅读：MYSQL中的COLLATE是什么？

chapter 28 安全管理

数据库服务器通常包含关键数据，确保这些 数据的安全 和 完整需要访问控制 。

28.1 访问控制

MySQL服务器的安全基础是： 用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

你需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访问控制。

管理访问控制需要创建和管理用户账号。

28.2 管理用户

需要获得所有用户账号列表时：

mysql> USE mysql;
Database changed
mysql> SELECT user FROM user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+

MySQL数据库有一个名为user的表， 它包含所有用户账号。
user表有一个名为user的列，它存储用户登录名。新安装的服务器可能只有一个用户（如这里所示）， 过去建立的服务器可能具有很多用户。

1.创建用户账号

为了创建一个新用户账号，最清楚、简单的方式是使用CREATE USER语句：

CREATE user ben IDENTIFIED BY 'p@$$wOrd';

CREATE USER创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子用IDENTIFIED BY 'p@$$wOrd’给出了一个口令。

为重新命名一个用户账号，使用RENAME USER语句：

RENAME USER ben TO bforta;

2.删除用户账号

为了删除一个用户账号（以及相关的权限），使用DROP USER语句：

DROP USER bforta;

3.设置访问权限

在创建用户账号后，必须接着分配访问权限。

新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

为看到赋予用户账号的权限，使用SHOW GRANTS FOR语句：

show grants for bforta;

输出结果显示用户bforta有一个权限USAGE ON *.*。 USAGE表示根本没有权限，所以，此结果表示在任意数据库和任意表上对任何东西没有权限。
为设置权限，使用GRANT语句。 GRANT要求你至少给出以下信息：

• 要授予的权限；
• 被授予访问权限的数据库或表;
• 用户名。

以下例子给出GRANT的用法：

GRANT SELECT ON crashcourse.* TO bforta;

此GRANT允许用户在crashcourse.*（ crashcourse数据库的所有表）上使用SELECT。 通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。

show grants for bforta;

每个GRANT添加（或更新）用户的一个权限。 MySQL读取所有授权，并根据它们确定权限。

GRANT的反操作为REVOKE，用它来撤销特定的权限：

revoke select on crashcourse.* from bforta;

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。 被撤销的访问权限必须存在，否则会出错。

GRANT和REVOKE可在几个层次上控制访问权限：

• 整个服务器，使用GRANT ALL和REVOKE ALL；
• 整个数据库，使用ON database.*；
• 特定的表，使用ON database.table；
• 特定的列；
• 特定的存储过程。

表列出可以授予或撤销的每个权限。

表 权限

权 限	说 明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、 DROP USER、 RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、 KILL、 LOGS、 PURGE、 MASTER 和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

4 更改口令

为了更改用户口令，可使用SET PASSWORD语句。新口令必须如下加密：

set password for bforta=Password('n3w p@$$w0rd');

set password 更新用户口令，新口令必须传递到Password()函数进行加密。

set password 还可以用来设置你自己的口令：

set password=Password('n3w p@$$w0rd');

在不指定用户名时，set password更新当前登录用户的口令。

chapter 29 数据库维护

29.1 备份数据

命令行程序mysqldump,转储所有数据库内容到某个外部文件。
命令行程序mysqlhostcopy，从一个数据库复制所有数据。
可使用SQL语句BACKUP TABLE或者SELECT INTO OUTFILE转储所有数据到某个外部文件，这俩个语句都接受将要创建的系统文件。 数据可以用RESTORE TABLE来复原。
在进行备份前需要使用FLUSH TABLES语句来刷新未写数据，以保证所有数据被写到磁盘中。

29.2 进行数据库维护

MySQL提供了一些语句来保证 数据库正确和正确运行。

检查表键是否正确：

MariaDB [course]> ANALYZE TABLE orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| course.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+

针对许多问题对表进行检查CHECK TABLE。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。

• CHANGED检查自最后一次检查以来改动过的表；
• EXTENDED执行最彻底的检查；
• FAST只检查为正常关闭的表；
• MEDIUM检查所有被删除的链接并进行键检查；
• QUICK只进行快速扫描。

MariaDB [course]> CHECK TABLE orders,orderitems;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| course.orders     | check | status   | OK       |
| course.orderitems | check | status   | OK       |
+-------------------+-------+----------+----------+

如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIRE TABLE来修复相应的表。

如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间。从而优化表的性能。

3.诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时 报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能 看不到。
在排除系统启动问题时，首先应该尽量手动启动服务器，MySQL服务器自身通过在命令行上执行mysqld启动。

下面是几个重要的mysqld命令行选项：

• --help显示帮助
• --safe-mode装载减去某些最佳配置的服务器
• --verbose显示全文信息
• --version显示版本信息然后退出。

4.查看日志文件

MySQL维护管理员依赖一系列的日志文件，主要的日志文件有以下几种：

• 错误日志。包含启动和关闭问题以及任意关键错误的细节，此日志通常名为hostname.err，位于data目录中。此日志名可以用--log-error命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用，此日志文件可能会很快地变得非常大，因此不应该长期使用。通常名为hostname.log，位于data目录。此名字可以用--log命令行选项进行修改。
• 二进制日志。它记录更新过数据的所有语句。此日志通常名为hostname-bin，位于data目录中。可以用--log-bin命令行选项进行修改。
• 缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处优化很有用，此日志通常命名为hostname-slow.log,位于data目录中。此名字可以用--log-=slow-queries命令行选项进行修改。

使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

chapter 30 改善性能

好文推荐：
【1】SQL优化极简法则，还有谁不会？
【2】数据库优化 -SQL优化

30.1 改善性能

• 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议；
• 一般来说，关键的生产DBMS应该运行在自己的专用服务器上；
• MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;）；
• MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）；
• 总是有不止一种方法编写同一条SELECT语句。 应该试验联结、并、子查询等，找出最佳的方法
  ；
• 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句；
• 一般来说，存储过程 执行得比一条一条地执行其中的各条MySQL语句 快；
• 应该总是使用正确的数据类型；
• 决不要检索比需求还要多的数据。换言之， 不要用SELECT *（除非你真正需要每个列）；
• 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作；
  - 在导入数据时，应该关闭自动提交 。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们；
• 必须索引数据库表以改善数据检索的性能。确定索引不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象； 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进；
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除）；
• LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE；
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变；
• 最重要的规则就是，每条规则在某些条件下都会被打破。