mysql 数据库学习笔记
数据库
目录
-
SQL语句
- DDL语句
- 关键字——CREATE、ALTER、DROP、TRUNCATE等
- 使用——DDL主要定义或改变表的结构,表之间的链接和约束等,在建表实使用
- DML语句
- 关键字——SELECT、UPDATE、INSERT、DELETE
- 使用——用来对数据库里的数据进行操作的语言
- DCL语句
- 关键字——GRANT、REVOKE等
- 使用——用来设置或更改数据库用户或角色权限的语句
- DDL语句
-
范式
- 作用——减少冗余
- 第一范式——要满足属性不可拆分
- 第二范式——消除非主属性对主属性的部分依赖
- 第三范式——要求一个关系中不包含已在其它关系已包含的非主关键字信息
- BCNF范式——在3NF基础上消除对主码子集的依赖
- 反范式——没有冗余的数据库未必是最好的数据库,有时为了提高运行效率和性能,低范式标准,适当保留冗余数据
-
主键外键
- 主键——表的一个特殊字段。该字段能惟一地标识该表中的每条信息;可以没有主键,有主键不能为空;
- 外键——特殊字段。字段sno是一个表A的属性,且依赖于表B的主键;外键必须依赖于已存在表的主键;可以为空值
-
索引
- 创建删除索引
- 创建索引——ALTER TABLE table_name ADD INDEX idx_1(code);
- 删除索引——DROP INDEX idx_1 ON table_name ;
- 定义——索引由数据库表中一列或多列组合而成,其作用是提高对表中数据的查询速度;
- 缺点——索引不是万能的!会使数据修改操作变慢,占用相当大的空间,使用索引有限制
- 优点——索引可以加快数据检索操作
- mysql中主键(primary key)和唯一键(unique)区别
- 主键索引——保证唯一性,不允许为空,一个表中至多一个
- 唯一索引——保证唯一性,允许一个空值,可以有多个
- mysql指定索引 from tables force 索引名
- 创建删除索引
-
事务
- 概念:事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所作的所有更改都会被撤消
- 特性
- 原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样
- 一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏
- 隔离性:隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。
- 持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚
-
mysql
-
常用语句
- 数据导出
- sql导出:SELECT * FROM new_product INTO OUTFILE 'D:/Program Files (x86)/export_data/new_product.sql' fields terminated by '|' ;
- 查看数据库版本——select version() from dual;
- mysql8.0之后支持row_numer——ROW_NUMBER() over(PARTITION by dynastyid ) rn
- 数据导出
-
启动关闭连接数据库
- 启动 mysql 服务:service mysqld start
- 查看mysqld 状态:mysqld status
- 关闭mysql服务:service mysql stop
- 重启mysql服务:service restart stop
- 连接数据库:mysql -u root -p pwd
-
函数
- GROUP_CONCAT()函数——group by 的聚合函数,将group by产生的同一个分组中的值连接起来,返回一个字符串结果,可以指定分隔符
- IF(expr,v1,v2)函数——if判断类似3目运算符,expr表达式正确返回v1,错误返回v2
- IFNULL(v1,v2)函数——空值转换函数
-
DELETE与TRUNCATE的区别
- DELETE是可以带WHERE的,所以支持条件删除;而TRUNCATE只能删除整个表;
- DELETE是DML,操作时原数据会被放到 rollback segment中,可以被回滚;而TRUNCATE是DDL,操作时不会进行存储,不能进行回滚;
- 在数据量比较小的情况下,DELETE和TRUNCATE的清理速度差别不是很大。但是数据量很大的时候TRUNCATE优势大
-
索引
- MERGE:当使用视图时,会把查询视图的语句和创建视图的语句合并起来,形成一条语句,最后再从基表中查询
- 视图中存在
- 汇总函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION或UNION ALL等不会使用索引
- TEMPTABLE:当使用视图时,会把创建视图的语句的查询结果当成一张临时表,再从临时表中进行筛选
- UNDEFINED:未定义,自动,让系统帮你选
- MERGE:当使用视图时,会把查询视图的语句和创建视图的语句合并起来,形成一条语句,最后再从基表中查询
-
sql优化
- 索引优化
- 索引使用情况
- 配置全值,对索引中索引列都指定具体值;
- 配置值得范围,对索引的值能够进行范围查找;
- 匹配最左前缀,仅仅使用索中的最左边列进行查找,如col1+col2+col3的联合索引,能被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到
- 仅对索引查询,当查询的列都在索引字段中时,查询效率更高;
- 不能使用索引的情况
- 以%开头的like查询不能使用B-Tree索引
- 数据类型出现隐式转换时不能使用索引
- 复合索引使用需满足最左原则
- 如果mysql估计使用索引比全表扫描慢,则不在使用索引
- 用or分割开的条件
- 索引使用情况
- sql语句优化
- 执行计划
- 语句——EXPLAIN SELECT语句
- id:选择标识符——id值越大优先级越高,越先被执行;id相同时,执行顺序由上至下
- select_type:表示查询的类型
- SIMPLE(简单SELECT,不使用UNION或子查询等)
- PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
- UNION(UNION中的第二个或后面的SELECT语句)
- DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
- UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
- SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)
- DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
- DERIVED(派生表的SELECT, FROM子句的子查询)
- UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
- table:输出结果集的表
- partitions:匹配的分区
- type:表示表的连接类型——ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
- possible_keys:表示查询时,可能使用的索引
- key:表示实际使用的索引
- key_len:索引字段的长度——不损失精确性的情况下,长度越短越好
- ref:列与索引的比较
- rows:扫描出的行数——估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算读取的行数
- filtered:按表条件过滤的行百分比
- Extra:执行情况的描述和说明
- Range checked for each record (index map: 0x4) (匹配字段类型,编码不相符等)
- MySQL发现没有使用好的索引,但是发现在前面的表的列值已知之后,可能会使用一些索引。 对于上表中的每一行组合,MySQL检查是否可以使用range或index_merge访问方法来检索行。
- https://www.cnblogs.com/MYSQLZOUQI/articles/3837828.html
- converting HEAP to ondisk
- 该线程正在将内部临时表从 MEMORY 表转换为磁盘表
- Using join buffer (Block Nested Loop),
- Using join buffer (Batched Key Access)
- Block Nested-Loop Join算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数。优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启,若果设置为off,优化器在选择 join方式的时候会选择NLJ(Nested Loop Join)算法。
- Batched Key Access原理:对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的(mrr目的是较为顺序)MRR使得查询更有效率,要使用BKA,必须调整系统参数optimizer_switch的值,batched_key_access设置为on,因为BKA使用了MRR,因此也要打开MRR (参考http://www.cnblogs.com/chenpingzhao/p/6720531.html)
- 执行计划
- 索引优化
-
表优化
- 分析表 ANALYZE
- 检查表 check
- 优化表 OPTIMIZE——通过可以消除删除和更新造成的磁盘碎片,从而减少空间的浪费 只读锁
-
查看和中止sql
- 查看正在运行的sql select * from information_schema.PROCESSLIST where info is not null;
- 中止正在运行的sql kill process_id;
-
慢查询日志
- 查询是否开启慢查询日志(slow_query_log)——语句:show variables like "%slow%;
- 开启慢查询日志——语句:set global slow_query_log = on
- 查询慢查询阈值(long_query_time)——语句:show variables like "%long%;
- 修改慢查询阈值——语句:set global long_query_time = 5
-
存储引擎
- 查看存储引擎
- 查看所有支持的 engine:show engines;
- 查看当前库的 engine:show variables like '%engine%;
- innodb
- 最常用,支持事务、回滚、自增、外键
- 表结构存在.frm 文件中
- 数据和索引存在表空间中,聚集索引方式方式
- 读写效率稍差,占用空间大
- myisam
- 表结构存在.frm 文件中
- .myd 存储数据, .myi 存储索引,采用非聚集索引方式
- 快速,占空间小,不支持事务和并发
- mysql索引底层数据结构采用B+树
- hash很快,但每次IO只能取一个数
- AVL和红黑树,在大量数据的情况下,IO操作还是太多
- B树每个节点内存储的是数据,因此每个节点存储的分支太少
- B+节点存储的是索引+指针(引用指向下一个节点),可以存储大量索引,同时最终数据存储在叶子节点,并且有引用横向链接,可以在2-3次的IO操作内完成千万级别的表操作。
- 建议索引是是自增长数字,这样适合范围查找
- mysql回表
- 主键索引——InnoDB 是聚集索引方式,因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树,B+树的叶子节点存储的是主键 ID 对应的数据
- 非主键索引——叶子节点存储的数据的是主键 KEY。拿到主键 KEY 后,InnoDB 才会去主键索引树里根据刚在 非主键索引树找到的主键 KEY 查找到对应的数据。
- 查看存储引擎
-
分表分区
- 分区
- 概念:是把一个数据表的文件和索引分散存储在不同的物理文件中
- 分区类型
- RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区
- LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
- HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
- KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
- 原理:mysql通过分区把数据保存到不同的文件里,同时索引也是分区的。相对于未分区的表来说,分区后单独的数据库文件索引文件的大小都明显降低,效率则明显的提示了。
- 分区的限制
- 主键或者唯一索引必须包含分区字段,如primary key (id,username),不过innoDB的大组建性能不好
- 很多时候,使用分区就不要在使用主键了,否则可能影响性能
- 只能通过int类型的字段或者返回int类型的表达式来分区,通常使用year或者to_days等函数(mysql 5.6 对限制开始放开了)
- 每个表最多1024个分区,而且多分区会大量消耗内存
- 分区的表不支持外键,相关的逻辑约束需要使用程序来实现
- 分区后,可能会造成索引失效,需要验证分区可行性
- 分表
- 概念:分表和分区类似,区别是,分区是把一个逻辑表文件分成几个物理文件后进行存储,而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。
- 分表规则
- 水平分割
- 水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。
- 水平分割
- 垂直分割
- 把常用、不常用的字段分开放
- 把大字段独立存放在一个表中
- 分区
-
思维导图:https://www.processon.com/view/link/607a41256376891132d9c9ec
密码:4URW