百战c++（数据库1）

 

三范式

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求

如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式 去除部分函数依赖

第三范式，表中的所有数据元素不但要能惟一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。去除传递函数依赖。

用交、并、补描述左连接、外连接、内链接、右连接

 

 

 

 

左连接返回左表的所有行和右表中匹配的的行，无匹配的用null替代。

外连接会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替

右连接返回右表的所有行和左表中匹配的的行，无匹配的用null替代

内连接 返回左表右表匹配的行

Union 合并两个结果集 列数目一样且列的属性值一样。

mysql的索引数据结构

MySQL学习笔记（五）索引之索引相关的数据结构_RedHaohao的博客-CSDN博客

二叉查找树

左结点比根节点小，右结点比根节点大。

所有非叶子结点最多有两个儿子，每个节点只存一个key

如果所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变结构（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销；

二叉查找树越是“矮胖”，也就是每层尽可能地被“塞满”（每个父节点均有两个子节点）时，查找效率越高。每层都被塞满时，查找效率最高，最高为O(log n)。当二叉查找树退化为单链表时，查找效率最低，最低为O(n)。

平衡二叉树

在二叉搜索树的基础上“平衡”。

任何结点的左右子树高度最多相差1.

当插入或移除一个结点时通常需要一次或多次左旋或右旋来保持平衡性。

 

 

 

索引结构(B树B+树，哈希，空间，全文)

1.1 B-Tree 索引

        B-Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

B-树

       是一种多路搜索树（并不是二叉的）：

       1.定义任意非叶子结点最多只有M个儿子；且M>2；

       2.根结点的儿子数为[2, M]；

       3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

       4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

       5.非叶子结点的关键字个数=指向儿子的指针个数-1；

       6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

       7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的

子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

       8.所有叶子结点位于同一层；

B+树

       B+树是B-树的变体，也是一种多路搜索树：

       1.其定义基本与B-树同，除了：

       2.非叶子结点的子树指针与关键字个数相同；

       3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树

（B-树是开区间）；

       5.为所有叶子结点增加一个链指针；

       6.所有关键字都在叶子结点出现；

1.2 哈希索引

    基于哈希表实现，优点是查找非常快。

    在 MySQL 中只有 Memory 引擎显式支持哈希索引。

    哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

1.3. 空间索引（R-Tree）

    MyISAM 存储引擎支持空间索引，可以用于地理数据存储。

    空间索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

1.4 全文索引

    MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较索引中的值。

mysql的索引有哪些，区别是什么，特点

·  聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

·  非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点通常是只存主键。需要二次查找

innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引（索引列的值必须唯一，但允许有空值）辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

聚簇索引并不是一种索引类型，而是一种数据存储方式。

术语“聚簇”表示数据行和相邻的键值紧密地存储在一起，InnoDB 的聚簇索引的数据行存放在 B-Tree 的叶子页中。

因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点

1. 可以把相关数据保存在一起，减少 I/O 操作；
2. 因为数据保存在 B-Tree 中，因此数据访问更快。

缺点

1. 聚簇索引最大限度提高了 I/O 密集型应用的性能，但是如果数据全部放在内存，就没必要用聚簇索引。
2. 插入速度严重依赖于插入顺序，按主键的顺序插入是最快的。
3. 更新操作代价很高，因为每个被更新的行都会移动到新的位置。
4. 当插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，页分裂会导致表占用更多的磁盘空间。
5. 如果行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描速度变慢。

索引优化

3.1 独立的列

    在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

3.2 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

对于前缀长度的选取需要根据 索引选择性 来确定：不重复的索引值和记录总数的比值。选择性越高，查询效率也越高。最大值为 1 ，此时每个记录都有唯一的索引与其对应。

3.3 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 file_id 设置为多列索引。

SELECT file_id, actor_ id FROM sakila.film_actor WhERE actor_id = 1 OR film_id = 1;

3.4 索引列的顺序

让选择性最强的索引列放在前面。

慢查询日志：会记录所有执行时间超过long_query_time的所有查询或不使用索引的查询，所以可以根据慢查询日志进行对应sql语句优化。

使用覆盖索引：覆盖索引即查询的所有列都有索引，使用覆盖索引可以减少大量回表操作，也会可以进行范围查找减少磁盘IO。

（回表操作：先索引扫描，再通过主键去取索引中未能提供的数据，即为回表）

索引顺序：将选择性高的索引放在前面，可以过滤大多数索引。如果已有（a,b）索引 就不需要再维护一个a索引。

定期清除过时索引：维护索引需要耗费系统大量资源，尤其是当表中数据比较多得时候。

尽量拓展索引而不是新建索引。

查询性能优化(Explain及其它)

1. Explain

用来分析 SQL 语句，分析结果中比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询和子查询
• key : 使用的索引
• rows : 扫描的行数

2. 减少返回的列

慢查询主要是因为访问了过多数据，除了访问过多行之外，也包括访问过多列。

最好不要使用 SELECT * 语句，要根据需要选择查询的列。

3. 减少返回的行

最好使用 LIMIT 语句来取出想要的那些行。

还可以建立索引来减少条件语句的全表扫描。例如对于下面的语句，不适用索引的情况下需要进行全表扫描，而使用索引只需要扫描几行记录即可，使用 Explain 语句可以通过观察 rows 字段来看出这种差异。

