SQL和NoSQL

数据存储方式：

通常SQL来说依靠的是表结构来存储，通过各个数据直接的联系来组成一个数据组织；而NoSQL的存储方式是多种多样的，比如可以使用类JSON文件存储数据。

表/数据集合的数据关系：

SQL中而言，定义好表和字段结构才能添加数据，后期更改表结构也比较困难，NoSQL可以随时随地添加，不需要先定义表。

外部数据存储

SQL——外键
NoSQL——外键/直接存储外部数据

性能

NoSQL因为没有复杂的系统结构设计，省略了JOIN查询的消耗，理论性能是比SQL好很多。

使用场景

SQL：固定的关系、固定的数据类型等，比如用户信息的存储，各数据与主键有强烈的关系。
NoSQL：数据类型不定，对性能要求极高，一般都是二者相互配合使用。

常见数据库

SQL

SQL Server，Oracle，MySQL(开源)

NoSQL

MongoDB，Redis，CouchDB

MySQL

image.png

数据库引擎

InnoDB

特点：
支持事务
支持外键
最小粒度支持行锁（但是只有在走索引查询的时候才会走行级锁），正常都是表级锁
应用场景：
需要事务支持
数据更新较为频繁的场景
行级锁对高并发比较友好，但是要确保查询是通过索引完成。
数据一致性要求高

MyISAM

特点：
不支持外键
最小粒度支持表级锁
不支持事务
SELECT比InnoDB要快
读取快

为什么select快有一个原因是因为MyISAM没有聚集索引，都是非聚集索引，每个索引都直接对应一个数据的地址，所以不会有回表产生，MyISAM是要快的，而InnoDB聚集索引的话，先找到聚集索引，然后再爬一次树，才能找到数据的地址，所以要慢一些，另外的原因就是InnoDB维护的东西太多了（要缓存数据和索引，My只缓存索引块）

应用场景：
适合大量读操作的场景，比如用户的订单
不需要事务支持（不支持）
并发相对较低（锁定机制问题

索引

索引可以加快查询速度，其实就是将无序的数据变成有序，就能加快检索速度

  • 聚集索引
  • 普通索引

数据结构

为什么不用hash表
1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
2、如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
3、索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加

B+树（InnoDB和MyISam都是B+树）
 因为MySQL的数据一般无法直接加载到内存，数据需要被检索出来的话，硬盘效率低，如果采用红黑树（数据量大的时候和二叉树一样，树太高了，这样的话查找太慢了），相当于二叉搜索树，遍历效率比B树和B+树要慢的多，所以不用，这个时候使用B+树的话，因为B+树是多路搜索树，叶子节点才存储数据，相比B树，树比较矮壮，查询更快。

