MySQL的一些知识点

数据库的三范式

第一范式: 列不可再分
第二范式: 行可以唯一区分,主键约束
第三范式: 表的非主属性不能依赖于其他表的非主属性,外键约束
三大范式是一级一级以来的,第三范式建立在第二范式上,第二范式建立在第一范式上

MySQL数据库引擎简介

1. MyISAM: 只支持表锁,拥有较高的执行速度,不支持事务,不支持外键,并发性能差,
   占用空间相对较小,没有事务要求,以新增,查询为主的应用可以使用此引擎.
2. Innodb: 支持表锁和行锁,提供了具有提交,回滚和崩溃恢复能力的事务安全,支持自动增长列,支持外键约束,并发能力强,占用空间是MyISAM的2.5倍,处理效率会相对差一些.
3. Memory: 支持表锁,存储在内存中,速度快,数据在MySQL重启时会丢失,默认使用hash索引,不适用范围查找,主要用于内容变化不频繁的表.

Innodb与MyISAM的区别

1. Innodb支持事务,MyISAM不支持
2. Innodb支持外键,MyISAM不支持
3. Innodb是聚集索引,索引和数据是存在一起的,必须要有主键,主键索引的效率很高,一次查询就可以取到数据.非主键索引需要两次查询,第一次获取对应记录的主键,第二次根据主键获取对应的数据.而MyISAM是非聚集索引,索引和数据存储在不同的文件,主键索引保存的是数据位置的指针.
4. Innodb不保存表数据的行数,执行SELECT COUNT(*) FROM table时会全表扫描.二MyISAM用一个变量来保存整个表的行数,执行上述语句只要读出变量即可,速度很快.
5. Innodb不支持全文索引,MyISAM支持全文索引,查询效率上MyISAM更高.

如何选择引擎

如果没有特别的需求,使用默认的Innodb即可.
MyISAM适合以读写为主的的应用程序,如: 播客系统,新闻门户网站.
Innodb适合更新,删除操作频率高且需要事务和外键支持的应用,如: OA自动化办公系统.

数据库事务

什么是事务?

一批sql语句执行时要么全部成功,要么全部失败.

数据库事务特性

1. 原子性: 组成一个事务的多个数据库操作是一个不可分割的原子单元,只有所有操作都成功,整个事务才会提交.任何一个操作失败,已经执行的任何操作都必须撤销,让数据库返回初始状态.
2. 一致性: 事务操作成功后,数据所处的状态和它的业务规则是一致的.即数据逻辑正确性不会被破坏,例: A转账100元给B,不管操作成功与否,A和B的账户总额数值不变.
3. 隔离性: 在并发数据操作时,不同的事务拥有各自的空间,他们的操作不会对彼此产生干扰.
4. 持久性: 一旦事务提交成功,事务中的所有操作都必须持久化到数据库中.

事务并发问题

1. 赃读: 当一个事务正在访问数据库并对数据进行了修改,而这修改还没提交,这时另一个事务也访问了这个数据,然后使用了这个数据.因为这个数据是还没有提交的数据,那么另一个事务读到的数据就是脏数据,依据脏数据所作的逻辑操作可能是不正确的.
2. 丢失修改: 一个事务在读取一个数据时,另一个事务也访问了该数据,那么在第一个事务修改了这个数据后,第二个事务也修改了这个数据,这样第一个事务内的修改结果就会丢失,称为丢失修改.
3. 不可重复读: 在一个事务内多次读取同一数据,在这个事务还没有结束时,另一个事务也访问该数据.那么在第一个事务的两次读取中,由于第二个事务的修改导致数据可能不太一样,这样就发生了一个事务中,两次读取同一数据得到的结果不一样,因此称为不可重复读.
4. 幻读: 一个事务读取了指定条件的一批数据,在这个事务未完成前,另一个事务插入了一些数据到这批数据中,后续第一个事务又读取了这批数据,发现这批数据多出一些原本不存在的数据,称为幻读.

事务的隔离级别

SQL标准定义了4个隔离级别:

1. 读未提交: 最低的隔离级别,允许读取另一个事务未提交的数据,可能导致赃读,幻读和不可重复读.
2. 读已提交: 允许读取另一个事务已提交的数据,可能会出现幻读和不可重复读.
3. 可重复读: 对同一字段多次读取的结果都是一致的,除非数据是被本身事务所修改,可能出现幻读.
4. 串行化: 最高隔离级别,所有事务一次串行执行,这样不同事务之间就不会相互影响.赃读,幻读和不可重复读都不会出现.

隔离级别	赃读	幻读	不可重复读
读未提交	√	√	√
读已提交	x	√	√
不可重复读	x	x	√
串行化	x	x	x

MySQL索引

索引的本质

索引是对数据库表中一列或多列进行排序的数据结构,索引的主要目的是提交检索速度.

