MySql用户管理与引擎介绍

数据库 元数据库介绍

 什么是元数据库
        记录mysql自身数据的数据库
    有哪些元数据库
        information_schema
             信息数据库，保存mysql所维护的其他数据库信息，例如：数据库名，数据库的表，表栏的数据类型与访问权 限等
        mysql
            核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
        performance_schema
            用于mysql的监控数据的存放
切换数据库 
    use  数据库名
    示例： use  mysql

MySql用户管理 

创建账户
    命令格式

 create user 用户名 identified by ‘密码’

    说明
        identified by会将纯文本密码加密作为散列值存储
    示例：

 CREATE USER test IDENTIFIED BY '123456';

查看账户
    命令格式：

  select host,user,password from user;

    host列说明：
        % 
              匹配所有主机
        localhost      
            localhost不会被解析成IP地址，直接通过UNIXsocket连接
                同一主机通讯，不经过网络协议栈，不用打包拆包，计算校验和、维护序列号应答等。只是将应用层数据从一个进程拷贝到另一个进程
        127.0.0.1
            会通过TCP/IP协议连接，并且只能在本机访问
        ::1
            ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

删除账户

    命令格式
        drop user 用户名
    示例：
        DROP USER test;
    一般不用，用时需谨慎

修改密码
    命令格式
        set password for 用户名=password('新密码')
    示例：

 SET PASSWORD FOR test=PASSWORD('123456');

刷新配置
    命令格式
        flush privileges

必须使用flush privileges的两种情况

1、改密码。

2、授权超用户。

flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。

MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。

通常是在修改ROOT帐号的设置后，怕重启后无法再登录进来，那么直接flush之后就可以看权限设置是否生效。而不必冒太大风险。

设置权限
    命令格式
  

     grant privileges on databasename.tablename to username@'host'

    privileges
             指定select，update等权限，全部权限使用all
    databasename
                指定数据库，所有数据库请使用*
     tablename
                指定数据表，所有表请使用*
     username
                需要赋权的用户名，@带的是Host，表示赋权操作针对那些链接，详情查看【host列说明】
    使用示例

   GRANT SELECT, UPDATE ON bookshop.`t_book` TO test@'%';

 将bookshop数据库中的t_book表的select，update权限赋予test用户，并且不对ip地址限制

  grant all on *.* to dba@'localhost'

一般用于赋予管理员最高权限，谨慎使用

 grant create view on testdb.* to developer@'192.168.0.%'; 

  以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限、

  grant show   view on testdb.* to developer@'192.168.0.%'

       以192.168.0开头的账户名deveoper用户针对testdb数据库赋予查看视图的权

 grant index on testdb.* to developer@'192.168.0.%';

          以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建索引的权限
      

  grant create routine on testdb.* to developer@'192.168.0.%'
  grant alter  routine on testdb.* to developer@'192.168.0.%';

            以192.168.0开头的账户名deveoper用户针对testdb数据库赋予操作存储过程与函数的权限
            以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建删除存储过程与函数的权限
    授权用户可以将拥有的权限再赋予其他用户
        命令格式
            grant privileges on databasename.tablename to username@'host' with grant option
        示例
            grant select on testdb.* to dba@localhost with grant option
        说明：
            一般不用，建议与数据库管理员（DBA）统一管理
查看权限
    show grants
        查看当前用户（自己）权限
    show grants for dba@localhost;
        查看其他 MySQL 用户权限
撤销权限
    命令格式
        revoke privileges on databasename.tablename from username@'host'
    示例
        REVOKE UPDATE ON bookshop.t_book FROM test@'%';
            收回test用户对于bookshop库中t_book表的update权限（ip不限）

MySQL引擎字符集基本命令

（1）MySQL查询数据库字符集    

 show VARIABLES like '%character%';

（2）Mysql配置文件：

windows系统：my.nin;
Linux系统：my.cnf;

（3）查询数据库存储引擎：

show engines;
show variables like '%storage_engine%'

 

 

 二、MySQL引擎介绍
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

---

（1）MyISAM存储引擎

不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表

支持3种不同的存储格式，分别是：静态表；动态表；压缩表

静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支

---

（2）InnoDB存储引擎

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎的特点：支持自动增长列，支持外键约束

---

（3）MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

Hash索引优点：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash索引缺点： 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

---

（4）MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

---

（5）MyISAM与InnoDB引擎比较

对比项    MyISAM    InnODB
主外键    不支持    支持
事务    不支持    支持
行表锁    表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作    行锁，操作时只锁操作的那一行，不对其他行有影响，适合高并发的操作。
缓存    只缓存索引，不会对其他真实数据进行缓存。    不仅缓存索引还有缓存真实数据，对内存要求比较高，而且内存大小对性能有决定性的影响。
表空间    小    大
关注点    性能    事务

---

（6）ARCHIVE存储引擎

   只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增id上建立索引
    使用场景：在日志和数据采集的时候可以使用
    特点
        Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%
        不支持索引（自增id列除外）
        支持insert，select操作，但不支持delete，update操作

---

（7）如何选择合适的存储引擎？

（1）选择标准可以分为：
（2）是否需要支持事务；
（3）是否需要使用热备；
（4）崩溃恢复：能否接受崩溃；
（5）是否需要外键支持；

（6）是否不需要更爱 比如日志；

  
然后按照标准，选择对应的存储引擎即可。