MySQL面试题

mysql数据库面试题大全！

原理篇

1：什么是数据库事务

参考答案：

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。

2：事务的特性及其含义

参考答案：

事务有四个特性，分别是原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)，简称ACID。

原子性：事务必须是原子工作单元；对于数据修改，要么全都执行，要么全都不执行。

一致性：事务在完成时，必须使所有的数据都保持一致状态。

隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。

持久性：事务完成之后，它对于系统的影响是永久性的。

3：关系型数据库和非关系型数据库的区别

参考答案：

关系型数据库采用了关系模型来组织数据，关系模型是一个二位表格，一般采用行存储，最大的特点就是事务的一致性；

非关系型数据库使用键值对存储数据，多用于分布式的数据存储，一般不支持ACID特性，严格上不是一种数据库，应该是一种数据结构化存储方法的集合。

4：什么是视图

参考答案：

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

1、视图能够简化用户的操作；

2、视图使用户能以多钟角度看待同一数据；

3、视图对重构数据库提供了一定程度的逻辑独立性；

4、视图能够对机密数据提供安全保护；

5、适当的利用视图可以更清晰的表达查询。

5：什么是存储过程

参考答案：

存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

6：简述数据库三范式

参考答案：

第一范式：字段是最小的的单元不可再分；

第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键；

第三范式：满足第二范式，非主键外的所有字段必须互不依赖。

7：Mysql两种引擎MyISAM和InnoDB的特点

参考答案：

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁；

不支持事务；

不支持外键；

不支持崩溃后的安全恢复；

在表有读取查询的同时，支持往表中插入新纪录；

支持BLOB和TEXT的前500个字符索引，支持全文索引；

支持延迟更新索引，极大提升写入性能；

对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用；

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

支持行锁，采用MVCC来支持高并发；

支持事务；

支持外键；

支持崩溃后的安全恢复；

不支持全文索引；

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

8：什么是索引

参考答案：

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

9：什么是主键和外键，主键与唯一键的区别

参考答案：

主键是能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键。

外键是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。

主键不能重复，不能为空，唯一键不能重复，可以为空。

建立主键的目的是让外键来引用。

一个表最多只有一个主键，但可以有很多唯一键。

语法篇

1：SQL语言包括哪几部分，每部分都有哪些操作关键字

参考答案：

SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL）四个部分。

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等

数据操纵：Select ,insert,update,delete,

数据控制：grant,revoke

数据查询：select

2：什么样的对象可以使用 CREATE 语句创建 ?

参考答案：

以下对象是使用 CREATE 语句创建的：

DATABASE、EVENT、FUNCTION、INDEX、PROCEDURE、TABLE、TRIGGER、USER、VIEW

3：Mysql中NULL是什么意思,它和空值有什么区别

参考答案：

NULL这个值表示UNKNOWN(未知)，它不表示“”(空字符串)。对NULL这个值的任何比较都会生产一个NULL值。不能把任何值与一个 NULL值进行比较，并在逻辑上希望获得一个答案。需要使用IS NULL或者IS NOT NULL来进行NULL判断，SQL语句函数中可以使用ifnull ()函数来进行处理。

空值(”)是不占用空间的，判断空字符用 = ” 或者 <> ” 来进行处理。

无法比较 NULL 和 0；它们是不等价的。

无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。

NULL值可以使用 <=> 符号进行比较，该符号与等号作用相似，但对NULL有意义。

进行 count ()统计某列的记录数的时候，如果采用的 NULL 值，会别系统自动忽略掉，但是空值是统计到其中。

4：IFNULL函数如何使用

参考答案：

IFNULL(expr1,expr2)

如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值。

5：什么情况下设置了索引但无法使用

参考答案：

1、以“%”开头的LIKE语句，模糊匹配

2、OR语句前后没有同时使用索引

3、数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

6：如何列出某个数据库，以及一个数据库中的所有表

参考答案：

show databases;

show tables;

7：如何获取表内所有 Field 对象的名称和类型

参考答案：

在mysql中如果想要查看表的定义的话，有以下两种方式：

1、show create table table_name;

2、desc table_name;

8：UNION 和 UNION ALL 有什么区别

参考答案：

UNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行。UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。

UNIONALL基本使用和UNION是一致的，但是UNION ALL不会消除表中的重复行。

9：连接的种类与区别

参考答案：

SQL 连接（JOIN）子句用于将数据库中两个或者两个以上表中的记录组合起来。连接通过共有值将不同表中的字段组合在一起。

SQL 中有多种不同的连接：

内连接（INNERJOIN）：当两个表中都存在匹配时，才返回行。

左连接（LEFTJOIN）：返回左表中的所有行，即使右表中没有匹配的行。

右连接（RIGHTJOIN）：返回右表中的所有行，即使左表中没有匹配的行。

全连接（FULLJOIN）：只要某一个表存在匹配，就返回行。

笛卡尔连接（CARTESIANJOIN）：返回两个或者更多的表中记录集的笛卡尔积。

10：如何做模糊查询

参考答案：

语法：SELECT 字段 FROM 表 WHERE 某字段 LIKE 条件

其中条件的匹配模式有：

1、%：表示任意0个或多个字符。可匹配任意类型和长度的字符。

2、_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

