MySQL总结2 | SkySea-GaoMing

普通索引和唯一索引如何选择

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码
已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查
姓名

查询过程

性能差距微乎其微，InnoDB 的数据是按数据页为单位来读写的。也就是说，
当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页
为单位，将其整体读入内存，每个数据页的大小默认是16KB。对于普通索引
来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针
寻找和一次计算

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个
记录，直到碰到第一个不满足条件的记录
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的
记录后，就会停止继续检索

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数
据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这
些更新操作缓存在change buffer 中，这样就不需要从磁盘中读入这个数
据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后
执行change buffer 中与这个页有关的操作

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一
性约束，必须要将数据页读入内存才能判断。如果都已经读入到内存了，那
直接更新内存会更快，就没必要使用change buffer 了。唯一索引的更新
就不能使用change buffer
对于普通索引来说，如果这个记录要更新的目标页不在内存中，则是将
更新记录在change buffer。将数据从磁盘读入内存涉及随机IO 的访问，
是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘
访问，所以对更新性能的提升是会很明显的

redo log和change buffer的区别

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而
change buffer 主要节省的则是随机读磁盘的 IO 消耗
change buffer记录的只是针对不在内存中的数据，redo log不管数据
在不在内存中，都记录

选择索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执
行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数
越少，意味着访问磁盘数据的次数越少，消耗的CPU 资源越少。还会结合
是否使用临时表、是否排序等因素进行综合判断

扫描行数怎么判断

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的
值的个数，我们称之为基数。也就是说这个基数越大，索引的区分度越好。

给字符串加索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加
太多的查询成本。在建立索引时关注的是区分度，区分度越高越好。因为区
分度越高，意味着重复的键值越少
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择
是否使用前缀索引时需要考虑的一个因素
遇到前缀的区分度不够好的情况时

第一种方式是使用倒序存储。如存储身份证号的时候把它倒过来存，每次查
询的时候，你可以这么写：reverse(‘input_id_card_string’);
第二种方式是使用hash 字段。你可以在表上再创建一个整数字段，来保存
身份证的校验码，同时在这个字段上创建索引。每次插入新记录的时候，都同
时用crc32() 这个函数得到校验码填到这个新字段。这样索引的长度变成了4
个字节，比原来小了很多
字段拆分（一个字段可拆分为两个以上）

表数据和表文件

一个InnoDB 表包含两部分：表结构定义和数据。表结构是存在以.frm 为后缀
的文件里。表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是
由参数innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟
数据字典放在一起
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd
为后缀的文件中

数据删除流程

假设我们要删掉某个记录，InnoDB 引擎只会把这个记录标记为删除，也就是
逻辑删除。如果我们删掉一个数据页上的所有记录，整个数据页就可以被复用

delete 命令把整个表的数据删除，所有的数据页都会被标记为可复用。
但是磁盘上，文件不会变小
重建表，消除表因为进行大量的增删改操作而产生的空洞，空洞就是那些
被标记可复用但是还没被使用的存储空间

count(*)

MyISAM 用一个变量存储表的总行数。InnoDB 则遍历整张表进行统计
InnoDB 不把数字存起来，因为即使是在同一个时刻的多个查询，由于多
版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。
每一行记录都要判断自己是否对这个会话可见，因此对于count(*) 请求来
说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计
算“基于这个查询”的表的总行数
MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽
量减少扫描的数据量，是数据库系统设计的通用法则之一

order by

执行流程

初始化sort_buffer，确定放入所要查询的字段
从索引city 找到第一个满足city=’杭州’条件的主键id
到主键id 索引取出整行，取要查询字段的值，存入sort_buffer 中
从索引city 取下一个记录的主键 id
重复步骤3、4 直到 city 的值不满足查询条件为止
对sort_buffer 中的数据按照字段name 做快速排序
按照排序结果取前1000 行返回给客户端

order by原理

order by 有两种排序模式。全字段排序和rowid排序

全字段排序首先在sort buffer中初始化select的字段，根据where
条件进行过滤，然后按照order by的字段进行排序
rowid排序

MySQL

组合两个表

当使用join连接时连接条件必须是on，left join会返回左边全部记录，
MySQL不支持全外连接，内连接和自然连接的唯一区别就在于自然连接必
需是相同属性（名字相同），可以不写连接条件。内连接放宽了限制，两
个属性列不必同名。using只能用于属性同名，on也可以用在属性不同名

1 2	select FirstName,LastName,City,State from Person p left join Address a on p.PersonId=a.PersonId;

