Day20 PythonWeb全栈课程课堂内容
1. 索引
-
索引
的使用主要是为了解决MySQL
查询方面的效率问题。由于当数据库的数据两很大时,查找数据会变得很慢,所以使用索引
可以提高查询效率。 -
加了索引之后可以提高查询效率,但是会降低更新速度。
索引是什么?
-
索引是一种特殊的文件(
InnoDB
数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。 -
更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
索引的原理(B+Tree)
-
除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
-
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?
索引的使用
- 创建一张表
students
用来查询索引。
- 查看索引
show index from 表名;
show index from 表名\G
- 只要是
主键
就会有索引
。
- 创建索引
-
- 如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
- 字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度));
create index idx_students_name on stundets(name(20));
-- 索引命名规则: idx_表名_字段名
- 删除索引
drop index 索引名称 on 表名;
drop index idx_students_name on students;
- 不能删除主键索引。
索引案例
创建测试表test
create table test(title varchar(10));
- 使用python程序向表中加入十万条数据
from pymysql import connect
def main():
conn = connect(
host='127.0.0.1',
port=3306,
database='day20',
user='root',
passwd='111111',
charset='utf8',
)
cursor = conn.cursor()
for i in range(100000):
cursor.execute("insert into test values('Sam-%s')" % i)
conn.commit()
cursor.close()
conn.close()
if __name__ == '__main__':
main()
查询
- 开启运行时间监测
set profiling=1;
- 查找第1万条数据Sam-99999
select * from test where title='Sam-99999';
- 查看执行的时间
show profiles;
- 为表title_index的title列创建索引
create index title_index on test(title(10));
- 执行查询语句
select * from test where title='Sam-99999';
- 再次查看执行的时间
show profiles;
适合建立索引的情况
- 主键自动建立索引。
- 频繁作为查询条件的字段应该建立索引。
- 查询中与其他表关联的字段,外键关系建立索引。
- 在高并发的情况下创建复合索引。
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 (建立索引的顺序跟排序的顺序保持一致)。
不适合建立索引的情况
- 频繁更新的字段不适合建立索引
- where条件里面用不到的字段不创建索引
- 表记录太少,当表中数据量超过三百万条数据,可以考虑建立索引
- 数据重复且平均的表字段,比如性别,国籍
索引类型
- 唯一索引
- 给某一个字段创建索引,数据必须唯一,但允许数据为NULL。
create unique index 索引名 on 表名(字段名称(长度));
- 复合索引
create unique index 索引名 on 表名(字段名称1(长度),字段名称2(长度));
2. 账户管理
账户管理
-
在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的crud
-
MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种
-
- 服务实例级账号:启动了一个mysql,即为一个数据库实例;如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表。
- 数据库级别账号:对特定数据库执行增删改查的所有操作。
- 数据表级别账号:对特定表执行增删改查等所有操作。
- 字段级别的权限:对某些表的特定字段进行操作。
- 存储程序级别的账号:对存储程序进行增删改查的操作。
-
账户的操作主要包括
创建账户
、删除账户
、修改密码
、授权权限
等。
创建用户
CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
说明:
- username:登录的用户名
- password:是登录的密码
- host:指定可以登录的主机,其中localhost表示本机,%表示所有主机
举例:
CREATE USER 'sam'@'%' IDENTIFIED BY '111111';
可以在mysql
数据库中的表user
里看到已经创建成功了;
修改用户
MySQL修改用户的信息使用ALTER USER语句,比如我们要修改当前用户的密码,可以这样
mysql> ALTER USER USER() IDENTIFIED BY '123456';
删除用户
MySQL删除用户使用DROP USER
语句,该语句用法如下
DROP USER [IF EXISTS] user [, user] ...
删除用户,要用root用户进行删除
drop USER sam;
授予权限
需要使用实例级账户登录后操作,以root为例
主要操作包括:
-
查看所有用户
-
修改密码
-
删除用户
查看所有用户
- 所有用户及权限信息存储在mysql数据库的user表中
- 查看user表的结构
desc user;
-
主要字段说明:
-
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
查看所有用户
select host,user,authentication_string from user;
创建账户、授权
- 需要使用实例级账户登录后操作,以root为例
- 常用权限主要包括:create、alter、drop、insert、update、delete、select
- 如果分配所有权限,可以使用all privileges
创建账户&授权
grant 权限列表 on 数据库.数据表 to '用户名'@'访问主机' identified by '密码';
使用GRANT
进行授权时,如果该用户不存在,可以跟上GRANT
语句后面跟上IDENTIFIED BY
直接创建该用户,不过如果在创建用户时要设置更详细的信息,则应该使用CREATE USER
语句来创建用户。
比如我们给之前sam@%
用户添加select
权限。
grant select on day20.test to 'sam'@'%';
-- 登录sam用户,查询数据库的数据表。
select * from day20.test limit2;
示例1
创建一个laowang
的账号,密码为123456
,只能通过本地访问, 并且只能对day20
数据库中的所有表进行读操作
step1:使用root登录
mysql -uroot -p
-- 回车后写密码,然后回车
step2:创建账户并授予所有权限
grant select on day20.* to 'laowang'@'localhost' identified by '123456';
如何报错,先刷新下数据库权限
mysql> grant select on day20.* 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 day20.* 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语句可以授予的全部权限,不同的权限有不同的作用域,比如有的是全局权限,有的只作用于数据库等。
权限 | 权限说明及作用的级别 |
---|---|
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 | 无特权 |
3. 数据库存储引擎
数据库存储引擎
connectiors
是数据连接层。
服务层
- 第二层服务层是MySQL的核心,MySQL的核心服务层都在这一层,查询解析,SQL执行计划分析,SQL执行计划优化,查询缓存。以及跨存储引擎的功能都在这一层实现:存储过程,触发器,视图等。通过下图来观察服务层的内部结构
连接管理器
-
连接管理器的作用是管理和维持所有MySQL客户端的请求连接,当我们向MySQL发起请求时,连接管理器会负责创建连接并校验用户的权限。
-
对于已经建立的连接,如果没有太久没有发送请求,连接管理器会自动断开连接,我们可以通过设置变量wait_timeout决定多久断开不活跃的连接。
查询缓存
-
当我们与连接器建立连接后,如果我们执行的是SELECT语句,那么连接器会先从查询缓存中查询,看看之前是否执行过这条语句,如果没有再往走,如果有则判断相应的权限,符合权限,则直接返回结果。
-
查询缓存其实是把查询语句当作一个key,查询结果当用value,建立起来的key-value缓存结构。
-
不过,当数据表的数据发生变化时,其所对应的查询缓存则会失败,因此很多时候往往不能命中查询缓存,所以一般建议不要使用查询缓存。
-
可能MySQL官方团队也意识到查询缓存的作用不大,在MySQL 8.0版本中已经将查询缓存的整块功能删掉了,所以如果你用的是MySQL 8.0的版本,查询缓存的功能就不存在了。
解析器
-
当在查询缓存中没有命令查询时,则需要真正执行语句,这时候就交给解析器先进行词法分析,对我们输入的语句进行拆解,折解后再进行语法分析,判断我们输入的语句是不是符合MySQL的语法规则,如果输入的语句不符合MySQL语法规则,则停止执行并提示错误。
-
比如我们输入下面的错误语句:
select * users
- 由于上面的语句少了from,所以会返回下面的错误提示:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users' at line 1
查询优化器
-
我们输入的语句,经过分析器的词法和语法分析,MySQL服务器已经知道我们要查询什么了,不过,在开始查询前,还要交由查询优化器进行优化。
-
在优化的过程,优化器会根据SQL语句的查询条件决定使用哪一个索引,如果有连接(join),会决定表的查询顺序,最终会根据优化的结果生成一个执行计划交由下面的执行器去执行。
执行器
-
SQL语句在经过查询优化器的优化后,接下来就交由执行器开始执行,不过执行器在开始执行前,会判断用户对相应的数据表是否有权限。
-
如果用户有权限,则开始调用数据,与其数据库不同的,MySQL的数据存储与调用交由存储实现,当我们调用时,执行器通过存储引擎API向底层的存储发送相应的指令,存储引擎负责具体执行,并将执行结果告诉执行器,然后再返回给客户端。
存储引擎层
- 负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信,通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎,每种存储引擎有不同的特点。我们可以根据不同的业务特点,选择最适合的存储引擎。如果对于存储引擎的性能不满意,可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine,为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。
查看存储引擎
-- transactions事务
show engines;
show variable like '%storage_engine%';
Variable_name | |
---|---|
default_storage_engine | 默认的存储引擎 |
default_tmp_storage_engine | 默认的零时引擎 |
disabled_storage_engines | 禁用存储引擎 |
internal_tmp_disk_storage_engine | 内部磁盘存储引擎 |
4. MySQL引擎之MyISAM
-
MySQL5.5之前的版本默认存储引擎。
-
MyISAM存储引擎表由
MYD
(数据文件)和MYI
(索引文件)组成。 -
frm
(文件)记录表结构
什么是锁?
-
锁主要作用是管理共享资源的并发访问。
-
锁用于实现事务的隔离性。
锁的类型
共享锁(也称读锁),针对同一份数据,多个读操作可以同时进行而不会互相影响。
独占锁(也称写锁),当前写操作没有完成前,它会阻断其他写锁和读锁。
锁的粒度
表级锁,锁整张表。MyISAM用的表级锁。
行级锁,锁其中一行或者多行,并发性更高。
MyISAM存储引擎特性
1.并发性与锁级别。
2.表损坏修复。
-- 查询表格是否损坏
check table 数据表;
-- 修复表格
repair table 数据表;
3.MyISAM表支持数据压缩。
myisampack -b -f myIsam.MYI
压缩完成之后,表格不能修改,但查询会更快。会产生OLD
备份表格。
例如:你可以压缩当年的日志表。
MyISAM存储引擎限制
- 版本 < MySQL5.0 时默认表大小为 4G,如存储大表则要修改 MAX_Rows 和AVG_ROW_LENGTH
- 版本 > MySQL5.0时默认支持256TB
适合场景
1.非事务型应用
2.只读类应用
5. MySQL引擎之InnoDB
MySQL引擎之Innodb
- MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性
- Innodb使用表空间进行数据存储
innodb_file_per_table
show variables like 'innodb_file_per_table';
- 关闭独立表空间
set global innodb_file_per_table=off;
关闭之后只有一个frm
文件。
- ON:独立表空间(系统默认),tablename.ibd
- OFF:系统表空间,ibdataX X是一个数字,进行表转移的时候会使用到系统表空间。
系统表空间和独立表空间如何选择?
- 系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
- 独立表空间可以同时向多个文件刷新数据
Innodb存储引擎的特性
1.支持事务的ACID特性
2.Innodb支持行级锁,可以最大程度的支持并发
MyISAM和InnoDB对比
6. MySQL引擎之CSV
文件系统存储特点
-
数据以文本方式存储在文件中
-
.CSV
文件存储表内容 -
.CSM
文件存储表的元数据如表状态和数据量 -
.frm
文件存储表结构信息
特点
以CSV
格式进行数据存储
使用场景
适合做为数据交换的中间表
创建会遇到的问题
CSV数据表的创建不允许自增和含主键,并且每个字段必须要非空。
7. MySQL引擎之Memory
也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来
功能特点
- 支持HASH索引和BTree索引
- 所有字段都为固定长度 varchar(10)=char(10)
- 不支持BLOB和TEXT等大字段
- Memory存储引擎使用表级锁
如何选择存储引擎
大部分情况下,InnoDB都是正确的选择,可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎
参考条件
事务
如果应用需要事务支持,那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择
备份
如果可以定期地关闭服务器来执行备份,那么备份的因素可以忽略。反之,如果需要在线热备份,那么选择InnoDB就是基本的要求
崩溃恢复
MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢
应用举例
- 日志型应用(MyISAM)
- 只读或者大部分情况下只读的表(MyISAM)
- 订单处理(InnoDB)