MySQL面试题总结

一.逻辑架构

MySQL 架构中的三层服务

第一层是服务器层，主要提供连接处理、授权认证、安全等功能。

第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等内置函数。

第三层是存储引擎层，负责数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应服务器请求。

MySQL 中的引擎

InnoDB、MyISAM 、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、Archive、CSV、Blackhole、MaxDB

MyISAM和InnoDB的区别

• InnoDB支持事物，而MyISAM不支持事物
• InnoDB支持行级锁，而MyISAM支持表级锁
• InnoDB支持MVCC, 而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持。
• InnoDB表必须有主键，而Myisam可以没有。

Innodb引擎的4大特性

• 插入缓冲（insert buffer)
• 二次写 (double write)
• 自适应哈希索引 (ahi)
• 预读 (read ahead)

Innodb使用的是哪种隔离级别?

InnoDB默认使用的是可重复读隔离级别.

二. 事务

事务的意思是一条或者是一组语句组成一个单元，这个单元要么全部执行，要么全不执行。

事务的特性（ACID）

原子性： 事务是最小的执行单位，不允许分割。事务内的语句要么全部执行成功，要么全部执行失败。
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

---

多事务并发的问题:

• 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚. （读取了未提交的新事物，然后被回滚了），因为内容不属于数据库，所以是脏读。
• 不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询结果不一样,因为在此期间B事务进行了提交操作. （读取了提交的新事物，指更新操作）
• 幻读: 一个事务按相同的查询条件重新读取以前的检索过的数据，却发现其他事务插入了满足其条件查询的新数据，这种现象被成为幻读。（也是读取了提交的新事物，指增删操作）

---

事务隔离级别

• 未提交读(READ UNCOMMITTED)

  这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

  这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

• 已提交读(READ COMMITTED)

  其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

• 可重复读(REPEATABLE READ)

  可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读.

  当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时另外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥

  那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

• 可串行化(SERIALIZABLE)

  这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

---

MVCC

MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

基本特征

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时Copy出当前版本，随意修改，各个事务之间无干扰。
• 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：
​ 1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
​ 2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

---

如何解决幻读？

MVCC 加上 间隙锁 的方式
（1）在快照读读情况下，mysql 通过 mvcc 来避免幻读。
（2）在当前读读情况下，mysql 通过 next-key 来避免幻读。锁住某个条件下的数据不能更改。

快照读：简单的 select 操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

三. 索引

索引分类

• 普通索引：加速查询
• 唯一索引：加速查询 和 唯一约束（此索引列的值不能重复，可含null）
• 主键索引：加速查询 和 唯一约束（此索引列的值不能重复，不可含null）
• 组合索引

---

索引使用原则

控制数量

索引数量不是越多越好，索引越多，维护索引的代价自然也就越高。对于 DML 操作比较频繁的表，索引过多会导致很高的维护代价。

使用短索引

索引使用硬盘存储，假如构成索引的字段长度比较短，那么在储块内就可以存储更多的索引，提升访问索引的 IO 效率。

建立索引

对查询频次较高且数据量比较大的表建立索引。索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

使用前缀索引

对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。前缀索引是一种能使索引更小更快的有效方法，缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 以及覆盖扫描。

选择合适的索引顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是 1，因此也可以使用唯一索引提升查询效率。

删除重复索引

MySQL 允许在相同列上创建多个索引，重复索引需要单独维护，重复索引是指在相同的列上按照相同顺序创建的同类型的索引，应该避免创建。如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引，对于 B-Tree 索引来说是冗余的。解决重复索引和冗余索引的方法就是删除这些索引。

索引失效的情况

1. like '%xx'
    select * from tb1 where name like '%cn';
2.使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
3. or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
4. 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
5. 不等于号 !=  
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
6. 大于号 > 
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
7. order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
8. 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
    当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

---

B 树与 B+ 树简明扼要的区别

定义一条数据记录为一个二元组[key,data]：

​ key为记录的键值，key唯一

​ data为数据记录除 key 外的数据

B树

	**每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。**

B+树

	**只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。**

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

主要原因：一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

InnoDB

data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

MyISAM

data存的是索引(数据的地址)。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

补充：

（1）在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

（2）MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

---

Hash 索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。

限制：

• 数据不是按照索引值顺序存储的，无法排序。
• 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
• 只支持等值比较查询，不支持范围查询。

自适应哈希索引

自适应哈希索引是 InnoDB 的一个特殊功能，当它注意到某些索引被使用得很频繁时，会在内存中创键哈希索引，让 B-Tree 索引也具有哈希索引的一些优点。

---

覆盖索引

指一个索引包含所有需要查询字段的值，不再需要根据索引回表查询。

优点：

① 索引条目通常远小于数据行大小，如果只需要读取索引可以减少数据访问量

