MySQL学习笔记（三）

说明

• 最近学习MySQL DBA功课，这里记录一下笔记。
• 作者：yfang (yangfang at fudan.edu.cn)
• 修改时间：2008-1-22
• 原创文章，欢迎转载，转载请注明出处及作者信息，谢谢

MySQL用户管理和安全

用户访问权限

• 因为MySQL是使用User和Host两个字段来确定用户身份的，这样就带来一个问题，就是一个客户端到底属于哪个host的问题。
• 如果一个客户端同时匹配几个Host，对用户的确定将按照下面的优先级来排
  • 基本观点 越精确的匹配越优先
  • Host列上，越是确定的Host越优先，[localhost, 192.168.1.1, wiki.yfang.cn] 优先于[192.168.%, %.yfang.cn]，优先于[192.%, %.cn]，优先于[%]
  • User列上，明确的username优先于空username。（空username匹配所有用户名，即匿名用户匹配所有用户）
  • Host列优先于User列考虑
• 举例如下：

mysql> select Host,User from mysql.user;
+-----------+--------+
| Host      | User   |
+-----------+--------+
| %         | root   |
| %         | xiaomi |
| 127.0.0.1 |        |
| 127.0.0.1 | yfang  |
| localhost | root   |
+-----------+--------+
5 rows in set (0.00 sec)

• 排序后的匹配顺序表：

+-----------+--------+
| Host      | User   |
+-----------+--------+
| 127.0.0.1 | yfang  |
| 127.0.0.1 |        |
| localhost | root   |
| %         | root   |
| %         | xiaomi |
+-----------+--------+

用户登录检查

• 当你登录mysql服务器之后，你可以使用user()和current_user()来检查你登陆的用户。
  • user() 返回你连接server时候指定的用户和主机
  • current_user() 返回在mysql.user表中匹配到的用户和主机，这将确定你在数据库中的权限
• 举例

root@S71[0]~17:01:03# /usr/local/mysql/bin/mysql -uyy -h 127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33 to server version: 5.0.18-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| yy@127.0.0.1 | @127.0.0.1     |
+--------------+----------------+
1 row in set (0.00 sec)

执行命令检查

• 当你登录服务器并执行MySQL的命令时，系统将检查你当前的用户(current_user)是否有权限进行当前操作。

1. 首先检查user表中的全局权限，如果满足条件，则执行操作
2. 如果上面的失败，则检查mysql.db表中是否有满足条件的权限，如果满足，则执行操作
3. 如果上面的失败，则检查mysql.table_priv和mysql.columns_priv(如果是存储过程操作则检查mysql.procs_priv)，如果满足，则执行操作
4. 如果以上检查均失败，则系统拒绝执行操作。

资源限制检查

• 如果一个用户被限制了资源的使用，比如被限定每小时最多连接3次，等等，将在执行命令权限检查之前首先进行资源限制检查，比如update次数限制。

跳过用户登录检查

• MySQL? 里有一个重要的启动参数叫做--skip-grant-tables，他将使你跳过用户检查的过程直接登录，当root密码忘记的时候这是一个重要的解决办法，你可以不用密码验证就登录root，然后重新设定你的密码。
• 使用--skip-grant-tables参数将禁用用户管理命令，比如 CREATE USER, GRANT, REVOKE, SET PASSWORD 等等，你可以使用下面的方法来实现root密码的修改。

UPDATE user SET password=PASSWORD('new_password') WHERE user='root'; 
FLUSH PRIVILEGES; 

• 带来方便的同时也存在安全隐患，因为系统不再验证密码，所以在使用--skip-grant-tables启动mysql的时候，最好同时开启--skip-networking参数。

使用安全连接

• MySQL? 的网络传输默认是明文传递，可能有被监听的风险，如果你不想被监听，可以使用SSL连接。

使用SSL

• MySQL? 支持SSL连接，你在编译的时候选择支持SSL。 * 我们只需要在我们需要安全的地方开启SSL连接就可以了，因为安全带来了一些性能上的损失。

MySQL 视图

视图的作用

• 更容易访问数据
• 结果因访问用户不同而不同，自动屏蔽当前用户没有权限访问的数据
• 临时创建一个视图可以减轻用户在真实表做结构调整（比如分表）时的痛感。

