MySQL-索引和账户管理(第六讲)_所有账号都可以用索引-CSDN博客

本文链接：https://blog.csdn.net/weixin_51158247/article/details/114651899

在这里插入图片描述

一.索引

思考

    索引是什么？
    不知道大家有没有在图书馆找过书？在图书馆一般都有一种机器，输入自己想找的书的名字，便会告诉你是几行几列哪个书架。
    一般在数据库的读写比例是10:1，也就是说在十次的查询操作中，有一次写入操作。
    且插入和更新数据库数据不容易出现性能问题。
    容易出现问题的是复杂的查询操作，所以优化查询语句是重中之重的事情。

解决方法

在MySQL中即使是百万级的数据量都不是什么事，除非千万级或者以上。

当数据库数据量很大的时候，查询会变慢
使用索引优化

索引是什么

索引是数据库的一种特殊文件，是无法正常读取的。(在InnoDB上，索引是表空间的组成成分)，索引包含着对所有记录的引用指针。
通俗一点，索引就是书的目录、字典前面的查询页面能让我们轻松的直接找到想要的信息。

索引的目的

就是为了使SQL查询语句能更快的查询到所需要的内容。

索引的原理

高铁的座位号、车站的车次表还有很多生活中随处可见的例子。它们的原理都是一样的，通过不断地缩小想要获取的数据的筛选范围，方便我们去筛选出它的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?
在这里插入图片描述

索引的使用

查看索引

show index from 表名;

创建索引

如果指定的字段类型为字符串，则需要指定长度
如果不是则不用

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

删除索引

dorp index 索引名称 on 表名;

索引的案例

创建测试表

create table test(title varchar(10));

使用python插入十万条数据

import pymysql  # 导入模块

cont = pymysql.connect(  # 连接MySQL
    host='127.0.0.1',  # MySQL地址
    port=3306,  # MySQL端口
    user='root',  # 用户名
    password='root',  # 密码
    db='demo'  # 使用的数据库
)
cursor = cont.cursor()  # 获取游标
for i in range(100000):  # 插入十万条数据
    cursor.execute(f'insert into text values ( "title_{i}");')  # 执行SQL语句
cont.commit()  # 提交数据
cursor.close()  # 关闭游标
cont.close()  # 关闭连接

查询

开启时间检测

set profiling=1;

查询第一万条数据

select title from text where title='title_9999';

查看查询时间

show profiles;

为表title_index的title列创建索引

create index idx_title on text (title);

再次查询第一万条数据

select title from text where title='title_9999';

再次查看查询时间

show profiles;

这时会看到，见了索引之后，运行时间会快了近百倍。

什么时候要建索引

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

不适合建立索引的情况

频繁更新的字段不要建索引
where条件用不到的建议不要建索引
表记录太少，建议三百万条记录以上才建索引
数据重复且平均的字段，如国家，性别

二.账户管理

简介

在生产环境下操作数据库时绝对不可以用root用户连接，而是针对性创建一个账户，把相关工作的权限分配给这个账户，主要的操作就是数据的crud
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种

服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
数据库级别账号：对特定数据库执行增删改查的所有操作
数据表级别账号：对特定表执行增删改查等所有操作
字段级别的权限：对某些表的特定字段进行操作
存储程序级别的账号：对存储程序进行增删改查的操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明：

username：用户名
password：密码
host：指定可以登录的主机，其中localhost表示本机，%表示所有主机
举个例子：

create user 'Jeff'@'localhost' identified by '123456';

修改用户信息

MySQL修改用户信息可以使用ALTER USER来修改当前用户，所以要登陆要修改的用户再使用。

ALTER USER USER() IDENTIFIED BY '密码';

删除用户

删除用户要用root用户删除
语法：

DROP USER [IF EXISTS] user [, user] ...

例如：

drop user 'jeff';

给用户授予权限

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

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

查看所有用户

MySQL的用户信息全部储存在MySQL数据库中的user表中

# 查看user表的结构
desc user;
# 查看所有用户
select host,user,authentication_string from user;

主要字段说明

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

给用户授权

需要登陆实例账户才能操作，例如root
主要权限有create、alter、drop、update、insert、delete、select
如果分配所有权限可以使用：all privileges

创建用户&授权

如果没有这个用户，则会直接创建一个出来

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

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

举个例子：我们要创建一个叫做Jeff的用户，但是他只能查看我们text数据库里的a表

第一步先登录实例账户

mysql -uroot -proot

第二步创建Jeff用户并授予权限

grant select on text.a to 'Jeff'@'localhost' identified by '123456';

如果报错就先刷新一下数据库的权限

flush privileges;

说明

可以查看text数据库中的a表
访问主机通常使用百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问
查看用户有哪些权限

show grants for '用户名'@localhost;

权限列表

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

权限	权限说明及作用的级别
ALTER[PRIVILEGES]	除了GRANT OPTION和PROXY之外，以指定的访问级别授予所有特权。

ALTER	修改权限，作用于全局，数据库，数据表

ALTER_ROUTINE	修改存储过程，作用于全局，数据库，存储过程

CREATE	创建权限，作用于全局，数据库，数据表

CREATE_ROUTINE	创建存储过程的权限，作用于全局，数据库

CREATE_TABLESPACE	表空间和日志文件组的创建、更改、删除，全局权限

CREATE_TEMPORARY_TABLES	创建临时表的权限，作用于数据库，数据表

CREATE_USER	创建、删除，重命用和移除用户权限的权限，全局权限

CREATE_VIEW	创建视图权限，作用于全局，数据库，数据表

DELETE	删除数据权限，作用于全局，数据库，数据表

DROP	删除数据库、数据表、视图的权限，作用于全局，数据库，数据表

EVENT	使用事件的权限，作用于全局，数据库

EXECUTE	执行存储过程的权限，作用于全局，数据库，存储过程

FILE	读取或写入文件的权限，全局权限

GRANT_OPTION	允许授权或取消授权的权限，作用于全局，数据库，数据表，存储过程，代理

INDEX	使用索引的权限，作用于全局，数据库，数据表

INSERT	写入权限，作用于全局，数据库，数据表，数据列

LOCK_TABLES	在执行SELECT时可以启动LOCK_TABLES的权限，全局或数据库级别的权限

PROCESS	使用SHOW PROCESSLIST查询全部存储过程的权限，全局权限

PROXY	启用用户代理，作用级别从用户到用户

REFERENCES	创建外健权限，作用于全局，数据库，数据表，数据列

RELOAD	启动FLUSH操作，全局权限

REPLICATION CLIENT	使用户能够询问主服务器或从服务器在哪里，全局权限

REPLICATION SLAVE	启用复制从属服务器以从主服务器读取二进制日志事件,全局权限

SELECT	查询权限，作用于全局，数据库，数据表，数据列

SHOW_DATABASES	查询全部数据库，全局权限

SHOW_VIEWS	启用使用SHOW CREATE VIEW,作用于全局，数据库，数据表

SHUTDOWN	关闭数据库服务器权限，全局权限

SUPER	启用其他管理操作的使用，例如CHANGE MASTER TO，KILL，PURGE BINARY LOGS，SET GLOBAL和mysqladmin debug命令。全局权限

TRIGGER	启用触发器的权限，作用于全局，数据库，数据表

UPDATE	更新权限，作用于全局，数据库，数据表，数据列

USAGE	特权