3、[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

4、[^ ]：表示不在括号所列之内的单个字符。其取值和[] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

11：如何定义 REGEXP，LIKE 和 REGEXP 操作有什么区别

REGEXP 是模式匹配，其中匹配模式在搜索值的任何位置。

语法：SELECT字段 FROM 表 WHERE 某字段 REGEXP 条件。

LIKE进行简单的模糊查询， REGEXP可以进行各种复杂匹配。

12：in与exists的区别

参考答案：

exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。IN语句对于子查询的返回字段只能由一个。

外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。

13：varchar与char的区别以及varchar(50)中的50代表的含义

参考答案：

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足。在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节。

varchar(50)表示最多占用50个字符。

14：int（5）中5的含义

参考答案：

int(M) M表示的不是数据的最大长度，只是数据宽度，并不影响存储多少位长度的数据。

int类型数据的字节大小是固定的4个字节。int(5)和int(11)区别在于，显示的数据位数一个是5位一个是11位，在开启zerofill(填充零）情况下,若int(5)存储的数字长度是小于5的则会在不足位数的前面补充0,但是如果int(5)中存储的数字长度大于5位的话，则按照实际存储的显示(数据大小在int类型的4个字节范围内即可）。

15：BLOB 和 TEXT 有什么区别

参考答案：

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。

BLOB有4种类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。TEXT也有4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些类型同BLOB类型一样，有相同的最大长度和存储需求。

BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。

BLOB和TEXT列不能有默认值。

16：drop,delete与truncate的区别

参考答案：

delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reusestorage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey）约束引用的表，不能使用truncate table，而应使用不带where 子句的delete语句。

4、truncate table不能用于参与了索引视图的表。

drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

安全与优化篇

1：MySQL 服务器默认端口是什么

参考答案：

MySQL 服务器的默认端口是 3306。

2：mysql怎样进行用户权限管理

参考答案：

可以通过如下方面进行用户权限管理：

1、限制用户访问哪些库、哪些表

2、限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作

3、限制用户登录的IP或域名

4、限制用户自己的权限是否可以授权给别的用户

3:MySQL有哪些日志，分别是什么用处

参考答案：

mysql日志一般分为5种

1、错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)

2、二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用）

3、查询日志：-log （记录建立的客户端连接和执行的语句）

4、慢查询日志:-log-slow-queries （记录所有执行超过long_query_time秒的所有查询）

5、更新日志: -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL5.1中不再使用）

4：sql注入的主要特点与危害

参考答案：

Sql注入变种极多，攻击简单，危害极大，主要有：

1、未经授权操作数据库的数据

2、恶意纂改网页

3、私自添加系统账号或者是数据库使用者账号

4、网页挂木马

5：如何提高insert性能

参考答案：

1、合并多条 insert 为一条，即： insert into t values(a,b,c), (d,e,f) ,,,

2、修改参数 bulk_insert_buffer_size，调大批量插入的缓存；

3、设置 innodb_flush_log_at_trx_commit = 0 ，相对于innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度；

4、手动使用事务

因为mysql默认是autocommit的，这样每插入一条数据，都会进行一次commit；所以，为了减少创建事务的消耗，我们可用手工使用事务，即STARTTRANSACTION;insert 。。,insert。。commit；即执行多个insert后再一起提交；一般1000条insert 提交一次。

6、数据库优化的思路

参考答案：

可以从以下方面进行展开

1、SQL语句优化

2、索引优化

3、数据库结构优化

范式优化：比如消除冗余

反范式优化：比如适当加冗余等（减少join）

拆分表：垂直拆分和水平拆分

4、服务器硬件优化

7、列举一些sql语句优化方法(sql语句优化的30种方法)

转载于：https://www.cnblogs.com/Little-Li/p/8031295.html

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30.尽量避免大事务操作，提高系统并发能力。

8：如何优化 DISTINCT

sql distinct详解以及优化

一.distinct简介

distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决，而这样对于一个数据量非常大的站来说，无疑是会直接影响到效率的。

下面先来看看例子：

table表

字段1 字段2

id name

1 a

2 b

3 c

4 c

5 b

库结构大概这样，这只是一个简单的例子，实际情况会复杂得多。

比如我想用一条语句查询得到name不重复的所有数据，那就必须使用distinct去掉多余的重复记录。

select distinct name from table

得到的结果是:

----------

name

好像达到效果了，可是，我想要得到的是id值呢？改一下查询语句吧:

select distinct name, id from table

结果会是:

----------

id name

1 a

2 b

3 c

4 c

5 b

distinct怎么没起作用？作用是起了的，不过他同时作用了两个字段，也就是必须得id与name都相同的才会被排除。。。。。。。

我们再改改查询语句:

select id, distinct name from table

很遗憾，除了错误信息你什么也得不到，distinct必须放在开头。难到不能把distinct放到where条件里？能，照样报错。

下面方法可行:

select *, count(distinct name) from table group by name

结果:

id name count(distinct name)

1 a 1

2 b 1

3 c 1

最后一项是多余的，不用管就行了，目的达到。。。。。

group by 必须放在 order by 和 limit之前，不然会报错

==============以上是关于Oracle的distinct的一种用法==============

用distinct关键字只能过滤查询字段中所有记录相同的（记录集相同），而如果要指定一个字段却没有效果，另外distinct关键字会排序，效率很低。

select distinct name from t1 能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。

select distinct id,name from t1 可以取多个字段，但只能消除这2个字段值全部相同的记录

所以用distinct达不到想要的效果，用group by 可以解决这个问题。

二. distinct使用

1 Distinct 位置

单独的distinct只能放在开头，否则报错，语法错误

mysql> Select player_id,distinct(task_id) from task;

ERROR 1064 (42000): You havean error in your SQL syntax; check the manual that

corresponds to your MySQLserver version for the right syntax to use near 'disti

nct(task_id) from task' atline 1

现在把distinct放在开头

mysql> Select distinct(task_id),taskid from task;

查询成功

与其他函数使用时候，没有位置限制如下

Select player_id,count(distinct(task_id))from task;

这种情况下是正确的，可以使用。

2 Distinct用法

a.在count计算不重复的记录的时候能用到

比如SELECT COUNT( DISTINCT player_id ) FROM task;

就是计算talbebname表中id不同的记录有多少条

b,在需要返回记录不同的id的具体值的时候可以用

比如SELECT DISTINCT player_id FROM task;

返回talbebname表中不同的id的具体的值

c.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义

比如SELECT DISTINCT player_id, task_id FROM task;

实际上返回的是player_id与task_id同时不相同的结果,也就是DISTINCT同时作用了两个字段，必须得player_id与task_id都相同的才被排除了,与我们期望的结果不一样,我们期望的是player_id不同被过滤

在这种情况下，distinct同时作用了两个字段，player_id,task_id

d.这时候可以考虑使用group_concat函数来进行排除,不过这个mysql函数是在mysql4.1以上才支持的

e. 其实还有另外一种解决方式,就是使用

SELECT player_id, task_id, count(DISTINCT player_id) FROM task.

虽然这样的返回结果多了一列无用的count数据(有时也许就需要这个数据)

f 同时我们还可以利用下面的方式解决b遇到的歧义问题通过group by 分组

select player_id,task_id from task group by player_id

9：explain出来的各种item的意义

MySQL Explain详解

原文链接：http://www.cnblogs.com/lurenjiashuo/p/mysql-explain-detail.html

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plan）

mysql> explain select * from servers;

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

1 row in set (0.03 sec)

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

一、 id

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时，执行顺序由上至下

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

3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

二、select_type

示查询中每个select子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

三、table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

| 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

四、type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

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

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

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

不损失精确性的情况下，长度越短越好

八、ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

参考资料：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

http://www.cnblogs.com/gomysql/p/3720123.html

10：profile的意义以及使用场景

mysql使用profile分析语句性能消耗

--查看profile是否开启

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| profiling | OFF | --开启SQL语句剖析功能

| profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling

+------------------------+-------+

2 rows in set (0.00 sec)

--基于会话级别开启

mysql> set profiling = 1; --关闭则用set profiling = off

Query OK, 0 rows affected (0.00 sec)

mysql> select distinct d.account,a.server_id from tab_appserver_user a

-> inner join tab_department_parent b on a.key_id = b.parent_id

-> inner join tab_department_member c on b.department_id = c.department_id and c.state=1

-> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1

-> where a.type=1

-> union

-> select distinct b.account,a.server_id from tab_appserver_user a

-> inner join tab_user_info b on a.key_id = b.user_id and b.state=1

-> where a.type=0;

--显示缓存的profile

mysql> show profiles;

+----------+------------+-------------------------------------------------------+

| Query_ID | Duration | Query

1 | 0.86754250 | select distinct d.account,a.server_id from tab_appserver_user a

+----------+------------+-------------------------------------------------------+

4 rows in set (0.00 sec)

从上面可以看到时间的消耗为0.8秒

以下是具体的消耗，进行详细的列出

mysql> show profile for query 1; --1是query_id

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000018 |

| checking query cache for query | 0.000099 |

| Opening tables | 0.000963 |

| System lock | 0.000015 |

| Table lock | 0.000169 |

| optimizing | 0.000020 |

| statistics | 0.000027 |

| preparing | 0.000018 |

| Creating tmp table | 0.000055 |

| executing | 0.000003 |

| Copying to tmp table | 0.704845 | --最主要的消耗点

| Sending data | 0.130039 |

| optimizing | 0.000029 |

| statistics | 0.000029 |

| preparing | 0.000020 |

| Creating tmp table | 0.000142 |

| executing | 0.000003 |

| Copying to tmp table | 0.000086 |

| Sending data | 0.000067 |

| optimizing | 0.000004 |

| statistics | 0.000005 |

| preparing | 0.000005 |

| executing | 0.000002 |

| Sending data | 0.023963 |

| removing tmp table | 0.003420 |

| Sending data | 0.000005 |

| removing tmp table | 0.003308 |

| Sending data | 0.000006 |

| removing tmp table | 0.000007 |

| Sending data | 0.000009 |

| query end | 0.000003 |

| freeing items | 0.000144 |

| storing result in query cache | 0.000011 |

| logging slow query | 0.000003 |

| cleaning up | 0.000006 |

+--------------------------------+----------+

35 rows in set (0.00 sec)

--查看cpu的消耗情况

mysql> show profile cpu for query 1;

+--------------------------------+----------+----------+------------+

+--------------------------------+----------+----------+------------+

+--------------------------------+----------+----------+------------+

35 rows in set (0.00 sec)

--查看内存消耗

mysql> show profile memory for query 1;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000018 |

| checking query cache for query | 0.000099 |

| Opening tables | 0.000963 |

| System lock | 0.000015 |

| Table lock | 0.000169 |

| optimizing | 0.000020 |

| statistics | 0.000027 |

| preparing | 0.000018 |

| Creating tmp table | 0.000055 |

| executing | 0.000003 |

| Copying to tmp table | 0.704845 |

| Sending data | 0.130039 |

| optimizing | 0.000029 |

| statistics | 0.000029 |

| preparing | 0.000020 |

| Creating tmp table | 0.000142 |

| executing | 0.000003 |

| Copying to tmp table | 0.000086 |

| Sending data | 0.000067 |

| optimizing | 0.000004 |

| statistics | 0.000005 |

| preparing | 0.000005 |

| executing | 0.000002 |

| Sending data | 0.023963 |

| removing tmp table | 0.003420 |

| Sending data | 0.000005 |

| removing tmp table | 0.003308 |

| Sending data | 0.000006 |

| removing tmp table | 0.000007 |

| Sending data | 0.000009 |

| query end | 0.000003 |

| freeing items | 0.000144 |

| storing result in query cache | 0.000011 |

| logging slow query | 0.000003 |

| cleaning up | 0.000006 |

+--------------------------------+----------+

--查看io及cpu的消耗

mysql> show profile block io,cpu for query 1;

+--------------------------------+----------+----------+------------+--------------+---------------+

+--------------------------------+----------+----------+------------+--------------+---------------+

+--------------------------------+----------+----------+------------+--------------+---------------+

35 rows in set (0.00 sec)

--使用查询语句对消耗进行排序

mysql> SELECT STATE, SUM(DURATION) AS Total_R,ROUND( 100 * SUM(DURATION) / (SE

CT SUM(DURATION)

-> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1), 2) AS Pct_R, CO

T(*) AS Calls,SUM(DURATION) / COUNT(*) AS "R/Call"

-> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1 GROUP BY STATE O

ER BY Total_R DESC;

+--------------------------------+----------+-------+-------+--------------+

+--------------------------------+----------+-------+-------+--------------+

| Copying to tmp table | 0.704931 | 81.26 | 2 | 0.3524655000 |

| Sending data | 0.154089 | 17.76 | 6 | 0.0256815000 |

| removing tmp table | 0.006735 | 0.78 | 3 | 0.0022450000 |

| Opening tables | 0.000963 | 0.11 | 1 | 0.0009630000 |

| Creating tmp table | 0.000197 | 0.02 | 2 | 0.0000985000 |

| Table lock | 0.000169 | 0.02 | 1 | 0.0001690000 |

| freeing items | 0.000144 | 0.02 | 1 | 0.0001440000 |

| checking query cache for query | 0.000099 | 0.01 | 1 | 0.0000990000 |

| statistics | 0.000061 | 0.01 | 3 | 0.0000203333 |

| optimizing | 0.000053 | 0.01 | 3 | 0.0000176667 |

| preparing | 0.000043 | 0.00 | 3 | 0.0000143333 |

| starting | 0.000018 | 0.00 | 1 | 0.0000180000 |

| System lock | 0.000015 | 0.00 | 1 | 0.0000150000 |

| storing result in query cache | 0.000011 | 0.00 | 1 | 0.0000110000 |

| executing | 0.000008 | 0.00 | 3 | 0.0000026667 |

| cleaning up | 0.000006 | 0.00 | 1 | 0.0000060000 |

| logging slow query | 0.000003 | 0.00 | 1 | 0.0000030000 |

| query end | 0.000003 | 0.00 | 1 | 0.0000030000 |

+--------------------------------+----------+-------+-------+--------------+

18 rows in set (0.01 sec)

--最后说明：

profile是一个非常量化的子标，可以根据这些量化指标来比较各项资源的消耗，有利于我们对该语句的整体把控

11：mysql如何实现高效分页

MySQL高效分页解决方案集（转）

很久以前的一次面试中，被面试官问到这个问题，由于平时用到的分页方法不多，只从索引、分表、使用子查询精准定位偏移以外，没有使用到其它方法。

后来在看其它博客看到了一些不同的方案，也一直没有整理。今天有时间，整理出来，分享给大家。

一，最常见MYSQL最基本的分页方式：

select * from content order by id desc limit 0, 10

在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：

select * from content order by id desc limit 10000, 10

一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过2种方式：

一，子查询的分页方式来提高分页效率，飘易用的SQL语句如下：

SELECT * FROM `content` WHERE id> (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。（via）通过explain SQL语句发现：子查询使用了索引！

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where

2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index

经过飘易的实测，使用子查询的分页方式的效率比纯LIMIT提高了14-20倍！

二，JOIN分页方式

select * FROM `content` AS t1

JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2

WHERE t1.id

经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。explain SQL语句：

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY system NULL NULL NULL NULL 1

1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where

2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

三，使用MYSQL的FOUND_ROWS()函数

Mysql FOUND_ROWS() 函数结合SQL_CALC_FOUND_ROWS在SELECT中可以得到两个结果：

1. 得到Limit的内容

2. 得到去除Limit以后所有行数

SELECT语句中经常可能用LIMIT限制返回行数。有时候可能想要知道如果没有LIMIT会返回多少行，但又不想再执行一次相同语句。那么，在SELECT查询中包含SQL_CALC_FOUND_ROWS选项，然后执行FOUND_ROWS()就可以了：

select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

SELECT FOUND_ROWS();

其中SQL_CALC_FOUND_ROWS 告诉Mysql将sql所处理的行数记录下来，FOUND_ROWS() 则取到了这个纪录。虽然也是两个语句，但是只执行了一次主查询，所以效率比原来要高很多。

1. 如果在前一条语句中使用SQL_CALC_FOUND_ROWS选项，FOUND_ROWS()将返回第一条语句没有LIMIT时返回的行数。

2. 如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项，FOUND_ROWS()将返回前一条语句实际返回的行数。

如果使用 SELECT SQL_CALC_FOUND_ROWS，MySQL必须计算所有结果集的行数。尽管这样，总比再执行一次不使用LIMIT的查询要快多了吧，因为那样结果集要返回客户端滴。（另外：应该不单是没有将结果集返回的原因，还有原因可能是比如LIKE之类比较费劲的SQL不需要再去劳累一次。）

-- 注意下面语句中的条件 LIKE

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;

SELECT FOUND_ROWS();

-- 上面语句等价于下面语句，但性能方面应该提升非常非常的明显：

SELECT COUNT(*) FROM tbl_name WHERE Name LIKE '%string%' ;

SELECT * FROM tbl_name WHERE Name LIKE '%string%' id > 100 LIMIT 10;

12：如何进行分库分表

数据库分库分表思路

一. 数据切分

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分

1、垂直（纵向）切分

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

垂直切分的优点：

解决业务系统层面的耦合，业务清晰
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）

2、水平（横向）切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平切分的优点：

不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块

缺点：

跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量极大

水平切分后同一张表会出现在多个数据库/表中，每个库/表的内容不同。几种典型的数据分片规则为：

1、根据数值范围

按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1~9999的记录分到第一个库，10000~20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

这样的优点在于：

单表大小可控
天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

2、根据数值取模

一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

优点：

数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈

缺点：

后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

二. 分库分表带来的问题

分库分表能有效的环节单机和单库带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶颈，同时也带来了一些问题。下面将描述这些技术挑战以及对应的解决思路。

1、事务一致性问题

分布式事务

当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，没有简单的方案，一般可使用"XA协议"和"两阶段提交"处理。

分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

最终一致性

对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。

2、跨节点关联查询 join 问题

切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。

解决这个问题的一些方法：

1）全局表

全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

2）字段冗余

一种典型的反范式设计，利用空间换时间，为了性能而避免join查询。例如：订单表保存userId时候，也将userName冗余保存一份，这样查询订单详情时就不需要再去查询"买家user表"了。

但这种方法适用场景也有限，比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证，就像上面订单表的例子，买家修改了userName后，是否需要在历史订单中同步更新呢？这也要结合实际业务场景进行考虑。

3）数据组装

在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

4）ER分片

关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题。在1:1或1:n的情况下，通常按照主表的ID主键切分。如下图所示：

这样一来，Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了，Data Node2上也一样。

3、跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。如图所示：

上图中只是取第一页的数据，对性能影响还不是很大。但是如果取得页数很大，情况则变得复杂很多，因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体的排序，这样的操作时很耗费CPU和内存资源的，所以页数越大，系统的性能也会越差。

在使用Max、Min、Sum、Count之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。如图所示：

4、全局主键避重问题

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

1）UUID

UUID标准形式包含32个16进制数字，分为5段，形式为8-4-4-4-12的36个字符，例如：550e8400-e29b-41d4-a716-446655440000

UUID是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，由于UUID非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在InnoDB下，UUID的无序性会引起数据位置频繁变动，导致分页。

2）结合数据库维护主键ID表

在数据库中建立 sequence 表：

CREATE TABLE `sequence` ( `id` bigint(20) unsigned NOT NULL auto_increment, `stub` char(1) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `stub` (`stub`) ) ENGINE=MyISAM;

stub字段设置为唯一索引，同一stub值在sequence表中只有一条记录，可以同时为多张表生成全局ID。sequence表的内容，如下所示：

+-------------------+------+ | id | stub |+-------------------+------+ | 72157623227190423 | a |+-------------------+------+

使用 MyISAM 存储引擎而不是 InnoDB，以获取更高的性能。MyISAM使用的是表级别的锁，对表的读写是串行的，所以不用担心在并发时两次读取同一个ID值。

当需要全局唯一的64位ID时，执行：

REPLACE INTO sequence (stub) VALUES ('a'); SELECT LAST_INSERT_ID();

这两条语句是Connection级别的，select last_insert_id() 必须与 replace into 在同一数据库连接下才能得到刚刚插入的新ID。

使用replace into代替insert into好处是避免了表行数过大，不需要另外定期清理。

此方案较为简单，但缺点也明显：存在单点问题，强依赖DB，当DB异常时，整个系统都不可用。配置主从可以增加可用性，但当主库挂了，主从切换时，数据一致性在特殊情况下难以保证。另外性能瓶颈限制在单台MySQL的读写性能。

flickr团队使用的一种主键生成策略，与上面的sequence表方案类似，但更好的解决了单点和性能瓶颈的问题。

这一方案的整体思想是：建立2个以上的全局ID生成的服务器，每个服务器上只部署一个数据库，每个库有一张sequence表用于记录当前全局ID。表中ID增长的步长是库的数量，起始值依次错开，这样能将ID的生成散列到各个数据库上。如下图所示：

由两个数据库服务器生成ID，设置不同的auto_increment值。第一台sequence的起始值为1，每次步长增长2，另一台的sequence起始值为2，每次步长增长也是2。结果第一台生成的ID都是奇数（1, 3, 5, 7 ...），第二台生成的ID都是偶数（2, 4, 6, 8 ...）。

这种方案将生成ID的压力均匀分布在两台机器上。同时提供了系统容错，第一台出现了错误，可以自动切换到第二台机器上获取ID。但有以下几个缺点：系统添加机器，水平扩展时较复杂；每次获取ID都要读写一次DB，DB的压力还是很大，只能靠堆机器来提升性能。

可以基于flickr的方案继续优化，使用批量的方式降低数据库的写压力，每次获取一段区间的ID号段，用完之后再去数据库获取，可以大大减轻数据库的压力。如下图所示：

还是使用两台DB保证可用性，数据库中只存储当前的最大ID。ID生成服务每次批量拉取6个ID，先将max_id修改为5，当应用访问ID生成服务时，就不需要访问数据库，从号段缓存中依次派发0~5的ID。当这些ID发完后，再将max_id修改为11，下次就能派发6~11的ID。于是，数据库的压力降低为原来的1/6。

3）Snowflake分布式自增ID算法

Twitter的snowflake算法解决了分布式系统生成全局ID的需求，生成64位的Long型数字，组成部分：

第一位未使用
接下来41位是毫秒级时间，41位的长度可以表示69年的时间
5位datacenterId，5位workerId。10位的长度最多支持部署1024个节点
最后12位是毫秒内的计数，12位的计数顺序号支持每个节点每毫秒产生4096个ID序列

这样的好处是：毫秒数在高位，生成的ID整体上按时间趋势递增；不依赖第三方系统，稳定性和效率较高，理论上QPS约为409.6w/s（1000*2^12），并且整个分布式系统内不会产生ID碰撞；可根据自身业务灵活分配bit位。

不足就在于：强依赖机器时钟，如果时钟回拨，则可能导致生成ID重复。

综上

结合数据库和snowflake的唯一ID方案，可以参考业界较为成熟的解法：Leaf——美团点评分布式ID生成系统，并考虑到了高可用、容灾、分布式下时钟等问题。

5、数据迁移、扩容问题

当业务高速发展，面临性能和存储的瓶颈时，才会考虑分片设计，此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据，然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和QPS，以及业务发展的速度，进行容量规划，推算出大概需要多少分片（一般建议单个分片上的单表数据量不超过1000W）

如果采用数值范围分片，只需要添加节点就可以进行扩容了，不需要对分片数据迁移。如果采用的是数值取模分片，则考虑后期的扩容问题就相对比较麻烦。

三. 什么时候考虑切分

下面讲述一下什么时候需要考虑做数据切分。

1、能不切分尽量不要切分

并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。

不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

2、数据量过大，正常运维影响业务访问

这里说的运维，指：

1）对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的

2）对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。

3）大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力

3、随着业务发展，需要对某些字段垂直拆分

举个例子，假如项目一开始设计的用户表如下：

id bigint #用户的ID name varchar #用户的名字 last_login_time datetime #最近登录时间 personal_info text #私人信息 ..... #其他信息字段

在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从10w激增到10亿，用户非常的活跃，每次登录会更新 last_login_name 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。

personal_info 属性是更新和查询频率较低的，并且text字段占据了太多的空间。这时候，就要对此垂直拆分出 user_ext 表了。

4、数据量快速增长

随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量

5、安全性和可用性

鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到100%的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。

四. 案例分析

1、用户中心业务场景

用户中心是一个非常常见的业务，主要提供用户注册、登录、查询/修改等功能，其核心表为：

User(uid, login_name, passwd, sex, age, nickname) uid为用户ID, 主键 login_name, passwd, sex, age, nickname, 用户属性

任何脱离业务的架构设计都是耍流氓，在进行分库分表前，需要对业务场景需求进行梳理：

用户侧：前台访问，访问量较大，需要保证高可用和高一致性。主要有两类需求：

用户登录：通过login_name/phone/email查询用户信息，1%请求属于这种类型
用户信息查询：登录之后，通过uid来查询用户信息，99%请求属这种类型

运营侧：后台访问，支持运营需求，按照年龄、性别、登陆时间、注册时间等进行分页的查询。是内部系统，访问量较低，对可用性、一致性的要求不高。

2、水平切分方法

当数据量越来越大时，需要对数据库进行水平切分，上文描述的切分方法有"根据数值范围"和"根据数值取模"。

"根据数值范围"：以主键uid为划分依据，按uid的范围将数据水平切分到多个数据库上。例如：user-db1存储uid范围为0~1000w的数据，user-db2存储uid范围为1000w~2000wuid数据。

优点是：扩容简单，如果容量不够，只要增加新db即可。
不足是：请求量不均匀，一般新注册的用户活跃度会比较高，所以新的user-db2会比user-db1负载高，导致服务器利用率不平衡

"根据数值取模"：也是以主键uid为划分依据，按uid取模的值将数据水平切分到多个数据库上。例如：user-db1存储uid取模得1的数据，user-db2存储uid取模得0的uid数据。

优点是：数据量和请求量分布均均匀
不足是：扩容麻烦，当容量不够时，新增加db，需要rehash。需要考虑对数据进行平滑的迁移。

3、非uid的查询方法

水平切分后，对于按uid查询的需求能很好的满足，可以直接路由到具体数据库。而按非uid的查询，例如login_name，就不知道具体该访问哪个库了，此时需要遍历所有库，性能会降低很多。

对于用户侧，可以采用"建立非uid属性到uid的映射关系"的方案；对于运营侧，可以采用"前台与后台分离"的方案。

3.1、建立非uid属性到uid的映射关系

1）映射关系

例如：login_name不能直接定位到数据库，可以建立login_name→uid的映射关系，用索引表或缓存来存储。当访问login_name时，先通过映射表查询出login_name对应的uid，再通过uid定位到具体的库。

映射表只有两列，可以承载很多数据，当数据量过大时，也可以对映射表再做水平切分。这类kv格式的索引结构，可以很好的使用cache来优化查询性能，而且映射关系不会频繁变更，缓存命中率会很高。

2）基因法

分库基因：假如通过uid分库，分为8个库，采用uid%8的方式进行路由，此时是由uid的最后3bit来决定这行User数据具体落到哪个库上，那么这3bit可以看为分库基因。

上面的映射关系的方法需要额外存储映射表，按非uid字段查询时，还需要多一次数据库或cache的访问。如果想要消除多余的存储和查询，可以通过f函数取login_name的基因作为uid的分库基因。生成uid时，参考上文所述的分布式唯一ID生成方案，再加上最后3位bit值=f(login_name)。当查询login_name时，只需计算f(login_name)%8的值，就可以定位到具体的库。不过这样需要提前做好容量规划，预估未来几年的数据量需要分多少库，要预留一定bit的分库基因。

3.2、前台与后台分离

对于用户侧，主要需求是以单行查询为主，需要建立login_name/phone/email到uid的映射关系，可以解决这些字段的查询问题。

而对于运营侧，很多批量分页且条件多样的查询，这类查询计算量大，返回数据量大，对数据库的性能消耗较高。此时，如果和用户侧公用同一批服务或数据库，可能因为后台的少量请求，占用大量数据库资源，而导致用户侧访问性能降低或超时。

这类业务最好采用"前台与后台分离"的方案，运营侧后台业务抽取独立的service和db，解决和前台业务系统的耦合。由于运营侧对可用性、一致性的要求不高，可以不访问实时库，而是通过binlog异步同步数据到运营库进行访问。在数据量很大的情况下，还可以使用ES搜索引擎或Hive来满足后台复杂的查询方式。

五. 支持分库分表中间件

站在巨人的肩膀上能省力很多，目前分库分表已经有一些较为成熟的开源解决方案：

六. 参考

数据库分布式架构扫盲——分库分表（及银行核心系统适用性思考）

数据库水平切分架构实践-【架构师之路】公众号

13：新创建的数据库，需要调整哪些参数

MySQL数据库建表、优化、算法、分区分库分表总结

11.11 智慧上云

云服务器企业新用户优先购，享双11同等价格

在这篇文章中：

建表

一、主键设置
二、数据库表创建注意事项
三、字段设置

优化

一、为什么使用数据索引能提高效率？
二、关于 MySQL 联合索引
三、关于MVVC
四、行级锁定的优缺点
五、MySQL优化
六、key和index的区别

算法

一、B+树索引和哈希索引的区别？
二、哈希索引的优势：
三、B 树和 B+ 树的区别？
四、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？
五、四种隔离级别
六、Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

分区

建表

一、主键设置

主键为什么不推荐有业务含义?

1、因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。

2、带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片

使用自增主键有什么好处

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。所以最好一定要设一个主键。

2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放

因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

3、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

4、如果使用非自增主键（如UUID等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销

同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

自增主键用完了怎么办

无符号整型存储范围为0～4294967295，约43亿。一旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常。此时将Int类型改为BigInt类型，就不用考虑达到最大值这个问题。每秒十亿条数据，跑100年，都还没有达到BigInt的上限。

如果已经上线，如何在线更改表结构

1、使用mysql5.6+提供的在线修改功能

可以支持在线修改表结构的pt-osc/gh-ost第三方工具，防止表阻塞。执行ALTER语句

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

2、改从库表结构，然后主从切换

唯一需要注意的是，主从切换过程中可能会有数据丢失的情况！

3、使用全局唯一的ID号生成策略来支持分库分表

自增主键id的数据范围为0～2147483648，也就是单表21亿条数据，一般达不到最大值，我们就分库分表了

二、数据库表创建注意事项

1、字段名及字段配制合理性

剔除关系不密切的字段；
字段命名要有规则及相对应的含义（不要一部分英文，一部分拼音，还有类似a.b.c这样不明含义的字段）；
字段命名尽量不要使用缩写（大多数缩写都不能明确字段含义）；
字段不要大小写混用（想要具有可读性，多个英文单词可使用下划线形式连接）；
字段名不要使用保留字或者关键字；
保持字段名和类型的一致性；
慎重选择数字类型；
给文本字段留足余量；

2、系统特殊字段处理及建成后建议

添加删除标记（例如操作人、删除时间）；
建立版本机制；

3、表结构合理性配置

多型字段的处理，就是表中是否存在字段能够分解成更小独立的几部分（例如：人可以分为男人和女人）；
多值字段的处理，可以将表分为三张表，这样使得检索和排序更加有调理，且保证数据的完整性！

4、其它建议

对于大数据字段，独立表进行存储，以便影响性能（例如：简介字段）；
使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的；
给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响；
避免表字段运行为null，建议设置默认值（例如：int类型设置默认值为0）在索引查询上，效率立显；
建立索引，最好建立在唯一和非空的字段上，建立太多的索引对后期插入、更新都存在一定的影响（考虑实际情况来创建）；

三、字段设置

1、表示枚举的字段为什么不用enum类型？一般用tinyint类型，因为： (1)ENUM类型的ORDER BY操作效率低，需要额外操作 (2)如果枚举值是数值，有陷阱举个例子，表结构如下

CREATE TABLE test (foobar ENUM('0', '1', '2'));

此时，你执行语句

mysql> INSERT INTO test VALUES (1);

查询出的结果为foobar：0

就产生了一个坑爹的结果。插入语句应该像下面这么写，插入的才是1

mysql> INSERT INTO test VALUES (`1`);

2、货币字段用什么类型?回答:如果货币单位是分，可以用Int类型。如果坚持用元，用Decimal。千万不要答float和double，因为float和double是以二进制存储的，所以有一定的误差。打个比方，你建一个列如下

CREATE TABLE `t` (`price` float(10,2) DEFAULT NULL,) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后insert给price列一个数据为1234567.23，你会发现显示出来的数据变为1234567.25，精度失准！

3、时间字段用什么类型? (1)varchar，如果用varchar类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为2013111的数据，请问这是代表2013年1月11日，还是2013年11月1日？其次，做时间比较运算，你需要用STR_TO_DATE等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，是个坑！

(2)timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间，是无法用timestamp类型存储的。但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区

SET TIME_ZONE = "america/new_york";

你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

(3)datetime，datetime储存占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

(4)bigint，也是8个字节，自己维护一个时间戳，表示范围比timestamp大多了，就是要自己维护，不大方便。

4、为什么不直接存储图片、音频、视频等大容量内容? 在实际应用中，都是用HDFS来存储文件。然后mysql中，只存文件的存放路径。mysql中有两个字段类型被用来设计存放大容量文件，也就是text和blob类型。但是在生产中，基本不用这两个类型！主要原因有如下两点

(1)Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
(2)binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！

5、字段为什么要定义为NOT NULL? (1)索引性能不好

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。 —— 出自《高性能mysql第二版》

(2)查询会出现一些不可预料的结果这里举一个例子，大家就懂了。假设，表结构如下

create table table_2 (`id` INT (11) NOT NULL, name varchar(20) NOT NULL )

表数据是这样的

id	name
1	张三
3	null
5	李四
7	null

你执行语句

select count(name) from table_2;

你会发现结果为2，但是实际上是有四条数据的！类似的查询问题，其实有很多，不一一列举。记住，因为null列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查Bug.

优化

一、为什么使用数据索引能提高效率？

数据索引的存储是有序的
在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

二、关于 MySQL 联合索引

1、联合索引是两个或更多个列上的索引。

对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。

如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

什么情况下应不建或少建索引？

1、表记录太少

2、经常插入、删除、修改的表

3、数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

4、经常和主字段一块查询但主字段索引值比较多的表字段

三、关于MVVC

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

LBCC：Lock-Based Concurrency Control，基于锁的并发控制
MVCC：Multi-Version Concurrency Control 基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

在MVCC并发控制中，读操作可以分成两类：

快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）
当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录

四、行级锁定的优缺点

优点：

1、当在许多线程中访问不同的行时只存在少量锁定冲突。

2、回滚时只有少量的更改

3、可以长时间锁定单一的行。

缺点：

比页级或表级锁定占用更多的内存。
当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

五、MySQL优化

开启查询缓存，优化查询
explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的
当只要一行数据时使用limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据
为搜索字段建索引
使用 ENUM 而不是 VARCHAR。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是VARCHAR
Prepared StatementsPrepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。 Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
垂直分表
选择正确的存储引擎

六、key和index的区别

key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等
index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；

算法

一、B+树索引和哈希索引的区别？

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的，如下图：

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的

如下图所示：

二、哈希索引的优势：

等值查询，哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

哈希索引不适用的场景：

不支持范围查询
不支持索引完成排序
不支持联合索引的最左前缀匹配规则

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：

# 仅等值查询

select id, name from table where name='李明';

而常用的 InnoDB 引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况。

如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引）。

通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

注意：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。

但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

三、B 树和 B+ 树的区别？

1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。

2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接

所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B 树的非终节点也包含需要查找的有效信息)

四、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

1、B+的磁盘读写代价更低。

B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小。

如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+-tree的查询效率更加稳定。

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

五、四种隔离级别

Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
Read committed (读已提交)：可避免脏读的发生。
Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

六、Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

区别：

InnoDB支持事务，MyISAM不支持对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

如何选择：

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

分区

一、什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

二、表分区与分表的区别？

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

三、表分区有什么好处？

1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

4、避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

四、分区表的限制因素

一个表最多只能有1024个分区
MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
分区表中无法使用外键约束
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

五、如何判断当前MySQL是否支持分区？

命令：show variables like '%partition%' 运行结果:

have_partintioning 的值为YES，表示支持分区。

六、MySQL支持的分区类型有哪些？

RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
LIST分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
HASH分区：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
KEY分区：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

posted @ 2019-11-08 21:06 lianbingrong 阅读(502) 评论(0) 编辑收藏举报

刷新页面返回顶部