创建视图

使用CREATE VIEW命令

CREATE [OR REPLACE] [ALGORITHM = algorithm_type]
            VIEW view_name [(column_list)]
            AS select_statement
            [WITH [CASCADED | LOCAL] CHECK OPTION]

• OR REPLACE 如果原来存在同名视图，则替换原有视图
• ALGORITHM 设定视图算法
• column_list 可以重载默认的视图中的列名
• WITH CHECK OPTION 数据更新时，检查数据类型，如果不符合视图中的类型定义，则不做更新

利用SELECT配合CREATE VIEW

CREATE VIEW UserView AS SELECT User, Host FROM mysq.user;

查询性能

查询优化

• 主要是索引的使用，不仅查询变快，更新，删除也会变快
• 数据类型的选择，比如使用更精确的描述，使得存储空间减小的同时，查询也变得快一些
• 注意写SQL的方法，除非特殊情况，尽量使用索引
• 使用EXPLAIN来查看当前执行的SQL性能，可以帮助你优化查询
• 适当的设计表结构也可以帮助优化
• 对于不同的查询，不同存储引擎的表现也各不相同

使用索引

索引类型

1. 主键(Primary key) 一种典型的索引，要求唯一，非NULL
2. 唯一索引(unique index) 和主键类似，不同点是可以是NULL，所有非NULL行要求值唯一
3. 可重复索引(non-unique index) 所有键值可以有重复

• 全文(FULLTEXT) 索引 全文检索索引，只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部(前缀)索引
• 空间(SPATIAL) 索引 只有MyISAM存储引擎支持空间类型。空间索引使用R-树。

创建索引的原则

1. 如果可能的话，尽量使用Not NULL的索引，系统性能上会好一些
2. 如果不用选择性的条件（比如GROUP BY, WHERE, ORDER BY等），就不用建索引，因为索引也是有开销的，比如更新的时候
3. 在选择性好的列上做索引，典型的不好例子是性别，如果男女比例接近1:1，建索引效果就很差
4. 根据需要适当选择是否unique的索引
5. 根据实际情况选择为字段的几位建立索引，比如为char(255)的前10位建立前缀索引，如果筛选性没有大大降低的话，可能会有比较大的性能提升。特别的，当索引一个BLOB或TEXT列时，你必须为索引指定前缀长度。
6. ALTER TABLE可以同时为一张表建立几个索引，CREATE INDEX只能一次创建或删除一个索引
7. 如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。如果列不构成索引最左面的前缀，MySQL不能使用局部索引。这个特点不适用于全文索引。

<前n位索引性能考查案例>
假定有一个表在name char(255)上建立了10位索引

SELECT COUNT(DISTINCT name) AS 'Distinct Name', 
            COUNT(*) - COUNT(DISTINCT name) AS 'Duplicate Name' 
            FROM tbl_name;
SELECT COUNT(DISTINCT LEFT(name,10)) AS 'Distinct Name', 
            COUNT(*) - COUNT(DISTINCT LEFT(name,10)) AS 'Duplicate Name' 
            FROM tbl_name;

这可以帮你查看只检查10位前缀是否对数据筛选性构成巨大影响，如果两次运行的结果比较接近，或者说后者筛选性你能接受的话，那就可以这么做了。

<多列索引使用案例>
假定有下面显示的SELECT语句

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
 
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1，col2，col3)有一个索引，只有前2个查询使用索引
第3个和第4个查询确实包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左边的前缀

找出需要优化的SQL

1. 根据你的经验和特定的应用
2. 检查服务的日志，包括通用日志，二进制日志和慢查询日志
3. 使用SHOW PROCESSLIST，查看当前哪些进程在跑

使用EXPLAIN帮助分析语句

• EXPLAIN SELECT 可以帮你查看你的查询是否需要一个索引，以及索引的使用情况，你可以一遍修改你的SQL一边测试，只到获得满意的结果
• EXPLAIN 虽然不能直接在UPDATE和DELETE上分析，不过你可以间接的去EXPAIN 拥有和你的目标一致的WHERE语句的SELECT语句。
• EXPLAIN 最大的用途是在分析联合查询的时候