② 索引按照列值顺序存储，对于 IO 密集型的范围查询会比随机从磁盘读取每行数据的 IO 少得多。

③ 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 很有帮助。InnoDB 的二级索引在叶子节点保存了行的主键值，如果二级主键能覆盖查询那么可以避免对主键索引的二次查询。

---

Like 查询能否用到索引

使用 like x% 查询是可以用得到索引的，而使用 like %x% 和 like %x 查询是用不到索引的。

那么使用 like %x% 和 like %x 查询为什么用不到索引？

因为索引是一种有序的 B+ Tree 数据结构，叶子节点都是按照顺序从左向右排的，如果使用 like %x% 和 like %x 查询的话，不知道开头是哪个，就会去进行全表扫描。

---

联合索引（abc）命中规则

AND

只要用到了最左侧a列，和顺序无关,都会使用索引

a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引

c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

OR 不使用索引

a = 1 AND b = 2 OR c = 3 ; 未使用索引
a = 1 OR b = 2 AND c = 3 ; 未使用索引
a = 1 OR b = 2 OR c = 3 ; 未使用索引

最左侧的‘a’列 被大于，小于，不等于比较的 ，使用 range 索引

a > 1 AND b = 2 AND c = 3 ; 使用range索引
a < 1 AND b =  2 AND c = 3 ; 使用range索引
a > 1 ; 使用range索引
a <> 1 AND b = 2 AND c = 3 ; 使用range索引

最左侧 a=xx，后面列大于小于无所谓，都使用索引（但后面必须 and and ）

a = 1 AND b < 2 AND c = 3 ; 使用索引
a = 1 AND c = 2 AND b < 3 ; 使用索引
a = 1 AND b < 2 ; 使用索引
a = 1 AND b <> 2 AND c = 3 ; 使用索引
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 ; 未使用索引

ORDER BY
a = xx，后面 order 无所谓 都 使用索引 （和最上面的最左匹配一样）

a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b ， ORDER BY c ，ORDER BY d ; 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d ; 使用abc索引

b = xx，不使用索引

b = 1 ORDER BY a; //ORDER BY b 都 未使用索引

---

为什么主键通常建议使用自增 id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

三个范式

第一范式: 每个列都不可以再拆分.

第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分.

第三范式: 非主键列只依赖于主键,不依赖于其他非主键.

左外连接、右外连接与内连接的区别

1. 内连接，显示两个表中有联系的共有数据，没有联系的数据不显示。
2. 左链接，以左表为参照，显示数据，右表数据少了补NULL值，多了不显示。
3. 右链接，以右表为参照，显示数据，左表少了补NULL，多了不显示。

---

四. 锁

MySQL 支持多用户同时读写吗？

mysql 支持多用户同时读写，mysql 是一个关系型数据库管理系统。

1. mysql中的锁机制就是为了解决共享资源的问题，当我们需要对一个数据读取的时候就需要获取读锁同理当我们需要对数据进行修改的时候就需要获取写锁，读取和写锁之间是互斥的。
2. mysql为了从不同程度上控制资源读写，相应引入全局锁，表级锁，行锁以及不同的锁类型。
3. mysql支持集群，一主多备，多主多备等，这样都能支持多用户同时读写

数据库并发场景

所有系统的并发场景都是三种，对于数据库而言为：

读-读：不存在任何问题，也不需要并发控制。
读-写：有线程安全问题，可能会造成事务隔离性问题，也就是脏读，不可重复读，幻读。
写-写：有线程安全问题，会存在更新丢失问题：第一类更新丢失（回滚丢失），第二类更新丢失（覆盖丢失）。

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：
​ 1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
​ 2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

共享锁和排它锁

分为共享锁（S 锁）和排它锁（X 锁），也叫读锁和写锁。

• 读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。
• 写锁是排他的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。

写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。

---

乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

---

表锁和行锁

表锁和行表，开销小，不会出现死锁，但锁冲突概率高、并发度低。

行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，InnoDB 实现了行锁。

---

数据库死锁

当多个事务以不同顺序锁定资源，或者同时锁定同一个资源时都可能产生死锁。

解决：

• InnoDB 会自动检测，并使一个事务回滚，另一个事务继续。
• 设置超时等待参数 innodb_local_wait_timeout。

避免：

• 不同业务并发访问多个表时，约定以相同的顺序访问。
• 在事务中，如果要更新记录，使用排它锁。

五. 存储过程、函数、视图、触发器

存储过程

存储过程是由流控制和 SQL 语句组成的程序，经过编译和优化后存储在数据库服务器中，使用时只需要调用即可。

好处

• 使用流控制语句编写，具有较强的灵活性。
• 保证数据安全性，使没有权限的用户间接存取数据库。
• 保证数据完整性，使一组相关动作在一起执行。
• 调用存储过程前，数据库已经对其进行了语法分析，并给出优化执行方案，可以改善 SQL 语句的性能。
• 降低网络通信量，减小负载。

