【数据库】基础知识与题解——后端开发工程师面试必考

Sql面试练习题

595、Big Countries

leetcode题目链接
答案：

select name, population, area 
from World 
where population >25000000 or area >3000000;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS World;
CREATE TABLE World ( NAME VARCHAR ( 255 ), continent VARCHAR ( 255 ), area INT, population INT, gdp INT );
INSERT INTO World ( NAME, continent, area, population, gdp )
VALUES
    ( 'Afghanistan', 'Asia', '652230', '25500100', '203430000' ),
    ( 'Albania', 'Europe', '28748', '2831741', '129600000' ),
    ( 'Algeria', 'Africa', '2381741', '37100000', '1886810000' ),
    ( 'Andorra', 'Europe', '468', '78115', '37120000' ),
    ( 'Angola', 'Africa', '1246700', '20609294', '1009900000' );

627、 变更性别

leetcode题目链接
答案：

update salary 
set sex = CHAR (ASCII(sex)^ASCII('f')^ASCII('m'));

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS salary;
CREATE TABLE salary ( id INT, NAME VARCHAR ( 100 ), sex CHAR ( 1 ), salary INT );
INSERT INTO salary ( id, NAME, sex, salary )
VALUES
    ( '1', 'A', 'm', '2500' ),
    ( '2', 'B', 'f', '1500' ),
    ( '3', 'C', 'm', '5500' ),
    ( '4', 'D', 'f', '500' );

620、有趣的电影

leetcode题目链接
答案：

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

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS cinema;
CREATE TABLE cinema ( id INT, movie VARCHAR ( 255 ), description VARCHAR ( 255 ), rating FLOAT ( 2, 1 ) );
INSERT INTO cinema ( id, movie, description, rating )
VALUES
    ( 1, 'War', 'great 3D', 8.9 ),
    ( 2, 'Science', 'fiction', 8.5 ),
    ( 3, 'irish', 'boring', 6.2 ),
    ( 4, 'Ice song', 'Fantacy', 8.6 ),
    ( 5, 'House card', 'Interesting', 9.1 );

596、超过5名学生的课

leetcode题目链接
答案：

select class 
from courses 
group by class 
having count(distinct student ) >= 5;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS courses;
CREATE TABLE courses ( student VARCHAR ( 255 ), class VARCHAR ( 255 ) );
INSERT INTO courses ( student, class )
VALUES
    ( 'A', 'Math' ),
    ( 'B', 'English' ),
    ( 'C', 'Math' ),
    ( 'D', 'Biology' ),
    ( 'E', 'Math' ),
    ( 'F', 'Computer' ),
    ( 'G', 'Math' ),
    ( 'H', 'Math' ),
    ( 'I', 'Math' );

182、查找重复的电子邮箱

leetcode题目链接
答案：

select Email 
from Person 
group by Email 
having count(Email) >=2;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Person;
CREATE TABLE Person ( Id INT, Email VARCHAR ( 255 ) );
INSERT INTO Person ( Id, Email )
VALUES
    ( 1, 'a@b.com' ),
    ( 2, 'c@d.com' ),
    ( 3, 'a@b.com' );

196、删除重复的电子邮箱

leetcode题目链接
答案：

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

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Person;
CREATE TABLE Person ( Id INT, Email VARCHAR ( 255 ) );
INSERT INTO Person ( Id, Email )
VALUES
    ( 1, 'a@b.com' ),
    ( 2, 'c@d.com' ),
    ( 3, 'a@b.com' );

181、超过经理收入的员工

leetcode题目链接
答案：

select e1.Name as Employee 
from Employee e1 inner join Employee e2 on e2.Id = e1.ManagerId
where e1.Salary > e2.Salary;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Employee;
CREATE TABLE Employee ( Id INT, NAME VARCHAR ( 255 ), Salary INT, ManagerId INT );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
    ( 1, 'Joe', 70000, 3 ),
    ( 2, 'Henry', 80000, 4 ),
    ( 3, 'Sam', 60000, NULL ),
    ( 4, 'Max', 90000, NULL );

183、从不订购的客户

leetcode项目链接
答案：
左连接：

