自主建设公司网站,如何编写网站开发文档,重庆seo推广方案,信用网站建设情况1、三范式 第一范式(1NF)#xff1a;属性不可分割#xff0c;即每个属性都是不可分割的原子项。(实体的属性即表中的列) 第二范式(2NF)#xff1a;满足第一范式#xff1b;且不存在部分依赖#xff0c;即非主属性必须完全依赖于主属性。(主属性即主键#xff1b;完全依赖…1、三范式 第一范式(1NF)属性不可分割即每个属性都是不可分割的原子项。(实体的属性即表中的列) 第二范式(2NF)满足第一范式且不存在部分依赖即非主属性必须完全依赖于主属性。(主属性即主键完全依赖是针对于联合主键的情况非主键列不能只依赖于主键的一部分) 第三范式(3NF)满足第二范式且不存在传递依赖即非主属性不能与非主属性之间有依赖关系非主属性必须直接依赖于主属性不能间接依赖主属性。A - B, B -C, A - C 2、数据库引擎 MYISAM全表锁执行速度高不支持事务、外键并发性能差占用空间较小 Innodb行级锁有提交、回滚的事务安全支持自动增长列、外键并发能力强对比MYSIAM占用空间高 Memory全表锁存储在内存中速度快但会占用和数据量成正比的内存空间数据在mysql重启时会丢失默认使用哈希索引检索效率非常高但不适用精确查找 Merge是一组MYISAM表的组合 3、InnoDB和MyIsam的区别
InnoDBMyIsam支持事务每一条sql都默认封装成事务自动提交影响速度不支持事务支持外键如果包含外键的InnoDB表转为myisam会失败不支持外键聚集索引数据文件和索引绑定一起必须要有主键辅助索引需要查询两次先查询到主键再通过主键查数据非聚集索引数据文件是分离的索引保存的是数据文件的指针不保存表的具体行数执行select count需要全表扫描用一个变量保存整个表的行数执行select count只需要读出变量不支持全文索引支持全文索引查询效率较高
没有特别的需求使用默认的Innodb以读写为主可以使用Mysam。 4、事务ACID 原子性要么全部成功要么全部失败 一致性事务前后数据的完整性必须保持一致。 隔离性并发操作时不同的事务不会彼此干扰 持久性一旦事务提交成功持久化到数据库 5、索引
对数据库表中一个或多个列的值进行排序的结构 主键索引列不允许重复不允许为null。一个表只能有一个主键 唯一索引列不允许重复允许为null。一个表允许多个列创建唯一索引。add unique(列) 普通索引add index 索引名(列) 全文索引add fulltext(列) 删除alter table 表名 drop key 索引名。如果主键自增长需要取消后再删除
创建索引需要耗费资源增加了数据库的存储空间在插入和删除时花费时间维护索引
索引加快检索速度通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能 6、聚集索引和非聚集索引
聚集索引 基于主键创建的索引innodb中聚集索引就是按照每张表的主键构建b树叶子结点存储每一行数据记录不仅仅是一种索引类型还是一种数据的存储方式。 每个表总必须要有一个主键如果没有主键innodb默认选择一个隐藏列作为主键索引存储。 innodb只能存在一个聚集索引如果存在多个就意味这个表的数据存在多个副本造成磁盘空间浪费。 对表中的进行插入删除更新时物理排序可能发生变化导致产生碎片数据库需要重新进行排序和存储代价较高 非聚集索引 索引的逻辑顺序和磁盘上存储的物理顺序不同 一张表可以拥有多个非聚集索引不改变表的物理存储格式对增删改的影响较小可以根据不同的列组合加速查询效率 需要维护独立的数据结构占用更多磁盘空间 非聚集索引和表使用不同的IO在查询时需要进行多次IO增加查询的成本和时间 7、为什么命中索引比不命中要快
执行select语句时innodb需要从磁盘读取数据涉及到磁盘IO并且磁盘IO是比较消耗的。所以innodb对磁盘上的数据建立一个索引然后把索引数据和索引列对应的磁盘地址以b树存储。B树是一颗多路平衡二叉树它的特点是非叶子节点只存储索引叶子节点存储数据从而减少 B树层高降低磁盘 IO 次数从而提升数据检索效率。 8、什么情况下不建立索引 数据量小 数据离散度不高的列比如性别、年龄 频繁变更的表索引需要重排序 9、索引失效 where里没有使用索引列 索引列使用函数mysql8增加了函数索引 对索引列进行类型转换如果索引是字符串传入的是数字就需要转换 组合索引中最左匹配法则从索引的最左列开始顺序检索否则不会走索引 like %xxx不符合最左匹配法则 使用、not查询 使用or连接查询or前后没有同时使用索引 查询条件涉及到大量数据mysql可能会认为使用索引并不高效放弃使用索引 10、视图
虚拟的表具有和物理表相同的功能可以增删改查对视图的修改不影响基本表使得获取数据更容易
create view 视图名 as 查询语句 11、隔离级别
innodb默认可重复读
级别脏读不可重复度幻读读未提交√√√读已提交×√√可重复读RR××innodb不存在可串行化×××
脏读第一个事务读取数据并修改但还没有提交另外一个事务读取数据并使用读取的就是脏数据
不可重复读一个事务读取并修改数据另一个事务也访问并修改第一个事务修改的丢失
幻读一个事务读取了几行另一个事务插入数据第一个事务回发现多了一些不存在的记录 12、RR隔离基本有没有解决幻读
MySQL在RR隔离级别下是有可能会发生幻读的。但需要具备一定的条件才能触发 在一个事务当中同时使用到快照读和当前读。 同时其他事务刚好有插入数据的操作发生且已提交。 当前读在当前事务中发生的时间点是在其他事务插入数据且提交之后。 在当前事务中再次执行快照读或当前读的时候如果查询条件搜索的范围刚好可以包含其他事务插入的数据则会发生幻读。
为了避免这种情况可以通过for update加锁 13、mysql事务实现原理
事务满足ACID特性 原子性如果事务失败要执行回滚innodb中有一个undolog的表把修改之前的数据快照保存进去回时读取数据执行反向操作 隔离性innodb默认是rr级别使用mvcc锁来解决这个问题。 持久性innodb引入redo_log文件存储数据被修改后的值当事务对数据变更时除了修改内存缓存区的数据以外还会把修改的值接入到redo_log提交事务时直接把redolog日志刷到磁盘如果出现宕机重启后可以直接用redolog保存的日志读取再执行一遍。 一致性就是通过AID保证的 14、innodb是如何解决幻读的
在RR级别下innodb采用mvcc机制解决幻读是一种乐观锁通过对不同事务生成不同得到快照版本通过undolog版本进行管理高版本能看到低版本的事务变更实现不同事务之间的数据隔离。
但是在当前读的情况下是直接读取内存的数据跳过了快照读可以使用next-key locking 的锁策略来防止幻读这种锁策略结合了间隙锁和记录锁使得其他事务无法插入幻影行。
记录锁锁定存在的记录行间隙锁锁住记录行之间的间隙前开后闭
因此如果对性能要求较高的场景把隔离级别设置成RC不存在间隙锁加锁影响并发性能 15、drop、delete、trancate
delete和trancate 删除表数据不删除表结构
速度droptrancatedelete
delete会放在rollback中事务提交后才生效如果有trigger执行时会触发
drop和trancate操作立即生效不能回滚不触发trigger 16、inner 、left、right join
inner 两张表联结字段相等的数据 inner join
left左表为基表匹配连接字段相等右表有的数据没有的话显示null
right 右表为基表匹配连接字段相等左表有的数据没有的话显示null 17、union/union all的区别
union在进行表链接后会筛选掉重复的记录union all不会去除重复记录。 union会按照字段的顺序进行排序union all 只是将两个结果集合并后就返回。
从效率上讲union all要比 union快的多所以如果确定合并的两个结果集中没有重复且不需要排序就用Union all. 18、为什么sql语句不要过多的join 每个join都要对两个或多个表进行连接操作需要消耗大量的计算资源和时间 可读性和维护性sql语句复杂难以理解和维护 19、大表如何优化 限制数据范围比如时间范围等 读写分离主库写、从库读 垂直分区一张列很多的表拆分为多张表使得列数据变小减少读取的block数减少IO次数。但是主键会出现冗余可以使用join 水平分区也就是分表根据规则落在不同的表。分包只是解决了单一表数据过大的问题但表的数据还在同一台机器所以最好是分库 20、整体优化 硬件优化cpu、内存大小、带宽等 结构优化主从集群-单个mysql服务容易单点故障集群可以保证服务的高可用性 读写分离-避免读写导致的性能影响 分库分表-分库降低单个服务器节点的IO压力分表降低表数据 使用redis等-环境访问压力提升数据检索性能 sql优化通过my.conf完成的 需要注意-配置的作用域和是否支持热加载 热加载--全局参数的设定对于已经存在的会话无法生效 会话参数的设定随着会话的销毁而失效 全局类的统一配置建议配置在配置文件中否则重启服务会失效 21、Sql优化
慢sql的定位和排查
通过慢查询日志和慢查询日志分析工具得到有问题的sql列表
执行计划分析
针对慢sql可以使用explain关键字查看当前sql的执行计划重点关注type key rows等字段定位原因
使用show profile工具
分析当前对话sql执行过程中所有的资源开销情况 22、sql优化规则 不使用select * 减少子查询使用关联查询代替 减少使用in/not in使用exists/not exists或者关联查询代替 减少使用or可以用union/union all代替 避免where中使用!/否则会放弃索引进行全表扫描 避免where子句对字段进行null判断否则会放弃使用索引进行全表扫描 避免索引上使用函数或者运算导致失效 where中like% 尽量放在右边 尽量使用sql语句用到的索引完成排序 小结果集驱动大结果集 23、权限表 user链接服务器的用户账号信息 db记录各个账号在各个数据库上的操作权限 table_priv记录数据表级的操作权限 columns_priv记录数据列级的操作权限 host配个db表对给定主机上数据库级操作权限更细致的控制 24、b树和b树
平衡二叉树左节点比根节点小右节点比根节点大且子树的高度差不超过1左旋右旋平衡。
B树在b树上做了增强 B树的数据存储在节点B树存储在叶子结点并且通过链表的方式将叶子结点数据连接 B数是一种多路平衡二叉树子路数量关键字数1B树子路数量关键字数 25、Innodb中对b树的使用
磁盘IO性能开销较大特别是查询较多的情况下。
在Innodb中对存储在磁盘上的数据建立索引把索引数据以及索引对的磁盘地址以B存储。
因为B树分路多所以只需要较少次数的磁盘IO就能查到。
b树深度为3基本可以维护10亿数据所以高度一般都在2~4层。也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。 26、为什么使用B而不是红黑树 数据库存储的数据量较多导致索引很大索引存储在磁盘磁盘IO又是很消耗资源的并且b树的深度相对红黑树会少IO次数也就少 b树相邻的数据在物理上也是相邻的因为b树的节点大小设为一个页一个节点上存有多个相邻的关键字和分支信息每个节点只需要一次IO相当于一次IO还加载了多个相邻的关键字而红黑树中大小相邻的数据在物理结构上可能相差很大。基于局部性原理预加载每次磁盘访问的时候除了将访问到的页加载到磁盘还加载相邻的数据到内存中这个加载是不需要消耗磁盘IO的。 27、为什么使用B而不是b树 B树的数据存储在叶子节点索引存储在非叶子结点所以每一层能够存储的索引数量会增加b树在层高相同的情况下存储的数据量比B树多磁盘IO也就更少 b树的数据存储使用双向链表查询时只要两个节点遍历而b树需要获取所有节点所以b树更少且更稳定 28、对MVCC的理解
线程A和B同时写操作可能存在数据更新丢失的问题MVCC就是为了解决事务操作中并发安全性问题的无锁并发控制技术也就是多版本并发控制通过数据库记录隐式字段undo日志Read view来实现的。MVCC主要解决三个问题 解决读写并发阻塞问题提升数据并发处理能力 采用乐观锁降低死锁概率 解决一致性读的问题事务启动时根据某个条件读取到的数据结束时执行相同条件读到的是同一份
原理 获取事务自己事务ID,即trx_id。(这个也不是select的时候获取的而是这个事务开启的时候获取的 也就是begin的时候) 获取ReadView(这个才是select的时候才会生成的) 数据库表中如果查询到数据那就到ReadView中的事务版本号进行比较。 如果不符合ReadView的可见性规则 即就需要Undo log中历史快照,直到返回符合规则的数据;
InnoDB 实现MVCC是通过ReadView Undo Log 实现的Undo Log 保存了历史快照ReadView可见性规则帮助判断当前版本的数据是否可见。
MVCC只在已提交度和可重复读两个隔离级别下工作其他两个隔离级别和MVCC是不兼容的。因为未提交读总数读取最新的数据行而不是读取符合当前事务版本的数据行。而串行化Serializable则会对读的所有数据多加锁。 29、mvcc过程会加锁吗
通常不需要加锁
写操作时在修改数据之前会复制一份建立一个新的快照当一个事务需要修改时会检查修改数据的快照版本号和该事务的快照版本是否一致不一致就要等待其他事务完成修改。 30、为什么不推荐使用存储过程 不好调试多复杂逻辑不好定位问题 一致性差oracle迁移到mysql可能需要重写 管理困难存储过程的量较大的时候 优化和维护麻烦数据库的表结构可能发生变化需要同步到存储过程 不易解读 31、update是锁行还是锁表
取决于执行的update语句的条件等因素 如果where包含了索引列并且只更新一条数据就是行锁 如果where不包含索引列就是加表锁 记录锁间隙锁 for update 32、binlog和redolog的区别
binlog和redolog都是记录数据库数据变更操作的日志 binlog主要用来做数据备份、数据恢复、数据同步。在主从数据同步的场景中redolog主要用来实现mysql事务恢复保证ACID特性当数据库崩溃时redol可以把未提交的事务回滚已提交的事务持久化 binlog记录数据库的逻辑变化redolog记录的是物理变化。 binlog在执行sql时在主线程中生成逻辑变化写入磁盘是语句级别的记录方式redolog是在innodb存储引擎层面的操作是在mysql后台线程中生成并写入磁盘是事务级别的记录方式。 33、binlog几种格式分别什么区别 statement记录sql原文。不需要记录每一行的变化较少binlog日志量节约IO提高性能由于sql执行时有上下文的因此在保存的时候需要保存相关的信息同时还有一些使用了函数之类的语句无法被记录复制 row不记录sql上下文相关信息仅保存哪条记录被修改记录单元为每一行的改动基本是可以全部记下来但是由于很多操作会导致大量行的改动因此日志量大 mixed折中方法普通操作使用statement记录当无法使用statement的时候使用row 34、MD5的值应该用什么类型存储
MD5由数字和字母组成16/32长度的字符串
char类型是固定长度修改后存储空间的长度不变
varchar类型是可变长度修改后都需要更新存储空间长度 35、优化-explain
id查询中执行select子句或操作表的顺序 id相同执行顺序从上往下 id不同id值越大优先级越高越先执行select_type查询类型主要用于区别普通查询联合查询子查询等的复杂查询 1、simple —简单查询 2、primary —主查询外层的查询 3、subquery—在select语句中出现的子查询语句结果不依赖于外部查询不在from语句中 4、derived—在from列表中包含的子查询被标记为derived衍生MySQL会递归执行这些子查询把结果放到临时表中 5、union—如果第二个select出现在UNION之后则被标记为UNION如果union包含在from子句的子查询中外层select被标记为derived 6、union result:UNION 的结果table输出的行所引用的表type 显示联结类型显示查询使用了何种类型按照从最佳到最坏类型排序 1、system表中仅有一行系统表这是const联结类型的一个特例。 2、const通过索引一次就找到const用于比较primary key或者unique索引。 3、eq_ref唯一性索引扫描每个索引键表中只有一条记录与之匹配。常见唯一索引或者主键扫描。 4、ref非唯一性索引扫描返回匹配某个单独值的所有行可能会找多个符合条件的行。 5、range只检索给定范围的行使用一个索引来选择行。一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好因为它开始于索引的某一个点而结束另一个点不用全表扫描。 6、index只遍历索引树。通常比all快因为索引文件比数据文件小很多。 7、all遍历全表以找到匹配的行。 注意:一般保证查询至少达到range级别最好能达到ref。 possible_keys指出MySQL能使用哪个索引在该表中找到行key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引则该索引和查询的select字段重叠。key_len表示索引中使用的字节数该列计算查询中使用的索引的长度在不损失精度的情况下长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度并非实际使用长度。ref显示索引的哪一列被使用了rows大致估算出找到所需的记录所需要读取的行数Extra1、Using filesort说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引避免访问了表的数据行。如果同时出现using where表名索引被用来执行索引键值的查找如果没有同时出现using where表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false不能用来获取任何元组 7、select tables optimized away在没有group by子句的情况下基于索引优化Min、max操作或者对于MyISAM存储引擎优化count*不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。 8、distinct优化distinct操作在找到第一匹配的元组后即停止找同样值的动作。 36、mysql主从集群同步延迟问题如何解决
步骤主库的更新写到binlog从库发起连接连接到主库此时主库创建一个binlog dump thread把binlog的内容发送到从库从库启动后创建一个IO线程读取主库传过来的binlog内容并写入relay log从库还会创建一个sql线程从relay log读取更新事件将更新内容写入到从库
涉及到网络数据传输由于网络通信的延迟以及从库数据处理的效率问题导致主从数据同步延迟
解决 涉及一主多从分担从库压力减少主从同步延迟问题 如果对数据一致性要求高在从库延迟的情况下可以强制走主库查询数据 可以在从库上执行show slave status命令获取seconds_behind_master字段的延迟时间通过sleep阻塞等待固定时间后再次查询 通过并行复制解决从库延迟问题 37、cpu飙升如何处理
排查top命令找到cpu占用高的进程是否是mysql如果是通过show processlist查看当前的会话确定是付有消耗资源的sql运行通过explain具体分析
处理如果不是sql问题导致分析cpu飚高时mysql的整体并发连接数如果有大量的请求连接再分析业务
可以从查询优化、索引优化、配置优化、硬件升级等解决问题 38、数据库连接池有什么用哪些关键参数
核心私下是实现资源的复用避免资源重复创建销毁的开销每次crud时都要创建连接。线程池在启动的时候就提前初始化一部分的连接当需要使用连接时直接获取一个已经建立好的连接 初始化连接数连接数 ((核心数 * 2) 有效磁盘数) 最大连接数如果连接数不够后续线程会阻塞 最大空闲连接数没有请求时保留的最大空闲连接 最小空闲连接数连接数小于这个值时连接池需要再创建连接补充 最大等待时间连接数用完后新的请求要等待的时间 无效链接清楚清除无效连接