SELECT * FROM sakila.film_actor WHERE film_id = 1;

4. 拆分大的 DELETE 或 INSERT 语句

如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

索引设计原则

　     ①主键自动建立唯一索引

　　②频繁作为查询条件的字段应该创建索引

③查询中与其他表关联的字段，外键关系建立索引

在频繁进行排序和分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

　　④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

　　⑤WHERE条件里用不到的字段不创建索引

　　⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

　　⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

　　⑧查询中统计或者分组字段

哪些情况不要创建索引

　　①表记录太少

　　②经常增删改的表

　　　　提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。

　　　　因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

　　　　数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。

　　③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引并非越多越好。过多的索引会占用大量内存空间，而且会影响增删改的性能。

避免对经常更新的表建立过多的索引，并且索引中的列要尽可能少。

数据量小的表最好不要建立索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短。

在条件表达式中经常用到的不同值较多的列上建立索引。如学生表中的‘性别’列，只有“男”，“女”两个值，就无需建立索引。

对于字符串类型的字段进行索引，如果可能应该指定一个前缀长度。如果有一个char（255）的列 在前10个或前30个字符内，多数值是唯一的，则不需要对整个列建立索引。短索引不仅可以提高查询速度而且可以节省磁盘空间，减少I/O操作。

使用LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。

使用多列索引时，只有在查询条件中使用了这些字段的第一个字段时，索引才会被使用。最左匹配原则。

ex：CREATE INDEX index_id_price ON fruits(f_id,f_price);

SELECT * FROM fruits WHERE f_id=‘12’;

此时会使用索引

SELECT * FROM fruits WHERE f_price=5.2;

此时不会使用索引

索引列不能是表达式的一部分，也不能是函数的参数。

ex: select * from table where id-1>4;

此时id是表达式的一部分 就不会使用索引。

说一说数据库中的锁

全局锁

全局锁就是对整个数据库实例进行加锁。命令为Flush Tables with read lock。执行后整个库处于只读状态，数据增删改语句，数据定义语句（建表，修改表结构）和更新类事务提交都会被阻塞。

全局锁常用于Myisam数据库的备份，加上只读锁之后备份。

但是整个库只读存在风险。

使用全局锁备份的风险

如果在主库上备份，那么备份期间都不能执行更新操作，业务停止。

如果在从库上备份，那么备份期间从库不能执行主库过来的binlog，导致主从延迟。

但是不加锁的话，如果在备份期间，数据进行修改，就会导致数据不一致问题。原库修改了，备份库没改。

保证数据一致的一个办法是在可重复度隔离级别开启一个事务。

mysqldump -single-transaction，导数据之前就会启动一个事务，确保拿到一致性视图，由于mvcc的支持，这个过程中数据时可以正常更新的。

但是single-transaction方法只适用于所有的表使用事务引擎的库。所以Myisam只能使用FTWRL。

为什么不使用set global readonly =true 而是FTWRL

在有些系统中，readonly的值会被用作其他逻辑，比如用来判断一个库是主库还是备库。

执行FTWRL命令之后，由于客户端发生异常断开，那么Mysql会自动释放这个全局锁，整个库回到可以正常更新的状态。如果将整个库设置为readonly之后，客户端发生异常，则数据库会一直保持readonly的状态，这样会导致整个库长时间处于不可写状态风险太高。

表级锁

Mysql里面表级别的锁有两种：一种是表锁，一种是元数据锁（MDL）。

表锁的用法是lock tables … read/write.可以使用unlock tables 主动释放，也可以在客户端断开的时候自动释放。

MDL不需要显式调用，在访问一个表的时候会自动加上。MDL是为了保证读写的正确性。比如查询的时候锁住表，不让其他线程修改表结构。

当对一个表做DML时， 加上MDL读锁。当对一个表做DDL时，加MDL写锁。

读锁之间不互斥。可以同时增删改查

读写锁，写锁之间互斥。

进行上图操作时，AB正常执行，C被锁住，因为A读锁还没有释放，D因为C被阻塞所以自己也被阻塞。

由此可以看出MDL锁是事务提交之后才释放的。

正确的做法应该是 在alter table 里设定等待时间，如果在这个时间里能够拿到MDL写锁最好，如果不能也不要阻塞后面的业务语句先放弃。然后再重试。

行锁

InnoDB支持行锁，Myisam不支持，只支持到表锁。因此业从务并发度方面来看，InnoDB表现更优秀一些。

在InnoDB事务中，行锁是在需要的时候才加上的，但不是不需要了就可以立马释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放。

因为往后放的话离事务提交的时间就越短，即离释放锁的时间越短，堵塞的时间越短。

设置共享锁：SELECT ... LOCK IN SHARE MODE; 共享锁都是行锁
设置排他锁：SELECT ... FOR UPDATE; 排它锁可以使行锁也可以是表锁

Insert update 会自动加排它锁

1.共享锁（又称读锁）、排它锁（又称写锁）：

InnoDB引擎的锁机制：InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁。

 

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

两个意向锁是系统自动加的。

2.乐观锁、悲观锁：

悲观锁：悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

乐观锁：

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（一般是回滚事务）。

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。