数据库-知识点整理

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

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

2、mysql存储引擎类型有哪些

• MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。
• MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。
• InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。

3、MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• mysql库主从读写分离。
• 找规律分表，减少单表中的数据量，提高查询速度。
• 添加缓存机制，比如memcached，apc，使用redis，减少数据库访问等。
• 不经常改动的页面，生成静态页面。
• 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

4、MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？

答案:都是B+树!

• MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
• Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

5、什么是事务？

　　事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

　　事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。

6、事务的 ACID

　　事务具有四个特征：原子性( Atomicity )、一致性( Consistency )、隔离性( Isolation )和持续性( Durability )。这四个特性简称为 ACID 特性。

　　1 、原子性( Atomicity )。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

　　2 、一致性( Consistency )。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

　　3 、隔离性( Isolation )。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

　　4 、持续性持续性( Durability )。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

7、mysql数据库隔离级别

　　当我们的数据是引擎是InnoDB的时候。

　　事务的隔离级别分为：未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)。

• 未提交读(read uncommitted)

　　　　

　　未提交读的意思就是比如原先name的值是小刚，然后有一个事务B`update table set name = '小明' where id = 1`,它还没提交事务。同时事务A也起了，

　　有一个select语句`select name from table where id = 1`，在这个隔离级别下获取到的name的值是小明而不是小刚。

　　那万一事务B回滚了，实际数据库中的名字还是小刚，事务A却返回了一个小明，这就称之为脏读(Drity Read)。

　　

• 已提交读(read committed)

　　

　　按照上面那个例子，在已提交读的情况下，事务A的select name 的结果是小刚，而不是小明，因为在这个隔离级别下，一个事务只能读到另一个事务修改的已经提交了事务的数据。但是有个现象，还是拿上面的例子说。如果事务B 在这时候隐式提交了时候，然后事务A的select name结果就是小明了，这都没问题，但是事务A还没结束，这时候事务B又`update table set name = '小红' where id = 1`并且隐式提交了。然后事务A又执行了一次`select name from table where id = 1`结果就返回了小红。这种现象叫不可重复读。

• 可重复读(repeatable read)

　　

　　可重复读就是一个事务只能读到另一个事务修改的已提交了事务的数据，但是第一次读取的数据，即使别的事务修改的这个值，这个事务再读取这条数据的时候还是和第一次获取的一样，不会随着别的事务的修改而改变。这和已提交读的区别就在于，它重复读取的值是不变的。所以取了个贴切的名字叫可重复读。按照这个隔离级别下那上面的例子就是：可重复读

• 串行化(serializable)

　　上面三个隔离级别对同一条记录的读和写都可以并发进行，但是串行化格式下就只能进行读-读并发。只要有一个事务操作一条记录的写，那么其他要访问这条记录的事务都得等着。

　　串行化一般没人用串行化，性能比较低，常用的是已提交读和可重复读。而已提交读和可重复读的实现主要是基本版本链和readView。

　　而它们之间的区别其实就是生成readView的策略不同。

 

8、海量数据的存储

　　针对问题：海量数据的存储和访问成为了系统设计的瓶颈问题

　　解决方案：数据库水平切分的实现原理解析－－－分库，分表，主从，集群，负载均衡器 

　　水平切分数据库：可以降低单台机器的负载，同时最大限度的降低了了宕机造成的损失；通过负载均衡策略，有效的降低了单台机器的访问负载，降低了宕机的可能性；

　　通过集群方案：解决了数据库宕机带来的单点数据库不能访问的问题；

　　读写分离策略：是最大限度了提高了应用中读取（Read）数据的速度和并发量。

 9、数据切分

　　分库降低了单点机器的负载；分表，提高了数据操作的效率，尤其是Write操作的效率。 

　　物理上的切分：分库，对数据通过一系列的切分规则将数据分布到不同的DB服务器上，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。

　　数据库内的切分：分表，对数据通过一系列的切分规则，将数据分布到一个数据库的不同表中，比如将article分为article_001,article_002等子表，若干个子表水平拼合有组成了逻辑上一个完整的article表，这样做的目的其实也是很简单的。

 10、分库的方法：

　　要想做到数据的水平切分（针对切分到不同机器上），在每一个表中都要有相冗余字符 作为切分依据和标记字段，通常的应用中我们选用user_id作为区分字段

　　(1)  user_id为区分

　　1～1000的对应DB1，1001～2000的对应DB2，以此类推；

　　优点：可部分迁移

　　缺点：数据分布不均

　　(2)hash取模分：

　　对user_id进行hash（或者如果user_id是数值型的话直接使用user_id 的值也可），然后用一个特定的数字，比如应用中需要将一个数据库切分成4个数据库的话，我们就用4这个数字对user_id的hash值进行取模运算，也就是user_id%4,这样的话每次运算就有四种可能：结果为1的时候对应DB1；结果为2的时候对应DB2；结果为3的时候对应DB3；结果为0的时候对应DB4，这样一来就非常均匀的将数据分配到4个DB中。