B B+区别

  • B+树中只有叶子节点会带有指向记录的指针（ROWID），也就是我们数据的存放地址，而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
  • B+树中所有叶子节点都是通过指针连接在一起，而B树不会。
    • B+树的优点：
  • 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
  • 范围查询：B树会二分查找到范围下限，再不断通过中序遍历，直到查找到范围的上限
    1.   - **B+树 先二分查找 再通过叶子节点的链表顺序遍历**
    • B树的优点：
  • 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。
    • InnoDB
  • InnoDB采用的是聚集索引
    • MyISAM（查询快）

  • 采用的是非聚集索引

    回表

    比如说要查询用户姓名和年龄，姓名创建了索引，所以年龄要根据主键ID再去回到表里查一次，把要查询的建立联合索引就可以解决，但是要遵循这个最左匹配。

    主键索引

    image.png
    主键索引是唯一索引、也是聚集索引，一个表只能有一个聚集索引，所以它的索引结构来看的话，叶子节点保存的就是整行数据的地址，就直接依靠主键ID来进行排序，非主键索引的话，叶子节点保存的是主键的值，排序是按照生成顺序ID在排。
    为什么建议使用主键自增的索引？
    这个得从B+树的结构来看，B+树是有序平衡树，它叶子节点存储的数据肯定是有序排列的，如果我们按照自增主键创建索引的话，那就会一直按顺序排下去，每次主键自增加入到索引里，就是往后加就好了，但是如果主键不是自增的，那就要插入到前面的数据中，那B+树又是平衡树，所以又分裂、还会有平衡的操作，会消耗时间，采用主键自增的话是顺序添加，是最适合B+树的状态。

    唯一索引

    唯一索引不仅能加快速度，它还能避免数据重复。主键一定是唯一索引

    普通索引

    普通索引就是单纯的加快查找速度，对于数据库这种而言，速度性能是非常重要的，所以只要有可能、有需要的话就应该为一个最整齐、最紧凑的数据列创建索引。

    全文索引

    全文索引是之前MyISAM支持的，后来InnoDB也支持，但是不好用，支持的格式有限，而且是仅支持char、varchar、text，通过空格来分词的。

    联合索引

    多个字段上建立的索引，能够加速复合查询条件的检索，
    举例：select uid,login_time from t_user where login_time=? and passwd=?;可以建立（login_name, passwd）的联合索引。
    遵循最左匹配原则，最左侧查询需求，并不是指SQL语句的写法必须满足索引的顺序，不是说and必须按顺序来，而是不能跳过login去查name，也就是只查name。

    最左匹配原则

    比如有abcd4个索引，要得到a=1,b=2,c>3,d=4的情况，会从左依次命中abc，无法命中d，索引碰到范围查询就不能进一步匹配了，退化为线性查找
    如果说有一个联合索引（login_name, passwd），如果查询的时候只查passwd是没有办法走联合索引的，因为索引都是基于B+树来的，B+树是有序的，如果是主键进去那就是按主键自增在那排，如果说多个字段的索引，那要进行排序，这就有点像字符串的排序，首个字段肯定是按顺序来的，就像数字比大小，肯定先比高位，高位相同才会比低位，放到这里也是一样的，只有第一个字段相同才会给第二个字段排序，也就是说是能保证第一个字段是有序的，基于第一个字段开始查询是没问题的，如果只查询第二个字段，那索引是没办法的。
    主键满了怎么办？
    主键满的话 是可以改类型为BigInt，基本上达不到最大值 就分库分表了

    索引失效

    模糊查询%前置
    使用Like关键字查询语句时 select from tb_user where username like’li%’\
    %如果在li前面那么就会失效
    OR查询
    如果条件中有OR，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
    要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
    最左匹配不满足
    对于多列索引，不是使用的第一部分，则不会使用索引
    where后加运算、函数
    `select id,name,age,salary from table_name where salary + 1000 *=     6000;`
    建议得出直接结果再去查询

    优化

    选好存储引擎

    查找多 用MyISAM，比如订单表，数据操作更新频繁的话就用InnoDB

    选好字段属性

    使用Join代替子查询

    执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程，如果是JOIN的话，它是走嵌套查询的，小表驱动大表，且通过索引字段进行关联，如果表记录比较少的话，还是可以的。

    创建索引

    索引是可以大大加快查询速度

    选取合适的事务隔离级别

    事务隔离级别越高，越慢

    需要和允许情况下可以加缓存

    字符串检索场景多可以用ElasticSearch、Solr

    Solr是一个基于Lucene的全文搜索服务器，http请求，向搜索引擎服务器提交一定格式的XML文件，生成索引

    合理分库分表、主从复制、负载均衡

    事务

    ACID

    原子性

    a给b转200块，b收到a200块，要不然都成功，要不然都失败

    一致性

    a:1000，b：800 a转出200，剩余800，b收到200，剩余1000，前后的和都是1800 保持一致

  • 一致性是事务的目的，使用事务想要达到的效果

    隔离性

    要求：
    a给b转800，c给b转200，转后保证b收到1000 ，事务互相隔离，互不影响，不能读取到错误数据

  • 脏读
    A事务：a 转200给b 未提交
    B事务：查询到b有200 想要支付给c
    A事务：发现a余额不足，rollback
    B事务：支付失败
  • 不可重复读
    A事务：a查询到账户有200
    B事务：a查询到有200 支付给了c
    A事务：进行一系列逻辑操作后准备发现a账户没钱了 针对的是update delete

  • 幻读
    A事务：查询到a截至17号的交易有20条
    B事务：交易了一条 insert
    A事务：再次查询发现变成了21条

    持久性

    假如a在转账200到b的时候发生了错误，
    没提交，那就a还是200，b 0
    提交了 a 0 b200

    • 一旦提交事务，就会数据持久化保存在硬盘里
    • 依靠redo log来保证
      如果说发生问题的时候内存还没到数据库，就依靠redo log来进行数据恢复
      • 写入速度很快

        事务隔离级别

  • read uncommit 读未提交
    会产生脏读

  • read commit 读提交
    解决了脏读，读取最新commit的版本号的数据

每行数据都存在一个版本，每次数据更新时都更新该版本
修改时Copy出当前版本随意修改（类似于Java并发包中的CopyOnWrie操作），各个事务之间无干扰
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）
但是InnoDB的实现有一些差别：
事务以排他锁的形式修改原始数据
把修改前的数据存放于Undo log，通过回滚指针与主数据关联
修改成功（commit）啥都不做，失败则恢复Undo log中的数据（rollback）

  • reaptable read 可重复读
    根据版本来读取，还是读取当前事务版本的数据

  • seriablizable 串行化
    最高事务隔离级别，不允许事务的并发，串行处理事务，效率最低，但最安全

    数据库三范式

  • 第一范式：每一列都要保持原子性，即不可再被分割

  • 第二范式：每一列数据必须完全依赖于主键
  • 第三范式：每一列数据必须直接依赖于主键，不能间接依赖

    数据库表的设计思路

    首先，先理清业务需求，根据业务需求先分好每个表以及每个表的内容，确立好主键和表引擎，比如说订单表因为基本不会修改，表引擎设为MyISAM其实更合理一点，MyISAM没有聚集索引，都是非聚集索引，每个索引都直接对应一个数据的地址，相比InnoDB其实查询要更快一些，像这种场景就可以选用MyISAM，然后就是外键