MySQL

1.MyISAM和InnoDB有关索引的疑问？
MyISAM（非聚集）：使用B+树作为索引结构，叶子结点的data域存放的是数据记录的地址。MyISAM中索引检索的算法首先按B+树搜索索引，如果指定的key存在，则取出data域的值，然后以data域的值为地址去读取相应的数据记录。
InnoDB（聚集索引）：InnoDB的数据文件本身就是索引文件，这棵树的叶节点data域保存了完整的数据记录。但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键在主索引中检索得到记录。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须要有主键（MyISAM可以没有）。1）如果没有显示指明主键，MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键。2）如果不存在这种列，MySQL会为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
如果我们定义了主键（Primary Key），那么InnoDB会选择其作为聚集索引；如果没有显示设定主键，则InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引。
2.InnoDB为什么要用自增ID 作为主键？
如果表使用自增主键，那么每次插入新纪录，记录会添加到顺着当前索引节点的后续位置，当一页满了以后就会自动开辟出新的一页。
如果使用非自增主键（如身份证号、学号等），由于每次插入主键的值都近似于随机分布，因此每次新纪录都要被插入到现有索引页中间的位置，频繁移动、分页操作造成大量碎片，得到了不够紧凑的索引结构，后续不得不通过表优化来重建表并优化填充页面。

3.说说分库和分表设计分片？
分库和分表的目的在于减少数据库的单库单表负担，提高查询性能，缩短查询时间。
通过分表：可以减少数据库的单表负担，将压力分散到不同表上，同时因为不同表上的数据减少了，起到了提高查询性能，缩短查询时间的作用，此外可以很大缓解表锁的问题。分表策略可以归纳为垂直拆分和水平拆分。
水平分表：取模分表就属于随机分表，而时间维度分表属于连续分表。如何设计好垂直拆分？将不常用的字段单独拆分到另外一张扩展表，将大文本的字段单独拆分到另外一张扩展表，将不常修改的字段放在同一张表，将经常修改的字段放在另一张表中。对于海量用户的场景，可以考虑取模分表，数据相对均匀，不容易出现热点和并发的瓶颈。
库内分表：仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻MySQL服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括CPU、内存、磁盘IO、网络带宽等。

分库与分表带来的分布式困境与之应对之策？
数据迁移与扩容问题？——先通过程序读出数据，然后按照指定的分表策略再将数据写入到各个分表中。
分页与排序问题？——需要在不同分表中将数据排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。
分布式全局唯一ID——UUID、GUID等。

4.聚集索引与非聚集索引的的区别？
通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对于的主键值，然后再使用主键值通过聚集索引查到需要的数据。
聚集索引与非聚集索引的根本区别是表的记录顺序与索引的排列顺序是否一致。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

5.事务的四大特征（ACID）
原子性：要么全部完成，要么全部不完成。
一致性：事务开始前和结束后，数据库的完整性没有被破坏。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行而导致数据不一致。事务的隔离级别又分为4级：读未提交、读已提交、可重复读、串行化。
持久性：事务结束后，对数据库的修改是永久的。

6.事务的并发？事务隔离级别，每个级别会引发什么问题？MySQL默认是哪个级别？
脏读：一个事务在处理过程中读取了另一个事务未提交的数据。
不可重复读：对于数据库中的数据，在一个事务范围内多次查询却返回了不同的数值。
幻读：在一个事务读的过程中，另外一个事务可能插入了新的数据记录，影响了该事务读的结果。
MySQL默认的事务隔离级别：可重复读。

7.MySQL常见的存储引擎InnoDB、MyISAM的区别？
1）事务：MyISAM不支持事务，InnoDB支持事务。
2）锁级别：MyISAM是表级锁，InnoDB是行级锁。（MyISAM表级锁有两种模式：表共享读锁和表独占写锁。对MyISAM表进行读操作时，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作则会阻塞其他用户对同一表的读和写操作）。
InnoDB行锁是通过给索引项加锁来实现，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。行级锁每次在获取锁或者释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景不涉及InnoDB时，InnoDB是检测不到的，只能依靠锁定超时来解决。
3）MyISAM存储表的总行数；InnoDB不存储总行数。
4）MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针；InnoDB采用聚集索引，B+树叶子结点存储数据。

适用场景？
MyISAM适合插入不频繁，查询频繁，如果执行大量Select语句，MyISAM是更好的选择，没有事务。
InnoDB适合更新和查询都相当频繁，有大量Insert或Update语句，可靠性要求比较高或者要求事务。

