0%

EveryThing About MySQL

Posted on Edited on Valine:
U can find everything be need in here.

MYSQL

彻底搞懂系列B-树、B+树、B-树、B*树

B树和B+树的插入、删除图文详解

关系型数据库和非关系型数据库为什么用B树或B+树

==B+树的数据只出现在叶子节点上==，因此在查询单条数据的时候，查询速度非常稳定。因此，==在做单一数据的查询上，其平均性能并不如B树==。但是，B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，==只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询==。

  • 没准是==Mysql中数据遍历操作比较多，所以用B+树作为索引结构==。凡做这种关联查询，你躲不开join操作的！既然涉及到了Join操作，无外乎从一个表中取一个数据，去另一个表中逐行匹配，如果索引结构是B+树，叶子节点上是有指针的，能够极大的提高这种一行一行的匹配速度
  • ==而Mongodb是做单一查询比较多，数据遍历操作比较少==，所以用B树作为索引结构。

那么为什么Mysql做数据遍历操作多？而Mongodb做数据遍历操作少呢？因为Mysql是关系型数据库，而Mongodb是非关系型数据。

平衡二叉树则是子树高读不能超过2，B树非叶子节点也可以存储数据，B+树则是叶子节点才可以存储数据。

  • 我们说的==平衡二叉树结构，指的是逻辑结构上的平衡二叉树，其物理实现是数组==。然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。而适合作为索引的结构应该是尽可能少的执行磁盘IO操作，因为执行磁盘IO操作非常的耗时。因此，平衡二叉树并不适合作为索引结构
  • 红黑树这种结构==h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性==，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。也就是说，使用红黑树（平衡二叉树）结构的话，每次磁盘预读中的很多数据是用不上的数据。因此，它没能利用好磁盘预读的提供的数据。然后又由于深度大（较B树而言），所以进行的磁盘IO操作更多。
  • ==B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能==。每次读取磁盘页时就会读取一整个节点。==也正因每个节点存储着非常多个关键字，树的深度就会非常的小==。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。
  • ==B树的查询，主要发生在内存中，而平衡二叉树的查询，则是发生在磁盘读取中==。因此，虽然B树查询查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时就可以忽略不计了。因此，B树更适合作为索引。

一步步分析为什么B+树适合作为索引的结构 以及索引原理(阿里面试，写的很好

B+Tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引(Memory表只存在内存中)，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。

  • 带顺序访问指针的B+Tree

    B+Tree所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。这样做是为了提高区间效率，例如查询key为从18到49的所有数据记录，当找到18后，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率。

  • 大大减少磁盘I/O读取

    数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点需要一次I/O就可以完全载入

全文索引（FULLTEXT）=mysql的myISAM搜索引擎默认的索引类型==不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法==。

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE%word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

这类场合正是全文索引(full-textindex)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：
ALTER TABLE table_name ADD FULLTEXT(column1, column2)

有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：
　　
SELECT * FROM table_name WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

InnoDB如何解决幻读

使用MVCC

一条SQL语句是如何执行的

由server层和存储引擎组成

server 层包括连接器、分析器、优化器、执行器，涵盖 MySQL的核心服务，以及所有的内置函数（如日期、时间、数学、加密函数等），还有所有跨存储引擎的功能，例如存储过程、触发器、视图等。

一条SQL更新语句是如何执行的？

里面包含binlog和redolog,两阶段提交

什么叫视图？游标是什么？

  • 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
  • 游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

请简洁描述Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

在Mysql中ENUM的用法是什么？

同一个属性里面包含多个字段，每个字段都有自己的索引.enum 在数据库底层以整型方式储存，从 1 开始，0 用于指代空或错误的字符串。

CHAR和VARCHAR的区别？

char 固定长度255，不足的部分补空格，取出时再去掉空格。varchar 不定长度

drop,delete与truncate的区别

drop删除表，truncate清空表，delete删除表、drop和truncate不能回滚。

存储过程与触发器的区别

  • 触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
  • 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。可以供外部程序调用，比如：java程序。

    存储过程是预编译过的，执行效率高。存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。安全性高，执行存储过程需要有一定权限的用户。存储过程可以重复使用，可减少数据库开发人员的工作量。==缺点：移植性差==。

2）可以供外部程序调用，比如：java程序。

完整性约束包括哪些？

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

  • 实体完整性：规定表的每一行在表中是惟一的实体。
  • 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
  • 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
  • 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE)

MySql不支持check

Mysql 的存储引擎,myisam和innodb的区别。

NULL | myisam | innodb

