目录
十三、Inner join,left outter join,right outter join之间的区别
一、数据库事务
事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
(1). 事务的特征
- 原子性(Atomicity)：事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚；
- 一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态；
- 隔离性(Isolation)：并发执行的事务之间不能相互影响；
- 持久性(Durability)：事务一旦提交，对数据库中数据的改变是永久性的。
(2). 事务并发带来的问题
- 脏读：一个事务读取了另一个事务未提交的数据；
- 不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
- 幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。
(3). 隔离级别
隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：
- READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；
- READ COMMITTED：在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；
- REPEATABLE READ：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。
- SERIALIZABLE：最高级别的隔离，只允许事务串行执行。
MySQL默认的隔离级别是REPEATABLE READ。
(4)、mysql的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：
- MyISAM：不支持事务，用于只读程序提高性能；
- InnoDB：支持ACID事务、行级锁、并发；
- Berkeley DB：支持事务。
二、实践中如何优化MySQL
实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面，如下图所示：
（1）SQL语句的优化
SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL，下面将逐一解释。
A. 怎么发现有问题的SQL?（通过MySQL慢查询日志对有效率问题的SQL进行监控）
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。
B. 通过explain查询和分析SQL的执行计划
使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。
C.SQL语句的优化
- 优化insert语句：一次插入多值；
- 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
- 应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
- 优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
- 很多时候用 exists 代替 in 是一个好的选择。
（2）索引优化
建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：
- 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
- OR语句前后没有同时使用索引；
- 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
- 对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则 索引生效的情形包括col1或col1，col2或col1，col2，col3)。
(3). 数据库表结构的优化
数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。
A. 选择合适数据类型
- 使用较小的数据类型解决问题；
- 使用简单的数据类型(mysql处理int要比varchar容易)；
- 尽可能的使用not null 定义字段；
- 尽量避免使用text类型，非用不可时最好考虑分表；
B. 表的范式的优化
一般情况下，表的设计应该遵循三大范式。
C. 表的垂直拆分
把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下几种拆分手段：
- 把不常用的字段单独放在同一个表中；
- 把大字段独立放入一个表中；
- 把经常使用的字段放在一起；
这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。
D. 表的水平拆分
表的水平拆分用于解决数据表中数据过大的问题，水平拆分每一个表的结构都是完全一致的。一般地，将数据平分到N张表中的常用方法包括以下两种：
- 对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值；
- 针对不同的hashID将数据存入不同的表中；
表的水平拆分会带来一些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了一些切实的好处：
表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度；
表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
需要把数据存放到多个数据库中，提高系统的总体可用性(分库，鸡蛋不能放在同一个篮子里)。
(4). 系统配置的优化
操作系统配置的优化：增加TCP支持的队列数
mysql配置文件优化：Innodb缓存池设置(innodb_buffer_pool_size，推荐总内存的75%)和缓存池的个数（innodb_buffer_pool_instances）
(5). 硬件的优化
CPU：核心数多并且主频高的
内存：增大内存
磁盘配置和选择：磁盘性能
三、数据库范式
什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。
什么是三大范式：
第一范式：当关系模式 R 的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
第二范式：如果关系模式 R 满足第一范式，并且 R 得所有非主属性都完全依赖于 R 的每一个候选关键属性，称 R 满足第二范式，简记为2NF。
第三范式：设 R 是一个满足第一范式条件的关系模式，X是 R 的任意属性集，如果 X 非传递依赖于 R 的任意一个候选关键字，称 R 满足第三范式，简记为3NF.
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
四、数据库的索引类型，数据库索引的作用
- 类型：
唯一索引——UNIQUE，例如：create unique index stusno on student（sno）；表明此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
主键索引——primary key，数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引（也叫聚簇索引）——cluster，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引，如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
- 索引的作用：
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
优点：大大加快数据的检索速度; 创建唯一性索引，保证数据库表中每一行数据的唯一性；加速表和表之间的连接; 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：索引需要占用数据表以外的物理存储空间；创建索引和维护索引要花费一定的时间；当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。
- 索引实现方式：
B+树、散列索引、位图索引
五、聚集索引和非聚集索引的区别
- 聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
- 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
六、唯一性索引和主码索引的区别
- 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
- 主键创建后一定包含一个唯一性索引，唯一性索引不一定就是主键。
- 唯一性索引列允许空值， 而主键列不允许为空值。
- 主键可以被其他表引用为外键，而唯一索引不能。
- 一个表最多只能创建一个主键，但是可以创建多个唯一索引。
- 主键更适合那些不容易改变的唯一标识，如自动递增列，身份证号等。
- 在RBO 模式下，主键的执行计划优先级高于唯一索引。两者可以提高查询的速度。
七、数据库引擎，innodb和myisam的特点与区别
- Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
- MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
- 大尺寸的数据集趋向于选择InnoDB引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。主键查询在InnoDB引擎下也会相当快，不过需要注意的是如果主键太长也会导致性能问题，关于这个问题我会在下文中讲到。大批的INSERT语句(在每个INSERT语句中写入多行，批量插入)在MyISAM下会快一些，但是UPDATE语句在InnoDB下则会更快一些，尤其是在并发量大的时候。
八、关系型和非关系型数据库的区别
九、数据库的隔离级别
- 隔离级别高的数据库的可靠性高，但并发量低，而隔离级别低的数据库可靠性低，但并发量高，系统开销小。
- READ UNCIMMITTED（未提交读），事务中的修改，即使没有提交，其他事务也可以看得到，比如说上面的两步这种现象就叫做脏读，这种隔离级别会引起很多问题，如无必要，不要随便使用；这就是事务还没提交，而别的事务可以看到他其中修改的数据的后果，也就是脏读；
- READ COMMITTED（提交读），大多数数据库系统的默认隔离级别是READ COMMITTED，这种隔离级别就是一个事务的开始，只能看到已经完成的事务的结果，正在执行的，是无法被其他事务看到的。这种级别会出现读取旧数据的现象
- REPEATABLE READ（可重复读），REPEATABLE READ解决了脏读的问题，该级别保证了每行的记录的结果是一致的，也就是上面说的读了旧数据的问题，但是却无法解决另一个问题，幻行，顾名思义就是突然蹦出来的行数据。指的就是某个事务在读取某个范围的数据，但是另一个事务又向这个范围的数据去插入数据，导致多次读取的时候，数据的行数不一致。虽然读取同一条数据可以保证一致性，但是却不能保证没有插入新的数据。
- SERIALIZABLE（可串行化），SERIALIZABLE是最高的隔离级别，它通过强制事务串行执行（注意是串行），避免了前面的幻读情况，由于他大量加上锁，导致大量的请求超时，因此性能会比较底下，再特别需要数据一致性且并发量不需要那么大的时候才可能考虑这个隔离级别。
十、数据库连接池的作用
- 在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法，如外部使用者可通过getConnection方法获取数据库连接，使用完毕后再通过releaseConnection方法将连接返回，注意此时的连接并没有关闭，而是由连接池管理器回收，并为下一次使用做好准备。
- 资源重用，由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，增进了系统环境的平稳性（减少内存碎片以级数据库临时进程、线程的数量）
- 更快的系统响应速度，数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池内备用。此时连接池的初始化操作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。
- 新的资源分配手段，对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接技术。
- 统一的连接管理，避免数据库连接泄露，较较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用的连接，从而避免了常规数据库连接操作中可能出现的资源泄露。
十一、数据的锁的种类，加锁的方式
- 锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。
- 数据库锁出现的目的：处理并发问题；
- 并发控制的主要采用的技术手段：乐观锁、悲观锁和时间戳。
- 从数据库系统角度分为三种：排他锁、共享锁、更新锁。从程序员角度分为两种：一种是悲观锁，一种乐观锁。
十二、数据库union join的区别
- join 是两张表做交连后里面条件相同的部分记录产生一个记录集，union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。
- union在数据库运算中会过滤掉重复数据，并且合并之后的是根据行合并的，即：如果a表和b表中的数据各有五行，且有两行是重复数据，合并之后为8行。运用场景：适合于需要进行统计的运算
- union all是进行全部合并运算的，即：如果a表和b表中的数据各有五行，且有两行是重复数据，合并之后为10行。
- join是进行表关联运算的，两个表要有一定的关系。即：如果a表和b表中的数据各有五行，且有两行是重复数据，根据某一列值进行笛卡尔运算和条件过滤，假如a表有2列，b表有2列，join之后是4列。
扫一扫
1147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
