MySQL

数据库三大范式

范式是在关系型数据库中，建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。

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

求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

请写出 InnoDB 主要特点。

1).支持事务(事务是指逻辑上的一组操作,组成这组操作的各个单元,要么全成功,要么全失败)

2).行级锁定(更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是锁定整个表,注意间隙锁的影响.

3).读写阻塞与事务隔离级别相关.

4).具有非常高效的缓存特性,能缓存索引,也能缓存数据.

5).整个表和主键以Cluster方式存储,组成一颗平衡树.

6).所有Secondary Index 都会保存主键信息.

7).支持分区,表空间.类似于Oracle数据库.

8).支持外键约束,不支持全文索引,5.5之前支持,后面不再支持.

9).和MyISAM相比,InnoDB对于硬件资源要求比较高.

nnoDB引擎的适用的生产场景

1).需要支持事务的业务(例如转账,付款)

2).行级锁定对于高并发有很好的适应能力,但是需要保证查询是通过索引完成.

3).数据读写及更新都比较频繁的场景,如:BBS,SNS,微博,微信等.

4).数据一致性要求很高的业务.如:转账,充值等.

5).硬件设备内存较大,可以很好利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO的开销.

InnoDB引擎调优精要

1).主键尽可能小,避免给Secondary index带来过大的空间负担.

2).避免全表扫描,因为会使用表锁.

3).尽可能缓存所有的索引和数据,提高响应速度,减少磁盘IO消耗.

4).在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交,有开关参数可以控制提交.

5).合理设置Innodb_flush_log_at_trx_commit 参数值,不要过度追求安全性.

1
2
如果Innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件进入磁盘,提交事务的时候不做任何操作.

6).避免主键更新,因为这会带来大量的数据移动.

MySQL目前主要有以下几种索引类型

1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引

MyISAM和InnoDB的主要区别和应用场景

InnoDB是Mysql的默认存储引擎(Mysql5.5.5之前是MyISAM)

InnoDB 使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISAM 使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

主要区别：

1).［事务］MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性能。

2).［锁］MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

3).［全文索引］MyISAM支持全文类型索引，而InnoDB不支持全文索引。

4).［查询效率］MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

5).［外健］MyISAM不支持外健，InnoDB支持。

6).［count］MyISAM保有表的总行数，InnoDB只能遍历。

6).MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

7).InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

8）MyIsam索引和数据分离，InnoDB在一起，MyIsam天生非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”

9）InnoDB提供多版本数据支持 MyIsam不支持

10）两者都仅支持B＋树索引，不支持hash索引

应用场景：

1).MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

2).InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

MySQL的四种事务隔离级别

一、事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

三、MySQL事务隔离级别

mysql默认的事务隔离级别为repeatable-read

MYSQL 优化常用方法

1. SQL语句的优化，使用精确查询，避免模糊查询，特别是在where语句时，少使用比较运算符，避免全表查询。

2. 使用联表查询，避免使用子查询。

3. 给经常被访问的字段创建索引，也可以创建联合索引，但是遵从最左原则。

   1
   2
   3
    例：create index '索引名'  on '表名'( 字段名 (长度 ) ) 
    create index index_name on table(column(length))
       
   

4. 创建表的时，使用合适字段，尽量缩小范围。

5. 使用缓存，把经常被访问的数据，可以缓存到内存型数据库（比如：Redis）

6. 合理的使用外键

7. 尽量避免使用游标，游标的效率比较低。

8. 在大量的请求访问数据的话，使用锁来保护。

9. 配置主从，读写分离。

10. 避免使用select * from table 这种全表查询，效率低。

MYSQL 性能优化方案

#如何查看索引

查询当前索引使用情况：

1
2
show status like '%Handler_read%';

如果索引正在工作，则Handler_read_key的值会很高，这个值代表一个行被索引值读的次数，很低值表名增加索引得到的性能改善不高，因此索引并不经常使用

如果Handler_read_rnd_next值很高意味着查询运行效率很低，应该建立索引补救，这个值含义是在数据文件中读取下一行的请求数。

如果正在进行大量表扫描，Handler_read_rnd_next的数值将会很高。说明索引不正确或者没有利用索引。

为什么索引会快

索引类型是BTREE(二叉树)，二叉树算法，让查询速度快很多，就是取最中间的一个数，然后把大于这个数的往右边排，小于这个数的就向左排，每次减半，然后依次类推，每次减半，形成一个树状结构图

建索引应该注意什么

索引的优点

1
2
3
4
5
大大加快数据的查询速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接

索引的缺点

1
2
3
4
创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

创建索引的原则

1
2
3
4
5
更新频繁的列不应设置索引
数据量小的表不要使用索引（毕竟总共2页的文档，还要目录吗？）
重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过百分之15就不该建索引）
首先应该考虑对where 和 order by 涉及的列上建立索引

MySQL几种索引类型：FULLTEXT，HASH，BTREE，RTREE有什么功能和性能上的不同

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持，只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引

HASH

hash就是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正是由于这个特性，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据。一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。

BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新，反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番，不过那次考试好像没怎么考这个。如二叉树一样，每次查询都是从树的入口root开始，依次遍历node，获取leaf。

RTREE

RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

各种索引的使用情况

（1）对于BTREE这种Mysql默认的索引类型，具有普遍的适用性

（2）由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法，至少愚安我是经常这么做的。

（3）对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是你的选择。

（4）正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

• Previous
  PHP演绎设计模式-结构型模式
• Next
  PHP百题斩（一)