—|—|—
事务支持|不支持| 支持| row 2 col 2
存储结构 |三种文件 | 一种文件
存储空间 |体积小 | 体积大
可移植性 |方便 | 困难
锁力度 |表级锁| 表级锁，行级锁
全文索引 |支持 | 不支持
表主键 |可以没有|必须有，没有就自动生成
外键 |不支持 | 支持

1.锁

  • MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发。
  • Innodb是支持事务的存储引擎，合于插入和更新操作比较多的应用，设计合理的话是行锁（最大区别就在锁的级别上），适合大数据，大并发
    image

    表锁和行锁的区别

    表锁
  • 特点：开销小、加锁快、无死锁；锁粒度大，发生锁冲突的概率高，高并发下性能低
  • 加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。
    行锁
  • 特点：锁的粒度小，发生锁冲突的概率低、处理并发的能力强；开销大、加锁慢、会出现死锁

  • 加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。

  • 乐观锁：不加锁，只有在更新时验证数据是否被其他线程更新，吞吐量较高，适用于多读场景。它==假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，那么当前正在提交的事务会进行回滚==。

    乐观锁实现方式是：版本号version和CAS(compare and swap)

  • 悲观锁：读取时加锁，更新完释放锁，再此过程中会造成其他线程阻塞，导致吞吐量低，适用于多写场景。当事务A对某行数据应用了锁，==并且当这个事务把锁释放后，其他事务才能够执行与该锁冲突的操作==，这里事务A所施加的锁就叫悲观锁。

行锁(共享锁和排他锁),间隙锁(next-keylock）都属于悲观锁。
InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：==允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁==。
  • 排他锁（X)：==允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。==
    另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁（IX）：事务打算给数据行加行排他锁* ，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
    间隙锁（Next-Key锁）:
  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
  • 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key)锁。
  • 1:表级锁不会死锁,行级锁和页级锁可能死锁
  • 2:在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
  • 3:==从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应==用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
  • 4:意向锁是InnoDB自动加的，不需用户干预。==对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)==；对于普通SELECT语句，InnoDB不会加任何锁；
  • 5:InnoDB行锁实现方式: ==InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁==

Mysql中MVCC的使用及原理详解

MVCC是一种多版本并发控制机制。MVCC是通过保存数据在某个时间点的快照来实现的。在每个表后添加创建版本号和删除版本号，版本号为修改该项的事务版本号。

  • SELECT:InnoDB会根据以下两个条件检查每行记录

    InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.

行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.
只有a,b同时满足的记录，才能返回作为查询结果.

  • DELETE:InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

  • UPDATE:InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间。

锁策略

2.日志

redolog，binlog，undolog对事务的影响

  • binlog
  • redolog,保证事务的持久性，缓存区中数据会慢慢更新到数据库的硬盘上。
  • undolog，记录了修改几率，可以随便回滚，保证原子性。

3.事务

MVCC (MultiVersion Concurrency Control) 叫做==多版本并发==控制。==由undolog和记录版本的一个东西组成==。

InnoDB的 MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。

以上片段摘自《高性能Mysql》这本书对MVCC的定义。他的主要实现思想是通过==数据多版本==来做到==读写分离==。从而实现不加锁读进而做到读写并行。

MVCC在mysql中的实现依赖的是undo log与read view

  • undolog :undolog中记录某行数据的多个版本的数据。
  • read view :用来判断当前版本数据的可见性

    ACID特性中的一致性是如何实现的

  • 事务的原子性是通过 undo log 来实现的
  • 事务的持久性性是通过 redo log 来实现的
  • 事务的隔离性是通过 (读写锁+MVCC)来实现的
  • 而事务的终极大boss一致性是通过原子性，持久性，隔离性来实现的！！！

    原子性，持久性，隔离性折腾半天的目的也是为了保障数据的一致性！ACID只是个概念，事务最终目的是要保障数据的可靠性，一致性

  • 事务是如何通过日志实现的
  • mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

    redolog来记录已成功提交事务的修改信息，并且会把redolog持久化到磁盘，系统重启之后在读取redo log恢复最新数据。redolog是用来恢复数据的 用于保障，已提交事务的持久化特性

undolog 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，redolog记录数据被修改后的信息。undolog主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。undolog记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undolog的信息来进行回滚到没被修改前的状态。

redo和undo基本流程如下：
因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

4.索引

MySQL索引背后的数据结构及算法原理

多种引擎的实现区别？聚族索引，非聚族索引，二级索引，唯一索引、最左匹配原则等等（非常重要）

  • 唯一索引

    唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

  • 主键索引

    数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

  • 聚集索引

    表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

    建索引有哪些原则

    索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说应该在这些列上创建索引：

  • 在经常需要搜索的列上，可以加快搜索的速度

  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

