《PHP MySQL和WEB开发》笔记第十二章 MYSQL高级管理 （上）

直接用****来写笔记吧，更方便！

12.1深入了解权限系统

以管理员身份登录后 mysql -u root -p

输入psw

use mysql；

show tables；

其中，保存权限信息的6个表为 user，host（没找到），db，tables_priv，columns_priv，proce（过程）_priv。这些表也被称为授权表。包含：范围字段和权限字段。

（疑点1：没找到host表，google了一下没看懂，https://stackoverflow.com/questions/9083408/fatal-error-cant-open-and-lock-privilege-tables-table-mysql-host-doesnt-ex，估计是是什么时候更新的时候去掉了？）

user+host确定用户是否可以链接数据库服务器；

db+host 确定用户可以访问哪些数据库；

tables_priv确定用户可以访问哪些表

columns_priv哪些列

procs_priv哪些过程

（疑点2：经过测试发现原来通过grant的用户lustudy_web应该是只能登录lustudy才对，但是他可以登录其他的空的数据库，并且可以在里面建立表格，不能创建数据库，不能登录不为空的数据库，没找到答案）

12.1.1user表

user表中的权限都是全局的，普通用户应当都为N

12.1.2

db表和host表

普通用户的权限储存在db表和host表中

db表确定哪些用户可以访问哪些主机和哪些数据库

host表是db表的补充，貌似是如果一个用户从多个地方访问的话，db表将不会列出他的主机名，

经过测试，主机名会一直储存在db表中，数据库版本为5.6

12.1.3

tables_priv and columns_priv and procs_priv 储存对其的权限，其中表的权限这样赋值：

grant update on lustudy.books to lustudy_test identified by '123456';

赋给某个用户修改某一列的权限这样赋值：

grant update(author) on lustudy.books to lustudy_test identified by '123456';

12.1.4访问控制：mysql如何使用grant 表

（1）链接验证，通过user表

（2）请求验证：先检查user表 如果权限不够 再检查db表，如果权限还不够那么在检查table_priv表 以此类推

系统管理员权限最大，检查到user表就可以了，然后是一个数据库的管理员，然后是一个表的管理员，一个列的管理员，一个过程管理员

当我们知道这些表的意思的时候就可以手动更改这些权限了，但是mysql不知道我们已经更改了这些权限，需要使用语句

flush privileges；

或者在系统中 

mysqladmin flush-privileges；

或者

mysqladmin reload；

或者在下次再连接数据库的时候，mysql会重新检查权限。、

12.2提高数据库的安全性，

12.2.1从操作系统的角度来保护mysql

（1）创建一个专门用于运行mysqld的特定mysql用户；

（2）建立只能由mysql用户访问的目录

（3）mysql应当位于防火墙的后面，mysql的默认端口号为3306

12.2.2密码

所有的用户都要有密码

如果要把密码放在php脚本中，必须要把这个脚本放在目录以外的地方，并且只有指定的用户可以读取。

不要以纯文本的形式保存密码，密码应当经过sha1()函数加密。

12.2.3用户权限

（1）不要给任何用户不必要的权限

（2）值得特别注意的权限不要给别人

process：可以观察用户正在做什么，输入了什么包括密码

file：可以读写操作系统中的文件

shutdown：’

reload：

grant：避免他分享自己的权限给其他用户

（3）使用grant的时候应该避免使用通配符，最好是以ip的形式区分用户

可以启动后台程序 --skip=name=resolve来加强(所有主机列的值必须为IP地址或者本地主机)

（4）防止非管理员用户访问web服务器中的mysqladmin程序

12.2.4WEB问题

（1）专门建立一个网络链接用户来访问数据库

（2）利用addslashes和stripslashes函数

（3）有些人能通过修改表单的url来进入脚本，应当检查stripslashesh and addslashes 的大小。

如果用户储存的用户名和密码需要保存在数据库中，请注意如果不使用ssl secure sockets layer 加密套接字层  ，会以纯文本的方式从浏览器传递到服务器，比较危险。

12.3或缺更多关于数据库的信息

利用show

show databases;

show tables;

show tables from books;

show columns from books;

show columns from books from lustudy;=show columns from lustudy.books;

熟练应用小数点和from

现在已知的可以show （databases，tables，columns，grants，index，privileges，还可以显示创建库、表的语句）

show还可以使用where和like语句来判断