8.数据库三范式
第一范式（确保每列保持原子性）：第一范式是最基本的范式，如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如每个数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行，但是如果系统经常访问“地址”属性中“城市”的部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便，这样的设计才算满足第一范式。
第二范式（确保表中每列都和主键相关 ）：第二范式在第一范式的基础上更上一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言），也就是说在一个数据库表中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据表中。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。
第三范式（确保每列和主键列直接相关，而不是间接相关）：第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，而不可以在订单表中添加关于客户其他信息（比如姓名、所属公司等）的字段。优点：可以尽量减少数据冗余。缺点：对于查询需要多个表进行关联，更难进行索引优化。
反范式化：优点：可以减少表的关联。缺点：数据冗余以及数据异常。

9.MySQL索引使用的注意情况
1）不要在列上使用函数，这将导致索引失效而进行全表扫描。
2）尽量避免使用！=或not in或<>等否定操作符。尽量避免使用or来连接条件。
3）多个单列索引并不是最佳选择。MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此创建多个单列索引并不能提高MySQL的查询性能。
4）复合索引的最佳左前缀原则。
5）覆盖索引。如果索引包含所有需要的查询字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。
6）范围查询对多列查询的影响。查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
7）索引不会包含有NULL值的列。
8）隐式转换的影响。当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响可能会导致索引失效而进行全表扫描。
9）like语句的索引失效问题。在like语句后面出现的%通用符在条件的最左边会导致全表扫描。

10.SQL怎么优化？
1.建索引。
2.减少表之间的关联。
3.优化SQL，尽量让SQL很快定位数据，不要让SQL做全表查询，应该利用索引，把数据量大的表排在前面。
4.简化查询字段，没用的字段不要，尽量返回少量数据。
5、尽量用PreparedStatement来查询，不要用Statement。

不要在列上使用函数或进行运算，会导致索引失效而进行全表扫描。
尽量避免使用！=或not in或<>等否定操作符。
尽量避免使用or来连接条件。
多个单列索引不是最佳选择，复合索引的最佳左前缀原则。
查询中的某个列有范围查询，则其右边的所有列都无法使用索引优化查找。
索引不会包含NULL值的列。
当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能会导致索引失效而进行全表扫描。
like语句中出现%在最左侧。

11.实践中如何优化MySQL？
顺序优化：
1）SQL语句及索引优化。
2）数据库表结构的优化。
3）系统配置的优化。
4）硬件的优化。

12.数据库悲观锁和乐观锁的原理和应用场景？
悲观锁：先获取锁，再进行业务操作，对数据加锁，避免其他事务意外修改数据，获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
乐观锁：先进行业务操作，只是在最后实际更新数据时进行检查数据是否被更新过，并不会对数据进行加锁，而是通过对比数据的时间戳或者版本号来实现乐观锁需要的版本判断。

13.索引是什么？MySQL为什么使用B+树，而不是使用其他？B+树的特点？
索引是帮助MySQL高效获取数据的数据结构，排好序的快速查找数据结构。
B+树（平衡多路查找树）：是MySQL所采取的存储结构，数据都在叶子节点上，并且增加了顺序访问指针，每一个叶子结点都指向相邻的叶子节点的地址。B+树索引需要从根节点到枝结点，最后才能访问到叶节点的多次IO访问。
hash：虽然可以快速定位，但没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟树的高度有关，并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。

为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据索引？
B+树的磁盘读写代价更低，B+树的查询效率更加稳定。
数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询时非常频繁的，而B树只能中序遍历所有节点，效率太低。
B+树的特点：1.所有关键字都出现在叶子节点的链表中，且链表中的关键字恰好是有序的。2.不可能在非叶子节点命中。3.非叶子节点相当于叶子节点的索引，叶子节点相当于是存储数据的数据层

14.创建索引时，需要注意什么？
1）非空字段：应该指定列为Not NULL,除非你想存储NULL。MySQL中包含空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0或者一个特殊的值或者空字符串代替空值。
2）取值离散大的字段：变量各个取值之间的差异程度较大的列放到联合索引的前面，可以通过count（）函数查看字段差异值，返回值越大说明字段的唯一值越多，字段的离散程度越大。
3）索引字段越小越好：数据库的数据存储以页为单位，一页存储的数据越多，一次IO获取的数据越多，效率越高。
4）唯一、不为空、经常被查询的字段适合建立索引。

15.Char和VarChar的区别？
1）Char的长度不可变，用空格填充到指定长度；而VarChar长度可以改变。
2）Char的存取速度比VarChar要快很多。
3）Char的存储方式对英文字符（ASCLL）占用一个字节，对汉字占用两个字节；而VarChar对每个英文字符和汉字都占两个字节。

