Mysql面试常见问题总结

• MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

1. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
2. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
3. mysql库主从读写分离。
4. 找规律分表，减少单表中的数据量提高查询速度。
5. 添加缓存机制，比如memcached，apc等。
6. 不经常改动的页面，生成静态页面。
7. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

• 对于大流量的网站,您采用什么样的方法来解决各页面访问量统计问题？

1. 确认服务器是否能支撑当前访问量。
2. 优化数据库访问。
3. 禁止外部访问链接（盗链）, 比如图片盗链。
4. 控制文件下载。
5. 使用不同主机分流。
6. f. 使用浏览统计软件，了解访问量，有针对性的进行优化。

• 如何进行SQL优化？

（1）选择正确的存储引擎
      以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

     InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

（2）优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

（3）为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

(4)避免使用Select *从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

(5)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

(6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

(7)固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

 

• 如何设计一个高并发的系统

1. 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引的优化
2. 使用缓存，尽量减少数据库 IO
3. 分布式数据库、分布式缓存
4. 服务器的负载均衡

• 锁的优化策略

1. 读写分离
2. 分段加锁
3. 减少锁持有的时间
4. 多个线程尽量以相同的顺序去获取资源

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

1. 以“%”开头的LIKE语句，模糊匹配
2. OR语句前后没有同时使用索引
3. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

 

• 实践中如何优化MySQL，影响由大到小

① SQL语句及索引的优化

② 数据库表结构的优化

③ 系统配置的优化

④ 硬件的优化

• sql注入的主要特点

1. 变种极多，攻击简单，危害极大
2. sql注入的主要危害
3. 未经授权操作数据库的数据
4. 恶意纂改网页
5. 私自添加系统账号或者是数据库使用者账号
6. 网页挂木马

 

• 优化数据库的方法

1.  选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
2.  使用连接(JOIN)来代替子查询
3.  适用联合(UNION)来代替手动创建的临时表
4.  事务处理
5.  锁定表、优化事务处理
6.  适用外键，优化锁定表
7.  建立索引
8.  优化查询语句

 

• 简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

1. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
2. 普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
3. 普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
4. 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建，不可重复。
5. 索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。
6. 索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

• 了解XSS攻击吗？如何防止？

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

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

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

防止SQL注入的方式：

1. 开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置
2. 执行sql语句时使用addslashes进行sql语句转换
3. Sql语句书写尽量不要省略双引号和单引号。
4. 过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。
5. 提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。
6. Php配置文件中设置register_globals为off,关闭全局变量注册
7. 控制错误信息，不要在浏览器上输出错误信息，将错误信息写到日志文件中。

• 写出三种以上MySQL数据库存储引擎的名称（提示：不区分大小写）

MyISAM、InnoDB、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、
Archive、CSV、Blackhole、MaxDB 等等十几个引擎

• 完整性约束包括哪些？

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

分为以下四类：

1. 实体完整性：规定表的每一行在表中是惟一的实体。
2. 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
3. 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
4. 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

约束是表级的强制规定，有以下五中：not null，unique，primary key，foreign key，check 。

• 什么是视图？游标是什么？

1. 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
2. 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

• 如何通俗地理解三个范式？  

1. 第一范式：第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
2. 第二范式：第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

范式化设计优缺点:

1. 优点：可以尽量得减少数据冗余，使得更新快，体积小
2. 缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

反范式化:

1. 优点:可以减少表得关联，可以更好得进行索引优化
2. 缺点:数据冗余以及数据异常，数据得修改需要更多的成本

 

• 试述视图的优点？

1. 视图能够简化用户的操作 
2. 视图使用户能以多种角度看待同一数据；
3. 视图为数据库提供了一定程度的逻辑独立性；
4. 视图能够对机密数据提供安全保护。

• 主键、外键的区别？

定义：

1.  主键–唯一标识一条记录，不能有重复的，不允许为空
2.  外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值

作用：

1.  主键–用来保证数据完整性
2.  外键–用来和其他表建立联系用的

个数：

1.  主键–主键只能有一个
2.  外键–一个表可以有多个外键

• 你可以用什么来确保表格里的字段只接受特定范围里的值?

Check限制，它在数据库表格里被定义，用来限制输入该列的值。

触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

 

• 说说对SQL语句优化有哪些方法？

 

1. Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
2. 用EXISTS替代IN、用NOT EXISTS替代NOT IN。
3. 避免在索引列上使用计算
4. 避免在索引列上使用IS NULL和IS NOT NULL
5. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
6. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
7. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

 

• SQL语句中‘相关子查询’与‘非相关子查询’有什么区别？

子查询：嵌套在其他查询中的查询称之。子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。所有的子查询可以分为两类，即相关子查询和非相关子查询

非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

select * from sclass
where cno='101' and degree>=(select degree from sclass where sno='9501101'and cno='101')

相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

select * from stock a
where not exists(select * from sale_item b where a.prod_id=b.prod_id and a.stup_id=b.stup_id)

故非相关子查询比相关子查询效率高。

 

• char和varchar的区别？

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

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．  

1.varchar适合场景:

字符串列得最大长度比平均长度大很多 2.字符串很少被更新，容易产生存储碎片 3.使用多字节字符集存储字符串

2.char适合场景:

    存储具有近似得长度（md5值,身份证，手机号）,长度比较短小得字符串（因为varchar需要额外空间记录字符串长度），更适合经常更新得字符串，更新时不会出现页分裂得情况，避免出现存储碎片，获得更好的io性能