1.char和varchar类型的区别charn固定长度类型比如存储“abc”,占用10个字节其中7个字节是空的char类型效率较高但是较占用内存。适用场景存储密码的MD5值固定长度非常合适。varchar(n)可变长度类型存储的值是每个值占用的字节再加上一个字节用来存储字节的长度。varchar占用的空间小但是效率比char的低。2.float和double的区别float最多可以存储8位的十进制数在内存中占用4个字节。double最多可以存储16位的十进制数在内存中占8个字节。3.数据库的三大范式第一范式每一列都是不可分割的整体。即不能在一列中再分为多列。第二范式消除非主属性对主属性组的部分依赖。如学号学生姓名系主任系名学科分数。此表中学号和学科是主属性组但是系主任和系名对主属性组只是部分依赖因此系主任和系名不能在这个表中违反了第二范式。第三范式消除非主属性对主属性的传达依赖。在学号系名和系主任这个表中系主任对学号是传递依赖因此将该表分为两个表否则违反了第三范式。4.一张自增表中总共有17条数据删除了最后两条数据重启mysql数据库又插入了一条数据此时id是几如果表类型是InnoDB则id是16因为InnoDB会将自增主键的最大id存在内存中如果mysql重启则会丢失最大id的记录因此是16.如果表类型是MyISAM则id是18。5.说一下mysql常用的引擎InnoDB引擎支持acid事物并且还提供了行级索和外键。但是InnoDB不支持全文检索。它的设计目标就是处理大数据容量的数据库系统。mysql运行的时候会在内存中建立缓冲池用来缓冲索引和数据但是InnoDB不支持全文搜索启动也比较慢并且不会保存表的行数所以当进行select count() from table造作时需要对全表进行扫描。MyISAM引擎mysql默认的引擎不支持事物行级锁外键所以当进行插入或更新操作时需要锁定整个表所以会导致效率非常低。但是它的查询速度非常快由于它记录了表的行数因此进行select count() from table操作时不用扫描整个表查询速度快因此当查询操作非常多且不需要事物时MyISAM引擎是不错的选择。6.说一下行锁和表锁MyISAM只支持表锁InnoDM支持行锁和表锁默认情况下是行锁。行级锁InnoDB提供了共享锁和排他锁。行锁开销大加锁慢容易出现死锁。锁粒度小发生锁冲突的概率小并发度高。1共享锁当某一事物给某一行加了共享锁则允许该事物读取改行的事物但不能修改该行允许其他事物给该行加共享锁但不能加排它锁。2排它锁当某一事物给某一行数据加了排它锁则允许该事物修改和读取该行但不允许其他事物对该行加上任何锁。**表锁**分为共享锁和独占锁表锁开销小加锁快不会出现死锁。但锁定粒度大易发生锁冲突并发量低。7.说一下乐观锁和悲观锁乐观锁每次去取数据的时候都认为别人不会修改数据所以不会上锁但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。悲观锁每次去拿数据的时候都会认为别人会修改数据因此在每次拿数据的时候都会上锁这样别人想拿这个数据就会阻止直到这个锁被释放。数据库的乐观锁需要自己实现在表里面添加一个version字段每次修改成功值加1这样每次修改的时候先对比一下自己拥有的version和数据库中现在的version是否一致如果不一致就不修改这样就实现了乐观锁。8.说一下mysql的索引索引就像是书的目录一样加快了数据库的查询速度。mysql最常用的索引的底层结构是B树。B数是一个平衡树。9.B树和B树的区别B树是一个平衡二叉树用阶数m表示每个节点最多有多少个孩子节点则1每个节点最多m-1个关键字2根节点最少有1个关键字非根节点最少有Math(m/2)-1个关键字。3每个节点中的关键字都是按照从小到达的顺序排列。4所有的叶子节点都在同一层即所有跟节点到叶子节点的路径长度相同。B树与B树不同点1B树非叶子节点存储的不是数据而是索引所有的数据都存储在叶子节点上。2每个叶子节点中的数据按照从小到大的顺序排列并且每个叶子节点都指向下一个叶子节点所有的叶子结点都以链表的形式连接起来。10.为什么要选择B树作为索引的底层结构而不是B树1B树所有关键字都在叶子节点所有叶子结点都以链表的形式连接且每个叶子结点的关键字都是以从小到大的顺序排列因此B树适合基于范围的查找。2B树所有关键字在叶子节点且所有叶子结点都在同一层因此B树的查询效率更稳定。3B树更便于遍历B树的所有数据在叶子结点因此遍历的时候只需要扫一遍叶子节点即可而B树在每个节点上都存储了数据因此遍历时需要扫所有节点。4B树的磁盘读写效率更高B树所有的数据指向数据库数据的指针都在叶子结点其内部存的都是指向叶子结点的索引即内部节点相对于B树小因此能同样大小的空间能存储更多的数据因此一次读进去内存的数据更多了减小了I/O读写次数。10.索引的分类主键索引加快查询速度列值唯一表中只有一个主键索引。唯一索引加快查询速度并且列值唯一。组合索引联合索引多列值组成一个索引使用组合搜索比索引组合的查询效率高。普通索引仅加快查询速度。全文索引可以提高全文搜索的查询效率用于检索文本信息。11.为什么使用数据库索引能提高查询效率1数据索引的存储是有序的索引中存储的指向数据的指针根据索引直接查询数据速度快。注意聚合索引数据的物理顺序和列的顺序是一致的。因此聚合索引的查询速率很高。2有序的情况下通过索引查询数据是无需遍历索引记录的。3极端情况下数据索引的查询效率为二分法查询效率趋近于log2n.12.索引的优点和缺点优点1索引能加快数据的查询速度。2索引能加快表与表之间的连接。3通过唯一索引可以使得本列值唯一。4对于有group by和order by的查询语句可以加快分组和排序的速度。缺点1索引会影响增删改对数据库中数据进行增删改的时候对索引也要进行动态的维护。2索引也要占用空间3创建维护索引会消耗时间。13.唯一索引为什么能保证数据值的唯一14 .什么情况下索引失效(1)模糊查询时 like(2)where语句后有or(3)where语句后对索引列有数学运算时4where语句后的条件队索引列使用了函数5存在索引列数据类型的隐形转换6组合索引中索引列不在where语句后第一个条件。15.什么情况下不适合使用索引1字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件不建议使用索引2 where 子句里对索引列使用不等于使用索引效果一般3频繁更新的字段不要使用索引4数据唯一性差一个字段的取值只有几种时的字段不要使用索引16 哈希索引和B树索引的区别B树索引中的B树是一个平衡二叉树B树所有的指向数据的指针都存储在叶子结点所有的叶子节点都在同一层每个节点中的关键字都是按照从小到大的顺序进行排序叶子结点之间都以链表的形式连接起来。Hash索引是根据一定的hash算法计算出每个键值的hash值检索时只需要一次哈希算法即可是无序的。17什么时候适合用hash索引什么时候不适用等值查询的时候适合用于等值索引。不适用1不支持范围查询2不支持索引完成排序3不支持联合索引的最左前缀匹配规则常用的InnoDB引擎中默认使用的是B树索引他会实时监控表上索引的使用情况。如果认为建立哈希表索引可以提高查询效率则自动在内存中自适应哈希索引缓冲区建立哈希索引。通过观察搜索模式mysql会利用index key的前缀建立哈希索引如果一个表几乎大部分都在缓冲池中那么建立一个哈希索引能加快等值查询。18.mysql联合索引联合索引组合索引是两个或多个列上的索引。对于联合索引mysql从左到右使用索引中的字段一个查询可以只使用索引中的一部分但必须包含最左边的字段。利用索引中的附加列可以缩小搜索范围。使用一个两列的索引不同于两个索引比两个单独的索引的查询速度要快。19.怎么验证mysql索引是否满足需求使用explain查看sql是如何执行查询语句的从而分析你的索引是否满足需求。explain select * from table where type1;20.为什么用自增列作为主键如果我们定义了主键那么InnoDB会选择主键作为聚集索引。如果没有定义主键那么会将第一个非空的唯一索引作为聚集索引。如果也没有这样一个唯一索引则InnoDB会选择内置长度为6的ROWID作为隐含的聚集索引。数据记录存放在B数的叶子结点按照顺序进行存放。如果聚集索引列为自增则每次增加的新的记录记录就会顺序添加到当前索引节点的后续位置。如果使用非自增主键则每次插入的记录近似随机因此每次数据新纪录毒药被添加到现有记录的某个中间位置。此时mysql为了插入数据不得不移动数据这增加了许多开销。同时频繁的移动分页操作增加了大量碎片得到了不紧凑的数据结构。21.如何获取数据库的版本select version()获取当前mysql的版本。22.数据库的acid是什么原子性每个事务都是不可分割的一个整体要么全部成功要么全部失败。一致性一个事务操作前后的数据是一致的。如银行转账数据整体是一致的。永久性一个事务对数据的改变是永久的不可逆转的。隔离性每个事务是相互独立的事务之间互不影响。问题一Mysql怎么保证一致性的OK这个问题分为两个层面来说。从数据库层面数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中C(一致性)是目的A(原子性)、I(隔离性)、D(持久性)是手段是为了保证一致性数据库提供的手段。数据库必须要实现AID三大特性才有可能实现一致性。例如原子性无法保证显然一致性也无法保证。但是如果你在事务里故意写出违反约束的代码一致性还是无法保证的。例如你在转账的例子中你的代码里故意不给B账户加钱那一致性还是无法保证。因此还必须从应用层角度考虑。从应用层面通过代码判断数据库数据是否有效然后决定回滚还是提交数据问题二: Mysql怎么保证原子性的OK是利用Innodb的undo log。undo log名为回滚日志是实现原子性的关键当事务回滚时能够撤销所有已经成功执行的sql语句他需要记录你要回滚的相应日志信息。例如(1)当你delete一条数据的时候就需要记录这条数据的信息回滚的时候insert这条旧数据(2)当你update一条数据的时候就需要记录之前的旧值回滚的时候根据旧值执行update操作(3)当年insert一条数据的时候就需要这条记录的主键回滚的时候根据主键执行delete操作undo log记录了这些回滚需要的信息当事务执行失败或调用了rollback导致事务需要回滚便可以利用undo log中的信息将数据回滚到修改之前的样子。ps:具体的undo log日志长啥样这个可以写一篇文章了。而且写出来看的人也不多姑且先这么简单的理解吧。问题三: Mysql怎么保证持久性的OK是利用Innodb的redo log。正如之前说的Mysql是先把磁盘上的数据加载到内存中在内存中对数据进行修改再刷回磁盘上。如果此时突然宕机内存中的数据就会丢失。怎么解决这个问题简单啊事务提交前直接把数据写入磁盘就行啊。这么做有什么问题只修改一个页面里的一个字节就要将整个页面刷入磁盘太浪费资源了。毕竟一个页面16kb大小你只改其中一点点东西就要将16kb的内容刷入磁盘听着也不合理。毕竟一个事务里的SQL可能牵涉到多个数据页的修改而这些数据页可能不是相邻的也就是属于随机IO。显然操作随机IO速度会比较慢。于是决定采用redo log解决上面的问题。当做数据修改的时候不仅在内存中操作还会在redo log中记录这次操作。当事务提交的时候会将redo log日志进行刷盘(redo log一部分在内存中一部分在磁盘上)。当数据库宕机重启的时候会将redo log中的内容恢复到数据库中再根据undo log和binlog内容决定回滚数据还是提交数据。采用redo log的好处其实好处就是将redo log进行刷盘比对数据页刷盘效率高具体表现如下redo log体积小毕竟只记录了哪一页修改了啥因此体积小刷盘快。redo log是一直往末尾进行追加属于顺序IO。效率显然比随机IO来的快。ps:不想具体去谈redo log具体长什么样因为内容太多了。问题四: Mysql怎么保证隔离性的OK,利用的是锁和MVCC机制。还是拿转账例子来说明有一个账户表如下表名t_balance在这里插入图片描述其中id是主键user_id为账户名balance为余额。还是以转账两次为例如下图所示至于MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据这些快照数据在undo log中。如果一个事务读取的行正在做DELELE或者UPDATE操作读取操作不会等行上的锁释放而是读取该行的快照版本。由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同就不赘述了。但是有一点说明一下在事务隔离级别为读已提交(Read Commited)时一个事务能够读到另一个事务已经提交的数据是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中是满足隔离性的。23.事务的隔离性脏读一个事务你能读到另一个事务未提交的数据。不可重复读一个事务由于在两次读数据的中间数据被另一个事务修改了导致两次读到的数据不一致。幻读由于另一个事务删除或增加了数据一个事务两次读到的数据个数不一样。事务的隔离级别未提交读read-uncommited可能会发生脏读不可重复读幻读提交读(read-commited)解决了脏读但还会发生不可重复读可重复读(repeatable-read)解决了不可重复读但还会发生幻读串性(serializable)解决了所有问题。\24.mysql问题排查都有哪些手段使用show processlist命令查看当前所有连接信息。使用explain查询sql语句执行计划。开启慢查询日志查看慢查询的sql。25.如何做mysql的性能优化1为搜索字段创建索引。2避免使用select *列出需要查询的字段。3垂直分割表。垂直拆分是拆分字段水平拆分是按行拆分4选择正确的存储引擎。26 存储函数和存储过程的区别存储过程指的是将一会断pl/sql语句块存储在数据库端当进行查询的时候直接调用该存储过程语句从而加快了查询速度。存储函数和存储过程扥区别1存储过程和存储函数本质的区别是存储函数有返回值存储过程没有返回值。2存储函数的关键字为function,存储过程的关键字为procedure.(3) 由于存储函数有返回值因此存储函数只能作为表达式的一部分。4存储过程和存储函数都可以使用out关键字来接收多个值并返回。5存储函数可以作为sql语句的一部分而存储过程不可以。27.key和index的区别1.key是数据库的物理结构它包含两层意义和作用一是约束二是索引。包含primary key,unique key,foreign key.2.index是数据库物理结构它只是辅助查询的它创建的时候会在另外的表空间以一个类似目录的结构存储。28 .mysql各个版本的区别mysql5.7 : 2015年发布mysql5.7查询性能得以大幅提升比 MySQL 5.6 提升 1 倍降低了建立数据库连接的时间。mysql5.6 : 2013年2月发布mysql5.6版本其中InnoDB可以限制大量表打开的时候内存占用过多的问题InnoDB性能加强。如大内存优化等InnoDB死锁信息可以记录到 error 日志方便分析InnoDB提供全文索引能力。mysql5.5 : 2010年12月发布mysql5.5版本默认存储引擎更改为InnoDB 多个回滚段Multiple Rollback Segments,之前的innodb版本最大能处理1023个并发处理操作现在mysql5.5可以处理高达128K的并发事物 改善事务处理中的元数据锁定。例如事物中一个语句需要锁一个表会在事物结束时释放这个表而不是像以前在语句结束时释放表。 增加了INFORMATION_SCHEMA[ˈski:mə]]表新的表提供了与InnoDB压缩和事务处理锁定有关的具体信息。mysql5.1 : 20o8年发布的MySQL 5.1 的版本基本上就是一个增加了崩溃恢复功能的MyISAM使用表级锁但可以做到读写不冲突即在进行任何类型的更新操作的同时都可以进行读操作但多个写操作不能并发。mysql-5.0 : mysql-5.0版本之前myisam默认支持的表大小为4G。从mysql-5.0以后myisam默认支持256T的表单数据。myisam只缓存索引数据。 2005年的5.0版本又添加了存储过程、服务端游标、触发器、查询优化以及分布式事务功能。mysql-4.1 : 2002年发布的4.0 Beta版至此MySQL终于蜕变成一个成熟的关系型数据库系统。 2002年mysql4.1版本增加了子查询的支持字符集增加UTF-8GROUP BY语句增加了ROLLUPMySQL.user表采用了更好的加密算法。支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC(多版本并发控制)来获取高并发性并且实现sql标准的4种隔离级别同时使用一种被称成next-key locking的策略来避免幻读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。28.关于MVCCMVCC基于多版本的并发控制协议数据库实现数据隔离方式一般有两种1在读取数据前对其加锁防止其他事务修改数据。2不用对数据加任何锁通过一定机制对数据的请求时间点形成一个一致性数据快照并用这个快照提供一定级别的一致性读取。在用户角度好像是数据库可以对同一数据提供不同的版本因此叫做多版本数据并发控制协议也叫多版本数据库。28.MVCC并发控制中读操作可以分为两类快照读读取的是可见版本不用加锁。当前读读取的是最新版本需要对返回记录加锁保证其他事务不会并发的修改这条记录。29数据库的日志主要分为几种分别记录什么内容数据库的日志主要是Undo,Redo,binlogUndo记录的是修改前的数据主要用来回滚后对数据进行恢复。Redo记录的主要是修改后的数据主要为了恢复已确认但未提交的数据如数据库突然重启重启后数据库会对所有Redo中存在但未存入数据库的数据重做一遍。binlog:记录的主要是增删改等对数据有更新的内容的记录但不记录查询记录如show,select查询的数据。主要是用来增量恢复和主从复制。30.如果数据库的日志满了会发生什么情况如果数据库的日志满了数据库几乎没法用只能对数据读操作没法进行写操作因为所有的写操作都会被记录在数据库。31.什么是表分区表分区指的是按照一定规则将数据库表分成更小的易于管理的几个部分从逻辑上看只有一张表但是底层是由多个物理分区组成的。32.表分区有什么好处存储更多的数据分区表的数据可以分布在不同的物理设备上从而高效的利用多个硬件设备。与单个磁盘或硬件设备相比可以存储更多的数据。优化查询在where语句中包含分区条件可以只查询一个或几个分区从而提高查询效率。涉及sum和count时可以同时在多个分区上进行最后汇总结果。分区表更容易维护想批量删除大量数据可以清除整个分区。避免某些特殊的瓶颈33.分区表的限制因素1一个表最多1024个分区2分区表不能有外键3一个分区中要么都包含主键和索引要么都不包含。4mysql数据表的分区适用于一个表的所有数据和索引必须对表的所有数据进行分区不能只对部分数据进行分区。5mysql5.1版本中分区表达式必须是整数或者返回整数的表达式。在mysql5.5中提供了非整数分区表达式的支持。34.mysql支持分区的类型有哪些RANGE分区这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区LIST分区这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区与RANGE的区别是range分区的区间范围值是连续的。HASH分区 这中模式允许通过对表的一个或多个列的Hash Key进行计算最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。KEY分区 上面Hash模式的一种延伸这里的Hash Key是MySQL系统产生的。RANGE分区这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区LIST分区这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区与RANGE的区别是range分区的区间范围值是连续的。HASH分区 这中模式允许通过对表的一个或多个列的Hash Key进行计算最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。KEY分区 上面Hash模式的一种延伸这里的Hash Key是MySQL系统产生的。35如何判断当前mysql是否支持分区命令show variables like ‘%partition%’ 运行结果:mysql show variables like ‘%partition%’;±------------------±------| Variable_name | Value |±------------------±------| have_partitioning | YES |±------------------±------1 row in set (0.00 sec)have_partintioning 的值为YES表示支持分区。