2015手机版网站制作,网站建设与维护一样吗,做网站后台要学,建网站的经历熟悉MySQL的使用#xff0c;熟悉MYSQL的索引、日志、事务、存储引擎、锁机制等相关原理。
1.mysql索引#xff0c;索引的底层数据结构实现
索引就是目录可以帮助快速的找到对应的资源。 MySQL默认的InnoDB存储引擎使用的索引底层数据结构是BTree。BTree是一种多路搜索查找树…熟悉MySQL的使用熟悉MYSQL的索引、日志、事务、存储引擎、锁机制等相关原理。
1.mysql索引索引的底层数据结构实现
索引就是目录可以帮助快速的找到对应的资源。 MySQL默认的InnoDB存储引擎使用的索引底层数据结构是BTree。BTree是一种多路搜索查找树。BTree其实是B-Tree的一种优化。 标准回答流程 索引的底层实际上是一个BTree。 起初底层数据是以链表形式存储基于这个数据的主键进行排序。 为了提高查询效率MySQL创建分页管理它是基于BTree的结构进行存储对数据一页一页的存储默认每页的大小为16KB因为InnoDB默认的存储引擎存储的是16KB。 查找的时候先在页目录上查找每页的主键值然后找到指定页查找数据。 BTree 只有叶子结点才存储数据非叶子结点不存储数据。 一页大概存储1K个键值。一个深度为3的BTree可以维护10亿条记录。 一般的项目就2层足够。基于主键去查的话最多动用一个I/O因为它的顶层是常驻内存的。建表的时候一般主键使用INT类型方便插入数据的时候底层进行排序。
1索引的基本操作主键索引普通索引组合索引唯一索引
①索引的优点 大大加快了数据的查询速度。②索引的缺点 1.维护索引需要耗费数据库的资源2.索引需要占用磁盘空间3.当对表的数据进行增删改的时候增删改的速度会变慢增删改对底层数据结构进行排序。因为要维护索引速度会受到影响。③索引的分类
a.主键索引 数据库表中设定主键后数据库会自动创建主键索引在InnoDB引擎中主键索引也称之为局促索引。 【注也就是说在创建以下索引表的同时主键索引也总是被默认创建了。】
b.单值索引普通索引 即一个索引只包含单个列一个表可以有多个单列索引。 共有2种方式创建表的时候创建创建表后进行创建索引
--创建普通索引共有2种方式创建表的时候创建创建表后进行创建索引
CREATE INDEX name_index ON t_user(name);--创建表的时候构建索引
CREATE TABLE t_user1(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20),KEY(name)
);
-- 索引的名默认是字段名
SHOW INDEX FROM t_user1;c.唯一索引 索引列的值必须唯一但允许有空值。
--创建唯一索引:唯一索引允许为null只允许一个值为null
CREATE TABLE t_user2(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20),UNIQUE(name)
);SHOW INDEX FROM t_user2;d.组合索引复合索引、联合索引 即一个索引包含多个列。
--创建复合索引组合索引
CREATE TABLE t_user3(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20),age INT,KEY(name,age)
);
-- 创建复合索引
CREATE INDEX nameageindex ON t_user3(name,age);SHOW INDEX FROM t_user3;最左前缀 MySQL组合索引“最左前缀”的结果。 简单的理解就是只从最左面的开始组合。组合索引的第一个字段必须出现在查询组句中并且不能跳跃这个索引才会被用到。
2MySQL索引为什么会失效
索引失效的情况主要是针对联合索引组合索引。普通的BTree是一个结点只有一个值联合索引对应的多路搜索树的结点是多个键值。 使用索引进行查询的时候必须遵循的是最左匹配原则。所谓最左匹配原则就是查询的条件属性必须从索引的最左边开始中间不能有间隙。 本质就是因为B树是有顺序的如果没有遵循BTree的顺序法则查询的是无序元素就无法使用索引只能扫描全表。
失效的3中情况 1以a,b建立索引表为例B树中a是有序的在a相等的情况下b是有序的但单看b是无序的跳着查b就是不遵循做前缀法则这种情况只能进行全表扫描。 2当出现范围查询时索引失效。比如当满足a1的范围时b是无序的此时是一个无序的b树索引失效只能进行全表扫描。(2,1),(2,2,),(3,2)(4,1) 3where a like “%%”以%为开头的查询条件索引会失效。比如%a就是没遵守前缀法则没有顺序索引失效。
2.为什么加索引可以加快扫描范围
1.减少数据扫描量
没有索引就要逐行扫描整个库知道知道符合条件的数据非常耗时。索引通过创建有序的数据结构使得数据库可以直接定位到范围的起点和终点然后扫描其中数据即可大大减少了扫描量提高了查询效率
2.优化数据检索路径
索引建立的有序的数据结构使得数据检索路径更加高效。例如B树索引是一种平衡树结构能够在对数时间复杂度内完成数据检索。进行范围查询是数据库可以利用B树的多级节点跳跃迅速定位到目标数据行无需逐行扫描。哈希索引可以通过哈希函数快速定位数据虽然它更适用于等值查询但在某些特定场景下也能对范围查询提供一定的优化效果。
3.提高缓存命中率
数据库系统通常使用内存缓存来存储经常访问的数据页以减少磁盘I/O。索引比原始数据表小得多可以更多的被缓存。
3. 查数据库响应慢怎么排查
1.初步定位问题
利用**应用日志、数据库日志慢查询日志来初步定位问题**检查是否有异常错误日志或网络延迟.询问用户具体使用场景和出现问题的小操作以缩小问题范围
2.深入分析SQL查询
执行计划分析使用 EXPLAIN或EXPLAIN ANALYZE 来分析慢查询SQL的执行计划索引优化根据查询条件和排序需求检查关键字段上是否存在合适索引没有就考虑添加索引。并确保索引的维护是定期的。SQL重构简化复杂查询逻辑避免不必要的子查询和多层嵌套
3.系统资源检查
检查CPU和内存使用情况如果资源利用率考虑增加硬件资源或优化数据库配置检查磁盘I/O如果性能不足考虑升级存储设备或优化数据库存储结构
4.网络和配置检查
检查数据库服务器与应用服务器间的网络延迟看是否有网络问题影响数据库响应速度检查数据库配置比如缓存大小、连接数限制、事务隔离级别等。根据实际情况调整配置参数以优化数据库性能
5.高级优化策略
对于读多写少的场景考虑读写分离将读请求分散到多个库上以减轻主库的压力。单个数据库的数据量过大导致查询性能下降考虑进行分库分表以提高查询效率。热点数据使用缓存技术Redis等来减少数据库访问压力
6.持续监控和优化
简历长期性能监控机制定期审查数据库状态和性能指标定期对数据库进行审查优化包括索引维护、查询优化、配置调整等、
3.1查询长时间不返回可能是什么原因应该如何处理
查询速度慢的原因很多常见如下几种 1、先利用应用日志慢查询日志初步定位哪个部分耗时缩小问题范围 1、查询字段没有索引或者没有触发索引查询没有触发索引查询的情况如下 以 % 开头的 like 查询不会使用 b-tree 索引几个查询条件不符合最左前缀原则导致索引失效直接扫描全表 没有索引则考虑添加索引并定期维护更新。 2、简化复杂查询逻辑避免不必要的子查询和多层嵌套。 3、 I/O 压力大读取磁盘速度变慢。 4、内存不足考虑增加硬件 5、网络速度慢 提升网速升级带宽。 6、查询出的数据量过大可以采用多次查询或其他的方法降低数据量 7、死锁一般碰到这种情况的话大概率是表被锁住了可以使用 show processlist;命令看看 SQL 语句的状态再针对不同的状态做相应的处理。设置死锁的超时时间限制和避免死锁消耗过多服务器的资源。
4.MySQL 有哪些重要的日志文件
MySQL 中的重要日志分为以下几个 1、 错误日志用来记录 MySQL 服务器运行过程中的错误信息比如无法加载 MySQL 数据库的数据文件或权限不正确等都会被记录在此。默认情况下错误日志是开启的且无法被禁止。 2、查询日志在 MySQL 中被称为 general log(通用日志)查询日志里面记录了数据库执行的所有命令不管语句是否正确都会被记录。在并发操作非常多的场景下查询信息会非常多那么如果都记录下来会导致 IO 非常大影响 MySQL 性能因此如果不是在调试环境下是不建议开启查询日志功能的。 3、慢日志 记录下查询超过指定时间的语句之后运维人员通过定位分析能够很好的优化数据库性能。默认情况下慢查询日志是不开启的手动开启set global slowquerylogON; 4、redo log重做日志为了最大程度的避免数据写入时因为 IO 瓶颈造成的性能问题MySQL 采用了这样一种缓存机制先将数据写入内存中再批量把内存中的数据统一刷回磁盘。 5、undo log回滚日志用于存储日志被修改前的值从而保证如果修改出现异常可以使用 undo log 日志来实现回滚操作。
undo log 和 redo log 记录物理日志不一样它是逻辑日志
可以认为当 delete 一条记录时undo log 中会记录一条对应的 insert 记录
反之亦然当 update 一条记录时它记录一条对应相反的 update 记录
当执行 rollback 时就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。6、bin log二进制日志二进制文件主要记录所有数据库表结构变更。记录了对 MySQL 数据库执行更改的所有操作并且记录了语句发生时间、执行时长、操作数据等其它额外信息但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。binlog 默认是关闭状态在配置文件中开启。
4.2 redo log 和 binlog 的区别
redo log重做日志和 binlog归档日志都是 MySQL 的重要的日志它们的区别如下
redo log 是物理日志记录的是“在某个数据页上做了什么修改”。binlog 是逻辑日志记录的是这个语句的原始逻辑比如“给 ID2 这一行的 c 字段加 1 ”。redo log 是 InnoDB 引擎特有的binlog 是 MySQL 的 Server 层实现的所有引擎都可以使用。redo log 是循环写的空间固定会用完binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个并不会覆盖以前的日志。
4.3 redo log 和 binlog 是怎么关联的
redo log和binlog一起处理恢复崩溃。
它们有一个共同的数据字段叫 XID。崩溃恢复的时候会按顺序扫描 redo log
如果碰到既有 prepare、又有 commit 的 redo log就直接提交如果碰到只有 parepare、而没有 commit 的 redo log就拿着 XID 去 binlog 找对应的事务。
这篇博客里介绍了一些面试问答挺详细 MySQL 面试题合集日志篇
4.4 Undo Log 如何保障事务的原子性呢
具体的方式为在操作任何数据之前首先将数据备份到一个地方这个存储数据备份的地方称为 Undo Log然后进行数据的修改。如果出现了错误或者用户执行了 Rollback 语句系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
4.5 Redo Log如何保障事务的持久性呢
具体的方式为Redo Log 记录的是新数据的备份和 Undo Log 相反。在事务提交前只要将 Redo Log 持久化即可不需要将数据持久化。当系统崩溃时虽然数据没有持久化但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容将所有数据恢复到崩溃之前的状态。
4.6 将MySQL 的数据恢复到过去某个指定的时间节点
只要你备份了这段时间的所有 binlog同时做了全量数据库的定期备份就可以恢复。 把之前备份的数据库先还原到测试库从备份的时间点开始将备份的 binlog 依次取出来重放到你要恢复数据的那个时刻这个时候就完成了数据到指定节点的恢复。 比如今天早上 9 点的时候你想把数据恢复成今天早上 6:00:00 的状态这个时候你可以先取出今天凌晨00:01:59备份的数据库文件还原到测试库再从 binlog 文件中依次取出 00:01:59 之后的操作信息重放到 6:00:00 这个时刻这就完成了数据库的还原。
5.MYSQL存储引擎
存储引擎是一个数据的存储方式、检索方式、与其他数据库功能交互方式。mysql支持很多存储引擎每一个存储引擎都对应了一种不同的存储方式。常见的MySQL存储引擎包括InnoDB、MyISAM、Memory、Federated等。
存储引擎是基于表的不是基于数据库MYSQL采用的是插件式架构支持多种存储引擎甚至可以为不同的数据库表设置不同的存储引擎以适应不同的场景需要。
知道那些存储引擎
InnoDB存储引擎是最常用的事务型引擎是MySQL的默认存储引擎它提供了事务安全ACID兼容、行级锁定和外键约束等高级功能。
特点 事务安全ACID兼容InnoDB通过事务来确保数据的完整性和一致性。它支持事务的原子性、一致性、隔离性和持久性ACID属性。 行级锁与MyISAM的表级锁定不同InnoDB采用行级锁定这提高了并发性能减少了锁冲突。 外键约束InnoDB支持外键约束有助于维护数据的引用完整性。通过外键可以确保相关数据之间的正确关系。 崩溃恢复InnoDB具有强大的崩溃恢复能力可以在数据库意外关闭后恢复数据。 MVCC多版本并发控制InnoDB使用MVCC技术允许多个读者和写者并发访问数据而不会相互干扰。 聚集索引InnoDB的表数据按主键的顺序存储这称为聚集索引。这种存储方式有助于快速检索数据。使用场景 需要事务支持、高并发读写、数据完整性和引用完整性要求较高的场景如电子商务网站金融交易订单管理等。
MyISAM 存储引擎最常用的非事务型存储引擎MySQL以前的默认引擎供了全文索引和压缩功能适用于主要进行读取操作不需要事务支持对存储空间有一定要求的场景如内容管理系统、日志记录。 MEMORY这是一种易失性非事务型存储引擎不支持事务适用于需要快速响应的场景如在线游戏、实时统计。
InnoDB 使用的是哪种隔离级别
InnoDB 默认使用的是可重复读隔离级别。
6.锁机制
见博客 并发性事务带来的问题并发控制(MySQL事务隔离级别、MySQL 锁)
7.SQL 语言完整性约束
数据库的完整性是指数据库正确性和相容性是防止合法用户使用数据库时向数据库加入不符合语义的数据。保证数据库中数据是正确的避免非法的更新。数据库完整性重点需要掌握的内容有完整性约束条件的分类、完整性控制应具备的功能。
完整性约束类别大概分为实体完整性约束、参照完整性约束、自定义完整性约束三类。
实体完整性约束非空值约束NOT NULL、默认值DEFAULT、唯一性约束UNIQUE、UNIQUE(列名)、主键约束PRIMARY KEY 、PRIMARY KEY(列名)。参照完整性约束外键约束REFERENCES 表名(列)、FOREIGN KEY(列名) REFERENCES 表名(列)。用户定义完整性约束约束表达式CHECK。
8.表结构设计相关
主键约束和唯一约束的区别
主键约束PRIMARY KEY唯一性约束UNIQUE 相同点 他们都属于实体完整性约束。都不可重复
不同点 唯一性约束所在的列允许空值 但是主键约束所在的列不允许空值 可以把唯一性约束放在一个或者多个列上 这些列或者列的组合必须是唯一性的 唯一性约束所在的列并不是表的主键列 唯一性约束强制在指定的列上创建一个唯一性索引 在默认情况下 创建唯一性的非聚簇索引 但是 也可以指定所创建的索引是聚簇索引
为什么要尽量设定一个主键
主键是数据库确保数据行在整张表唯一性的保障即使业务上本张表没有主键也建议添加一个自增长的 ID 列作为主键。设定了主键之后在后续的删改查的时候会更加快速以及确保操作数据范围安全。
主键使用自增 ID 还是 UUID
推荐使用自增ID因为UUID 在数据量较大的情况下其效率直线下滑。
1使用自增长做主键的优点 1、很小的数据存储空间 2、性能最好 3、容易记忆
2使用自增长做主键的缺点 1、如果存在大量的数据可能会超出自增长的取值范围 2、很难并不是不能处理分布式存储的数据表尤其是需要合并表的情况下 3、安全性低因为是有规律的容易被非法获取数据
3使用UUID做主键的优点 1、它是独一无二的出现重复的机会少 2、适合大量数据中的插入和更新操作尤其是在高并发和分布式环境下 3、跨服务器数据合并非常方便 4、安全性较高
4使用UUID做主键的缺点 1、存储空间大16 byte因此它将会占用更多的磁盘空间 2、会降低性能 3、很难记忆
5那么一般情况下是如何选择的呢 1、项目是单机版的并且数据量比较大百万级时用自增长的此时最好能考虑下安全性做些安全措施。 2、项目是单机版的并且数据量没那么大对速度和存储要求不高时用UUID。 3、项目是分布式的那么首选UUID分布式一般对速度和存储要求不高。 4、项目是分布式的并且数据量达到千万级别可更高时对速度和存储有要求时可以用自增长。
实在难以抉择那就雪花算法做主键。 6SnowFlake是一种介于自增长和UUID之间的一种主键存储空间小、速度快、分布式、时间序列它有如下优点 1.所有生成的id按时间趋势递增 2.整个分布式系统内不会产生ID碰撞重复id因为有datacenterId和workerId来做区分 3.id生成的效率高
9.数据库MySQL优化
1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取但是一般说来数据库中的表越小在它上面执行的查询也就会越快。因此在创建表的时候为了获得更好的性能我们可以将表中字段的宽度设得尽可能小。 2、使用连接JOIN来代替子查询(Sub-Queries)。使用连接JOIN来完成查询速度将会快很多是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 3、使用联合(UNION)来代替手动创建的临时表 4、事务。事务的另一个重要作用是当多个用户同时使用相同的数据源时它可以利用锁定数据库的方法来为用户提供一种安全的访问方式这样可以保证用户的操作不被其它的用户所干扰。 5、锁定表 尽管事务是维护数据库完整性的一个非常好的方法但却因为它的独占性有时会影响数据库的性能尤其是在很大的应用系统中。由于在事务执行的过程中数据库将会被锁定因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用事务造成的影响不会成为一个太大的问题但假设有成千上万的用户同时访问一个数据库系统例如访问一个电子商务网站就会产生比较严重的响应延迟。其实有些情况下我们可以通过锁定表的方法来获得更好的性能。 6、使用外键 锁定表的方法可以维护数据的完整性但是它却不能保证数据的关联性。这个时候我们就可以使用外键。 7、使用索引 索引是提高数据库性能的常用方法它可以令数据库服务器以比没有索引快得多的速度检索特定的行尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候性能提高更为明显。 8、优化的查询语句 绝大多数情况下使用索引可以提高查询的速度但如果SQL语句使用不恰当的话索引将无法发挥它应有的作用。
Lottery项目中不确定
使用自增ID用UUID做UNIQUE唯一性约束。 UUID在计算出来时就能保持唯一性因为它根据。。然后再表中会将UUID设置为UNIQUE添加唯一性约束即不允许重复添加。
10.如何实现一个高并发的系统
1.前端优化 ① 静态资源缓存将活动页面上的所有可以静态的元素全部静态化尽量减少动态元素通过 CDN、浏览器缓存来减少客户端向服务器端的数据请求。 ② 禁止重复提交用户提交之后按钮置灰禁止重复提交。 ③ 用户限流在某一时间段内只允许用户提交一次请求比如采取 IP 限流。2.中间层负载分发 利用负载均衡比如 nginx 等工具可以将并发请求分配到不同的服务器从而提高了系统处理并发的能力。 nginx 负载分发的五种方式① 轮询默认② 按权重weight③ IP 哈希值ip_hash④ 响应时间fair⑤ URL 哈希值url_hash3.控制层网关层 限制同一个用户的访问频率限制访问次数防止多次恶意请求。4.服务层 ① 业务服务器分离比如将秒杀业务系统和其他业务分离单独放在高配服务器上可以集中资源对访问请求抗压。 ② 采用 MQ消息队列缓存请求MQ 具有削峰填谷的作用可以把客户端的请求先导流到 MQ程序在从 MQ 中进行消费执行请求这样可以避免短时间内大量请求导致服务器程序无法响应的问题。 ③ 利用缓存应对读请求比如使用 Redis 等缓存分担数据库压力。5.数据库层 ① 合理使用数据库引擎 ② 合理设置事务隔离级别合理使用事务 ③ 正确使用 SQL 语句和查询索引 ④ 合理分库分表 ⑤ 使用数据库中间件实现数据库读写分离 ⑥ 设置数据库主从读写分离
