MYSQL基础知识要点摘记

非关系型数据库数据结构可以是键值对。

优点：

1、格式灵活，存储数据的格式可以是key,value形式、文档形式、图片形式等等，应用场景广泛。key、value没有耦合，容易横向扩展。

2、可以使用硬盘或者缓存作为数据载体，而关系型数据库只能使用硬盘。而且不需要解析SQL所以性能较高。

缺点：

1、不提供sql支持，复杂查询较难；

2、无事务处理；

关系型数据库数据结构是表，由二维表及其之间的联系所组成。

优点：

1、易于维护：都是使用表结构，格式一致；

2、使用方便：SQL语言通用，可用于多个表的复杂查询；

3、支持事务处理。

缺点：

1、固定的表结构，灵活度稍欠，扩展性相对差一些。

2、为了维护一致性(比如并发事务处理和写修改索引)所付出的巨大代价就是读写性能比较差，尤其是海量数据的高效率读写；高并发读写时的硬盘I/O也使得性能相对减弱。

 

1. 关系模型（数据库数据模型）：以二维表描述数据，每个表有多个字段列和记录行，字段列有固定的属性。

2. 范式是关系型数据库需要遵守的规则，为了解决数据冗余，高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1NF：字段具有原子性，不可再分，如计算机系三班就不符合“班级”字段。

2NF：主属性(主键)对其他字段有决定性，其他字段对主键不存在部分依赖，即被主键的一部分决定。可降低数据冗余。比如说主键为联合主键学号和课名，分数完全依赖它，但姓名只依赖学号。

3NF：非主属性不依赖于其他的非主属性。比如说“系主任名”依赖于非主属性“系名”。

3.范式与反范式：（实际中一般是混合使用，不用完全范式化或完全反范式化）

范式优点是没有数据冗余即重复数据，表通常更小，只需要修改少量数据，更新操作通常比反范式快。缺点是通常需要多表关联查询，关联查询一般需要对另一张表或两张表的数据进行逐一对比，效率低下。

反范式优点是所有数据都在一张表中，无需关联查询。使得原来需要关联查询的操作变得很快。想一个场景，person表中有person.carid，而car表中有car.id，参考mysql副中的15条。缺点是数据冗余，更新效率低。

4.关系模型的3个组成部分：关系数据结构、关系操作集合、完整性约束。

1.数据完整性约束：对关系的某种约束，使存储在数据库中的所有数据值均处于正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

2.数据完整性约束分为四类：

    1. 实体完整性（实体完整性是对关系中的记录唯一性，一般通过主键约束和候选键约束实现。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。）建索引都在下，候选键约束在右写unique，会自动创建unique索引，主键在右边下面都可写，可以是联合索引。

    2. 域完整性（域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束。）

    3. 参照完整性（参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现）

4. 用户定义的完整性（check、not null）。P223 P224

约束是表级的强制规定，有以下五中：not null，unique，primary key，foreign key，check

第二章

1.MySQL是多用户、多线程（充分利用CPU资源）的SQL（结构化查询语言，世界上最流行和标准化的数据库语言）数据库服务器，用C/C++编写，支持多种OS，为编程语言提供API。

3.Schema\database都是数据库，用可视化工具操作mysql就是用产生的可编辑的SQL语句来操作。

第五章

1.如何选择存储引擎P82存储引擎是数据库建立与增删查改数据的实现方法。

InnoDB(默认)支持行级锁(只在用索引查找时生效，否则退化成表锁)，所以插入\更新\写操作比MYISAM更快，高并发表现更好。支持外键，加强表的参照完整性约束。支持事务处理。Innodb是索引和数据是紧密捆绑的，索引文件就是数据文件，也叫做“聚簇索引”，查找效率慢一些。

应用在：如果对事务的完整性要求比较高(比如银行)，或要求外键，或要高并发、进行频繁的插入更新。

MYISAM前缀压缩来减少索引节点的大小，先完全保存索引节点中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数（长度）和剩余的不同后缀部分，所以不能二分查找，空间和内存使用少。不支持外键。不支持事务处理，管理非事务表。使用表锁定机制，来同步多个并发的读写操作，维护表数据的一致\完整性。MyISAM的索引和数据是分开的，索引存放数据地址，也叫做“非聚集索引”，查找效率高，但写操作会锁定整张表(它只支持表锁)。

应用在：不要求支持事务，不要求外键，插入\更新\写操作比较少，要求高效率的读取。

MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。（因为它是写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘，崩溃时造成缓冲数据丢失。而InnoDB 是用事务日志进行崩溃恢复）

Memery数据全部放在内存中，使用存储在内存中的内容来创建表，有利于数据的快速处理，比前二者都快。所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。对表的大小有要求，只使用相对较小的数据库表。应用在：要求很快的读写而对数据安全性要求较低。支持哈希索引。