全外连接可以使用以下语句完成，union用于合并多个SELECT语句的结果
集，如果要允许重复可以使用 union all

1
2
3

select FirstName,LastName,City,State from Person p left join Address a
on p.PersonId=a.PersonId union select FirstName,LastName,City,State 
from Person p right join Address a on p.PersonId=a.PersonId;

第二高的薪水

limit position,count 从索引位置开始count个数据，索引从0开始，
现在要查询第N高的薪水只需要修改为 limit N-1,1

1 2	select ifNull((select distinct salary from Employee order by Salary Desc limit 1,1),null) as SecondHighestSalary;

第N高的薪水

首先要清楚函数的格式

create function 函数名(参数列表) returns 返回类型
begin 
	declare c int default 0;
	set c=1;
	函数体 into c
	return c;
end
select 函数名(参数列表)

这个题不需要使用ifNull，因为不需要起一个别名

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct salary from Employee order by
      salary desc limit N,1 
  );
END

也可以这样写

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1;
    declare c int default null;
    select distinct salary from Employee order by
      salary desc limit N,1 into c;
  RETURN (
      # Write your MySQL query statement below.
      c
  );
END

分数排名

这个问题看起来有点复杂，可以分成两部分解决。第一部分就是得到降序
排列的分数，第二部分就是分数的排名。给定一个分数与自己对照如果有
N个不重复分数大于等于这个分数那么这个分数的排名就是N

1
2
3

select a.Score,count(distinct(b.Score)) 'Rank'
from Scores a,Scores b where a.Score<=b.Score
group by a.ID order by a.Score desc;

还可以这样写

1
2
3

select a.Score as Score,(select count(distinct(b.Score))
from Scores b where a.Score<=b.Score ) as `Rank`
from Scores a  order by a.Score DESC;

如果使用函数的话有三种函数可以使用，RANK，DENSE_RANK和ROW_NUMBER

1 2	select score, DENSE_RANK() OVER (ORDER BY Score DESC) as 'Rank' from Scores;

连续出现的数字

我看到的题解中最简单的方法是使用窗口函数，将num列复制两次，现在需要
获得连续三个数的话num1列上移一次，num2列上移两次

# Write your MySQL query statement below
select distinct num as ConsecutiveNums  from
(
    select num,lead(num,1)over()as num1,lead(num,2)over()as num2 
    from logs
) as c
where c.num = c.num1 and c.num1 = c.num2

还有一种更好的方法，思路也很清晰，但是实现很复杂。首先根据id获取
一个排序，然后将num分组排序。这两个列相减如果值一样说明是重复的
值

SELECT Id,Num,
row_number() over(order by id) as SerialNum
FROM ContinueNumber
SELECT Id,Num,
ROW_NUMBER() over(partition by Num order by Id) as SerialGroup
FROM ContinueNumber

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

还有一种简明解法

SELECT
    DISTINCT L1.num ConsecutiveNums
FROM
    Logs L1,
    Logs L2,
    Logs L3
WHERE L1.id = l2.id - 1
    AND L2.id = L3.id - 1
    AND L1.num = L2.num
    AND l2.num = l3.num;

超过经理收入的员工

1 2	select a.Name as Employee from Employee a where Salary> (select b.Salary from Employee b where a.ManagerId=b.Id);

查找重复的电子邮箱

奇怪为什么把in换成=就出错

1 2	select distinct(a.Email) from Person a where a.Email in (select b.Email from Person b group by b.Email having count(*)>1);

从不订购的客户

1 2	select Name as Customers from Customers where Id not in (select CustomerId from Orders)

删除重复的电子邮箱

1	delete p1 from Person p1,Person p2 where p1.Email = p2.Email and p1.Id > p2.Id

上升的温度

1 2	select a.id from Weather a,Weather b where a.Temperature>b.Temperature and datediff(a.recordDate,b.recordDate)=1;

有趣的电影

终于有一道会的了

1 2	select id,movie,description,rating from cinema where description!='boring' and id%2=1 order by rating desc;

有座位

大佬的思路就是不一样，换name的话比较麻烦直接换id就好了

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

变更性别

一开始没想到用三目运算符

1	update salary set sex = if(sex = 'm','f','m');

还有一种很巧妙的方法就是异或

1	UPDATE salary SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );

重新格式化部门表

在select子句中出现了group by子句中没有出现的列名revenue，而这种
写法在SQL标准中是没有的，MySQL允许这种写法实际会造成一种错觉

select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;