基础知识

数据保存在内存：读取速度快，不能永久保存
数据保存在文件：数据永久保存，频繁操作IO速度慢，查询数据不方便
数据保存在数据库：数据永久保存，使用SQL语句，方便查询，管理数据方便

MYSQL

mysql是一个关系型数据库

Mysql，Oracle，Sql Server区别

  1. sql server 只能在window上使用
  2. mysql免费开源的
  3. Oracle支持大并发量，访问量
  4. Oracle支持多用户不同权限进行操作，

  5. sql语句不同

    三大范式

  6. 每个列都不可以再拆分

  7. 在1的基础上，非主键列完全依赖于主键
  8. 在2的基础上，非主键列只依赖于主键

    mysql关于权限表

  • user权限表：记录允许连接到服务器的用户账户信息，全局权限
  • db权限表：记录各个账号在各个数据库上的操作权限
  • table_priv权限表：记录数据表级的操作权限
  • columns_priv权限表：记录数据列级的操作权限

  • host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。

    mysql的binlog有几种录入格式及区别？

  • statement模式：每一条会修改数据的sql都会记录在binlog中，不需要记录每一行的变化，减少binlog日志量。节约IO提高性能。由于sql执行是有上下文的，因此在保存的时候需要保存相关的信息，同时一些使用了函数之类的语句无法被记录复制。

  • row级别：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但由于很多操作，会导致大量的改动，因此这种模式的文件保存的信息太多，日志量太大
  • mixed：普通的操作使用statement记录，无法使用statement时使用row

新版本row级别当表结构发生变化时，会记录语句而不是逐行记录

MYSQL引擎