什么情况下设置了索引但无法使用

  • 条件中使用了 or，而相关列不全有索引
  • 条件中使用了and，而相关列不满足最左原则
  • 索引列的数据类型出现隐形转换，如varchar不加单引号的话可能会自动转换为int型）
  • like查询是以%开头
  • where 子句里对索引列上有数学运算/函数
  • mysql估计使用全表扫描要比使用索引快

    什么情况下不宜建立索引？

  • 对于那些在查询中==很少使用或者参考的列不应该创建索引==。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  • 对于那些==只有很少数据值的列==也不应该增加索引。举例比如性别只有男和女
  • 对于==那些定义为text,image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少==。
  • 经==常频繁更新的列==不要建立索引，因为肯定会影响插入或更新的效率
  • 尽量避==免在 where 子句中使用 ！= 或者 <> 操作符，查询引用会放弃索引而进行全表扫==描。
  • 当==修改性能远远大于检索性能时，不应该创建索引==。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
  • 尽量避免NULL：在MySQL中，==含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂==。可以采用0、一个特殊的值或者一个空串代替空值

    创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

    为什么用B-树

    上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了==磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入== 为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

为什么不用红黑树

红黑树这种结构，h(层数)明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

MySQL索引失效的情形很多。

例如： 在WHERE条件的LIKE关键字匹配的字符串以”%“开头，这种情况下，索引是不会起到作用的；WHERE条件中使用OR关键字来连接多个查询条件，如果有一个条件没有使用索引，那么其他的索引也不会起作用；多列索引的第一个字段没有使用，那么这个多列索引也不会起作用。 使用in查询时，in查询条件超过数据库表的一半的时候也会失效。

最左匹配原则

  • 索引字段要尽量的小
  • 索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

MySQL索引优化深入分析执行计划

如果有2级索引怎么存

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，==磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。==这样做的理论依据是计算机科学中著名的==
局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中==。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行

5.SQL语句

SQL语句菜鸟教程

SQL教程

  • AND运算符，如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

  • OR运算符如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

    1
    2
    3
    SELECT * FROM Websites
    WHERE alexa > 15
    AND (country='CN' OR country='USA');

  • ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

  • IN操作符IN操作符允许在WHERE子句中规定多个值。

  • BETWEEN操作符用于选取介于两个值之间的数据范围内的值。

    1
    2
    3
    SELECT * FROM Websites
    WHERE (alexa BETWEEN 1 AND 20)
    AND country NOT IN ('USA', 'IND');

  • INNER JOIN：如果表中有至少一个匹配，则返回行

  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行

SQL高级教程

  • 模糊查询(like)
    1
    2
    3
    4
    SELECT * FROM Websites
    以"G"开始的所有客户WHERE name LIKE 'G%';
    以"k"结尾的所有客户WHERE name LIKE '%k';
    还可以写NOT LIKE做反向查询

解释MySQL外连接、内连接(outer join和inner join)与自连接的区别外连接
SQL JOINS

  • 左连接：连接两张表，左表数据完全显示，右表匹配，匹配不上的补null；在这里，t_employee就是左表，也就是基准表，==用基准表的数据去匹配右表的数据，所以左表的记录是全部会查询出来的，如果右表没有记录对应的话就显示null==。

    LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

  • 右连接：同上，换个方向；
    全外连接：两表字段都匹配，匹配不上的补null；
  • 内连接：所有查询出的结果都是能够在连接的表中有对应记录的。这就是内连接的特点，==只查询在连接的表中能够有对应的记录==，

    INNER JOIN关键字在表中存在至少一个匹配时返回行。

  • 全外连接：==mysql是没有全外连接的==(mysql中没有full outer join关键字)，==想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接==。如果在Oracle中，直接就使用full outer join关键字连接两表就行了。

  • UNION

    SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

    1
    2
    3
    4
    SELECT country FROM Websites
    UNION
    SELECT country FROM apps
    ORDER BY country;

  • SELECT INTO

    通过 SQL，您可以从一个表复制信息到另一个表。SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中

1
2
3
CREATE TABLE 新表
AS
SELECT * FROM 旧表

  • INSERT INTO SELECT

    通过 SQL，您可以从一个表复制信息到另一个表。INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

    1
    2
    INSERT INTO table2
    SELECT * FROM table1;

  • 自连接：找到两表的公共字段，然后和内连接一样。

  • 交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

子查询

一个SELECT语句嵌套在另一个SELECT语句中，子查询也叫做内部查询，而包含子查询的语句又称为外部查询或主查询，子查询自身可以包含一个或多个子查询，一个查询语句中可以嵌套任意数量的子查询。

SQL查询重复记录

MySQL两道经典SQL面试题(行转列/列转行/求最值)

