数据库
目录
数据库
SQL语句
范式
主键外键
索引
事务
mysql
常用语句
启动关闭连接数据库
函数
DELETE与TRUNCATE的区别
索引
sql优化
表优化
查看和中止sql
慢查询日志
存储引擎
分表分区
SQL语句
- DDL语句
- 关键字——CREATE、ALTER、DROP、TRUNCATE等
- 使用——DDL主要定义或改变表的结构，表之间的链接和约束等，在建表实使用
- DML语句
- 关键字——SELECT、UPDATE、INSERT、DELETE
- 使用——用来对数据库里的数据进行操作的语言
- DCL语句
- 关键字——GRANT、REVOKE等
- 使用——用来设置或更改数据库用户或角色权限的语句
范式
- 作用——减少冗余
- 第一范式——要满足属性不可拆分
- 第二范式——消除非主属性对主属性的部分依赖
- 第三范式——要求一个关系中不包含已在其它关系已包含的非主关键字信息
- 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:未定义，自动，让系统帮你选
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访问方法来检索行。
- 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 (参考)
表优化
- 分析表 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或者视图。
- 分表规则
- 水平分割
- 水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。
- 垂直分割
- 把常用、不常用的字段分开放
- 把大字段独立存放在一个表中
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