16.Now（）和Current_Date（）有什么区别？
Now（）用于显示当前年份、月份、日、时、分、秒；Current_Date（）仅显示当前年份、月份、日期。

17.MySQL数据库作为发布系统的存储，一天五万条以上的增量，预计运维3年，怎么优化？
a.设计良好的数据库结构，允许部分数据冗余，避免Join查询，提高效率。
b.选择合适的表字段数据类型和存储引擎，适当添加索引。
c.MySQL数据库主从读写分离。
d.找规律分表，减少单表中的数据量，提高查询速率。
e.添加缓存机制。
f.不经常改动的页面生成静态页面。
g.书写高效率的SQL语句。

18.各种索引的概念
索引分类：MySQL常见的索引有主键索引、唯一索引、普通索引、全文索引、组合索引、聚簇索引（一次查询就能搞定，子节点命中，例如InnoDB）、非聚簇索引（多次查询才能搞定，第一次查询到的是地址值，然后根据地址值才能找到数据，例如MyISAM）。
（普通）索引：指定字段唯一、不为空值的列。
唯一索引：唯一索引可以保证数据记录的唯一性，使用关键字UNIQUE把它定义为唯一索引，唯一索引允许空值，但只能出现一次。
主键：是一种特殊的唯一索引，一张表中只能定义一个主键索引，使用关键字Primary Key来创建。
外键：表的外键是另一个表的主键。
组合索引：索引可以覆盖多个数据列，如INDEX（columnA，columnB）。
使用语句：CREATE INDEX index_name ON table_name(column_list)；
全文索引：全文索引的索引类型为FULLTEXT，可以在VARCHAR或者TEXT类型的列上创建。

19.建立索引的使用场景？
在频繁使用、用以缩小查询范围的字段、需要排序的字段上建立索引。
不宜在：1.对于查询中很少涉及的列或者重复值比较多的列上建立索引。2.对于一些特殊的数据类型不宜建立索引，如文本字段（TEXT）。

20.MySQL中的事务回滚机制、持久性、隔离级别的实现？
在MySQL中，恢复机制是通过回滚日志（undo log）来实现的，所有的事务进行的修改都会先记录到这个回滚日志中，然后再对数据库进行相应的读写操作。当事务提交以后就无法使用日志进行回滚。
回滚日志的作用：1）能够在发生错误或者用户执行ROLLBACK操作时提供回滚相关信息。2）在整个系统发生崩溃、数据库进程被直接杀死后，用户再次启动数据库进程时，还能立即通过查询回滚日志将之前未完成的事务进行回滚，这也是需要回滚日志必须先于数据，持久化到磁盘上，这也是需要我们先写日志后写数据库的主要原因。
MySQL使用重做日志（redo log）实现事务的持久性在数据库中，这两种日志经常都是一起工作。
隔离级别的实现：数据库对隔离级别的实现就是使用并发控制机制对同一时间执行的事务进行控制，限制不同事务对同一资源的访问和更新。使用共享锁和互斥锁来实现。
时间戳：使用时间戳实现事务的隔离性时，往往都会使用乐观锁，先对数据进行修改，在写回时再去判断当前时间戳是否改变过，如果没有改变就直接写入，否则就生成一个新的时间戳并再次更新数据。

21.说一说drop、delete与truncate的区别？
drop从数据库中删除表，所有数据行，索引和权限也会被删除，这个命令不能回滚。
delete用来删除表或者表中的部分数据行，执行delete之后需要提交（commit）或者回滚（rollback来执行删除或者撤销删除）。这个命令可以回滚。
truncate删除表中的所有数据，但表仍然存在，truncate比delete更快，占用的空间也更小。
因此在不需要一张表时，用drop；想删除部分数据行时使用delete；想保留表而删除所有数据时用truncate。

22.什么叫视图？游标是什么？
视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能。
游标是对查询出来的结果集作为一个单元来进行有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

23.内连接、自连接、外连接（左、右、全）、交叉连接的区别？
内连接：只有两个表相匹配的项才能在结果集中显示。
左外连接：左边为驱动表，驱动表的数据全部显示，被驱动表不匹配的数据不会显示。
右外连接：右边为驱动表，驱动表的数据全部显示，被驱动表不匹配的数据不会显示。
全外连接：连接的表中不匹配的数据全部会显示出来。
交叉连接：笛卡尔效应，显示的结果是连接表数的乘积。