Mysql知识的小结03-汇总

1.数据库的三范式是什么

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束

且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

2.查看mysql数据库引擎

mysql> show engines;

3.InnoDB与MyISAM的区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，

这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

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

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但

是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，

因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的

是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一

个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

如何选择引擎？

如果没有特别的需求，使用默认的 Innodb 即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如

OA自动化办公系统。

4.MySql调优

1、选择最合适的字段属性：类型、⻓度、是否允许NULL等；尽量把字段设为not null，⼀⾯查询时对⽐是否为null；

2.要尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索引。

3.应尽量避免在 where ⼦句中对字段进⾏ null 值判断、使⽤!= 或 <> 操作符，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描

4.应尽量避免在 where ⼦句中使⽤ or 来连接条件，如果⼀个字段有索引，⼀个字段没有索引，将导致引擎放弃使⽤索引⽽进⾏

全表扫描

5.in 和 not in 也要慎⽤，否则会导致全表扫描

6.模糊查询也将导致全表扫描，若要提⾼效率，可以考虑字段建⽴前置索引或⽤全⽂检索；

9.应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。

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

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

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

13.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调⽤会引起明显的性能消耗，同时带来⼤量⽇志。

14.对于多张⼤数据量（这⾥⼏百条就算⼤了）的表JOIN，要先分⻚再JOIN，否则逻辑读会很⾼，性能很差。

15.select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是⼀定要杜绝的。

16.索引并不是越多越好，索引固然可以提⾼相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为

insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况⽽定。⼀个表的索引数最好不要超过6个，若太多则应考虑⼀些不常使⽤到的列上建的索引是否有必要。

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

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

19.尽可能的使⽤ varchar/nvarchar 代替 char/nchar ，因为⾸先变⻓字段存储空间⼩，可以节省存储空间，其次对于查询来说，在⼀个相对较⼩的字段内搜索效率显然要⾼些。

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

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

22. 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使⽤，适当地使⽤它们可以使某些例程更有效，例如，当需要重复引⽤⼤型表或常⽤表中的某个数据集时。但是，对于⼀次性事件， 最好使⽤导出表。

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

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

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

29.尽量避免⼤事务操作，提⾼系统并发能⼒。

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

5.数据库的事务

什么是事务？： 多条sql语句，要么全部成功，要么全部失败。

事务的特性：

数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durabiliy)。

原子性：组成一个事务的多个数据库操作(多个sql)是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A

转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰。

持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

6.索引问题

或者说：索引就是加快检索表中数据的方法。

mysql 有4种不同的索引：

主键索引（PRIMARY）

数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引（UNIQUE）

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引（INDEX）

可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引

可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2,

column3); 创建组合索引

全文索引（FULLTEXT）

可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引

索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时

要花费较多的时间维护索引。

索引加快数据库的检索速度

索引降低了插入、删除、修改等维护任务的速度

唯一索引可以确保每一行数据的唯一性

通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

索引需要占物理和数据空间

7.SQL优化

1、查询语句中不要使用select *

2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union

all会更好)

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

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

描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null

值，然后这样查询： select id from t where num=0

8.说说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构；drop删除表的数据也删除表的结构

速度,一般来说: drop> truncate >delete

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback

segment中,不能回滚. 操作不触发trigger

9.什么是内联接、左外联接、右外联接？

内联接（Inner Join）：匹配2张表中相关联的记录。

左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，

右表中未匹配到的字段用NULL表示。

右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，

左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右

位置关系。

10.并发事务带来哪些问题?

隔离级别 脏读 不可重复读 幻读（存在问题√ 不存在x）

READ-UNCOMMITTED √ √ √ --读未提交

READ-COMMITTED × √ √ --读已提交

REPEATABLE-READ × × √ --可重复读

SERIALIZABLE × × × --串行化

在应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一

数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并对数据进行了修改，而这种修改还没有提交到

数据库中，这时另一个事务也访问了这个数据，然后使用了这个数据。因为这个数据还没有提

交，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那

么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结

果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事

务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束

时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改

导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样

的情况，因此称为不可重复读。

幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接

着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了

一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者

删除比如多次读取一条记录发现记录增多或减少了。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级

别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READCOMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLEREAD（可重读） 并不会有任何性能损失。

11.大表如何优化？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些优化措施如下：

1. 限定数据的范围

禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以

控制在一个月的范围内；

2. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，

可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂

直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列。此外，垂直分区会让事务变得更加复杂；

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了

分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成

多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据

量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但

由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好

分库 。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决 ，跨节点Join性能较

差，逻辑复杂。推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、

部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题

的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。

Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在

谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

12.分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的

名字的标示比如文件的名字。

数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成

的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更

加复杂，可用性降低，编码更加复杂，增加了系统成本。

Twitter的snowflake算法

美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html

12mysql有哪些数据类型

1、整数类型 ，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意

义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配

合使用才有意义。

例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数

据库实际存储数据为00012。

2、实数类型，包括FLOAT、DOUBLE、DECIMAL。

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。

而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型，包括VARCHAR、CHAR、TEXT、BLOB

VARCHAR用于存储可变长字符串，它比定长类型更节省空间。

VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。

VARCHAR存储的内容超出设置的长度时，内容会被截断。

CHAR是定长的，根据定义的字符串长度分配足够的空间。

CHAR会根据需要使用空格进行填充方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4、枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。

有时可以使用ENUM代替常用的字符串类型。

ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。

ENUM在内部存储时，其实存的是整数。

尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。

排序是按照内部存储的整数

5、日期和时间类型，尽量使用timestamp，空间效率高于datetime，

用整数保存时间戳通常不方便处理。如果需要存储微秒，可以使用bigint存储。