select Name as Customers
from Customers c Left join Orders o on c.Id = o.CustomerId
where o.CustomerId is Null;

子查询

select Name as Customers
from Customers
where Id not in (
    select c.Id
    from Customers c, Orders O
    where c.Id = o.CustomerId
)

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Customers;
CREATE TABLE Customers ( Id INT, NAME VARCHAR ( 255 ) );
DROP TABLE
IF
    EXISTS Orders;
CREATE TABLE Orders ( Id INT, CustomerId INT );
INSERT INTO Customers ( Id, NAME )
VALUES
    ( 1, 'Joe' ),
    ( 2, 'Henry' ),
    ( 3, 'Sam' ),
    ( 4, 'Max' );
INSERT INTO Orders ( Id, CustomerId )
VALUES
    ( 1, 3 ),
    ( 2, 1 );

184、部门工资最高的员工

leetcode题目链接
答案：

select d.Name as Department, e.Name as Employee, e.Salary
from Employee e, Department d, (
    select DepartmentId, Max(Salary) AS Salary
    from Employee
    group by DepartmentId
) m
where e.DepartmentId = d.Id 
and E.DepartmentId = m.DepartmentId 
and e.Salary = m.Salary

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee ( Id INT, NAME VARCHAR ( 255 ), Salary INT, DepartmentId INT );
DROP TABLE IF EXISTS Department;
CREATE TABLE Department ( Id INT, NAME VARCHAR ( 255 ) );
INSERT INTO Employee ( Id, NAME, Salary, DepartmentId )
VALUES
    ( 1, 'Joe', 70000, 1 ),
    ( 2, 'Henry', 80000, 2 ),
    ( 3, 'Sam', 60000, 2 ),
    ( 4, 'Max', 90000, 1 );
INSERT INTO Department ( Id, NAME )
VALUES
    ( 1, 'IT' ),
    ( 2, 'Sales' );

176、第二高的薪水

leetcode项目链接
答案：

select (
    select distinct Salary
    from Employee
    order by Salary desc
    limit 1,1
) as SecondHighestSalary

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Employee;
CREATE TABLE Employee ( Id INT, Salary INT );
INSERT INTO Employee ( Id, Salary )
VALUES
    ( 1, 100 ),
    ( 2, 200 ),
    ( 3, 300 );

177、第N高的薪水

leetcode项目链接
答案：
子查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select (
          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;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary
      from Employee
      group by Salary
      order by Salary desc
      limit N, 1
  );
END

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Employee;
CREATE TABLE Employee ( Id INT, Salary INT );
INSERT INTO Employee ( Id, Salary )
VALUES
    ( 1, 100 ),
    ( 2, 200 ),
    ( 3, 300 );

178、分数排名

leetcode题目链接
答案：

select s1.Score 'Score', count( DISTINCT s2.Score ) 'Rank'
from 
    Scores s1 
    inner join Scores s2 
    on s1.Score <= s2.Score
group by s1.Id, s1.Score
order by s1.Score desc;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS Scores;
CREATE TABLE Scores ( Id INT, Score DECIMAL ( 3, 2 ) );
INSERT INTO Scores ( Id, Score )
VALUES
    ( 1, 4.1 ),
    ( 2, 4.1 ),
    ( 3, 4.2 ),
    ( 4, 4.2 ),
    ( 5, 4.3 ),
    ( 6, 4.3 );

180、连续出现的数字

leetcode项目链接
答案：

select distinct l1.Num as 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

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS LOGS;
CREATE TABLE LOGS ( Id INT, Num INT );
INSERT INTO LOGS ( Id, Num )
VALUES
    ( 1, 1 ),
    ( 2, 1 ),
    ( 3, 1 ),
    ( 4, 2 ),
    ( 5, 1 ),
    ( 6, 2 ),
    ( 7, 2 );

626、换座位

leetcode题目链接
答案：

select if ( id %2 =0,id-1,
    if (
        id=(select count(distinct id) from seat ),
        id,
        id+1
    )

) as id, student
from seat
order by id;

SQL scheme 本地调试所需：

-- 基于mysql的，需要先use选择一下数据库
DROP TABLE
IF
    EXISTS seat;
