MySQL 三万字精华总结 + 面试100 问，吊打面试官绰绰有余（收藏系列）

❝

写在之前：不建议那种上来就是各种面试题罗列，然后背书式的去记忆，对技术的提升帮助很小，对正经面试也没什么帮助，有点东西的面试官深挖下就懵逼了。

个人建议把面试题看作是费曼学习法中的回顾、简化的环节，准备面试的时候，跟着题目先自己讲给自己听，看看自己会满意吗，不满意就继续学习这个点，如此反复，好的offer离你不远的，奥利给

一、MySQL架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

• 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

• 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

• 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

• 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

❝

画出 MySQL 架构图，这种变态问题都能问的出来

MySQL 的查询流程具体是？or  一条SQL语句在MySQL中如何执行的？

客户端请求 ---> 连接器（验证用户身份，给予权限）  ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作）  ---> 优化器（主要对执行的sql优化选择最优的执行方案方法）  ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）图：极客时间

 

❝

说说MySQL有哪些存储引擎？都有哪些区别？

二、存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表

存储引擎对比

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键

文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为 数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：show variables like 'data%'

MyISAM 物理文件结构为：

• .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

• .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据

• .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

• .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

• .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

  独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

❝

ps：正经公司，这些都有专业运维去做，数据备份、恢复啥的，让我一个 Javaer 搞这的话，加钱不？

面试这么回答

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

❝

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

❝

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

• 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。

• 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

三、数据类型

主要包括以下五大类：

• 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

• 浮点数类型：FLOAT、DOUBLE、DECIMAL

• 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

• 日期类型：Date、DateTime、TimeStamp、Time、Year

• 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

❝

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

相同点：

1. char(n)，varchar(n)中的n都代表字符的个数

2. 超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

