[大数据]数据库课堂笔记6（索引 账户管理）

索引

思考

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，

而且插入操作和更新操作很少出现性能问题，
遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

解决办法

当数据库中数据量很大时，查找数据会变得很慢

优化方案：索引

索引是什么

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引的目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？

索引的使用

• 查看索引

 show index from 表名;

• 创建索引
  • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  • 字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))

• 删除索引

drop index 索引名称 on 表名;

索引案例

创建测试表test

create table test(title varchar(10));

使用python程序向表中加入十万条数据

from pymysql import connect


def main():
    # 创建连接
    conn = connect(host='127.0.0.1', port=3306, database='demo0128', user='root', password='root', charset='utf8')

    # 获取游标cursor对象
    cursor = conn.cursor()

    # 插入数据
    # 循环插入
    for i in range(1000000):
        cursor.execute('insert into test values("jr-%d")' % i)

    # 提交事务
    conn.commit()


if __name__ == 'main__':
    main()

查询

• 开启运行时间监测

set profiling=1;

• 查找第1万条数据ha-99999

select * from test where title='jr-99999';

• 查看执行的时间

show profiles;

• 为表title_index的title列创建索引

create index title_index on test(title(10));

• 执行查询语句

select * from test where title='ha-99999';

• 再次查看执行的时

show profiles;

适合建立索引的情况

• 主键自动建立索引
• 频繁作为查询条件的字段应该建立索引
• 查询中与其他表关联的字段,外键关系建立索引
• 在高并发的情况下创建复合索引
• 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 (建立索引的顺序跟排序的顺序保持一致)

不适合建立索引的情况

• 频繁更新的字段不适合建立索引
• where条件里面用不到的字段不创建索引
• 表记录太少,当表中数据量超过三百万条数据,可以考虑建立索引
• 数据重复且平均的表字段,比如性别,国籍

权限管理

我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向 和纵向的分组

所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；所 谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是 删除。

我们把具有相同数据访问范围和程度的用户归成不同的类别，这种类别就叫角色。通过角 色，管理用户对数据库访问的范围和程度就更加方便了。这也就是对用户的数据访问权限 的管理

恰当的权限设定，可以确保数据的安全性，这是至关重要的

那么，怎么进行权限管理呢？这节课，我就结合超市项目的实际案例，给你讲一下权限管 理的具体操作，包括怎么操作角色和用户，怎么通过角色给用户授权，怎么直接给用户授 权，从而帮助你管理好用户的权限，提升数据库的安全性

角色的作用

角色是在 MySQL 8.0 中引入的新功能，相当于一个权限的集合。引入角色的目的是方便管 理拥有相同权限的用户

下面我举个超市项目中的例子，来具体说明一下如何通过角色管理用户权限。

超市项目中有库管、营运和财务等不同的模块，它们各自对应不同的数据表。比如库存模 块中的盘点表（demo.invcount）、营运模块中的商品信息表（demo.goodsmaster）， 还有财务模块中的应付账款表（demo.settlement）。下面是这些表的具体信息。

盘点表：

商品信息表

应付账款表

在超市项目中，员工的职责不同，包括库管、营运和财务等，不同的职责有不同的数据访 问权限。比如

张三是库管，他就可以查询商品信息表，对盘点表有增删改查的权限，但无权访问应付 账款表；
李四是营运，他就拥有对商品信息表有增删改查的权限，而对库存表和应付账款表，只 有查看的权限；
王五是财务，他就有对应付账款表有增删改查的权限，对商品信息表和库存表，只有查 看的权限。

所以，我们需要为每一个职责创建一个对应的角色，为每个员工创建一个对应的数据库用 户。然后通过给角色赋予相关的权限，再把角色赋予用户，实现对超市员工访问数据权限 的管理，从而保证数据的安全性。

如何操作角色？

首先，我们要创建一个角色，为后面的授权做好准备

如何创建角色？

MySQL 中的角色名称由角色名称加主机名称组成。创建角色的语法结构如下：

• CREATE ROLE 角色名;

假设我们现在需要创建一个经理的角色，就可以用下面的代码：

mysql> CREATE ROLE 'manager'@'localhost'; 
Query OK, 0 rows affected (0.06 sec)

这里的意思是，创建一个角色，角色名称是“manager”，角色可以登录的主机 是“localhost”，意思是只能从数据库服务器运行的这台计算机登录这个账号。你也可以 不写主机名，直接创建角色“manager”：

