MYSQL面试题

1.何设计一个高并发的系统

① 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引的优化

② 使用缓存，尽量减少数据库 IO

③ 分布式数据库、分布式缓存

④ 服务器的负载均衡

2.优化策略

 ① 读写分离

 ② 分段加锁

 ③ 减少锁持有的时间

 ④ 多个线程尽量以相同的顺序去获取资源

3.实践中如何优化MySQL

① SQL语句及索引的优化

② 数据库表结构的优化

③ 系统配置的优化

④ 硬件的优化

4.sql注入的主要特点、主要危害

①变种极多、攻击简单、危害极大

②未经授权操作数据库的数据 、恶意纂改网页 、私自添加系统账号或者是数据库使用者账号 、网页挂木马

5.优化数据库的方法

选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL

使用连接(JOIN)来代替子查询

适用联合(UNION)来代替手动创建的临时表

事务处理

锁定表、优化事务处理

适用外键，优化锁定表

建立索引

优化查询语句

6.了解XSS攻击吗？如何防止？

XSS是跨站脚本攻击，首先是利用跨站脚本漏洞以一个特权模式去执行攻击者构造的脚本，然后利用不安全的Activex控件执行恶意的行为。 使用htmlspecialchars()函数对提交的内容进行过滤，使字符串里面的特殊符号实体化。

7.SQL注入漏洞产生的原因？如何防止？

SQL注入产生的原因：程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

防止SQL注入的方式：

开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

执行sql语句时使用addslashes进行sql语句转换 Sql语句书写尽量不要省略双引号和单引号。 过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。

提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的

8. SQL语言包括哪几部分？每部分都有哪些操作关键字？

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

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

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

数据控制：grant,revoke

数据查询：select

9. 完整性约束包括哪些？

答：数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

分为以下四类：

1) 实体完整性：规定表的每一行在表中是惟一的实体。

2) 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

3) 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

4) 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。  

与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

10.说说对SQL语句优化有哪些方法？（选择几条）

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN

（3）避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

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

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

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

11.数据库中事务的四大特性（ACID）

事务概念： 事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

⑴ 原子性（Atomicity） 　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

⑵ 一致性（Consistency） 　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。 　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation） 　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

⑷ 持久性（Durability） 　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

12.MySQL中myisam与innodb的区别，至少5点

(1)、问5点不同；    

1>.InnoDB支持事物，而MyISAM不支持事物    

2>.InnoDB支持行级锁，而MyISAM支持表级锁    

3>.InnoDB支持MVCC, 而MyISAM不支持    

4>.InnoDB支持外键，而MyISAM不支持    

5>.InnoDB不支持全文索引，而MyISAM支持。    

InnoDB提供提交、回滚、崩溃恢复能力的事务安全（ASID）能力，实现并发控制。    

MyISAM提供较高的插入和查询记录的效率，主要用于插入和查询。    

memory用于临时存放数据，数据量不大并且不需要较高数据安全性。    

archive：如果只有插入和查询可以用，支持高并发的插入操作，但本身不是事务安全。

(2)、2者select count(*)哪个更快，为什么    

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

(3)mysql中四个存储引擎：innodb、myisam、memory、archive

13.innodb的事务与日志的实现方式

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

二进制日志：记录对数据库执行更改的所有操作。

实现方式：事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的。

14.MYSQL的三级模式

（1）模式（逻辑模式）：是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

（2）外模式（用户模式）：是数据库用户的数据视图，是局部数据的逻辑结构和特征的描述。

（3）内模式（存储模式）：一个数据库只有一个内模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

15.mysql中视图和表的区别以及联系是什么？

区别：

(1)视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。

(2)视图没有实际的物理记录，而表有。

(3)视图是窗口，表是内容。

(4)视图是逻辑概念的存在，不占用物理空间；而表占用物理空间。

(5)表可以及时对它进行修改；而视图只能用创建语句来修改。

(6)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。

(7)从安全来说，视图可以防止用户直接接触表，因而用户不知道表结构。

(8)表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

(9)视图的建立和删除只影响视图本身，不影响对应的表。

联系：      

视图是在表之上建立的虚表，它的结构（所定义的列）和内容（所有记录）都来自表，视图依据表存在而存在。一个视图可以对应多个表。视图是表的抽象和在逻辑意义上建立的新关系。      

删除视图中的数据,数据库中表的数据会一起被删除。

16.安全性操作

授权：grant 权限（列） on 表名 to 用户

所有权限：all priviliges

收回权限：revoke 权限（列） on 表名 from 用户

17.存储过程(procedure)和函数(function)区别      

本质上它们都是存储程序。函数只能通过return语句返回单个值或表对象；而存储过程不允许执行return语句，但是可以通过output参数返回多个值。函数限制比较多，不能用临时变量，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用，可以在select语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

18.触发器和约束的区别      

触发器是由服务器自动激活的，类似于约束，但是比约束更加灵活，可以实施比约束更加复杂的检查和操作，具有更强大的数据控制能力。

19.事务隔离级别

（1）Read uncommitted 未提交读（RU）        

最弱的隔离级别，事务中的修改即使没有提交，对其他事务也都是可见的。（即脏读）

（2）Read committed 提交读  不可重复读（RC）        

大多数数据库系统的默认隔离级别。解决了脏读的问题，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。一个事务两次执行同样的查询，可能会得到不一样的结果。

（3）Repeatable read 可重复读（RR）        

