网站建设实训内容,提高怎样做网站的外链,如何做美食的视频网站,建设一个购物网站的费用1 引擎-MyISAM和InnoDB的区别a 是否支持行级锁 :MyISAM 只有表级锁(table-level locking)#xff0c;而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。b 是否支持事务和崩溃后的安全恢复#xff1a;MyISAM 强调的是性能#xff0c;每次查询具有原子性,其执行…1 引擎-MyISAM和InnoDB的区别a 是否支持行级锁 :MyISAM 只有表级锁(table-level locking)而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。b 是否支持事务和崩溃后的安全恢复MyISAM 强调的是性能每次查询具有原子性,其执行速度比InnoDB类型更快但是不提供事务支持。但是InnoDB 提供事务支持事务外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。c 是否支持外键MyISAM不支持而InnoDB支持。d 是否支持MVCC 仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。e 文件Innodb 创建表后生成的文件有【聚簇索引】--【只在主键索引树的叶子节点存储了具体数据节省磁盘】frm:创建表的语句idb:表里面的数据索引文件Myisam 创建表后生成的文件有【非聚簇索引】---【查询性能高】frm:创建表的语句MYD:表里面的数据文件(myisam data)MYI:表里面的索引文件(myisam index)f 树节点值MyISAM:BTree叶节点的data域存放的是数据记录的地址。在索引检索的时候首先按照BTree搜索算法搜索索引如果指定的Key存在则取出其 data 域的值然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。InnoDB:其数据文件本身就是索引文件。相比MyISAM索引文件和数据文件是分离的。InnoDB表数据文件本身就是按BTree组织的一个索引结构树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引辅助索引的data域存储相应记录主键的值而不是地址这也是和MyISAM不同的地方。在根据主索引搜索时直接找到key所在的节点即可取出数据在根据辅助索引查找时则需要先取出主键的值再走一遍主索引。 因此在设计表的时候不建议使用过长的字段作为主键也不建议使用非单调的字段作为主键这样会造成主索引频繁分裂。2 索引MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引。BTree索引使用的是B树中的BTree但对于主要的两种存储引擎的实现方式是不同的。索引结构【B树】1.二叉查找树也叫二叉排序树时间复杂度取决于树高为O(lgn)。但是树可能不平衡退化为链表时间复杂度为O(n)2.平衡二叉树做旋转操作所有节点的左右子树高度不能超过1时间复杂度O(lgn)。当插入数据时需要1次旋转维持平衡。删除数据时需要O(lgn)次维持平衡。3.红黑树根到叶子的最长的可能路径不多于最短的可能路径的两倍长。插入或者删除O(1)次的旋转或者变色。但是查询慢。4.B(B-)树,每个非叶节点有多个子树。矮胖子。局部性原理。缓存命中率高。每个节点存储真实的数据。5.B树只有叶子节点存储真实的数据非叶子节点只存储键。真实数据包括行的全部数据、主键、地址等。叶子节点之间通过双向链表链接。与B树相比优势更少的IO次数。每个节点存储的记录数多访问时所需要的IO次数更少。这样访问局部性原理的利用更好缓存命中率更高。更适用范围查询。只对叶节点的指针链表进行遍历即可。更稳定的查询效率复杂度稳定为树高因为所有数据都在叶节点。劣势键重复时占用空间大。因此最大优势在于树高更小。B树高度一般在2-4层。树高由阶数决定阶数越大树越矮而阶数的大小又取决于每个节点可以存储多少条记录。Innodb中每个节点使用一个页(page)页的大小为16KB其中元数据只占大约128字节左右(包括文件管理头信息、页面头信息等等)大多数空间都用来存储数据。对于非叶节点记录只包含索引的键和指向下一层节点的指针。假设每个非叶节点页面存储1000条记录则每条记录大约占用16字节当索引是整型或较短的字符串时这个假设是合理的。延伸一下我们经常听到建议说索引列长度不应过大原因就在这里索引列太长每个节点包含的记录数太少会导致树太高索引的效果会大打折扣而且索引还会浪费更多的空间。对于叶节点记录包含了索引的键和值(值可能是行的主键、一行完整数据等具体见前文)数据量更大。这里假设每个叶节点页面存储100条记录(实际上当索引为聚簇索引时这个数字可能不足100当索引为辅助索引时这个数字可能远大于100可以根据实际情况进行估算)。对于一颗3层B树第一层(根节点)有1个页面可以存储1000条记录第二层有1000个页面可以存储1000*1000条记录第三层(叶节点)有1000*1000个页面每个页面可以存储100条记录因此可以存储1000*1000*100条记录即1亿条。而对于二叉树存储1亿条记录则需要26层左右。那为什么使用B树不用hash呢1.hash需要把全部数据加载到内存中如果数据量大将非常消耗内存。采用B树按照节点分段加载会减少内存消耗。2.唯一查找一个值时hash更快。但是数据库中经常查询多条数据由于B树数据的有序性叶子节点有链表相连查询效率会快很多。3 事务事务是逻辑上的一组操作要么都执行要么都不执行。事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元这个转账会涉及到两个关键操作就是将小明的余额减少1000元将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃导致小明余额减少而小红的余额没有增加这样就不对了。事务就是保证这两个关键操作要么都成功要么都要失败。四大特性原子性(Atomicity)事务是最小的执行单位不允许分割。事务的原子性确保动作要么全部完成要么完全不起作用一致性(Consistency) 执行事务前后数据保持一致多个事务对同一个数据读取的结果是相同的隔离性(Isolation)并发访问数据库时一个用户的事务不被其他事务所干扰各并发事务之间数据库是独立的持久性(Durability) 一个事务被提交之后。它对数据库中数据的改变是持久的即使数据库发生故障也不应该对其有任何影响。4 并发事务的问题在典型的应用程序中多个事务并发运行经常会操作相同的数据来完成各自的任务(多个用户对同一数据进行操作)。并发虽然是必须的但可能会导致以下的问题。脏读(Dirty read):当一个事务正在访问数据并且对数据进行了修改而这种修改还没有提交到数据库中这时另外一个事务也访问了这个数据然后使用了这个数据。因为这个数据是还没有提交的数据那么另外一个事务读到的这个数据是“脏数据”依据“脏数据”所做的操作可能是不正确的。丢失修改(Lost to modify):指在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失因此称为丢失修改。 例如事务1读取某表中的数据A20事务2也读取A20事务1修改AA-1事务2也修改AA-1最终结果A19事务1的修改被丢失。不可重复读(Unrepeatableread):指在一个事务内多次读同一数据。在这个事务还没有结束时另一个事务也访问该数据。那么在第一个事务中的两次读数据之间由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况因此称为不可重复读。幻读(Phantom read):幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据接着另一个并发事务(T2)插入了一些数据时。在随后的查询中第一个事务(T1)就会发现多了一些原本不存在的记录就好像发生了幻觉一样所以称为幻读。不可重复读和幻读区别不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。5 事务隔离级别READ-UNCOMMITTED(读取未提交)最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读。READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生。REPEATABLE-READ(可重复读) 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生。SERIALIZABLE(可串行化) 最高的隔离级别完全服从ACID的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。默认隔离级别MySQL InnoDB 存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重读)。使用的是Next-Key Lock 锁算法因此可以避免幻读的产生。InnoDB 存储引擎在 分布式事务的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。6 锁机制与InnoDB锁算法MyISAM和InnoDB存储引擎使用的锁MyISAM采用表级锁(table-level locking)。InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁表级锁和行级锁对比表级锁 MySQL中锁定 粒度最大 的一种锁对当前操作的整张表加锁实现简单资源消耗也比较少加锁快不会出现死锁。其锁定粒度最大触发锁冲突的概率最高并发度最低MyISAM和 InnoDB引擎都支持表级锁。行级锁 MySQL中锁定 粒度最小 的一种锁只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小并发度高但加锁的开销也最大加锁慢会出现死锁。InnoDB存储引擎的锁的算法有三种Record lock单个行记录上的锁Gap lock间隙锁锁定一个范围不包括记录本身。目的是为了阻止多个事务将记录插入到同一范围内而这会导致幻读问题的产生。有两种方式显式关闭gap锁(除了外键约束和唯一性检查外其余情况仅使用record lock) A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1Next-key lockrecordgap 锁定一个范围包含记录本身。为了解决Phantom Problem幻读问题。Innodb对于行的查询使用next-key lock。当查询的索引含有唯一属性时将next-key lock降级为record key。7 表优化当MySQL单表记录数过大时数据库的CRUD性能会明显下降一些常见的优化措施如下a 限定数据的范围务必禁止不带任何限制数据范围条件的查询语句。比如我们当用户在查询订单历史的时候我们可以控制在一个月的范围内b 读写分离经典的数据库拆分方案主库负责写从库负责读c 垂直分区根据数据库里面数据表的相关性进行拆分。 例如用户表中既有用户的登录信息又有用户的基本信息可以将用户表拆分成两个单独的表甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分把一张列比较多的表拆分为多张表。优点 可以使得列数据变小在查询时减少读取的Block数减少I/O次数。此外垂直分区可以简化表的结构易于维护。缺点 主键会出现冗余需要管理冗余列并会引起Join操作可以通过在应用层进行Join来解决。此外垂直分区会让事务变得更加复杂d 水平分区保持数据表结构不变通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中达到了分布式的目的。 水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分表的行数超过200万行时就会变慢这时可以把一张的表的数据拆成多张表来存放。举个例子我们可以将用户信息表拆分成多个用户信息表这样就可以避免单一表数据量过大对性能造成影响。水平拆分可以支持非常大的数据量。需要注意的一点是分表仅仅是解决了单一表数据过大的问题但由于表的数据还是在同一台机器上其实对于提升MySQL并发能力没有什么意义所以水平拆分最好分库 。水平拆分能够 支持非常大的数据量存储应用端改造也少但 分片事务难以解决 跨节点Join性能较差逻辑复杂。一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片尽量选择客户端分片架构这样可以减少一次和中间件的网络I/O。下面补充一下数据库分片的两种常见方案客户端代理 分片逻辑在应用端封装在jar包中通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。中间件代理 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。8 池化思想池化设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源解决的问题就是抵消每次获取资源的消耗如创建线程的开销获取远程连接的开销等。就好比你去食堂打饭打饭的大妈会先把饭盛好几份放那里你来了就直接拿着饭盒加菜即可不用再临时又盛饭又打菜效率就高了。除了初始化资源池化设计还包括如下这些特征池子的初始值、池子的活跃值、池子的最大值等这些特征可以直接映射到java线程池和数据库连接池的成员属性中。在连接池中创建连接后将其放置在池中并再次使用它因此不必建立新的连接。如果使用了所有连接则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接尤其是对动态数据库驱动的网站应用程序的请求既昂贵又浪费资源。9 如何做持久化undo日志记录的是修改前的值在操作任何数据之前首先将数据备份到一个地方(这个存储数据备份的地方称为Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。举例事务A要将字段age的值由原来的1修改为2要将name的值由原来的Alice修改为Bob假设现在数据库出现了宕机的问题分为两种情况如果在上表第三步之前数据库挂了则最终数据和日志均为原数据如果第三步及以后出现异常则undo已经记录了原来的值则可以利用undo日志将数据恢复为原数据。原理当事务提交之后undo不会被马上删除而是放入待删除队列由purge线程来判断是否删除和处理。在mysql5.6之前undo只存在于共享表空间中之后的版本中则可以配置为独立的文件。undo内部默认128个回滚段槽(rseg slot)每个rseg slot内部有1024个回滚段(rollback segment)。其中slot0——共1个预留给系统表空间slot1~slot31——共31个预留给临时表空间slot32~slot127——共96个预留给undo独立表空间缺点由于undo日志会被清理掉不能保证事务的持久性因此才需要引入redo日志来保证事务的持久性。redo日志记录的是修改后最新的数据和冗余的undo日志和Undo Log相反Redo Log记录的是新数据的备份。在事务提交前只要将Redo Log持久化即可不需要将数据持久化。当系统崩溃时虽然数据没有持久化但是Redo Log已经持久化。系统可以根据Redo Log的内容将所有数据恢复到最新的状态。举例事务B要将字段age的值由原来的1修改为2要将name的值由原来的Alice修改为Bobredo日志记录的过程表如下其中redo日志必须先于数据写入磁盘(即步骤8和步骤9的顺序不能改变)。因为如果不这样在数据提交之后再写redo日志一旦redo日志的写入过程出现异常将无法保证持久性。记录redo日志时先记入redo log buffer最后再一起写入磁盘这样可以减少IO提升性能。另外未提交的事务和回滚了的事务也会计入redo日志。mysql恢复的策略恢复时先根据redo重做所有事务(包括未提交和回滚了的)。再根据undo回滚未提交的事务。如上如果事务B异常未提交事务就宕机恢复时先根据redo日志将数据恢复为age2nameBob然后再根据undo记录的age1nameAlice将数据恢复如初。如果事务C异常未提交事务就宕机恢复时根据redo日志可以直接恢复至age1nameAlice的初始状态。redo日志会随着时间推移而越来越大为了提升redo的恢复性能引入了checkpoint机制在恢复的时候只需要从checkpoint的位置往后恢复即可。