1. char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0 show variables ——显示系统变量（扩展show variables like ‘XXX’） Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等 Shell> mysqladmin variables -u username -p password——显示系统变量 Shell> mysqladmin extended-status -u username -p password——显示状态信息

   Explain(执行计划)

   是什么：使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

   能干吗：

   • 表的读取顺序

   • 数据读取操作的操作类型

   • 哪些索引可以使用

   • 哪些索引被实际使用

   • 表之间的引用

   • 每张表有多少行被优化器查询

   怎么玩：

   • Explain + SQL语句

   • 执行计划包含的信息（如果有分区表的话还会有partitions）

   expalin

   各字段解释

   • id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）

     • id相同，执行顺序从上往下

     • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

     • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

   • select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

     • SIMPLE ：简单的select查询，查询中不包含子查询或UNION

     • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY

     • SUBQUERY：在select或where列表中包含了子查询

     • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里

     • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED

     • UNION RESULT：从UNION表获取结果的select

   • table（显示这一行的数据是关于哪张表的）

   • type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

     tip: 一般来说，得保证查询至少达到range级别，最好到达ref

     • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现

     • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量

     • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

     • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体

     • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引

     • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

     • ALL：Full Table Scan，将遍历全表找到匹配的行

   • possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

   • key

     • 实际使用的索引，如果为NULL，则没有使用索引

     • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

   explain-key

   • key_len

     • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

     • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

   • ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

   • rows（根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

   • Extra（包含不适合在其他列中显示但十分重要的额外信息）

     1. using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中

     2. Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

     3. using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

     4. using where：使用了where过滤

     5. using join buffer：使用了连接缓存

     6. impossible where：where子句的值总是false，不能用来获取任何元祖

     7. select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

     8. distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

   case:

   explain-demo

   1. 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】

   2. 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】

   3. 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

   4. 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

   5. 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

   慢查询日志

   MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

   • long_query_time 的默认值为10，意思是运行10秒以上的语句

   • 默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启

   查看开启状态

   SHOW VARIABLES LIKE '%slow_query_log%'
   

   开启慢查询日志

   • 临时配置：

   mysql> set global slow_query_log='ON';
   mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
   mysql> set global long_query_time=2;
   

   也可set文件位置，系统会默认给一个缺省文件host_name-slow.log

   使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。

   • 永久配置

     修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入两个配置参数

   [mysqld]
   slow_query_log = ON
   slow_query_log_file = /var/lib/mysql/hostname-slow.log
   long_query_time = 3
   

   注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time=2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。

   可以用 select sleep(4) 验证是否成功开启。

   在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具mysqldumpslow。

   通过 mysqldumpslow --help 查看操作帮助信息

   • 得到返回记录集最多的10个SQL

     mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log

   • 得到访问次数最多的10个SQL

     mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log

   • 得到按照时间排序的前10条里面含有左连接的查询语句

     mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log

   • 也可以和管道配合使用

     mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

   也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志

   Show Profile 分析查询

   通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的具体执行情况，索引使用等，还可以结合Show Profile命令查看执行状态。

   • Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

   • 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

   • 分析步骤

     mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration   | Query                           | +----------+------------+---------------------------------+ |        1 | 0.00385450 | show variables like "profiling" | |        2 | 0.00170050 | show variables like "profiling" | |        3 | 0.00038025 | select * from t_base_user       | +----------+------------+---------------------------------+

     • converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

     • create tmp table 创建临时表，这个要注意

     • Copying to tmp table on disk   把内存临时表复制到磁盘

     • locked

     1. 诊断SQL，show profile cpu,block io for query  id(上一步前面的问题SQL数字号码)

     2. 日常开发需要注意的结论

     1. 是否支持，看看当前的mysql版本是否支持

        mysql>Show  variables like 'profiling';  --默认是关闭，使用前需要开启
        

     2. 开启功能，默认是关闭，使用前需要开启

        mysql>set profiling=1;
        

     3. 运行SQL

     4. 查看结果

   ❝

   查询中哪些情况不会使用索引？

   性能优化

   索引优化

   1. 全值匹配我最爱

   2. 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)

   3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

   4. 存储引擎不能使用索引中范围条件右边的列

   5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select

   6. is null ,is not null 也无法使用索引

   7. like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，

   8. 字符串不加单引号索引失效

   9. 少用or，用它来连接时会索引失效

   10. =，BETWEEN，IN 可用到索引，，not in ，!= 则不行，会导致全表扫描

   一般性建议

   • 对于单键索引，尽量选择针对当前query过滤性更好的索引

   • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

   • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

   • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

   • 少用Hint强制索引

   查询优化

   永远小标驱动大表（小的数据集驱动大的数据集）

   slect * from A where id in (select id from B)`等价于
   #等价于
   select id from B
   select * from A where A.id=B.id
   

   当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

   select * from A where exists (select 1 from B where B.id=A.id)
   #等价于
   select * from A
   select * from B where B.id = A.id`
   

   当 A 表的数据集小于B表的数据集时，用 exists优于用 in

   注意：A表与B表的ID字段应建立索引。

   order by关键字优化

   • order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

   • MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；

   • ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列

   • 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀

   • 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序

     • 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据

     • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序

   • 优化策略

     • 增大sort_buffer_size参数的设置

     • 增大max_lencth_for_sort_data参数的设置

   GROUP BY关键字优化

   • group by实质是先排序后进行分组，遵照索引建的最佳左前缀

   • 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置

   • where高于having，能写在where限定的条件就不要去having限定了

   数据类型优化

   MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

   • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

     简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。

   • 尽量避免NULL：通常情况下最好指定列为NOT NULL

   九、分区、分表、分库

   MySQL分区

   一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

   当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

   能干嘛

   • 逻辑数据分割

   • 提高单一的写和读应用速度

   • 提高分区范围读查询的速度

   • 分割数据能够有多个不同的物理文件路径

   • 高效的保存历史数据

   怎么玩

   首先查看当前数据库是否支持分区

   • MySQL5.6以及之前版本：

     SHOW VARIABLES LIKE '%partition%';
     

   • MySQL5.6：

     show plugins;
     

   分区类型及操作

   • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。

     按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。

     range 来分，好处在于说，扩容的时候很简单。

   • LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。

   • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

     hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

   • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

   看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

   • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

   • 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难

   • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

   ❝

   随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

   这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

   区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

   ❝

   说说分库与分表的设计

   MySQL分表

   分表有两种分割方式，一种垂直拆分，另一种水平拆分。

   • 垂直拆分

     垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

   • 水平拆分(数据分片)

     单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

     水平分割的几种方法：

     • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。

     • 还可根据时间放入不同的表，比如：article_201601，article_201602。

     • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。

     • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

   MySQL分库

   ❝

   为什么要分库?

   数据库集群环境后都是多台 slave，基本满足了读取操作;  但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

   ❝

   分库是什么？

   一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

   优点：

   • 减少增量数据写入时的锁对查询的影响

   • 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

   但是它无法解决单表数据量太大的问题

   分库分表后的难题

   分布式事务的问题，数据的完整性和一致性问题。

   数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

   ❝

   配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道

   十、主从复制

   复制的基本原理

   • slave 会从 master 读取 binlog 来进行数据同步

   • 三个步骤

     img

     1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

     2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;

     3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

   复制的基本原则

   • 每个 slave只有一个 master

   • 每个 salve只能有一个唯一的服务器 ID

   • 每个master可以有多个salve

   复制的最大问题

   • 延时

   十一、其他问题

   说一说三个范式

   • 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

   • 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

   • 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y

   百万级别或以上的数据如何删除

   关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

   1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

   2. 然后删除其中无用数据（此过程需要不到两分钟）

   3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

   4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

   感谢阅读，更多的java课程学习路线,笔记,面试等架构资料，需要的同学可以私信我（资料）即可免费获取！