MySQL存储引擎MyISAM与InnoDB区别

  • Innodb引擎：提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。
  • MyISAM引擎：不提供事务，不支持行级锁和外键

  • MEMORY引擎：所有的数据都在内存中，数据的处理速度快，安全性低

    MyISAM索引与InnoDB索引区别

  • InnoDB索引是聚簇索引，MyISAM是非聚簇索引

  • InnoDB的主键索引的叶子节点存储着行数据，主键索引非常高效
  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据

  • InnoDB非主键索引的叶子节点存储的是主键和其他索引的列数据，因此查询时做到覆盖索引会非常高效

    InnoDB引擎特性

  • 插入缓冲

  • 二次写
  • 自适应哈希索引

  • 预读

    存储引擎选择

  • 使用默认InnoDB

  • MyISAM：以读写插入为主的应用程序

  • InnoDB：更新（删除）操作频率高，或者要保证数据完整性。并发量高，支持事务和外键

    MYSQL索引

    什么是索引

  • 索引记录着所有数据表里的引用指针

  • 索引是一种数据结构，索引通常使用B树和B+树
  • 索引相当于目录，为了方便查找。

  • 索引是一个文件，占用物理内存

    索引的优点

  • 加快数据的检索速度

  • 使用索引在查询过程中使用优化隐藏器，提高系统的性能

    索引的缺点

  • 时间方面：创建索引和维护索引需要时间，表中数据增加、删除、修改时，索引需要动态维护，会降低增删改的效率

  • 空间方面：索引占用物理空间

    怎么创建索引

  1. 创建索引：create index depeunique_ide on depe(dept_no) tablespace idx
  2. 创建索引可以增加查询速度，唯一索引可以保证数据库列的一致性，可以确定表与表之间的连接

  3. 索引分类：

    1. 逻辑分类：单列索引，复合索引，唯一索引，非唯一索引，函数索引
    2. 物理分类：B数索引，反向键索引，位图索引

      有哪些索引和作用

      索引的作用：通过索引可以大大的提高数据库的索引速度，改善数据库性能

  4. 唯一索引：不允许有两行具有相同的值

  5. 主键索引：为了确保数据库表与表之间的关系
  6. 聚集索引：表中行的物理顺序与键值的逻辑（索引）顺序相同
  7. 非聚集索引：聚集索引和非聚集索引的根本区别是表记录的排列顺序和索引的排列顺序是否一致
  8. 复合索引：在创建索引时，并不是只能对一列进行创建索引，可以与主键一样，将多个组合为索引

  9. 全文索引：全文索引为在字符串数据中进行复杂的词搜索提供有效支持

    索引使用场景

  10. 当数据多且字段值有相同值时使用普通索引

  11. 当字段多且字段值没有重复的时候使用唯一索引
  12. 当有多个字段名都经常被查询的话使用复合索引
  13. 普通索引不支持空值，唯一索引支持空值
  14. 若是这张表增删改多而查询较少的话，就不要创建索引了，如果给一列创建了索引，那么对该列进行增删改的时候，会先访问这一列的索引
  15. 若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集。
  16. 若是改，则会把原来的删掉，再填入一个以这个字段名的新值为名创建索引的子集
  17. 若是删，则会把索引中以这个字段为名的索引的子集删掉
  18. 索引对增删改的执行减缓速度
  19. 增删改多而查询少就不创建索引
  20. 更新频繁的字段不适合创建索引

  21. 不会出现在where条件中的字段不创建索引

    主键索引与唯一索引

  22. 主键是一种约束，唯一索引是一种索引

  23. 主键创建后一定包含一个唯一索引，唯一索引并不一定是主键索引
  24. 唯一索引列允许空值，主键索引列不允许空值
  25. 主键列在创建时，已经默认不为空+唯一索引
  26. 一个表最多只能创建一个主键，可以创建多个索引
  27. 主键更适合不会修改的唯一标识
  28. 主键可以被其他表引用为外键，唯一索引不能

    索引的数据结构

  • 索引的数据结构和具体存储引擎的实现有关，mysql中有Hash索引，B+索引等。
  • InnoDB存储引擎的默认索引实现：B+树索引。

  • 哈希索引底层的数据结构是哈希表，单条记录查询的时候，可以选择哈希索引

    1、B树索引/BTree算法

    B树索引是mysql数据库中使用最频繁的索引类型。

  • B+tree性质：

    • n棵子tree的节点包含n个关键字，保存数据的索引
    • 所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身根据关键字的大小自小而大顺序链接
    • 所有非终端节点可以看成是索引部分，节点中仅含其子树中的最大关键字
    • B+树中，数据对象的插入和删除仅在节点上进行
    • B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点

      2、哈希索引/哈希算法

      在mysql中用哈希索引时，通过Hash算法（直接定址法，平方取中法，折叠法，除数取余法，随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置。如果Hash发生碰撞，则在对应Hash键下以链表形式存储。

      索引的基本原理

  • 索引用来快速地寻找具有特定值的记录，如果没有索引，一般执行查询时遍历整张表

  • 索引原理：
    • 把创建了索引的列内容进行排序
    • 对排序结果生成倒排表
    • 在倒排表内容上拼上数据地址链
    • 查询先拿倒排表内容，再取数据库地址链

      创建索引/删除索引

      创建：create table、alert table、create index
      删除普通索引，唯一索引，全文索引
      1. alter table user_index drop KEY name;
      2. alter table user_index drop KEY id_card;
      3. alter table user_index drop KEY information;
      删除主键索引 ```java //非自增主键删除策略 alter table user_index drop primary KEY;

//自增主键删除策略—先取消自增，再删除 ```

百万级别的以上的数据如何删除

  1. 先删除索引
  2. 在删除无用数据
  3. 再重新创建所用

    B树和B+树

  • 在B树中，可以将键和值存放在内部节点和叶子节点；B+树中，内部节点都是键，叶子节点同时存放键和值。
  • B树的叶子节点各自独立；B+树的叶子节点有一条链相连。
  • B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特点使得B树在特定数据重复多次查询的场景中更加高效。

  • 由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于快速的缩小查询范围。B+树的叶子节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。B树则需要对树的每一层遍历，这需要更多的内存置换次数，需要更多的时间。

    数据库使用B+

  • B树适合随机检索。B+树同时支持随机检索和顺序检索

  • B+树空间利用率高，可减少IO次数，磁盘读写代价更低。因为B+树的内部节点没有指向关键字具体信息的指针，只是作为索引使用，内部节点比B树小，盘块容纳的节点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。
  • B+树的查询效率更加稳定。B树搜索有可能在非叶子节点结束，越靠近根节点的记录查询时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查询。在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
  • B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
  • 增删文件（节点）时，效率更高。B+树的叶子节点包含所有关键字，并以有序的链表结构存储。