数据库学习

数据库基础知识

索引

MySQL索引使用的数据结构主要有BTree索引和哈希索引。

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；

其余⼤部分场景，建议选择BTree索引 。

---

MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

• MyISAM： B+Tree叶节点的data域存放的是数据记录的地址。

  在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。

• InnoDB： 其数据⽂件本身就是索引⽂件。 相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据
  ⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。

  这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集
  索引） ”。

  ⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。

  在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 （叶子存放的是数据）

  因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。

摘自：https://www.cnblogs.com/keme/p/9856964.html

每个InnoDB表都有一个特殊的索引，叫聚簇索引，索引中包含了所有的行数据。 聚簇索引和主键是一个意思的两种叫法。

当显示定义一个主键时，则InnoDB就把它作为聚簇索引，当表中没有代表唯一的一个或一组字段时，可以增加一个auto-increment字段作为主键。

当没有定义主键时，则MySQL会寻找是否有非NULL的唯一索引，如果有就把第一个唯一索引作为聚簇索引。

当没有主键或合适的唯一索引时，InnoDB内部会创建一个虚构的聚簇索引，其中包含row ID。

聚簇索引的优势：

当SQL语句通过聚簇索引访问表数据时，由于通过索引能直接定位并访问表数据，所以性能很高。

相关数据会保存在一起，比如表是包含用户的邮件信息，通过用户ID创建聚簇索引，则查询一个用户的所有邮件只需要读取少量的数据页。

使用覆盖索引扫描的查询可以直接使用页节点上的主键值。

聚簇索引的叶子节点包含了行的全部数据，而节点页只包含了索引列，比如下图索引列

辅助索引：

所有非聚簇索引都叫做辅助索引，在InnoDB里，辅助索引的每一行包含了对应的主键值和辅助索引值，索引对辅助索引的SQL执行是先定位对应的主键值，然后再到聚簇索引中查找对应的行数据。

索引数据分布

针对同一个表分别使用MyISAM和InnoDB存储引擎创建主键索引和普通索引，看看索引的数据结构不同。

CREATE TABLE layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);

针对上表，主键上随机插入1~10000的随机整数，col2上随机插入1到100的随机整数。

MyISAM索引数据分布

MyISAM存储引擎对定长的行数据采用行号，行号从0开始递增，按照数据插入的顺序存储在磁盘上

主键数据分布情况如下：

辅助索引的数据分布和主键一致，情况如下：

每个叶子节点的对应的是行号和对应的键值。

所以MyISAM中的主键索引和其他索引在结构上没有什么不同。 例行号0 ，值是99，数据按照顺序插入在磁盘上。

---

InnoDB索引数据分布

因为InnoDB支持聚簇索引，所以使用了不同的方式存储同样的数据。在InnoDB中，聚簇索引相当于表，不像MyISAM一样需要独立的行存储。

聚簇索引数据分布情况如下：

聚簇索引的每个叶子节点包含了主键值，事务ID，用于事务和多版本并发控制的回滚指针以及其他剩余的列。

另外，InnoDB的二级索引也和MyISAM不同，其叶子节点中存储的不是行指针（行号），而是主键值，从而当出现行移动或者数据页分裂时无需更新二级索引中的该值。

MyISAM和InnoDB的索引数据对比情况如下：

聚簇 ：二级（辅助） 索引键值对应主键的键值 ，
非聚簇 ： 键值对应的行号， 指向关系

---

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

经典案例：转账

假如⼩明要给⼩红转账1000元，这个转账会涉及到两个关键操作就是：将⼩明的余额减少1000元，将⼩红的余额增加1000元。万⼀在这两个操作之间突然出现错误⽐如银⾏系统崩溃，导致⼩明余额减少⽽⼩红的余额没有增加，这样就不对了。

事务就是保证这两个关键操作要么都成功，要么都失败。

事务的ACID特性

1. 原子性（Atomicity）：事务是最⼩的执⾏单位，不允许分割。 事务的原⼦性确保动作要么全部
   完成，要么完全不起作⽤；
2. 一致性（Consistency）：执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是
   相同的；
3. 隔离性（Isolation）：并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务
   之间数据库是独⽴的；
4. 持久性（Durability）：⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据
   库发⽣故障也不应该对其有任何影响。

并发事务带来哪些问题

在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀
数据进⾏操作）。 并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）：当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交
  到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。 因为这个数据是还没
  有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是
  不正确的。

  另一个事务访问了被修改且提交的数据，就是脏读。

• 丢失修改（Lost to Modify）：指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，
  那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。 这样第⼀个事务内的修
  改结果就被丢失，因此称为丢失修改。

  事务修改过程中，另一个事务也修改了事务。

• 不可重复读（Unrepeatableread）：指在⼀个事务内多次读同⼀数据。在这个事务还没有结束
  时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修
  改导致第⼀个事务两次读取的数据可能不太⼀样。

  这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。

  事务读取过程中，另一个事务修改了数据

• 幻读（Phantom read）：幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接
  着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了
  ⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者
删除⽐如多次读取⼀条记录发现记录增多或减少了。

数据库连接操作

使用连接查询的场景

将多张表进行记录的连接查询(按照某个字段指定的条件进行数据的拼接)； 进行数据的拼接(两张表的内容显示在一个结果表中 使用连接查询)

最终的结果是：记录数有可能变化，字段数一定会增加(至少两张表的合并)

意义：在用户查看数据的时候，显示的数据来自于多张表。

有两张表，分别是student、course表：

连接查询分4类：

内连接（inner join）

满足on条件表达式，内连接是取满足条件表达式的两个表的交集（即两个表都有的数据）。

select * from Student s
	inner join Course c on s.C_S_Id=c.C_Id

两个表的字段，在查询结果中都会显示。

外连接（outer join）

分为：左外连接（left join / left outer join）；外连接（right join / right outer join）；全外连接（full join / full outer join）

左外连接（left join / left outer join）

满足on条件表达式，左外连接是以左表为准，返回左表所有的数据，与右表匹配的则有值，没有匹配的则以空（null）取代。

select * from Student s 
	left join Course c on s.C_S_Id=c.C_Id

右外连接（right join / right outer join）

满足on条件表达式，右外连接是以右表为准，返回右表所有的数据，与左表匹配的则有值，没有匹配的则以空（null）取代。

select * from Student s
	right join Course c on s.C_S_Id=c.C_Id

全外连接（full join / full outer join）

满足on条件表达式，返回两个表符合条件的所有行，a表没有匹配的则a表的列返回null，b表没有匹配的则b表的列返回null，即返回的是左连接和右连接的并集。

在mysql中不支持全外连接。

select * from Student s
	full join Course c on s.C_S_Id=c.C_Id

左外连接=左表全部记录+相关联结果

右外连接=右表全部记录+相关联结果

全外连接=左表全部记录+右表全部记录+相关联结果=左外连接+右外连接-相关联结果（即去重复）

在Mysql中可以使用union操作符可以合并两个或多个 SELECT 语句的结果集。

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT * from student s LEFT JOIN course c on s.CS_ID = c.C_ID
UNION
SELECT * from student s RIGHT JOIN course c on s.CS_ID = c.C_ID

---

交叉连接（cross join）

交叉连接将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

不加条件返回两个表行数的乘积：

select * from Student s
	cross join Course c 

加了条件的交叉链接貌似和内连接一样：

SELECT * from student s CROSS JOIN course c on s.CS_ID = c.c_ID

数据库查询语句

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

**group by:**对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

执行顺序

select –>where –> group by–> having–>order by

数据库优化

索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。