CREATE TABLE seat ( id INT, student VARCHAR ( 255 ) );
INSERT INTO seat ( id, student )
VALUES
    ( '1', 'Abbot' ),
    ( '2', 'Doris' ),
    ( '3', 'Emerson' ),
    ( '4', 'Green' ),
    ( '5', 'Jeames' );

数据库系统原理

一、事务

概念

事务指的是满足acid特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback进行回滚。

acid

1.原子性
事务不可被分割的最小单元，事务的所有操作要么全部都成功提交，要么全部都失败回滚
回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可
2.一致性
数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都时相同的
3.隔离性
一个事务所做的修改在最终提交以前，对其他事务时不可见的
4.持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失
系统发生崩溃可以用重做日志（redo log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改
事务的acid特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

• 只有满足一致性，事务的执行结果才是正确的
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要满足原子性，就一定能满足一致性
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
• 事务满足持久化是为了能应对系统奔溃的情况

auto commit

mysql默认采用自动提交模式。也就是说，如果不显式使用start transsation语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交

二、并发一致性问题

在并发环境下，事物的隔离性很难的得到保证，因此会出现很多并发一致性的问题

丢失修改

丢失修改指一个事务的更新操作被另一个事务的更新操作替换。一般在现实生活中经常会遇到，例如：T1和T2两个事务都对一个数据进行修改，T1先修改并提交生效，T2随后修改，T2的修改覆盖了T1的修改

读脏数据

读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1修改了一个数据但是未提交，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据

不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2读取一个数据，T1对该数据做了修改。如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同

幻影读

幻读本质上也属于不可重复读的情况，T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一次读取的结果不同
ps:
产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发已执行问题

三、封锁

封锁粒度

MySQL提供了两种封锁粒度：行级锁和表级锁
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少越好，发生锁争用的可能就越小，系统的并发程度就越高
但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁的状态)都会增加系统开销。因此封锁粒度越小，系统的开销越大。
在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

锁的类型

1、 读写锁

• 互斥锁（exclusive）：简写为X锁，又称写锁
• 共享锁（shared）：简写为S锁，又称读锁
  有以下两个规定：
• 一个事务对数据对象A加了X锁，就可以对A进行读取和更新。加锁期间其他事务不能对A加任何锁
• 一个事务对数据对象A加了S锁，可以对A进行读取操作，但是不能进行更新操作。加锁期间其它事务能对A加S锁，但是不能加X锁

2、意向锁
使用意向锁可以更容易地支持多粒度封锁
在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检测是否有其它事务对表A中的任意行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的
意向锁在原来的X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：

• 一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
• 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其它事务对表A加了X/IX/S/IS锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败
各种锁的兼容关系如下：

锁	X	IX	S	IS
X	×	×	×	×
IX	×		×
S	×	×
IS	×

ps:

• 任意IS/IX锁之间都是兼容的，因为它们只表示想要对表加锁，并不是真正加锁
• 这里兼容关系针对的是表级锁，而表级的IX锁和行级的X锁兼容，两个事务可以对两个数据行加X锁。（事务T1想要对数据行R1加X锁，事务T2想要对同一个表的数据行R2加X锁，两个事务都需要对该表加IX锁，但是IX锁是兼容的，并且IX锁与行级的X锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

封锁协议

1. 三级封锁协议
   一级封锁协议
   事务T要修改数据A时必须加X锁，直到T结束才释放锁
   可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖

   二级封锁协议
   在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。
   可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
   二级封锁协议
   在一级的基础上，要读取数据A时必须加S锁，读取完马上释放S锁
   可以解决脏读数据问题，因为如果一个事务在对数据A进行修改，根据一级封锁协议，会加小锁，那么就不能再加S锁了，也就是不会读入数据
   三级封锁协议
   在二级的基础上，要求读取数据A时必须加S锁，只要事务结束才释放S锁
   可以解决不可重复读的问题，因为读A时，其它事务不能对A加X锁，从而避免了在读的期间数据发生改变

2. 二段锁协议
   加锁和解锁分为两个阶段进行
   可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题
   事务遵循两端锁协议是保证可串行化的充分条件。例如以下操作满足两段锁协议，他是可串行化调度。