mysql> CREATE ROLE 'manager'; 
Query OK, 0 rows affected (0.01 sec)

如果不写主机名，MySQL 默认是通配符“%”，意思是这个账号可以从任何一台主机上登 录数据库

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。

怎么给角色赋予权限？

给角色授权的语法结构是：

• GRANT 权限 ON 表名 TO 角色名;

假设我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用 下面的代码来实现：

mysql> GRANT SELECT ON demo.settlement TO 'manager'; 
Query OK, 0 rows affected (0.03 sec) 

mysql> GRANT SELECT ON demo.goodsmaster TO 'manager'; 
Query OK, 0 rows affected (0.01 sec) 

mysql> GRANT SELECT ON demo.invcount TO 'manager'; 
Query OK, 0 rows affected (0.01 sec)

如果我们需要赋予库管角色盘点表的增删改查权限、商品信息表的只读权限，对应付账款 表没有权限，就可以这样：

mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'stocker'; 
Query OK, 0 rows affected (0.02 sec) 

mysql> GRANT SELECT ON demo.goodsmaster TO 'stocker';
Query OK, 0 rows affected (0.02 sec)

查看角色权限

赋予角色权限之后，我们可以通过 SHOW GRANTS 语句，来查看权限是否创建成功了：

mysql> SHOW GRANTS FOR 'manager';

删除角色

删除角色的操作很简单，你只要掌握语法结构就行了。

• DROP ROLE 角色名称;

账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
• MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
  • 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
  • 数据库级别账号：对特定数据库执行增删改查的所有操作
  • 数据表级别账号：对特定表执行增删改查等所有操作
  • 字段级别的权限：对某些表的特定字段进行操作
  • 存储程序级别的账号：对存储程序进行增删改查的操作
• 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

创建用户

命令：CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;

说明：

• username：登录的用户名
• password：是登录的密码
• host：指定可以登录的主机（登陆的ip地址），其中localhost表示本机，%表示所有主机

举例：

CREATE USER 'testuser'@'%' IDENTIFIED BY '123';

修改用户

MySQL修改用户的信息使用ALTER USER语句，比如我们要修改当前用户的密码，可以这样

mysql> ALTER USER USER() IDENTIFIED BY 'test123457';

删除用户

MySQL删除用户使用DROP USER语句,该语句用法如下：

• DROP USER [IF EXISTS] user [, user] …
  删除用户,要用root用户进行删除

drop USER juran;

授予权限

需要使用实例级账户登录后操作，以root为例
主要操作包括：

• 查看所有用户
• 修改密码
• 删除用户

查看所有用户

所有用户及权限信息存储在mysql数据库的user表中
查看user表的结构：

• desc user;
  查看所有用户
• select host,user,authentication_string from user;

主要字段说明：

• Host表示允许访问的主机
• User表示用户名
• authentication_string表示密码，为加密后的值

创建账户、授权

• 需要使用实例级账户登录后操作，以root为例
• 常用权限主要包括：create、alter、drop、insert、update、delete、select
• 如果分配所有权限，可以使用all privileges

创建账户&授权

grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ identified by ‘密码’;

使用GRANT进行授权时，如果该用户不存在，可以跟上GRANT语句后面跟上IDENTIFIED BY直接创建该用户，不过如果在创建用户时要设置更详细的信息，则应该使用CREATE USER语句来创建用户。

示例1

创建一个laowang的账号，密码为123456，只能通过本地访问, 并且只能对lg数据库中的所有表进行读操作

• step1：使用root登录

mysql -uroot -p
回车后写密码，然后回车

• step2：创建账户并授予所有权限

grant select on lg.* to 'laowang'@'localhost' identified by '123456';

如何报错,先刷新下数据库权限

mysql> grant select on jd.* to 'laowang'@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on jd.* to 'laowang'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

说明

• 可以操作python数据库的所有表，方式为:jing_dong.*
• 访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
• 访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问
• 查看用户有哪些权限

show grants for laowang@localhost;

接下来删除jd数据库中的goods表数据,进行测试

delete from goods where id = 21;

• step3：退出root的登录

quit

• step4：使用laowang账户登录

mysql -ulaowang -p
回车后写密码，然后回车

示例2

创建一个laoli的账号，密码为12345678，可以任意电脑进行链接访问, 并且对jing_dong数据库中的所有表拥有所有权限

grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"

权限列表

下面是使用GRANT语句可以授予的全部权限，不同的权限有不同的作用域，比如有的是全局权限，有的只作用于数据库等。

关注博主即可阅读全文