2.数据类型P85

第九章

1. P136如何运用前面知识查
2. distinct、order by、group by、limit、count（）
3. P147连接查询 P150子查询示例  P157表、字段别名  P158正则表达式
4. P165数学函数、字符串函数、日期和时间函数current_time,current_date，now、条件判断函数、系统信息函数connection_id获取服务器连接数、加密函数有可逆\不可逆、cast改变数据类型P185

第十一章

1.创表时创索引，已有表加索引，修改表的索引，删除索引P187

2.索引由数据表中一列或多列组合而成，为了优化数据库查询速度，不用遍历数据库中所有数据，但使插入删除变复杂。索引两种存储类型：BTREE(默认)\HASH索引。

m阶Btree是一个平衡的多路查找树，节点中指向子树的指针和key交替放置，key从左到右递增，叶子节点出现在同一层。与数据相关的key集合分布在整棵树，任何key只出现在一个结点中。根节点最少两个子树，其他节点最少m/2个子树，最多m。Ki<Ki+1，两个key中间的指针所指指子树的所有关键字都小于Ki+1，大于Ki。

B+tree①是一个平衡的多路查找树，节点中每个key对应一个子树，key从左到右递增，key对应子树中的所有key大于等于这个key②叶子节点出现在同一层,叶节点通过指针连接成链表结构，提高区间查询和遍历的性能，root指向根节点，sqt指向最小key节点，所以查找起点可选。③非叶节点中存储的key只起到导向作用，而与数据相关的key集合存储在叶子节点中，该节点的value存储数据或数据指针。

两种树在每个节点都是进行二分查找。树都是为了加快检索数据、减少磁盘的IO。

利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

Innodb主索引的叶子节点 value域记录着完整的数据记录，辅助索引的叶子节点的 value域记录着对应主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引树中进行查找。

B+树比B树：内部节点并没有指向key对应数据的信息的指针，因此相同节点大小时相对B树存放的key更多，高度更低，一个节点大小为一页，能一次读入更多key（索引），减少IO次数，查询更快。叶子节点形成有序链表，便于遍历和范围查询。所有的数据都存在叶子节点，其他都是导向索引，查询性能稳定。而红黑树太高，需要读取很多次io量大，且调整太频繁。

1. 联合索引（即多列索引），最左匹配原则：只有在查询条件中使用了联合索引时的第一个字段，索引才会被使用，所以在建立联合索引的时候查询最频繁的条件中的字段要放在左边。因为数据库是依据联合索引最左的字段来构建B+树，叶子节点中才有其他索引，而在叶子结点中只有靠左边的字段相同时，靠右边的字段才是有序的。

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好，因为查询到的需要返回的结果记录少。

让区分度最强的索引列放在前面，区分度越高，查询效率也越高。

4. 索引

普通索引、唯一索引、主键索引、多列索引、全文索引

优点：唯一索引，可以保证数据库表中的每一行数据的唯一性。减少查找时需要扫描的数据行，加快数据的检索读取速度。ORDER BY 和 GROUP BY 排序和分组操作(还有子查询)会创建临时表，B+Tree 索引是有序的，不需要排序和分组，可以减少临时表的创建。索引将随机 I/O 变为顺序 I/O，提高IO性能。

缺点：增删改维护索引要耗费时间。索引需要占用物理空间。

需要：频繁作为查询条件的字段应该创建索引，提高速度。

不需要：频繁更新的字段不适合创建索引。非常小的表、大部分情况下简单的全表扫描比建立索引更高效。查询条件里用不到的字段，不创建索引。取值区别度不大的字段如性别。

hash索引(实现方式)用hash表和key，因为比较的是进行Hash运算之后的Hash值，所以它只能用于等值查询，不能用作范围查询和避免排序，只有memory引擎支持。

全文索引。可以对表的多个字段建立全文索引，MySQL就会将这几列拼接成一个字符串，然后进行相似索引查询。

查询能够被索引覆盖，也就是能利用到索引，就叫查询覆盖了索引。

第十二章

1.视图是由数据库中的一个或多个表导出的临时虚拟表，方便用户对数据的操作，内容由查询定义，对视图的修改不影响原表。安全、简单使多表查询变成单表查询。create view tablename as select...

场景：查询的数据来源于不同的表，而查询者希望以单表的方式查询；只暴露部分字段给访问者，所以就建一个虚表

第十四章

2. 存储过程是用户定义的一组为了完成某种特定功能的SQL语句集，经编译后存储在数据库中。通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

优点：代码封装，保证了一定的安全性、代码复用性；预先编译，因此具有很高的性能。

第十五章