show columns from lustudy.books like '%s%';

进阶show：

show index from books：显示此表的索引

show status：给出系统项的信息

show variables：显示系统变量

show processlist：显示进程

show privileges：显示权限

show engines：显示默认存储引擎

show warning：显示上一个任何错误、警告、提示

show errors：显示错误信息

12.3.2使用describe语句

describe只能用于table

describe lustudy.books=show columns from lustudy.books

12.3.3使用explain语句

explain lustudy.books=describe lustudy.books=show columns from lustudy.books尼玛。。三个意思一样的

explain可以解释并执行查询

EXPLAIN语句解释查询我草这么复杂的么。

没搞懂explain select 的意义是什么，得到这些东西可以得出什么信息呢？本段摘抄为主：

explain select 会得到几张表，这些表的字段如下：

id：select查询语句的ID号

select_type:所使用的查询类型

tables:用来完成查询所需要的表

type:解释了查询在关联中是如何使用的

possible_key:可能用来链接表的关键字

key:是mysql实际使用的表的关键字或者为null

key_len:关键字的长度

ref:用来从表中选择列所必须使用的关键字

rows:粗略的列出执行链接锁必须扫描的每个表的行数

extra:链接是如何执行的

select type的值：

1.simple：简单的select 没有union和嵌套查询，join on的也是simple

2.primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。但是值得注意的是使用了in的除外。使用了in的类型为materialized

3.union：联合中的第二个或者后一个查询，貌似中间的也是union。

4.dependend union：联合中的第二个或者后一个查询，根据主查询而定（不知道怎么样会出来这个）

5.union result：union查询的结果

6.subquery：内部子查询

7.dependent subquery：内部子查询，根据关联而定（也就是说一个关联子查询）

8.derived：在from中使用了子查询

9.uncacheable subquery：一个结果无法缓存的子查询，必须重新查看每一行

10.uncacheablue union ：一个union中的下一个或者第二个select属于uncacheable subquery

11.materialized：具体化,被物化的子查询

type:列出了查询在关联中是怎么用的

感谢：http://www.cnblogs.com/xiaoboluo768/p/5400990.html

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

1.const：只从该表读取一次，非系统表，使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

2.system：系统表

3.eq_ref：对来自关联中其他表的每组行，从改表中读取一行。使用条件是：结合使用了该表索引所有部分，而该索引是unique或主键。

另一个解释：出现在要连接这个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引。

4.ref：关联只使用的关键字一部分，该关键字不是unique或主键。总之，返回数据不唯一的等值查找就可能出现。

5.fulltext：使用fulltext索引执行了关联。

6.ref_or_null:类似于一个ref查询，但是mysql也查询null的行。（例如在子查询中）

7.unique_subquery:用于where中使用in子查询，子查询返回不重复的唯一值。

8.index_subquery:类似于unique_subquery,但是用作索引的非唯一子查询。

9.range：索引范围扫描

10index_merge:特定的优化用于INDEX_MERGE

11.index：扫描整个索引

12.all：表中的每一行都将被索引

ROWS列列出了粗略的执行链接所必须扫描的每个表的行数。rows相乘就是粗略的行数。

.

.

.key_len列给出了使用了的关键词的长度，可以用它来判断是否只使用了关键词的一部分。

ref列显示了：从表中选择列而必须用与关键字一起使用的列。

extra列显示的是链接如何执行的，以及其他所有的信息。

extra：（可以有多个）

1.distinct：找到第一个匹配行后，停止查找

2.not exist：查询已经使用joinleft优化

3.range checked for each record：对连接中的其他表的集合中的每一行，试图找到最佳索引

4.using filesort：要经过两个步骤才能分类数据，显然比较长

5.using index：表中的所有信息来自索引，也就是说并没有搜索行

6.using join buffer：将联合缓冲器写入段

7.using temporary：要执行该查询需要一个临时表

8.using where：选择行时使用where语句

对于explain中出现的问题，可以使用以下方法来解决：

（1）检查列类型并且确认他们相通

（2）修改列类型使其匹配

（3）让关联优化器来检查关键字的分布，使用myisamchk或者analyze table语句对关联进行优化

myisamchk

myisamchk先没有看。228页。

（4）添加新的索引，

（5）如果possible_key中包含一些null值，可能需要对该表添加一个索引来提高查询性能。

alter table books add index isbn；