Mysql摘要

• datetime 和timestamp区别
  1,最主要的区别-受时区影响不同。timestamp会跟随设置的时区变化而变化，而datetime保存的是绝对值不会变化；一个timestamp字段，一个datetime字段，修改时区SET TIME_ZONE = “america/new_york”;后，timestamp字段的值变了!
  因此，如果应用场景有跨时区要求的要特别注意这点。
  2,占用存储空间不同。timestamp储存占用4个字节，datetime储存占用8个字节：12.8 Data Type Storage Requirements
  3,可表示的时间范围不同。timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07，datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  4,索引速度不同。timestamp更轻量，索引相对datetime更快。
• varchar与char区别
  1,char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。
  2,对 char 来说，最多能存放的字符个数 255，和编码无关。
  而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据(Dirty Data)，依据脏数据所做的操作可能是不正确的.
不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读.
幻读幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）；

数据库优化

• 避免进行null判断
  应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，这里最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。备注、描述、评论之类的可以设置为 NULL，最好不要使用NULL。不要错误的认为NULL 不需要空间，如char(100) 型，在字段建立时，空间就固定了。不管是否插入值(NULL也包含在内)，都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。可以在num上设置默认值0，确保表中num列没有null值。
• 不要使用select (*)
  使用select *的话会增加解析的时间，另外也会把不需要的数据同时查询出来，从而延长数据传输时间，耗费精力。如text类型的字段，通常用来保存一些内容比较繁杂的东西，如果使用select *，则会把该字段也查询出来。
• 谨慎使用模糊查询
  当模糊匹配以%开头时，该列索引将失效。若不以%开头，该列索引有效
• 不要使用列号
  使用列号的话，将会增加不必要的解析时间。
• 优先使用UNION ALL，避免使用UNION
  因为UNION 会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况，如果业务上能够确保不会出现重复记录。
• 在where语句或者order by语句中避免对索引字段进行计算操作
  当在索引列上进行操作之后，索引将会失效。正确做法应该是将值计算好再传入进来。
• 使用not exist代替not in
  如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引;而not extsts 的子查询依然能用到表上的索引。
• exist和in的区别
  in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。因此，in用到的是外表的索引， exists用到的是内表的索引。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
• 避免在索引列上做如下操作
  1.避免在索引列上使用IS NULL和IS NOT NULL。
  2.避免在索引列上出现数据类型转换。(比如某字段是String类型，参数传入时是int类型)当在索引列上使用如上操作时，索引将会失效，造成全表扫描。
• 事务的基本特征
  原子性（atomicity）：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
  一致性（consistency）：数据库总数从一个一致性的状态转换到另一个一致性的状态。
  隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
• Mysql事务的隔离级别，mysql默认的隔离级别是什么？
  读未提交(Read uncommitted)，一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证。
  读已提交(Read committed)，一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生。
  可重复读(Repeatable read)，就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生。
  串行(Serializable)，是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
  Mysql的默认隔离级别是Repeatable read。
• Mysql中的MyISAM与InnoDB的区别
  InooDB支持事务，而MyISAM不支持事务；InnoDB支持行级锁，而MyISAM支持表级锁；InnoDB支持MVCC，而MyISAM不支持；InnoDB支持外键，而MyISAM不支持；InnoDB不支持全文索引，而MyISAM支持；InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器， MyISAM 支持；InnoDB表支持多种行格式， MyISAM 不支持；InnoDB是索引组织表， MyISAM 是堆表。
• 数据库三大范式
  第一范式：数据库表中的字段都是单一属性的，不可再分(保持数据的原子性)；
  第二范式：第二范式必须符合第一范式，非主属性必须完全依赖于主键。
  第三范式：在满足第二范式的基础上，在实体中不存在其他实体中的非主键属性，传递函数依赖于主键属性，确保数据表中的每一列数据都和主键直接相关，而不能间接相关(表中字段[非主键]不存在对主键的传递依赖)
• sql语句优化，至少五种
  1,避免select *，将需要查找的字段列出来；
  2,使用连接（join）来代替子查询；
  3,拆分大的delete或insert语句；
  4,使用limit对查询结果的记录进行限定；
  5,用 exists 代替 in 是一个好的选择；
  6,用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤；
  7,不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
  8,尽量避免在where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
  9,尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描；
  10,尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
• 表结构优化，至少五种
  1,永远为每张表设置一个ID (所有建表的时候不设置主键的程序猿都应该被辞退)；
  2,选择正确的存储引擎 ;使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob；
  3,使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数；
  4,使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar；
  5,尽可能使用not null定义字段(给空字段设置默认值)；
  6,尽量少用text;
  7,给频繁使用和查询的字段建立合适的索引；
• 索引的作用？和它的优点缺点是什么？
  索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
• MySQL主要的索引类型普通索引
  是最基本的索引，它没有任何限制；唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，mysql中MyISAM支持全文索引而InnoDB不支持；
• 使用索引注意事项
  索引不会包含有NULL的列，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的；使用短索引，对串列进行索引，如果可以就应该指定一个前缀长度；短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作；mysql查询只使用一个索引，因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引；注意like，上文已经提到；不要在列上进行运算；不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的；索引要建立在经常进行select操作的字段上；索引要建立在值比较唯一的字段上；对于那些定义为text、image和bit数据类型的列不应该增加索引；在where和join中出现的列需要建立索引；如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引；在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用；
• 说一说什么是外键，优缺点
  外键指的是外键约束，目的是保持数据一致性，完整性，控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中列的值；
  优点：由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。外键在一定程度上说明的业务逻辑，会使设计周到具体全面。
  缺点：可以用触发器或应用程序保证数据的完整性；过分强调或者说使用外键会平添开发难度，导致表过多，更改业务困难，扩展困难等问题；不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在insert, update, delete 数据的时候更快）；
  业务逻辑非常简单，业务一旦确定不会轻易更改，表结构简单，业务量小的时候我会选择使用外键。因为当不符合以上条件的时候，外键会影响业务的扩展和修改，当数据量庞大时，会严重影响增删改查的效率。
• mysql怎么在查询时给查出来的数据设置一个自增的序号？
  set @i=0;SELECT (@i:=@i+1) 别名 FROM table, (SELECT @i:=0) AS 别名 ;
• 如何使用explain优化sql和索引？
  explain sql ;table：显示这一行的数据是关于哪张表的；type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL；all: full table scan ;MySQL将遍历全表以找到匹配的行；index ： index scan; index 和 all的区别在于index类型只遍历索引；range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，< ,>等查询；ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型；如果将主键置于where列表中，MySQL就能将该查询转化为一个常量；possible_keys：显示可能应用在这张表中的索引；如果为空，没有可能的索引；可以为相关的域从WHERE语句中选择一个合适的语句；key： 实际使用的索引；如果为NULL，则没有使用索引；很少的情况下，MySQL会选择优化不足的索引；这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引key_len：使用的索引的长度；在不损失精确性的情况下，长度越短越好；ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；rows：MySQL认为必须检查的用来返回请求数据的行数；Extra：关于MySQL如何解析查询的额外信息；以上就是阿伟暂时对mysql面试题的总结，mysql能问的当然不止那么多，不过咱们都是干后端开发，也不是专业的DBA，所以这30题能满足大部分程序猿的需求，当然如果你想更好的复习mysql相关知识，还要多做笔试题，多写写sql才行；