1. 触发器是由MySQL的基本命令（insert\delete\update）事件来触发的一系列特定操作。
2. 同一个表的同一个基本命令的同一时间（before\after）只能创建一个触发器。

第十六章

1. 事务由一条或多条SQL语句组成，每条语句相互依赖，一条出错则受影响数据回滚到事务开始前状态，所有成功则事务顺利执行，可保证一个业务的完整性与安全性（考虑多用户）。原子性、一致性(事务前后数据总额一致)、隔离性（不能让一个事务影响另一个，要让事务间感受不到正在并发进行）、持久性（执行成功后对数据的改变是永久的）。
2. 用事务前先把自动提交关了。回滚是要在commit前rollback的，提交后不能回滚，因为事务已经顺利执行。
3. 事务：初始化、创建、查询、提交、撤销。
4. 可以通过@@查看全局变量如autocommit。
5. 为了使事务互不影响，保证数据库安全性、一致性，采用事务隔离级别是十分必要的(如果多个线程操作，基于同一个查询结果对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来,1类为撤销覆盖,2类为提交覆盖)，级越高安全性越高但性能越低。应该考虑数据表具有完善的功能，且高效执行下，也不会对系统增加额外的负担（选择合理的引擎和孤立级，达到效率和功能的平衡）。

①脏读：在A事务读取了B事务中未提交的数据，而B可能回滚使A读到的数据产生不一致性。

②不可重复读：A事务两次读取同一行的数据，结果得到不同状态的结果，中间正好B事务更新了该数据，两次结果相异，不可被信任。

③幻读也叫虚读：一个事务执行两次规定范围的查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。一般情况下，幻象读应该正是我们所需要的，但有时候却不是。

④不可重复读查询的都是同一个记录行，而幻读针对的是范围查询或统计结果集。

Read uncommitted读未提交（最低级别，解决1类更新丢失，可能脏读。读取数据的事务允许其他事务访问该行数据，写事务不允许该行进行其他事务写操作，但允许其他事务读此行数据。）

Read committed读提交（可避免脏读的发生。读取数据的事务允许其他事务访问该行数据，所以会出现不可重复读，但是未提交的写事务则禁止任何其他事务。）

Repeatable read可重复读取（可避免脏读、不可重复读的发生。读取数据的事务将会禁止写该行事务（但允许读事务），写事务则禁止任何其他事务。）

Serializable序列化（事务串行化顺序执行，效率低下，比较耗数据库性能，一般不使用。可避免脏读、不可重复读、幻读的发生。）

Innodb默认隔离级别是RR，MVCC + next-key实现了RR并解决了幻读。

幻读都发生在insert或delete后。而且是快照读、当前读一个范围数据发生的。

https://www.cnblogs.com/limuzi1994/p/9684083.html

Mysql如何解决幻读https://blog.csdn.net/wzhzzzz/article/details/102552360

版本号指的是系统版本号，随着创建事务个数而递增，所以并不是一个事务对应一个版本号。事务每个记录有两个隐藏字段即创建删除版本号。快照读只读版本号小于他创建使时的版本号的快照，大于或等于的可能是其他事务的最新修改。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改

使用MVCC的读取叫做快照读，消除了幻读，而且读取undo log中的快照而不用加锁，为了提高高并发下的读取效率，快照存储在undo日志中，回滚指针把一个数据行（Record）的所有快照连接起来

当前读用间隙锁+记录锁=next-key。当前读的锁都用的X或S锁。

比如说select * from table where ? lock in share mode;是当前读，加S锁，其他的都加X锁

当我们用当前读的范围条件检索数据，对于字段值在条件范围内但并不存在的记录，叫做"间隙(GAP)"。InnoDB也会对这个"间隙"加锁，使insert失败。

解决快照读(如果事务2在事务3 select之后又插入数据，那么这个数据是属于版本3的，因为事务3已被创建，则事务3还是读刚才快照)与当前读(读取当前最新数据，会加上next-key)的幻读现象。

1. 数据库锁机制：表锁行锁，锁粒度越小，加锁开销越大(包括检索行获取锁释放锁、检查行的锁状态)。共享锁(读\S锁)，排它锁(写\X锁)。

1. 首先说明：数据库的增删改操作默认都会加排他锁，而查询会加共享锁(快照读则不会)。
   |--共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改（即加排它锁）。要想修改就必须等所有共享锁都释放完之后。语法为：
   select * from table lock in share mode
   |--排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。语法为：
   select * from table for update --增删改自动加了排他锁

死锁（目前innodb对死锁的处理是将持有最少行级排它锁的事务回滚）：

假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁，当 T1 的 select 执行完，准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update.在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。

2.悲观锁依靠数据库的排他锁机制实现，保证操作最大程度的独占性，但如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是长事务的加锁。