---

函数

由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。

和存储过程的区别：

• 存储过程的参数有 in，out，inout 三种，存储过程声明时不需要返回类型；函数参数只有 in，需要描述返回类型，且函数中必须包含一个有效的 return 语句。
• 存储过程可以有 0 或多个返回值，适合做批量插入、更新；函数有且仅有一个返回值，针对性更强。
• 存储过程可以返回参数，如记录集，函数只能返回值或者表对象。
• 存储过程一般作为独立部分执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，所以在查询中位于from 关键字后面，SQL 语句中不可以含有存储过程。

---

触发器

触发器是一段能自动执行的程序，和存储过程的区别是，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。触发器在对某一个表或者数据进行操作时触发，例如进行 UPDATE、INSERT、DELETE 操作时，系统会自动调用和执行该表对应的触发器。触发器一般用于数据变化后需要执行一系列操作的情况，比如对系统核心数据的修改需要通过触发器来存储操作日志的信息等。

---

视图

视图本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

六. 主从复制

为什么需要主从复制？

• 数据备份。
• 读写分离。
• 架构扩展，业务量越来越大，读写频率过高时，单机无法满足。

什么是主从复制？

指数据可以从一个mysql数据库服务器主节点复制到一个或者多个从节点。mysql使用异步复制方式。

主从复制过程

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
  

MySQL中有哪些日志？

mysql server层：

1. binlog (二进制日志)： 记录数据库的变化情况，必要时可以使用二进制日志恢复数据库。
2. error log (错误日志)：记录Mysql实例每次启动、停止的详细信息，以及Mysql实例运行过程中产生的警告或者错误信息。默认开启，无法关闭。
3. general log (普通查询日志)：记录了Mysql运行的所有操作，无论这些操作执行成功与否。另外还包括一些事件，例如客户端连接断开的一些信息。默认不开启。
4. slow query log (慢日志)：记录执行时间过程和没有使用索引的查询语句。

innodb层：

5. redo log (重做日志)：记录的是物理数据页面的修改的信息。
6. undo log (回滚日志)：在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的。这一点是不同于redo log的。
7. relay log (中继日志)：从库同步主库时的一个中间文件。

七. 优化

查询执行流程

① 客户端发送一条查询给服务器。

② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。

③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。

④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。

⑤ 将结果返回给客户端。

查询优化器？

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个

EXPLAIN 的字段

执行计划是 SQL 调优的重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC。EXPLAIN 的指标及含义如下：

指标名	含义
id	SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。
select_type	查询中每个 SELECT 子句的类型，例如 SIMPLE 表示简单查询，PRIMARY 表示复杂查询的最外层查询。
type	访问类型，性能由差到好：ALL、index、range（至少达到）、ref（要求）、const、system、NULL。
possible_keys	查询时可能用到的索引，列出大量可能索引时意味着备选索引太多。
key	查询时实际使用的索引，没有则为 NULL。
key_len	所用索引字段的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。
ref	表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows	估算找到所需记录所需要读取的行数。
Extra	额外信息，例如 Using temporary 表示需要使用临时表存储结果集；Using index 表示只需使用索引就可满足查询要求，说明表正在使用覆盖索引。

优化查询

• 避免全表扫描

  考虑在 where 和 order by 涉及的列上建立索引，in 和 not in 也要慎用，尽量用 between 取代。

• 优化 COUNT 查询

  count 可以统计列的数量，统计列值时要求列非空；COUNT 还可以统计行数，当 MySQL 确定括号内的表达式不可能为 NULL 时，实际上就是在统计行数。当使用 COUNT(*) 时，会忽略所有列而直接统计行数。

  某些业务场景不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。

• 避免子查询

  在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。

• 禁止排序

  当查询使用 GROUP BY 时，结果集默认会按照分组的字段进行排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。

• 优化分页

  在偏移量非常大的时候，需要查询很多条数据再舍弃，代价非常高。最简单的优化是尽可能地使用覆盖索引扫描，而不是查询所有的列，然后做关联操作再返回所需的列。还有一种方法是从上一次取数据的位置开始扫描，避免使用 OFFSET。

• 优化 UNION

  MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。

• 使用用户自定义变量

  用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。

MySQL 优化

MySQL 数据库常见的优化手段分为三个层面：SQL 和索引优化、数据库结构优化、系统硬件优化等。

1. SQL和索引优化
此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率，具体内容如下。

① 使用正确的索引

② 查询具体的字段而非全部字段

③ 优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，但 Join 语句并不会创建临时表，因此性能会更高。

④ 注意查询结果集

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where id in (select id from B);

⑤ 不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率。

⑥ 适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。