　　优点：数据分布均匀

　　缺点：数据迁移的时候麻烦，不能按照机器性能分摊数据

　　(3)在认证库中保存数据库配置

　　就是建立一个DB，这个DB单独保存user_id到DB的映射关系，每次访问数据库的时候都要先查询一次这个数据库，以得到具体的DB信息，然后才能进行我们需要的查询操作。

　　优点：灵活性强，一对一关系

　　缺点：每次查询之前都要多一次查询，性能大打折扣

 　　分布式数据库方案提供功能

　　（1）提供分库规则和路由规则（RouteRule简称RR），将上面的说明中提到的三中切分规则直接内嵌入本系统，具体的嵌入方式在接下来的内容中进行详细的说明和论述；

　　（2）引入集群（Group）的概念，保证数据的高可用性；

　　（3）引入负载均衡策略（LoadBalancePolicy简称LB）；

　　（4）引入集群节点可用性探测机制，对单点机器的可用性进行定时的侦测，以保证LB策略的正确实施，以确保系统的高度稳定性；

　　（5）引入读/写分离，提高数据的查询速度；（Master负责写入，slave负责读，每个group中Master：Slave约1:10，MySQL大部分都是读操作）

11、锁的优化策略（待续）

　　① 读写分离

　　② 分段加锁

　　③ 减少锁持有的时间

　　④ 多个线程尽量以相同的顺序去获取资源

　　等等，这些都不是绝对原则，都要根据情况，比如不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

12、如何设计一个高并发的系统

　　① 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引的优化。

　　② 使用缓存，尽量减少数据库 IO。

　　③ 分布式数据库、分布式缓存。

　　④ 服务器的负载均衡。

13、实践中如何优化MySQL

　　四条从效果上第一条影响最大，后面越来越小。

　　① SQL语句及索引的优化

　　② 数据库表结构的优化

　　③ 系统配置的优化

　　④ 硬件的优化

14、优化数据库的方法

　　选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
　　使用连接(JOIN)来代替子查询
　　适用联合(UNION)来代替手动创建的临时表
　　事务处理
　　锁定表、优化事务处理
　　适用外键，优化锁定表
　　建立索引
　　优化查询语句

 

15、简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

　　索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

　　普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。 

　　普通索引允许被索引的数据列包含重复的值。

　　如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。 

　　主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。

　　索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。 

　　索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

 

16、对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

　　a)、索引的目的是什么？
　　快速访问数据表中的特定信息，提高检索速度

　　创建唯一性索引，保证数据库表中每一行数据的唯一性。

　　加速表和表之间的连接

　　使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

　　b)、索引对数据库系统的负面影响是什么？
　　1》创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；

　　2》索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；

　　3》当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。　　

　　c)、为数据表建立索引的原则有哪些？

　　在最频繁使用的、用以缩小查询范围的字段上建立索引。

　　在频繁使用的、需要排序的字段上建立索引

　　d)、 什么情况下不宜建立索引？
　　对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

　　对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

 

17、Myql中的事务回滚机制概述

　　事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

　　要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚

 

18、完整性约束包括哪些？

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

　　分为以下四类：

　　1) 实体完整性：规定表的每一行在表中是惟一的实体。

　　2) 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

　　3) 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

　　4) 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。

　　用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

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

19、如何通俗地理解三个范式？

　　第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解。

　　第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性。

　　第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

　　范式化设计优缺点:

　　优点:可以尽量得减少数据冗余，使得更新快，体积小。

　　缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化。

　　反范式化:

　　优点:可以减少表得关联，可以更好得进行索引优化。

　　缺点:数据冗余以及数据异常，数据得修改需要更多的成本。

 

20、说说对SQL语句优化有哪些方法？

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

（3） 避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

 

21、SQL语句中‘相关子查询’与‘非相关子查询’有什么区别？

　　答：子查询：嵌套在其他查询中的查询称之。子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。

　　所有的子查询可以分为两类，即相关子查询和非相关子查询

　　（1）非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

　　（2）相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

　　故非相关子查询比相关子查询效率高

 

22、char和varchar的区别？

　　答：是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

　　char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．

　　varchar得适用场景:

　　1》字符串列得最大长度比平均长度大很多

　　2》字符串很少被更新，容易产生存储碎片 

　　3》使用多字节字符集存储字符串

　　Char得场景:

　　存储具有近似得长度（md5值,身份证，手机号）,长度比较短小得字符串（因为varchar需要额外空间记录字符串长度）。

　　更适合经常更新得字符串，更新时不会出现页分裂得情况，避免出现存储碎片，获得更好的io性能。