乐观锁认为数据一般情况下不会发生并发冲突，只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息给用户。一般通过比对更新时读取版本号\时间戳字段和上次读取的进行比对如果相同则更新。

第十八章

1. 用mysqldump备份导出数据库或数据表，用use tbname source filename读取导入。

1. 事务日志：存储引擎在修改表数据时只需要将数据的更新操作记录到持久在硬盘上的事务日志中，不用每次将都修改的数据本身持久到磁盘，所以IO不需要在多个地方移动磁头，效率很高，事务提交后或日志已满，再从内存根据日志慢慢将数据持久化到磁盘。提高事务效率，并且有助于数据的恢复（崩溃重启后可以查看到哪步操作了）。

第十九章

1. Show create table tbname查看表创建时的详细结构（包括索引）
2. 用show status like’value’查询数据库性能，有连接、慢查询、查询、插入、删除次数。
3. ①explain分析查询语句，查扫描到的row、使用的key、type(查询所使用的访问类型，表示查询的sql语句好坏，全表扫描<索引扫描<范围扫描)。

①优化索引--经常作为查找条件、区分度大的(最好是唯一性索引)作为索引。区分度大的、常用的放在左边。使用多列索引可以比单列索引返回更少的记录。

②优化查询

1.用带索引的条件查询。

2.只返回必要的列：最好不要使用 SELECT * 语句。

3.只返回必要的行：使用 LIMIT 语句来限制返回的数据。

4.多表查询--尽量用连接而非子查询，mysql就不需要建立临时表。

5.用慢查询日志定位慢查询。

6.切分大查询为小查询，减少锁争用，比如查询1-10000可以拆为1000查询一次。

分解关联查询成每一个表进行一次单表查询，然后在应用程序中进行关联。

1.让缓存更高效。（对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用）

2.减少锁竞争，防止一次锁住很多表。3.在应用层做关联，容易对数据库进行拆分，扩展性更好。

③缓存重复查询的较热点数据。

④使用一次插入多条记录的语句，可以减少与数据库之间的连接操作。

⑤调整字段的数据宽度，要用多少设多少，表越小，在它上面执行的查询也就会越快(因为缓存结果变多、索引树变矮)。

第二十章

慢查询：如大表没索引就会逐行扫描。

要设计合理的查询和索引(库表结构优化、优化查询、优化索引)

Set slow_query_log=1开启慢查询跟踪

set long_query_time=1;超时时间

slow_query_log_file慢查询日志文件

具体记录了：是那条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。

分库分表：

数据库中的数据量猛增，单机存储容量、连接数、处理能力都有限，访问性能也变慢，而单一数据表也会出现如锁争用、索引树太高的问题，分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题。大数据库拆分成若干数据库 ，大表拆分成若干数据表，使得单一数据库、单一数据表的数据量变小来缓解性能问题，提升数据库性能。

垂直分表：同一个数据库中，一个表按字段分成多表。①将访问频率较高的和较低字段分在两表中，一般也是将大字段拆出来放在附表中。②经常组合查询的列放在一张表中（就不用联合查询了）。

效果：减少锁表的几率，减少争用行锁的几率（即提高并发性）。数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

垂直分库：按业务将表分放在不同数据库，数据库可以分在不同服务器上，多个服务器性能分担并发压力。

水平分库：把同一个表的数据保持原表字段结构，按行分成多个表，放到不同的数据库中，每个库可以放在不同的服务器上。解决单台服务器大数据的高并发性能瓶颈(连接数、处理能力)。

水平分表：在同一个数据库内，把一个表的数据按行分到多个表中。

效果：减少一个表的数据量，于是降低索引树的深度，于是提高检索性能。减少表锁的争用几率，于是提高并发性。

表空间方式：

1.共享表空间方式

由于是默认的方式，就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个（或几个）文件中，比较利于管理，而且在操作的时候只需要open这一个（或几个）文件即可，相对来说代价很低。

但问题是在数据达到以G为单位来计算的时候优劣逆转。①一个大小惊人的文件很不利于管理。②一个巨大的文件来说，读写它需要耗费的资源一样巨大。③将索引和数据保存于同一个文件中，索引和数据之间尚存在资源争用，不利于性能的提升。

2.独立表空间方式

相对而言对立表空间每个表都有独立的多个数据文件，而且做到了索引和数据的分离。多个小文件之间很方便的完成跨数据库甚至跨硬件的数据拷贝和迁移。相对来说灵活性很好。

但是“仅仅做文件拆分而已”，多个文件缺一不可，当数据库中的表数量达到一定级别时，①每次操作所涉及的文件过多，如果按照默认Centos的ulimit -n = 1024的话，仅仅只能保证同时打开256个表以内。②这种数据文件的利用率不算很高，当大量“不高”的文件集中起来，浪费的空间很惊人。