在当今的IT界,MySQL以其开源和强大的关系型数据库管理系统特性,成为全球最受欢迎的数据库之一。无论是小型个人项目还是庞大的企业级应用,MySQL都能游刃有余地应对。因此,精通MySQL技能对数据库开发人员和数据库管理员(DBA)来说至关重要。在求职面试时,面试官经常会针对MySQL提出一系列高频核心问题,以此来评价应聘者的技术实力和行业经验。本文旨在揭示这些常见的MySQL面试题,助你充分准备,以便在面试中脱颖而出,展现自己的专业实力。
1、BLOB 和TEXT 有什么区别?
BLOB
是一个二进制对象, 可以容纳可变数量的数据。
TEXT
是一个不区分大小写的
BLOB。
BLOB 和
TEXT
类型之间的唯一区别在于对
BLOB
值进行排序和比较时区分大小写, 对
TEXT
值不区分大小写。
2、MySQL_fetch_array 和MySQL_fetch_object 的区别是什么?
MySQL_fetch_array
( )
–
将结果行作为关联数组或来自数据库的常规数组返回。
MySQL_fetch_object –
从数据库返回结果行作为对象。
3、MyISAM 表格将在哪里存储,并且还提供其存储格式?
每个
MyISAM
表格以三种格式存储在磁盘上:
·“.frm”
文件存储表定义
·
数据文件具有
“.MYD”
(
MYData
)
扩展名索引文件具有
“.MYI”
(
MYIndex
) 扩展名
4、MySQL 如何优化DISTINCT?
DISTINCT
在所有列上转换为
GROUP BY
, 并与
ORDER BY
子句结合使用。
5、如何显示前 50 行?
在
MySQL
中, 使用以下代码查询显示前
50
行:
SELECT*FROM TABLE LIMIT 0,50;
6、可以使用多少列创建索引?
任何标准表最多可以创建
16
个索引列。
7、NOW()和 CURRENT_DATE()有什么区别?
NOW
() 命令用于显示当前年份, 月份, 日期, 小时, 分钟和秒。
CURRENT_DATE
() 仅显示当前 年份, 月份和日期。
8、什么是非标准字符串类型?
1
、
TINYTEXT
2
、
TEXT
3
、
MEDIUMTEXT
4
、
LONGTEXT
9、什么是通用 SQL 函数?
1
、
CONCAT(A, B) –
连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
2
、
FORMAT(X, D)-
格式化数字
X
到
D
有效数字。
3
、
CURRDATE(), CURRTIME()-
返回当前日期或时间。
4
、
NOW
()
–
将当前日期和时间作为一个值返回。
5
、
MONTH
(),
DAY
( ),
YEAR
(),
WEEK
(),
WEEKDAY
()
–
从日期值中提取给定数据。
6
、
HOUR
(),
MINUTE
(),
SECOND
()
–
从时间值中提取给定数据。
7
、
DATEDIFF
(
A
,
B
)
–
确定两个日期之间的差异, 通常用于计算年龄
8
、
SUBTIMES
(
A
,
B
)
–
确定两次之间的差异。
9
、
FROMDAYS
(
INT
)
–
将整数天数转换为日期值。
10、MySQL 支持事务吗?
在缺省模式下,
MySQL
是
autocommit
模式的,所有的数据库更新操作都会即时提交, 所以在缺省情况下, MySQL
是不支持事务的。但是如果你的 MySQL
表类型是使用
InnoDB Tables
或
BDB tables
的话, 你的
MySQL
就可以使用事 务处理,
使用
SET AUTOCOMMIT=0 就可以使
MySQL
允许在非
autocommit
模式, 在非 autocommit 模式下,你必须使用
COMMIT
来提交你的更改,或者用
ROLLBACK
来回滚你的更改。
11、MySQL 里记录货币用什么字段类型好
NUMERIC
和
DECIMAL
类型被
MySQL
实现为同样的类型, 这在
SQL92
标准允许。他们被用于保存值, 该值的准确精度是极其重要的值, 例如与金钱有关的数据。当声明一个类是这些类型之一时,精度和规模的能被(
并且通常是
)
指定。 例如:salary DECIMAL(9,2) 在这个例子中, 9(precision)
代表将被用于存储值的总的小数位数,而
2(scale)
代表将被用于存储小数 点后的位数。因此, 在这种情况下, 能被存储在 salary
列中的值的范围是从
-9999999.99
到9999999.99。
12、MySQL 有关权限的表都有哪几个?
MySQL
服务器通过权限表来控制用户对数据库的访问, 权限表存放在
MySQL
数据库里,由
MySQL_install_db
脚本初始化。这些权限表分别
user
,
db
,
table_priv
,
columns_priv
和
host
。
13、列的字符串类型可以是什么?
字符串类型是:
1
、
SET
2
、
BLOB
3
、
ENUM
4
、
CHAR
5
、
TEXT
14、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.
15、锁的优化策略
1
、读写分离
2
、分段加锁
3
、减少锁持有的时间
16、索引的底层实现原理和优化
B+
树, 经过优化的
B+
树 主要是在所有的叶子结点中增加了指向下一个叶子节点的指针, 因此 InnoDB
建议为大部分表使用默认 自增的主键作为主索引。
17、什么情况下设置了索引但无法使用
1
、以
“%”
开头的
LIKE
语句, 模糊匹配
2
、
OR
语句前后没有同时使用索引
3
、数据类型出现隐式转化( 如
varchar
不加单引号的话可能会自动转换为
int
型)
18、实践中如何优化 MySQL
最好是按照以下顺序优化:
1
、
SQL
语句及索引的优化
2
、数据库表结构的优化
3
、系统配置的优化
4
、硬件的优化
19、简单描述 MySQL 中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)
1. 索引(Index):
索引是数据库表中一种特殊的数据结构,它可以帮助快速地检索表中的数据。索引存储在表之外,但与表中的行数据关联。通过索引,数据库可以快速定位到数据所在的位置,从而加快查询速度。
2. 主键(Primary Key):
主键是表中一列或列的组合,它的值用于唯一地标识表中的每一行。一张表只能有一个主键,且主键列中的值不能为NULL。主键的选择对于数据完整性至关重要,同时它也是创建索引的首选列,因为主键的查询非常频繁。
3. 唯一索引(Unique Index):
唯一索引与主键类似,但它允许列中的值重复。也就是说,唯一索引确保了索引列的值唯一,但并不要求表中的每一行都有唯一的标识。唯一索引可以有多个,它们可以用于保证某些列的值是唯一的,而不必是整张表的唯一标识。
4. 联合索引(Composite Index):
联合索引是根据表中多个列共同构建的索引。当查询条件中包含这些列时,联合索引可以提高查询的效率。它要求所有列的组合是唯一的,但并不要求每个单独的列是唯一的。
对数据库性能的影响:
- 读性能:
- 索引可以显著提高查询速度,特别是对于大型数据集。
- 主键索引通常用于最频繁的查询,因为它们具有最高的查询性能。
- 唯一索引在确保唯一性的同时,也可以提高查询效率,尤其是在需要排除重复数据的查询中。
- 联合索引在查询条件涉及多个列时非常有用,但它的效率取决于这些列的查询频率和索引的覆盖范围。
- 写性能:
- 索引会略微降低写入性能,因为在插入、删除或更新数据时,数据库还需要维护索引。
- 主键的写入性能影响最小,因为主键列通常不包含重复值,且每个表只有一个主键。
- 唯一索引也会影响写入性能,尤其是在插入大量数据时。
- 联合索引可能会在多列更新时影响写入性能,尤其是如果索引包含多个列的话。
创建和使用索引需要权衡,过多的索引会占用额外的磁盘空间,并可能在写入操作上带来性能开销。合理地设计索引策略,为常用的查询创建索引,同时避免不必要的索引,是提高数据库性能的关键。
20、数据库中的事务是什么?
事务(
transaction
) 是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功, 则认为 事务成功, 即使只有一个操作失败, 事务也不成功。如果所有操作完成, 事务则提交, 其修改将作用 于所有其他数据库进程。如果一个操作失败, 则事务将回滚, 该事务所有操作的影响都将取消。
事务特性:
1
、
原子性
: 即不可分割性, 事务要么全部被执行, 要么就全部不被执行。
2
、
一致性或可串性
。事务的执行使得数据库从一种正确状态转换成另一种正确状
态
3
、
隔离性
。在事务正确提交之前,不允许把该事务对数据的任何改变提供给任何
其他事务,
4
、
持久性
。事务正确提交后, 其结果将永久保存在数据库中, 即使在事务提交后有了其他故障, 事 务的处理结果也会得到保存。或者这样理解:事务就是被绑定在一起作为一个逻辑工作单元的 SQL
语 句分组, 如果任何一个语句操作失败那么整个操作就被失败, 以后操作就会回滚到操作前状态, 或者 是上有个节点。为了确保要么执行, 要么不执行, 就可以使用事务。要将有组语句作为事务考虑, 就 需要通过 ACID
测试, 即原子性, 一致性, 隔离性和持久性。
21、SQL 注入漏洞产生的原因?如何防止?
SQL
注入产生的原因:
程序开发过程中不注意规范书写
sql
语句和对特殊字符进行过滤,导致客户端可以通过全局变量 POST
和
GET
提交一些
sql
语句正常执行。
防止
SQL
注入的方式:
开启配置文件中的
magic_quotes_gpc
和
magic_quotes_runtime
设置 执行 sql
语句时使用
addslashes
进行
sql
语句转换
Sql
语句书写尽量不要省略双引号和单引号。 过滤掉 sql
语句中的一些关键词:
update
、
insert
、
delete
、
select
、
*
。 提高数据库表和字段的命名技巧, 对一些重要的字段根据程序的特点命名, 取不易被猜到的。
22、为表中得字段选择合适得数据类型
字段类型优先级
:
整形
>date,time>enum,char>varchar>blob,text
优先考虑数字类型, 其次是日期或者二进制类型, 最后是字符串类型, 同级别得数据类型, 应该优先 选择占用空间小的数据类型
23、存储时期
Datatime:
以
YYYY-MM-DD HH:MM:SS
格式存储时期时间, 精确到秒, 占用
8
个字节得存储空间, datatime 类型与时区无关
Timestamp:
以时间戳格式存储,占用
4
个字节,范围小
1970-1-1
到
2038-1-19
, 显示依赖于所指定的时区, 默认在第一个列行的数据修改时可以自动得修改timestamp
列得值
Date:
( 生日) 占用得字节数比使用字符串.datatime.int
储存要少, 使用
date
只需要
3
个字节, 存储日期月份, 还可以利用 日期时间函数进行日期间得计算Time:
存储时间部分得数据
注意
:
不要使用字符串类型来存储日期时间数据(
通常比字符串占用得储存空间小, 在进行查找过滤可以利用日期得函数)使用 int
存储日期时间不如使用
timestamp
类型
24、对于关系型数据库而言,索引是相当重要的概念,请回答有关索引的几个问题:
1、索引的目的是什么?
快速访问数据表中的特定信息, 提高检索速度创建唯一性索引, 保证数据库表中每一行数据的唯一性。加速表和表之间的连接使用分组和排序子句进行数据检索时,
可以显著减少查询中分组和排序的时间
2、索引对数据库系统 的负面影响是什么?
创建索引和维护索引需要耗费时间, 这个时间随着数据量的增加而增加; 索引需要占用物理空间, 不光是表需要占用数据空间, 每个索引也需要占用物理空间; 当对表进行增、删、改、的时候索引也要动态维护,
这样就降低了数据的维护速度。
3、为数据表建立索引的原则有哪些?
在最频繁使用的、用以缩小查询范围的字段上建立索引。 在频繁使用的、需要排序的字段上建立索引
4、什么情况下不宜建立索引?
对于查询中很少涉及的列或者重复值比较多的列, 不宜建立索引。对于一些特殊的数据类型, 不宜建
立索引, 比如文本字段(
text
) 等
25、解释 MySQL 外连接、内连接与自连接的区别
MySQL中的连接(JOIN)是用来结合两个或多个数据库表中的行的。根据连接的类型,可以分为外连接(OUTER JOIN)、内连接(INNER JOIN)和自连接(SELF JOIN)。下面是它们的区别:
1. **内连接(INNER JOIN)**:
内连接是最常见的连接类型,它只返回两个表中匹配成功的记录。换句话说,内连接会返回左表(FROM子句之前的表)和右表(ON子句之后的表)中那些满足连接条件(ON子句中指定的条件)的记录。如果左表中的一行在右表中没有匹配的行,那么这条左表的行将不会出现在结果集中;反之亦然。
2. **外连接(OUTER JOIN)**:
外连接包括左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。
- **左外连接**:
返回左表中的所有记录,以及左表和右表匹配成功的记录。如果右表中没有匹配的记录,那么结果集中右表的部分会包含NULL。
- **右外连接**:
返回右表中的所有记录,以及左表和右表匹配成功的记录。如果左表中没有匹配的记录,那么结果集中左表的部分会包含NULL。
- **全外连接**:
返回两个表中所有的记录。当左表或右表中没有匹配的记录时,对应的部分会包含NULL。全外连接在MySQL中是通过UNION操作实现的,效率相对较低。
3. **自连接(SELF JOIN)**:
自连接是一种特殊的连接,它允许表与自身的某一列进行连接。这意味着表的行会与自己或其他行的特定列匹配。自连接通常用于需要对一个表的数据进行分组或关联操作的情况,以便于从多个角度查看数据。
**示例**:
- 内连接示例:
SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
- 左外连接示例:
SELECT orders.order_id, customers.name
FROM orders
LEFT OUTER JOIN customers ON orders.customer_id = customers.customer_id;
- 自连接示例:
SELECT a.order_id, a.product_name, b.name AS customer_name
FROM orders a
SELF JOIN customers b ON a.customer_id = b.customer_id;
26、Myql 中的事务回滚机制概述
事务是用户定义的一个数据库操作序列, 这些操作要么全做要么全不做, 是一个不可分割的工作单位,
事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时, 如果它们不是一个事务的话, 当第一个表修改完, 可能第二个表修改过程中出现了异常而没能修改, 此时就只有第二个表依旧是未修改之前的状态, 而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候, 当第一个表修改完, 第二表修改出现异常而没能修改, 第一个表和第二个表都要回到未修改的状态, 这就是所谓的事务回滚。
27、SQL 语言包括哪几部分?每部分都有哪些操作关键字?
SQL
语言包括数据定义
(DDL)
、数据操纵
(DML),
数据控制
(DCL)
和数据查询(
DQL
) 四个部分。
数据定义
:
Create Table,Alter Table,Drop Table, Craete/Drop Index
等;
数据操纵
:
Select ,insert,update,delete;
数据控制
:
grant,revoke;
数据查询
:
select
28、完整性约束包括哪些?
数据完整性
(Data Integrity)
是指数据的精确
(Accuracy)
和可靠性
(Reliability)
。分为以下四类:
1
、实体完整性: 规定表的每一行在表中是惟一的实体。
2
、域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括
取值范围、精度等 规定。
3
、参照完整性:
是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
4
、用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需
要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件, 它反映某一具体应用必须满足的语义要求。与表有关的约束:包括列约束(NOT NULL
( 非空约束)
)
和表约束
(PRIMARY KEY
、
foreign key
、check、
UNIQUE)
。
29、简述使用MySQL的优点
1. **开源性质**:MySQL是免费的软件,可以不受任何限制地使用、修改和分发,这为个人开发者、小型企业和大型组织节省了显著的成本。
2. **跨平台兼容性**:MySQL支持多种操作系统,如Windows、Linux、Mac OS X等,这使得它能够灵活地部署在不同的环境中。
3. **高性能**:MySQL经过优化,可以处理大量数据,提供高速的查询性能,尤其适用于读密集型应用。
4. **可靠性与稳定性**:MySQL被证明是一个稳定的数据库管理系统,特别是在经历了多年的发展和优化之后。
5. **易于使用**:MySQL的SQL语言相对简单易学,使得数据库管理和查询变得更加容易。
6. **可扩展性**:MySQL支持多数据库系统,允许用户根据需要扩展其功能。此外,它还支持分布式数据库。
7. **社区支持**:由于MySQL拥有庞大的用户社区,用户可以轻松地找到技术支持、教程和文档。
8. **成熟的商业支持**:尽管MySQL是开源的,但它背后还有商业公司(如甲骨文公司)提供专业的支持服务。
9. **良好的文档和社区资源**:MySQL有着详尽的文档和丰富的社区资源,这可以帮助用户解决问题并提高使用效率。
10. **集成与兼容性**:MySQL能够与多种编程语言和开发框架集成,如PHP、Python、Java等,这为开发者提供了极大的方便。
11. **备份和恢复**:MySQL提供了强大的数据备份和恢复功能,确保数据安全。
12. **多版本并发控制(MVCC)**:MySQL的存储引擎InnoDB实现了MVCC,这使得事务处理既快速又安全。
30、优化数据库的方法
1
、选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置
NOTNULL
2
、使用连接
(JOIN)
来代替子查询
3
、适用联合
(UNION)
来代替手动创建的临时表
4
、事务处理
5
、锁定表、优化事务处理
6
、适用外键, 优化锁定表
7
、建立索引
8
、优化查询语句