Skip to main content

sql

MySQL 中支持几种模糊查询？它们有什么区别？​

MySQL 中支持两种模糊查询：regexp 和 like，like 是对任意多字符匹配或任意单字符进行模糊匹配，而 regexp 则支持正则表达式的匹配方式，提供比 like 更多的匹配方式。 regexp 和 like 的使用示例如下：

select * from person where uname like '%SQL%';> select from person where uname regexp '.SQL*.';

MySQL 支持枚举吗？如何实现？它的用途是什么？​

MySQL 支持枚举，它的实现方式如下：

create table t(
 sex enum('boy','grid') default 'unknown'
);

枚举的作用是预定义结果值，当插入数据不在枚举值范围内，则插入失败，提示错误 Data truncated for column 'xxx' at row n 。

count(column) 和 count(*) 有什么区别？​

count(column) 和 count() 最大区别是统计结果可能不一致，count(column) 统计不会统计列值为 null 的数据，而 count() 则会统计所有信息，所以最终的统计结果可能会不同。

以下关于 count 说法正确的是？​

A. count 的查询性能在各种存储引擎下的性能都是一样的。 B. count 在 MyISAM 比 InnoDB 的性能要低。 C. count 在 InnoDB 中是一行一行读取，然后累计计数的。 D. count 在 InnoDB 中存储了总条数，查询的时候直接取出。

答：C

为什么 InnoDB 不把总条数记录下来，查询的时候直接返回呢？​

因为 InnoDB 使用了事务实现，而事务的设计使用了多版本并发控制，即使是在同一时间进行查询，得到的结果也可能不相同，所以 InnoDB 不能把结果直接保存下来，因为这样是不准确的。

能否使用 show table status 中的表行数作为表的总行数直接使用？为什么？​

不能，因为 show table status 是通过采样统计估算出来的，官方文档说误差可能在 40% 左右，所以 show table status 中的表行数不能直接使用。

以下哪个 SQL 的查询性能最高？​

A. select count(*) from t where time>1000 and time<4500 
B. show table status where name='t' 
C. select count(id) from t where time>1000 and time<4500 
D. select count(name) from t where time>1000 and time<4500

答：B 题目解析：因为 show table status 的表行数是估算出来，而其他的查询因为添加了 where 条件，即使是 MyISAM 引擎也不能直接使用已经存储的总条数，所以 show table status 的查询性能最高。

InnoDB 和 MyISAM 执行 select count(*) from t，哪个效率更高？为什么？​

MyISAM 效率最高，因为 MyISAM 内部维护了一个计数器，直接返回总条数，而 InnoDB 要逐行统计。

在 MySQL 中有对 count(*) 做优化吗？做了哪些优化？​

count(*) 在不同的 MySQL 引擎中的实现方式是不相同的，在没有 where 条件的情况下：

  • MyISAM 引擎会把表的总行数存储在磁盘上，因此在执行 count(*) 的时候会直接返回这个这个行数，执行效率很高；
  • InnoDB 引擎中 count(*) 就比较麻烦了，需要把数据一行一行的从引擎中读出来，然后累计基数。

但即使这样，在 InnoDB 中，MySQL 还是做了优化的，我们知道对于 count() 这样的操作，遍历任意索引树得到的结果，在逻辑上都是一样的，因此，MySQL 优化器会找到最小的那颗索引树来遍历，这样就能在保证逻辑正确的前提下，尽量少扫描数据量，从而优化了 count() 的执行效率。

在 InnoDB 引擎中 count(*)、count(1)、count(主键)、count(字段) 哪个性能最高？​


count(字段)<count(主键 id)<count(1)≈count(*) 题目解析：
- 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
- 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
- 对于 count(字段) 来说，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
- 对于 count(*) 来说，并不会把全部字段取出来，而是专门做了优化，不取值，直接按行累加。

所以最后得出的结果是：count(字段)<count(主键 id)<count(1)≈count(*)。

MySQL 中内连接、左连接、右连接有什么区别？​

  • 内连（inner join）— 把匹配的关联数据显示出来；
  • 左连接（left join）— 把左边的表全部显示出来，右边的表显示出符合条件的数据；
  • 右连接（right join）— 把右边的表全部显示出来，左边的表显示出符合条件的数据；

什么是视图？如何创建视图？​

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作。视图通常是一个表或者多个表的行或列的子集。 视图创建脚本如下：

create view vname as
select column_names
from table_name
where condition

视图有哪些优点？​

  • 获取数据更容易，相对于多表查询来说；
  • 视图能够对机密数据提供安全保护；
  • 视图的修改不会影响基本表，提供了独立的操作单元，比较轻量。

MySQL 中“视图”的概念有几个？分别代表什么含义？​

MySQL 中的“视图”概念有两个，它们分别是：

  • MySQL 中的普通视图也是我们最常用的 view，创建语法是 create view …,它的查询和普通表一样；
  • InnoDB 实现 MVCC（Multi-Version Concurrency Control）多版本并发控制时用到的一致性读视图，它没有物理结构，作用是事务执行期间定于可以看到的数据。

使用 delete 误删数据怎么找回？​

可以用 Flashback 工具通过闪回把数据恢复回来。

Flashback 恢复数据的原理是什么？​

Flashback 恢复数据的原理是是修改 binlog 的内容，拿回原库重放，从而实现数据找回。