2. 数据库结构优化
① 最小数据长度
一般说来数据库的表越小，那么它的查询速度就越快，因此为了提高表的效率，应该将表的字段设置的尽可能小，比如身份证号，可以设置为 char(18) 就不要设置为 varchar(18)。

② 使用最简单数据类型
能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高。

③ 尽量少定义 text 类型
text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率。

④ 适当分表、分库策略
分表和分库方案也是我们经常说的垂直分隔（分表）和水平分隔（分库）。

分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率。

分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率。

3. 硬件优化
MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存。

① 磁盘

磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率。

磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样。

② 网络

保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率。

③ 内存

MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率。

八. 数据类型

MySQL 中有哪些常见的数据类型：

• 数值类型：tinyint,smallint,mediumint,int/integer,bigint,float,double,decimal

• 日期/时间类型:datetime,date,timestamp,time,year

• 字符串类型：char,varchar,binary,varbinary,blob,text,enum,set

类型	大小（字节）	范围（有符号）	范围（无符号）	用途
tinyint	1	(-128，127)	(0，255)	小整数值
smallint	2	(-32 768，32 767)	(0，65 535)	大整数值
int	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8	(-9223372036854775808，9223372036854775807)	(0，18 446 744 073 709 551 615)	极大整数值
float	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
double	8	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYYY	年份值
char	0-255			定长字符串
varchar	0-65535			变长字符串
tinytext	0-255			短文本字符串
text	0-65 535			长文本数据
longtext	0-4 294 967 295

int(3) 和 int(10) 的区别？

声明字段是int类型的那一刻起，int就是占四个字节，一个字节 8 位，也就是4*8=32，可以表示的数字个数是 2的 32 次方(2^32 = 4 294 967 296个数字)。

有符号型：-2147483648 ~ 2147483647

无符号型： 0 ~4294967295

int(m) zerofill，加上zerofill后 m 才表现出有点效果，比如 int(3) zerofill,你插入到数据库里的是10，则实际插入为010，也就是在前面补充加了一个0.

如果 int(3) 和 int(10) 不加 zerofill，则它们没有什么区别。m不是用来限制 int 列内保存值的范围的，int(M)的最大值和最小值与有无符号型有关。

char 和 varchar 的区别

1．char 是固定长度，varchar 是可变长度。

字段b：类型char(10)， 值为：abc，存储为：abc （abc+7个空格）

字段d：类型varchar(10)， 值为：abc，存储为：abc （自动变为3个的长度）

2．char 的效率比 varchar 的效率高。

何时用 char ，何时用 varchar ？

char 存取效率更高，varchar 更节省空间。
varchar更节省空间，但是如果一个varchar列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用char 代替varchar 会更好一些。

varchar(100) 能存的汉字数？
这和mysql的版本有关系：

• 4.0 版本以下，varchar(100)，代表 100 字节。UTF8 编码下存放汉字时，只能存 33 个（每个汉字 3 字节） 。
• 5.0 版本以上，varchar(100)，代表 100 字符。即：无论存放的是数字、字母还是 UTF8 汉字（每个汉字3字节），都可以存放 100 个。

DATETIME 和 TIMESTAMP 的区别

DATETIME 能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。

TIMESTAMP 和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。

九.场景查询

查询前三名（包括并列）

1. 准备数据

CREATE TABLE student(
	name 		VARCHAR(10),
	subject		VARCHAR(10),
	score		INT(10)
	);

# 插入数据
INSERT INTO student VALUES
	("张三","java",71),
	("李四","java",82),
	("王五","java",90),
	("赵六","java",98),
	("孙七","java",90),
	("张三","python",81),
	("李四","python",92),
	("王五","python",93),
	("赵六","python",97),
	("孙七","python",70);

思路：查询各科成绩前三名，前三名都有一个特点，比他们成绩高的人数小于3，成绩就是前三名。

SELECT T1.*
FROM student T1
LEFT JOIN 
    (SELECT DISTINCT subject,score FROM student) T2
    ON T1.subject = T2.subject AND T1.score < T2.score
GROUP BY  name,subject,score
HAVING COUNT(1) < 3
ORDER BY  subject,score DESC

count() 语法：

（1）count(*) — 包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为 NULL 的记录。

（2）count(1) — 忽略所有列，1 表示一个固定值，也可以用 count(2)、count(3) 代替，在统计结果的时候，不会忽略列值为 NULL 的记录。

（3）count(列名) — 只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为 NULL的记录（不包括空字符串和 0 ），即列值为 NULL 的记录不统计在内。

（4）count(distinct 列名) — 只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为 NULL 的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

举例：
查询 user 表中，年龄重复次数大于等于 3 的 age，并输出 age 的值

SELECT
    age 
FROM
    user
GROUP BY
    age 
HAVING
    count(age) >= 3