lock-x(A) …lock-s(B)…lock-s©…unlock(A)…unlock©…unlock(B)

但不是必要条件，例如一下操作不满足两段锁协议，但它还是可串行化调度

lock-x(A)…unlock-x(A)…lock-s(B)…unlock-s(B)…lock-s(C )…unlock(C )

MySQL隐式和显式锁定

MySQL的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定
InnoDB也可以使用特定的语句进行显式锁定：

SELECT …LOCK IN SHOW MODE
(Lock in show mode是is锁，是给所有符合条件的rows上都加了共享锁)
SELECT…FOR UPDATE
(for update走的是IX锁(意向排它锁)，是给所有符合条件的rows上都加了排他锁))

四、隔离级别

未提交读（read uncommitted）

事务中的修改，即使没有提交，对其它事务也是可见的

提交读（read committed）

一个事务只能读取已经提交的事务所做的修改，或者说是，一个事务所做的修改只有在提交后才对其他事务可见，未提交的修改，其他事务是看不见的

可重复读（repeatable read）

保证在一个事务中多次读取同一数据的结果是一样的

可串行化

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题
该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行

MySQL

一、索引

B+ tree 原理

1、数据结构

B tree指的是balance tree，也是平衡树，平衡树是一个查找树，并且所有叶子节点都位于同一层
B+ tree是基于B tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询性能
在B+ Tree中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别是keyi和keyi+1，且不为null，则该指针指向节点的所有key大于等于keyi且小于等于keyi+1

2、操作

进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data
插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性

3、与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用B+ Tree作为索引结构，这是因为使用B+ Tree访问磁盘数据有更高的性能。
（一）B+ Tree有更低的树高
平衡树的树高o(h) = o(lognN)，其中d为每个节点的出度。红黑树的出度为2，而B+ Tree的出度一般都非常大，所以红黑树的树高h很明显比B+ Tree大非常多（出度指的是每个节点指向的下一个节点的数量，可以这么含糊的解释，本来出度就指的是每个点有多少条往外指出的线）
（二）磁盘访问原理
操作系统一般将内存和磁盘分割成固定大小的block，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次I/O就能完全载入一个节点
如果数据不在同一磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致移动效率低下，从而增加磁盘数据读取时间。B+ Tree 相对于红黑树更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很低的磁盘旋转时间，所以B+ Tree更适合磁盘数据的读取
（三）磁盘预读特性
为了减少磁盘I/O操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

MySQL索引

1、B+ Tree索引

是大多数MySQL存储引擎的默认索引类型
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多
因为B+ Tree的有序性，所以除了用于查找，还可以用于排序和分组
可以指定多个列表作为索引列，多个索引列共同组成键
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引
InnoDB的B+ Tree索引分为主索引和辅助索引。主索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引；辅助索引的叶子节点的data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找

2、哈希索引

哈希索引能以O(1)时间进行查找，但是失去了有序性：

• 无法用于排序与分组
• 只支持精确查找，无法用于部分查找和范围查找

InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+ Tree索引之上再创建一个哈希索引，这样就让B+ Tree索引具有哈希索引的一些优点，比如快速的哈希查找

3、全文索引

MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等
查找条件使用MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4、空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

索引优化

1、独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2、多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3、索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

4、前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

5、覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

• 大大减少服务器需要扫描的数据行数
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；（量太小就没必要使用索引了，徒增复杂度）
• 对于中到大型的表，索引就非常有效；（中大型用索引有很明显的效果）
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

二、查询性能优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询、子查询等
• key : 使用的索引
• rows : 扫描的行数

优化数据访问

1. 减少请求的数据量

• 只查询必要的列：避免select *的用法
• 只查询必要的行（数据量）：用limit限制查询的数据量
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫面的行数

• 最有效的办法就是建立索引来覆盖查询

重构查询方式

1. 切分大查询
   一个大的查询如果一次性执行的话，可能会一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2. 分解大连接查询
   将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

三、存储引擎

MyISAM和InnoDB的区别

1. 事务：InnoDB是事务型的，支持commit和rollback语句
2. 锁：MyISAM只支持表级锁，InnoDB支持行级锁
3. 外键：仅InnoDB支持外键
4. 备份：InnoDB支持热在线备份
5. 崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB要大，而且恢复速度很慢
6. 其他：MyISAM支持压缩表和空间数据索引

