SQL高级二
1. 索引
思考
在图书馆中是如何找到一本书的?
一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作),
而且插入操作和更新操作很少出现性能问题,
遇到最多、最容易出问题还是一些复杂的查询操作,所以查询语句的优化显然是重中之重
1.1 解决办法
当数据库中数据量很大时,查找数据会变得很慢
优化方案:索引
1.2 索引是什么
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度
1.3 索引的目的
索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。
1.4 索引原理
除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?
1.5 索引的使用
-
查看索引
show index from 表名;
-
创建索引
-
如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
-
字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度)) create unique index 索引名称 on 表名(字段名称(长度)) 加上 unique 代表创建一个唯一索引 字段里面的数据不能重复 多用于身份证啊,手机号这种 唯一数据
-
-
删除索引
drop index 索引名称 on 表名;
1.6 索引案例
创建测试表test
create table test(title varchar(10));
- 使用python程序向表中加入十万条数据
import pymysql
def main():
conn = pymysql.connect(
host = '127.0.0.1',port =3306,
user = 'root' ,passwd = 'root',
db = 'demo012',charset = 'utf8'
)
cs = conn.cursor()
for i in range(100000):
cs.execute(f'insert into test values("zhou%d")'%i)
conn.commit()
cs.close()
conn.close()
if __name__ == '__main__':
main()
1.7 查询
-
开启运行时间监测
set profiling = 1;
-
查找第1万条数据zhou99999
select * from test where title = 'zhou99999';
-
查看执行的时间
show profiles;
-
为表title_index的title列创建索引
create index title_index on test(title(10));
1.8 适合建立索引的情况
- 主键自动建立索引
- 频繁作为查询条件的字段应该建立索引
- 查询中与其他表关联的字段,外键关系建立索引
- 在高并发的情况下创建复合索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 (建立索引的顺序跟排序的顺序保持一致)
1.9 不适合建立索引的情况
- 频繁更新的字段不适合建立索引
- where条件里面用不到的字段不创建索引
- 表记录太少,当表中数据量超过三百万条数据,可以考虑建立索引
- 数据重复且平均的表字段,比如性别,国籍
2 账户管理
账户管理
- 在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的crud
- MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种
- 服务实例级账号:,启动了一个mysqld,即为一个数据库实例;如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
- 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
2.1 创建用户
命令:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明:
- username:登录的用户名
- password:是登录的密码
- host:指定可以登录的主机,其中localhost表示本机,%表示所有主机
举例:
CREATE USER 'zzj'@'%' IDENTIFIED BY '5203';
2.2 修改用户
MySQL修改用户的信息使用ALTER USER语句,比如我们要修改当前用户的密码,可以这样
mysql> ALTER USER USER() IDENTIFIED BY '6666';
2.3 删除用户
MySQL删除用户使用DROP USER语句,该语句用法如下
DROP USER [IF EXISTS] user [, user] ...
删除用户,要用root用户进行删除
drop USER zzj;
3 授予权限
需要使用实例级账户登录后操作,以root为例
主要操作包括:
- 查看所有用户
- 修改密码
- 删除用户
3.1 查看所有用户
-
所有用户及权限信息存储在mysql数据库的user表中
-
查看user表的结构(先进入mysql数据库 use mysql;)
desc user;
-
主要字段说明:
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
查看所有用户
select host,user,authentication_string from user;
3.2 创建账户、授权
- 需要使用实例级账户登录后操作,以root为例
- 常用权限主要包括:create、alter、drop、insert、update、delete、select
- 如果分配所有权限,可以使用all privileges
创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
使用GRANT进行授权时,如果该用户不存在,可以跟上GRANT语句后面跟上IDENTIFIED BY直接创建该用户,不过如果在创建用户时要设置更详细的信息,则应该使用CREATE USER语句来创建用户。
- 权限列表
下面是使用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 | 无特权 |