MySQL的索引类型

1. 主键索引: 一张表只能有一个主键索引,一个主键索引可以由多个字段组成.主键不允许重复,不能为null,在Innodb引擎中,主键索引为聚集索引.
2. 唯一索引: 唯一索引不可重复,可以为null,一张表可以有多个唯一索引,一个唯一索引可以由多个字段组成.
3. 普通索引: 一张表可以有多个普通索引,一个普通索引可以由多个字段组成,普通索引为非聚集索引.
4. 全文索引: MySQL5.6之前只有MyISAM引擎支持全文索引,MySQL5.6及以后,MyISAM和Innodb都支持全文索引.全文索引主要用于支持模糊查询.

索引的优缺点

优点

1. 在数据表数据量达到一定级别后,合理利用索引可以避免全表扫描,提高检索速度.
2. 唯一索引可以用来保证字段的唯一性.

缺点

1. 索引本身需要占用物理空间.
2. 索引会降低新增,修改,和删除操作的速度.

索引并非越多越好,对于数据量足够大,且以查询为主的表可以合理创建索引.

MySQL索引底层数据结构

B-Tree特点

1. 节点为M叉,不再是二叉,所以相同的节点数量,B-Tree的高度要比二叉树小得多.
2. 树的高度降低可以降低磁盘IO读取次数,可以用cpu来分担IO压力.一个节点存储大量数据,符合磁盘局部性原理(软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；磁盘中由块组成，数据所在的块不同都需要重新寻址查找数据。操作系统与磁盘打交道的最小单位是磁盘块。目前是4k大小。1个节点的数据如果为块的大小，读取一个节点的数据全读取出来，未来查找的数据在读取的数据中，将会提升效率。)
3. B-Tree中所有节点的最大孩子节点数称为B-Tree的阶,用M表示.B-Tree中每个节点至多有M个子树.每个非叶子节点(根节点除外)至多有M/2向上取整个子树.根节点至少有2个子树,除非根节点的孩子是叶子节点.所有叶子节点都在同一层,且内部节点不携带任何信息.
4. 中序遍历得到所有数据.

B+Tree特点

1. 在B-Tree的基础上进行了改进,只有叶子节点存储数据.
2. 叶子节点增加了链表结构,更适合进行范围查找,遍历不需要进行中序遍历.
3. 非叶子节点不存储数据,因此可以存储更多的关键信息,如索引.

B-Tree和B+Tree比较

1. B-Tree的节点都存储数据,在数据库中进行查找定位的时候,会比B+Tree多出不必要的读取.
2. B-Tree在遍历,分组,排序时需要中序遍历才能得到,而B+Tree的链表结构本身就是有序的.
3. B-Tree中同一数值不会重复出现,可能出现在非叶子节点和叶子节点.而B+Tree中同一数值一定会出现在叶子节点,并且可能也出现在非叶子节点中.
4. 因为B-Tree数据的位置不确定在非叶子节点还是在叶子节点,且只在整个树中出现一次,虽然可以节省存储空间,但使得插入,删除操作的时间复杂度增加,B+Tree相对而言是一种较好的折中.
5. B-Tree的查询效率与某数值在整棵树中的位置有关,数值在叶子节点的时候为最大时间复杂度与B+Tree相同(O(log(n))).数值在根节点的时候为最小时间复杂度(O(1)).而B+Tree的时间复杂度对某建成的树来说是固定的(O(log(n))).

MySQL查询优化

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num is null
   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
   select id from t where num=0
4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num=10 or num=20
   可以这样查询：
   select id from t where num=10
   union all
   select id from t where num=20
5. 下面的查询也将导致全表扫描：
   select id from t where name like ‘%abc%’
   若要提高效率，可以考虑全文检索。
6. in 和 not in 也要慎用，否则会导致全表扫描，如：
   select id from t where num in(1,2,3)
   对于连续的数值，能用 between 就不要用 in 了：
   select id from t where num between 1 and 3
7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
   select id from t where num=@num
   可以改为强制查询使用索引：
   select id from t with(index(索引名)) where num=@num
8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   select id from t where num/2=100
   应改为:
   select id from t where num=100*2
9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   select id from t where substring(name,1,3)=‘abc’–name以abc开头的id
   select id from t where datediff(day,createdate,‘2005-11-30’)=0–'2005-11-30’生成的id
   应改为:
   select id from t where name like ‘abc%’
   select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’
10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12. 不要写一些没有意义的查询，如需要生成一个空表结构：
    select col1,col2 into #t from t where 1=0
    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
    create table #t(…)
13. 很多时候用 exists 代替 in 是一个好的选择：
    select num from a where num in(select num from b)
    用下面的语句替换：
    select num from a where exists(select 1 from b where num=a.num)
14. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
22. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
30. 尽量避免大事务操作，提高系统并发能力。