三、切分

水平切分

当数据量越来越大的时候，水平切分是必然的，水平切分就是将数据分布到集群的不同节点上，从而缓解单个节点数据库的压力

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

策略

• hash取模：hash(key)%N
• 范围：可以是ID范围，也可以是时间范围
• 映射表：创建一个数据库专门存储这些映射

sharding存在的问题

• 事务问题：可以用分布式事务来解决，比如XA接口
• 连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
• ID唯一性：1.使用全局唯一ID（GUID）；2.为每个分片指定一个范围；3.分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

四、复制

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog线程：负责将主服务器上的数据更改写入二进制日志中
• I/O线程：负责读取主服务器的二进制日志，并写入从服务器的中继日志中
• SQL线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器上重放

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读写，减少锁的竞争
• 从服务器可以使用MyISAM存储引擎，提高查询性能以及节约系统开销
• 增加冗余，提高可用性

Redis

1、概述

Redis 是速度非常快的非关系型（NoSQL）内存键值数据库，可以存储键和五种不同类型的值之间的映射。

键的类型只能为字符串，值支持五种数据类型：字符串、列表、集合、散列表、有序集合。

Redis 支持很多特性，例如将内存中的数据持久化到硬盘中，使用复制来扩展读性能，使用分片来扩展写性能。

2、数据类型

数据类型	可以存储的值	操作
string	字符串、整数或者浮点数	对整个字符串或者字符串的其中一部分执行操作 对整数和浮点数执行自增或者自减操作
list	列表	从两端压入或者弹出元素 对单个或者多个元素进行修剪， 只保留一个范围内的元素
set	无序集合	添加、获取、移除单个元素 检查一个元素是否存在于集合中 计算交集、并集、差集 从集合里面随机获取元素
hash	包含键值对的无序散列表	添加、获取、移除单个键值对 获取所有键值对 检查某个键是否存在
zset	有序集合	添加、获取、删除元素 根据分值范围或者成员来获取元素 计算一个键的排名

3、数据结构

字典

dictht 是一个散列表结构，使用拉链法解决哈希冲突。

跳跃表

有序集合的底层实现之一
跳跃表是基于多指针有序链表实现的，可以看成多个有序链表。
与红黑树等平衡树相比，跳跃表具有以下优点：

• 插入速度非常快，因为不需要进行旋转操作来维持平衡性
• 更容易实现
• 支持无锁操作

4、使用场景

计数器

可以对string进行自增自减操作，从而实现计数器的功能
由于redis是内存型数据库，读写性能非常高，所以很适合存储频繁读写的计数器

缓存

将热点数据存放到内存中，设置内存的最大使用量以及淘汰策略来保证缓存的命中率

查找表

查找表跟缓存类型，利用redis的快速的查找特性。但是查找表的数据不能失效，而缓存的数据可以失效，缓存的数据不作为可靠的数据来源

消息队列

reids的list是一个双向链表，可以通过push 和 rpop 写入和读取消息
但是一般都是用kafka、rabbitMQ或者MQTT

会话缓存

可以使用redis来统一存储多台服务器的绘画消息

分布式锁实现

不太会，pass

5、键的过期时间

一般在存储登陆信息的token时候会设置键的过期时间
volatile-lru：从已设置过期时间的数据集中挑选最近最少使用的数据淘汰
volatile-ttl：从已设置过期时间的数据集中挑选将要过期的数据淘汰
volatile-random：从已设置过期时间的数据集中任意选择数据淘汰
allkeys-lru：从所有数据集中挑选最近最少使用的数据淘汰
allkeys-random：从所有数据集中任意选择数据进行淘汰
noeviction：禁止驱逐数据

6、持久化

RDB持久化

将某个时间点的所有数据都存放到硬盘上
可以将快照复制到其它服务器从而创建具有相同数据的服务器副本
如果系统发生故障，将会丢失最后一次创建快照之后的数据
如果数据量很大，保存快照的时间会很长

AOF持久化

将命令填写到AOF（append only files）文件的末尾