mysql默认隔离级别。解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。该级无法解决幻读的问题，幻读是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻读。innodb和xtradb存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

（4）Serializable 可串行化        

该级是最高的级别，通过强制事务串行执行，避免了幻读的问题，该级会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，       

20.索引

MYSQL中索引文件以B树结构存储，索引可分为单列索引和多列索引。 对于多列索引中，一个SQL语句是否用到了索引取决于其数据是否符合最左前缀原则。 MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT id FROM people WHERE firstname LIKE ‘Li%’;”这个查询将使用索引，但“SELECT id FROM people WHERE firstname LIKE ‘%ike’;”这个查询不会使用索引。

21.关系数据库的特点（记住6个点即可）

1）数据集中控制。在文件管理方法中，文件是分散的，每个用户或每种处理都有各自的文件，这些文件之间一般是没有联系的，因此，不能按照统一的方法来控制、维护和管理。而数据库则很好地克服了这一缺点，可以集中控制、维护和管理有关数据。

2）数据独立性高。数据库中的数据独立于应用程序，包括数据的物理独立性和逻辑独立性，给数据库的使用、调整、优化和进一步扩充提供了方便，提高了数据库应用系统的稳定性。

3）数据共享性好。数据库中的数据可以供多个用户使用，每个用户只与库中的一部分数据发生联系；用户数据可以重叠，用户可以同时存取数据而互不影响，大大提高了数据库的使用效率。

4）数据冗余度小。数据库中的数据不是面向应用，而是面向系统。数据统一定义、组织和存储，集中管理，避免了不必要的数据冗余，也提高了数据的一致性。 5）数据结构化，整个数据库按一定的结构形式构成，数据在记录内部和记录类型之间相互关联，用户可通过不同的路径存取数据。

6）统一的数据保护功能，在多用户共享数据资源的情况下，对用户使用数据有严格的检查，对数据库规定密码或存取权限，拒绝非法用户进入数据库，以确保数据的安全性、一致性和并发控制。

22.主键 超键 候选键 外键

1）超键(super key) ：在关系中能唯一标识元组的属性集称为关系模式的超键

2）候选键(candidate key)：不含有多余属性的超键称为候选键

3）主键(primary key)：用户选作元组标识的一个候选键程序主键

4）外键(foreign key)：如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键则该属性集是关系模式R1的外键

实例讲解 假设有如下两个表：          

学生（学号，姓名，性别，身份证号，教师编号）        

教师（教师编号，姓名，工资）

超键：由超键的定义可知，学生表中含有学号或者身份证号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（身份证号，性别）等。

候选键：候选键属于超键，它是最小的超键，就是说如果再去掉候选键中的任何一个属性它就不再是超键了。学生表中的候选键为：（学号）、（身份证号）。 主键：主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，教师表中让“教师编号”做主键。

外键：外键比较简单，学生表中的外键就是“教师编号”。外键主要是用来描述两个表的关系。 

23. drop,delete与truncate的区别

drop直接删掉表；truncate删除表中数据，再插入时自增长id又从1开始 ；delete删除表中数据，可以加where字句。

1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

3） 一般而言，drop > truncate > delete

4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

10） Truncate table 表名 速度快,而且效率高,因为:truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。 12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

24.MySql的复制原理以及流程

基本原理流程，3个线程以及之间的关联；        

1） 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；        

2） 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；        

3） 从：sql执行线程——执行relay log中的语句； 

25. MySQL binlog的几种日志录入格式以及区别

1）Statement：每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入 row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使 用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执 行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以user-defined-functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE()

(除非启动时启用了 --sysdate-is-now 选项) 同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2）Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节 而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改 都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3）Mixedlevel: 是以上两种level的混合使用        

一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区 分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变 更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

26. MySQL数据库cpu飙升到500%的话他怎么处理

1）列出所有进程：show processlist  

2）观察所有进程：多秒没有状态变化的(干掉)

3）查看超时日志或者错误日志 (做了几年开发，一般会是查询以及大批量的插入会导致cpu与i/o上涨 .... 当然不排除网络状态突然断了，导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送，当然的一次被坑经历)

27. 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录

1）如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2）如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

28. MySQL中InnoDB引擎的行锁是通过加在什么上完成

答：InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update; for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列, 如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

29. xtrabackup实现原理        

在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检 查数据文件的事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。 

30.sql语句应该考虑哪些安全性？

（1）防止sql注入，对特殊字符进行转义，过滤或者使用预编译的sql语句绑定变量。

（2）最小权限原则，特别是不要用root账户，为不同的类型的动作或者组建使用不同的账户。

（3）当sql运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。

31.MYSQL相比于其他数据库有哪些特点？

MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司，现在已经被Sun公司收购，支持FreeBSD、Linux、MAC、Windows等多种操作系统与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能稍弱一些

1、可以处理拥有上千万条记录的大型数据

2、支持常见的SQL语句规范

3、可移植行高，安装简单小巧

4、良好的运行效率，有丰富信息的网络支持

5、调试、管理，优化简单（相对其他大型数据库）

48.你们数据库是否支持emoji表情，如果不支持，如何操作？

如果是utf8字符集的话，需要升级至utf8_mb4方可支持

49.表中有大字段X（例如：text类型），且字段X不会经常更新，怎么办？

（1）您是选择拆成子表，还是继续放一起？ 放在子表中

（2）理由？ 避免大数据被频繁的从buffer重换进换出，影响其他数据的缓存