阅读量:14
题外推荐
本文的面试题如下：
MyisAM和innodb的有关索引的疑问
两者都是什么索引？聚集还是非聚集
MyISAM（ 非聚集）
使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
InnoDB（ 聚集索引）
第一个重大区别是InnoDB的数据文件本身就是索引文件， 这棵树的叶节点data域保存了完整的数据记录。
但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）
简单说：
如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；
innodb不需要myi文件
innodb为什么要用自增id作为主键：
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。
说说分库与分表设计（面试过）分片
分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。
通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。
分表策略可以归纳为垂直拆分和水平拆分。
水平分表： 取模分表就属于随机分表，而时间维度分表则属于连续分表。
如何设计好垂直拆分，我的建议：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。
对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。
库内分表： 仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。
分库与分表带来的分布式困境与应对之策
聚集索引与非聚集索引的区别
非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚集索引（innodb）的叶节点就是数据节点，而非聚集索引(myisAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。（结合上文）
推荐：
事务四大特性（ACID）原子性、一致性、隔离性、持久性？事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？
脏读是指在一个事务处理过程中读取了另一个事务未提交的数据。
不可重复读：对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值幻读：事务非独立执行时发生的一种现象，即在一个事务读的过程中，另外一个事务可能插入了新数据记录，影响了该事务读的结果
MySQL的默认隔离级别就是Repeatable read，可重复读。
从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行， 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行， 事务的隔离级别可以通过隔离事务属性指定。事务的并发问题
事务的并发问题
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
事务的隔离级别
读未提交（Read uncommitted），就是一个事务能够看到其他事务尚未提交的修改，允许脏读出现。
读已提交（Read committed），事务能够看到的数据都是其他事务已经提交的修改，也就是保证不会看到任何中间性状态，当然脏读也不会出现。
可重复读（Repeatable reads），保证同一个事务中多次读取的数据是一致的，这是 MySQL InnoDB 引擎的默认隔离级别。
串行化（Serializable），并发事务之间是串行化的，通常意味着读取需要获取共享读锁，更新需要获取排他写锁，如果 SQL 使用 WHERE 语句，还会获取区间锁（MySQL 以 GAP 锁形式实现，可重复读级别中默认也会使用），这是最高的隔离级别。
MySQL默认的事务隔离级别为repeatable-read
MySQL 支持 4 中事务隔离级别.
事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
MySQL常见的存储引擎InnoDB、MyISAM的区别？
1）事务：MyISAM不支持，InnoDB支持
2）锁级别：MyISAM 表级锁，InnoDB 行级锁及外键约束
（MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。）
3）MyISAM存储表的总行数；InnoDB不存储总行数；
4）MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引，B+树叶子存储数据
适用场景：
数据库三范式，根据某个场景设计数据表？优缺点第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。
上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。
第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。
第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
优点：可以尽量得减少数据冗余 缺点:对于查询需要多个表进行关联，更难进行索引优化反范式化: 优点:可以减少表得关联 缺点:数据冗余以及数据异常
MySQL 索引使用的注意事项
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。
函数，运算，否定操作符，连接条件，多个单列索引，最左前缀原则，范围查询，不会包含有NULL值的列，like 语句不要在列上使用函数和进行运算
1）不要在列上使用函数，这将导致索引失效而进行全表扫描。
select * from news where year(publish_time) < 2017
为了使用索引，防止执行全表扫描，可以进行改造。
select * from news where publish_time < '2017-01-01'
还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。
select * from news where id / 100 = 1
为了使用索引，防止执行全表扫描，可以进行改造。
select * from news where id = 1 * 100
2）尽量避免使用 != 或 not in或 等否定操作符
select * from news where id = 1 or id = 2
3）多个单列索引并不是最佳选择
MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。
假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：
select * from news where news_year = 2017 and news_month = 1
事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。
4）复合索引的最左前缀原则
复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：
select * from news where news_month = 1
此时，无法使用 news_year_month_idx(news_year, news_month)索引，因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。
5）覆盖索引的好处
如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。
6）范围查询对多列查询的影响
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
举个例子，假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。那么，SQL 语句可以写成：
select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1
这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable)索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说，news_publish_idx(publish_time, enable)索引等价于 news_publish_idx(publish_time)。
对于这种情况，我的建议：对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。
例如，上面案例的需求是查询本周发布的资讯文章，因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，SQL 可以改写成：
select * from news where news_weekth = 1 and enable = 1
然而，并不是所有的范围查询都可以进行改造，对于必须使用范围查询但无法改造的情况，我的建议：不必试图用 SQL 来解决所有问题，可以使用其他数据存储技术控制时间轴，例如 Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能。
7）索引不会包含有NULL值的列
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。
因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。
8）隐式转换的影响
当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。
select * from news where date_str = 201701
因此，要谨记隐式转换的危害，时刻注意通过同类型进行比较。
9）like 语句的索引失效问题
like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。
SQL怎么优化
SQL优化是一个分析，优化，再分析，再优化的过程。站在执行计划的角度来说，我们这个过程，就是在不断的减少rows的数量。
建索引
减少表之间的关联
优化 sql，尽量让 sql 很快定位数据，不要让sql 做全表查询，应该走索引,把数据 量大的表排在前面
简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
尽量用PreparedStatement 来查询，不要用 Statement
like 语句的索引失效问题
先运行看看是否真的很慢，注意设置SQL_NO_CACHE
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果，不符合预期继续从0分析
如何做 MySQL 的性能优化？
为搜索字段创建索引。
避免使用 select *，列出需要查询的字段。
垂直分割分表。
选择正确的存储引擎。
实践中如何优化MySQL
顺序优化：
SQL语句及索引的优化
数据库表结构的优化
系统配置的优化
硬件的优化
数据库悲观锁和乐观锁的原理和应用场景？
悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。
当数据库执行select … for update时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
乐观锁则与，先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。推荐：
索引是什么？MySQL为什么使用B+树，而不是使用其他？B+树的特点
索引是帮助MySQL高效获取数据的数据结构。索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序。
B+Tree索引（平衡多路查找树）
是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。B-Tree需要获取所有节点，相比之下B+Tree效率更高。B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;
为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？
B+tree的磁盘读写代价更低，B+tree的查询效率更加稳定
数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
B+树的特点：
创建索引时需要注意什么？
非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。唯一、不为空、经常被查询的字段 的字段适合建索引
CHAR和VARCHAR的区别？NOW（）和CURRENT_DATE（）有什么区别？MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？各种索引的概念：索引，主键，唯一索引，联合索引，索引分类建立索引的使用场景
在最频繁使用的、用以缩小查询范围的字段,需要排序的字段上建立索引。
不宜：
Myql中的事务回滚机制,持久性，隔离级别的实现
而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了回滚日志作用：
MySQL 使用重做日志（redo log）实现事务的持久性在数据库中，这两种日志经常都是一起工作的.隔离级别的实现
数据库对于隔离级别的实现就是使用并发控制机制对在同一时间执行的事务进行控制，限制不同的事务对于同一资源的访问和更新.锁: 共享锁（Shared）和互斥锁（Exclusive），前者也叫读锁，后者叫写锁
时间戳:
使用时间戳实现事务的隔离性时，往往都会使用乐观锁，先对数据进行修改，在写回时再去判断当前值，也就是时间戳是否改变过，如果没有改变过，就写入，否则，生成一个新的时间戳并再次更新数据
说一说drop、delete与truncate的区别
drop、delete、truncate都表示删除，但是三者有一些差别：
因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。
什么叫视图？游标是什么？
视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能
游标是对查询出来的结果集作为一个单元来有效的处理。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。推荐：
什么是存储过程？用什么来调用？
存储过程是一个预编译的 SQL 语句， 使用存储过程比单纯 SQL 语句执行要快。
调用：
内连接、自连接、外连接（左、右、全）、交叉连接的区别手写SQL
感谢阅读，希望对你有所帮助:)
来源：blog.csdn.net/Butterfly_resting/article/details/89704636
●
●
●
与其在网上拼命找题？ 不如马上关注我们~
PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我们吧！
