MySQL基础知识

MySQL基础知识

关系型数据库的三大范式

	描述
第一范式	数据库表的每一列都是不可分割的基本数据项，即一列中不能有多个值
第二范式	在满足1NF的基础上，数据库表中每个实例必须可以被唯一的区分 非主关键字字段必须完全依赖于主关键字字段
第三范式	在满足2NF的基础上，数据库表中不能包含在其他表中已包含的非主关键字信息 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

MySQL查询生命周期

• 客户端发送一条查询给服务器
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器段进行SQL解析、预处理，在优化器生成对应的执行计划
• mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端

mysql客户端和服务器通讯

mysql客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让mysql通信简单快速，但也限制了mysql。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

查询状态

对于mysql连接，任何时刻都有一个状态，该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释：

• sleep：线程正在等待客户端发送新的请求；
• query：线程正在执行查询或者正在将结果发送给客户端；
• locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
• analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
• copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
• sorting Result：线程正在对结果集进行排序。
• sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。
 如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前 查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

• 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
• 查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
• 执行计划：mysql不会生成查询字节码来执行查询，mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

查询执行引擎

在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。
mysql简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询，mysql只需要重复执行计划中的各个操作，知道完成所有的数据查询。

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么mysql在这个阶段也会将结果放到查询缓存中。
mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对mysql的封包进行缓存然后批量传输。

SQL语句执行顺序

from--where--group by--having--select--order by,

• from:需要从哪个数据表检索数据
• where:过滤表中数据的条件
• group by:如何将上面过滤出的数据分组
• having:对上面已经分组的数据进行过滤的条件
• select:查看结果集中的哪个列，或列的计算结果
• order by :按照什么样的顺序来查看返回的数据

varchar和char的区别

• 定长和变长
  char 表示定长，长度固定，varchar表示变长，即长度可变。
  当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。
  如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。
• 存储的容量不同
  char ：最多能存放的字符个数 255，和编码无关。
  varchar：最多能存放 65532 个字符

varchar(20)中20的含义

varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个

int(M)中M的含义

M表示最大显示宽度, 建表若设置了zerofill(0填充), 会在数字前面补充0.既
假设有一个变量名为id，它的能显示的宽度能显示10位。在使用id时，假如我给id输入10，那么mysql会默认给你存储0000000010。当你输入的数据不足10位时，会自动帮你补全位数。假如我设计的id字段是int(20)，那么我在给id输入10时，mysql会自动补全18个0，补到20位为止。

drop、delete与truncate

drop 直接删掉表
truncate 删除表中数据，再插入时自增长id又从1开始
delete删除表中数据，可以加where字句

MySQL存储引擎

MyISAM和InnoDB的区别

描述	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
锁表	表锁，即使操作一条记录也会锁住整个表 不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据 对内存要求较高，而且内存大小对性能有决定性的影响
特点	性能，查询速度快	事务
数据压缩	支持	不支持

索引

为什么使用索引

在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关

MySQL索引分类

MySQL索引包括非唯一索引、唯一索引、全文索引、空间索引

• 唯一索引
  唯一索引是不允许其中任何两行具有相同索引值的索引

• 非唯一索引
  非唯一索引是相对唯一索引，允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，数据库是允许将新创建的索引与表一起保存

• 全文索引
  全文索引时将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获取全文中有关章，节，段，句，词等信息，也可以进行各种统计和分析。
  只能为CHAR、VARCHAR、TEXT列创建

• 空间索引
  空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
  MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

聚簇索引和非聚簇索引

按照执行的过程，索引可以聚簇索引和非聚簇索引两个类型

聚簇索引(聚集索引)

　　聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
　　简单来说聚簇索引就是把索引的叶子节点保存的是整行数据
　　Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优缺点
优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
  缺点：
• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

辅助索引（非聚簇索引）

　　在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
　　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

索引设计原则

• 选择唯一性索引
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引
• 限制索引的数目
• 尽量使用数据量少的索引
• 数据量小的表最好不要使用索引
• 尽量使用前缀来索引

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上
（1）如果第一个字段是范围查询需要单独建一个索引
（2）在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边
当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合