SQL函数

  • GROUP BY

    GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

    1
    2
    3
    统计 access_log 各个 site_id 的访问量：
    SELECT site_id, SUM(access_log.count) AS nums
    FROM access_log GROUP BY site_id;
1
2
3
4
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;
  • HAVING

    在 SQL 中增加 HAVING 子句原因是，==WHERE 关键字无法与聚合函数一起使用==。HAVING 子句可以让我们筛选分组后的各组数据。

    1
    2
    3
    4
    5
    查找总访问量大于 200 的网站。
    SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites
    ON access_log.site_id=Websites.id)
    GROUP BY Websites.name
    HAVING SUM(access_log.count) > 200;

6.SQL优化

慢查询解决的基本步骤
500万数量级查询优化
我们是如何对实际项目进行查询优化的
巧用这19条MySQL优化，效率至少提高3倍
为什么要分区，分表和分库？
一次SQL查询优化原理分析（900W+数据，从17s到300ms
面试官：给我讲一下分库分表方案
数据库如何保证数据不丢失
MYSQL的主从同步延迟的原因及解决方案
  • mysql主从复制用途

    从库生成两个线程，一个I/O线程，一个SQL线程；i/o线程去请求主库的binlog，并将得到的binlog日志写到relaylog（中继日志）文件中；主库会生成一个 log dump 线程，用来给从库i/o线程传binlog；
    SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

实时灾备，用于故障切换,==读写分离(主写从读比例为10：1)==，提供查询服务
备份，避免影响业务

  • 主从部署必要条件：
    主库开启binlog日志（设置log-bin参数）,主从server-id不同,从库服务器能连通主库
    数据库如何保证高可用
    灰度发布
    在灰度发布开始后，先启动一个新版本应用，但是并不直接将流量切过来，而是测试人员对新版本进行线上测试，启动的这个新版本应用，就是我们的金丝雀。如果没有问题，那么可以将少量的用户流量导入到新版本上，然后再对新版本做运行状态观察，收集各种运行时数据，如果此时对新旧版本做各种数据对比，就是所谓的A/B测试。

当确认新版本运行良好后，再逐步将更多的流量导入到新版本上，在此期间，还可以不断地调整新旧两个版本的运行的服务器副本数量，以使得新版本能够承受越来越大的流量压力。直到将100%的流量都切换到新版本上，最后关闭剩下的老版本服务，完成灰度发布。

如果在灰度发布过程中（灰度期）发现了新版本有问题，就应该立即将流量切回老版本上，这样，就会将负面影响控制在最小范围内。

MySQL优化

创建表时候的优化
  • 整数TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存储 8,16,24,32,64 位存储空间。使用 Unsigned 表示不允许负数，可以使正数的上线提高一倍。
  • 实数Float,Double , 支持近似的浮点运算。
    Decimal，用于存储精确的小数。
  • 字符串VarChar，存储变长的字符串。需要 1 或 2 个额外的字节记录字符串的长度。Char，定长，适合存储固定长度的字符串，如 MD5 值。
  • Blob，Text为了存储很大的数据而设计的。分别采用二进制和字符的方式。
  • 时间类型DateTime，保存大范围的值，占 8 个字节。TimeStamp，推荐，与 UNIX 时间戳相同，占 4 个字节。
    SQL语句优化
  • 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
  • 用EXISTS替代IN、用NOT EXISTS替代NOT IN
  • 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤。
  • 尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
  • Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
    索引优化(选择索引的数据类型)
    MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。
  • 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快。
  • 简单的数据类型更好：整形数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应用内置的日期和时间数据类型，而不是字符串来存储时间；以及用整形数据存储IP地址。
  • 尽量避免NULL：应该制定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。
  • 为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引应该将最常用（频率）做限制条件的列放在最左边，一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引。
    分库分表优化

  • 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力

    方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上。

  • 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺

    用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)，
    ==可以把学校范围内的用户单独独立出来一张表==。

MySQL不需要执行后面的复杂操作，就可以直接返回结果，效率很高，==但是查询缓存失效非常频繁，只要有对一个表的更新，这个表的所有查询缓存都会被清空，因此可能你费力地把结果缓存起来，还没使用，就被一个更新全部清空了。除非你的业务是一张静态表，很长时间才会更新一次，这种情况下可以使用查询缓存==。(==把管理员信息放进去==)

7.三范式

第一范式（1NF）

==如果一个关系模式的所有属性的域都是原子的是指数据库表的每一列都是不可分割的基本数据项==，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。（2NF）==要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性==，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

第三范式（3NF）

  • 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）
  • 优点:可以尽量得减少数据冗余，使得更新快，体积小
  • 缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化