常见MySQL面试题(1)(MySQL面试笔试题)

1.你能想到的优化MYSQL数据库的方法

• 尽量避免使用select *，查询具体到字段
• 设计数据表合理，减少连表查询
• 建立合理的索引
• 数据库的读写分离
• 使用redis缓存来减轻数据库的查询压力

2.应用有大量数据，主要给用户查询，更新操作比较少，那么存储引擎用MyISAM好还是InnoDB好，为什么？

使用MyISAM，因为MyISAM拥有较高的插入、查询速度。

• 存储结构：MyISAM在磁盘上存储成三个文件。而InnoDB所有的表都保存在同一个数据文件中，一般为2GB
• 事务支持：MyISAM不提供事务支持。InnoDB提供事务支持事务。
• 表锁差异：MyISAM只支持表级锁。InnoDB支持事务和行级锁。
• 全文索引：MyISAM支持 FULLTEXT类型的全文索引(不适用中文，所以要用sphinx全文索引引擎)。InnoDB不支持。
• 表的具体行数：MyISAM保存有表的总行数，查询count(*)很快。InnoDB没有保存表的总行数,需要重新计算。

3.写出无限级的分类表结构，表个数不限（表字段名和说明就行，不需要脚本）

id、pid、name、orders、create_time、update_time

4.表 user,字段 USER_ ID,USER_ NAME,BIRTHDAY

USER_ID(int10)	USER_NAME(varchar100)	BIRTHDAY(varchar100)
1	Name01	10 September 1980
2	Name01	1988-02-09
3	Name03	1112564781
4	Name04	2/3/1983

1. 写 sql 脚本取出两条数据按 USER_ID 倒序排列

SELECT * FROM user ORDER BY user_id DESC LIMIT 2;

2. 写 sql脚本能够随机取出一条数据

• SELECT * FROM user ORDER BY RAND() LIMIT 1;

• SELECT * FROM user AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(user_id) FROM user)-(SELECT MIN(user_id) FROM user))+(SELECT MIN(user_id) FROM user)) AS user_id) AS t2 WHERE t1.user_id >= t2. user_id ORDER BY t1. user_id LIMIT 1;

• SELECT * FROM user WHERE user_id >= ((SELECT MAX(user_id) FROM user)-(SELECT MIN(user_id) FROM user)) * RAND() + (SELECT MIN(user_id) FROM user) LIMIT 1

3. 写sql 脚本取出的数据 USER_NAME 不能同名

SELECT DISTINCT user_name FROM `user`;

(去重关键字 distinct。需要注意的是此关键字必须紧挨着select关键字。)

4. 写sql 脚本取出的数据 BIRTHDAY 大于 1983-01-01

答案（不全）：

SELECT * FROM `user` WHERE (birthday LIKE '%-%-%' AND STR_TO_DATE(birthday, '%Y-%m-%d') > '1983-01-01') OR
    (birthday LIKE '%/%/%' AND STR_TO_DATE(birthday, '%d/%m/%Y') > '1983-01-01');

5.一张采用 Innodb 的User 表，其中 id为主键，name 为普通索引，试从索引的数据结构角度分析，以下两条语句（均返回一条记录）在检索过程中有哪些区别？

Sql 1: SELECT id, name, address FROM User WHERE name
"smith’
Sql 2: SELECT id, name, address FROM User WHERE id = 1;

sql2性能更高。
因为name为普通索引，即辅助索引，由于Innodb中辅助索引的数据存储的是聚簇索引的值，因此使用辅助索引进行查询时，当查到数据后，还需要再使用主键查询一次聚簇索引，因此会查询两次索引，而sql2只需要查询一次。

6.现有一统计网站独立访客的需求，流量百万以上，如以 IP 为标识，可以查看当天实时或者指定某天的 IP 数(需要去重)，才用MySQL来实现，那么：

1. 你会如何设计表和索引？(文字、sql均可，方案尽可能高效)

IP 地址转换为整形存储，按日期分表，以 (IP, 日期) 作为联合索引。

2. 数据如何入库，当天实时和某天数据该如何查询？(写出sql语句)

redis 队列缓存 -> MySQL 批量入库

查询当天：

select count(distinct ip) as ip_num from log_{yyyymmdd} where TO_DAYS(add_time)=TO_DAYS(now());

查询某天：

select count(distinct ip) as ip_num from visit_log_{yyyymmdd} where TO_DAYS(add_time) = {yyyy-mm-dd};

7.mySql的执行顺序

mysql执行sql的顺序从 From 开始，以下是执行的顺序流程

1. FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2. JOIN table2 所以先是确定表，再确定关联条件

3. ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4. WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

5. GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6. HAVING 对分组后的记录进行聚合 产生中间表Temp5

7. SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

8. DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9. ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10. LIMIT 对中间表Temp8进行分页，产生中间表Temp9