mysql 数据库学习笔记

最新推荐文章于 2023-06-28 10:54:48 发布

lxpaopao

最新推荐文章于 2023-06-28 10:54:48 发布

阅读量623

点赞数 2

分类专栏：数据库 mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/lxpaopao/article/details/115788410

版权

数据库同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

mysql

1 篇文章 0 订阅

订阅专栏

数据库

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访问方法来检索行。
        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或者视图。
    - 分表规则
      - 水平分割
        水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。
    - 垂直分割
      - 把常用、不常用的字段分开放
      - 把大字段独立存放在一个表中