Mysql常见考点

爱被打了一巴掌 2022-02-04 14:34 273阅读 0赞

##  ##

1.  MySQL架构组成  
    2．MySQL备份和恢复  
    3．MySQL查询和慢查询日志分析  
    4．MySQL索引优化  
    5．MySQL存储引擎优化  
    6．MySQL锁机制优化  
    7．MySQL高可用设计  
    8．MySQL集群

## SQL分类：DDL DML DCL ##

一、DDL（数据库定义语言）  
定义不同的数据段、数据库、表、列、索引等数据库对象，常用语句关键字：create drop alter等

二、DML（数据操作语句）  
1、用于添加、删除、更新和查询数据库记录，并检测数据完整性，常用语句关键字：insert delete update select等

三、DCL（数据控制语句）  
控制不同数据段之间的许可和访问级别，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，语句关键字：grant revoke等

## 索引优化 ##

mysql索引的类型：

<table> 
 <thead> 
  <tr> 
   <th> 
    <div> 
     <p>索引</p> 
    </div></th> 
   <th> 
    <div> 
     <p>MyISAM引擎</p> 
    </div></th> 
   <th> 
    <div> 
     <p>InnoDB引擎</p> 
    </div></th> 
   <th> 
    <div> 
     <p>Memory引擎</p> 
    </div></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <p>B-Tree 索引</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
  </tr> 
  <tr> 
   <td> 
    <div> 
     <p>HASH 索引</p> 
    </div></td> 
   <td> 
    <div> 
     <p>不支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>不支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
  </tr> 
  <tr> 
   <td> 
    <div> 
     <p>R-Tree 索引</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>不支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>不支持</p> 
    </div></td> 
  </tr> 
  <tr> 
   <td> 
    <div> 
     <p>Full-Text 索引</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持</p> 
    </div></td> 
   <td> 
    <div> 
     <p>支持5.6＋</p> 
    </div></td> 
   <td> 
    <div> 
     <p>不支持</p> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

B-TREE索引类型包含如下索引类型：  
**普通索引**  
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：  
（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1, 列名2, …);  
（2）修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1, 列名2, …);  
（3）创建表时指定索引：CREATE TABLE 表名 ( \[…\], INDEX 索引名 (列名1, 列名2, …) );  
组合索引：INDEX 索引名 (列名1, 列名2, …)为了更多的提高mysql效率可建立组合索引，**遵循”最左前缀“原则**，  
比如，INDEX ageSexIndex (age,sex, …)；select \* from student where age=12 and sex=1;这个语句优先匹配age=12的数据，这个原则**有利于提高索引速度**

**UNIQUE索引**  
表示唯一的，不允许重复的索引，如果该字段信息保证不会重复，例如身份证号，可设置为unique：  
（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名1, 列名2, …);  
（2）修改表：ALTER TABLE 表名 ADD UNIQUE 索引名 (列名1, 列名2, …);  
（3）创建表时指定索引：CREATE TABLE 表名( \[…\], UNIQUE 索引名 (列名1, 列名2, …) );  
**主键索引（PRIMARY KEY）**  
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。  
（1）主键一般在创建表的时候指定：CREATE TABLE 表名( \[…\], PRIMARY KEY (列名1, 列名2, …) );  
（2）也可以通过修改表的方式加入主键：ALTER TABLE 表名 ADD PRIMARY KEY (列名1, 列名2, …);  
每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）  
注：不能用CREATE INDEX语句创建PRIMARY KEY索引

**数据库中主键和唯一索引的区别？**  
1.主键为一种约束，唯一索引为一种索引，本质上就不同。  
2.主键在表中只能有一个，唯一索引可以有多个。  
3.主键创建后一定包含唯一性索引，而唯一索引不一定就是主键。  
4.主键不能为null,唯一索引可以为null.  
5.主键可以被其它表引用，唯一索引不能。  
6.主键和索引都是键，主键是逻辑键，索引为物理键，即主键不实际存在。

**聚集索引和非聚集索引的根本区别**  
根本区别  
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

聚集索引  
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引  
非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

**MySQL只对一下操作符才使用索引：<、<=、=、>、>=、between、in, 以及某些时候的like(不以通配符%或\_开头的情形)。**

1 较频繁的作为查询条件的字段应该创建索引  
2 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件(比如性别:男，女，做索引毫无意义而且浪费空间)  
3 更新非常频繁的字段不适合创建索引

**索引的优势**  
（1）减少查询IO;  
（2）优化等值查询或者范围查询；也即是范围查询和等值查询都是会走索引的；  
（3）利用有序特性（例如：order by/group by/distinct/max/min等函数）；这些操作都是利用排序技术来实现的，而索引天然就是有序的，因此使用到order by等有序操作时，对相关字段建立索引会提高效率。

**如何用好索引**  
（1）依据where查询条件建立索引；  
（2）使用联合索引，而不是多个单列索引；  
例如：select \* from tab\_a where b=? and c=?这个SQL，对b c字段建立联合索引的效率比单列的索引效率更高。  
（3）联合索引中索引的顺序根据区分度排，区分度大的放在前面。区分度是指字段值的种类，字段值种类越多的字段要放在前面，例如：idx\_smp(name,gender)的效率要比idx\_smp(gender,name)的效率高  
（4）联合索引能为前缀单列、复列查询提供帮助；  
例如：  
有idx\_smp(a,b,c)这样的索引，where a=?或者where a=? and b=?都可以使用该索引，但是where c=?就无法使用该索引。  
（5）同样的，要合理创建联合索引，避免冗余  
例如建立了idx\_smp（a,b,c）就不需要建立idx\_smp(a)、idx\_smp(a,b)索引了。  
（6）order by group by distinct等需要排序的操作，在没有索引的大数据量情况下需要排序，对IO和CPU性能消耗很大。如果有类似排序需求，则需要对相关字段建立索引，这样利用索引的有序特性不需要排序，直接按着索引顺序扫描即可。  
（7）select …where … like ‘%xx’;这种%放在头部的，是无法走索引的。  
（8）select ＊ 不建议使用，因为会读取大量数据，也不利于使用索引覆盖技术。索引字段能够完全在索引中获取， 就不要使用select ＊（因为会导致回表），无法完整在索引中获取，也是建议select具体字段。

**查看是否使用了索引：explain命令查看工具**  
（1）explain是确定一个查询如何走索引的最简便有效的方法；  
（2）关注的字段值：  
——id字段：表示查询中执行select子句或操作表的顺序。  
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中， id值越大，优先级越高，越先执行。  
——type字段：查询access的方式；  
type=all表示全表扫描数据，不走索引；  
type=index表示full index scan，和all的区别是index类型只遍历索引树。  
——key字段：本次查询最终选择使用哪个索引，NULL表示未使用索引；  
——key\_len字段：选择的索引使用的前缀长度或者整个长度（判断联合索引的使用情况）；  
——rows字段：可以理解为查询逻辑读，需要扫描过的记录行数；  
——extra字段：额外信息，主要指的fetch data的具体方式；  
extra=using tmporary表示mysql需要使用临时表来存储结果集，常见于排序和分组查询。  
extra=using filesort表示文件排序，需要对其优化。mysql中无法利用索引完成的排序操作称为“文件排序”。  
using tmporary可能是内存临时表也可能是磁盘临时表，如果临时表大小超过tmp\_table\_size大小才会产生基于磁盘的临时表，也就是说，只是通过explain执行计划是无法查看是否用来磁盘临时表的，如果show processlist查看的线程有“Created\_tmp\_disk\_tables”关键字才能代表是用使用了磁盘临时表

explain的一些使用建议：  
（3.1）对不确定执行计划的关键语句上线前务必explain；  
（3.2）type为all的要格外注意，避免全表扫描；  
（3.3）key\_len只能用很少一部分前缀的，要注意索引字段顺序等；  
（3.4）extra里看到using filesort和using tmporary都要尽量优化，这两种fetch方式不应该出现在任何执行频繁的关键语句中。

强制使用索引hint：

## 存储引擎 ##

一、主要存储引擎

5.5以后的版本，默认存储引擎从myisam改成了innodb；线上推荐都用innodb  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pqY2phdmE_size_16_color_FFFFFF_t_70]

### 二、innodb存储引擎 ###

（一）结构

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pqY2phdmE_size_16_color_FFFFFF_t_70 1]

INNODB存储引擎大致分三部分，图中已经（1）（2）（3）标出。至于MYSQL Server层暂不管它，就是SQL接口、解析器、优化器、缓冲池的那层组件。  
上图中：  
（1）是缓存层，由一个大的innodb buffer pool和很多个其他小的内存组件组成，用来缓冲数据的，innodb的数据读取写入不是直接操作文件，而是从文件加载到缓存，在缓冲里做操作，再flush到磁盘文件‘；  
（2）是各种后台线程，例如IO线程、日志线程、监控线程等；  
（3）是各种数据文件层，例如innodb的数据文件、redo log等；

（二）**innodb的特点**  
innodb存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比myisam存储引擎，innodb写的处理效率差一些且会占用更多的磁盘空间以保留数据和索引。  
innodb通过使用MVCC来获取高并发性，且实现SQL标准的4种隔离级别，同时使用一种被称为next-key locking（间隙锁）的策略来避免幻读（phantom）现象。除此之外innodb存储引擎还提供插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能技术。

1、innodb支持事务，事务ACID特性完整支持：  
（1）原子性（A）：通过回滚段失败回滚来保证原子性，也就是unlog log日志回滚保证原子性；  
（2）一致性（C）：通过支持主外键来保证一致性；  
（3）隔离性（I）：通过事务版本+回滚段=MVCC（多版本并发），也即是通过MVCC（多版本并发）机制来保证隔离性；  
（4）持久性（D）：通过事务日志（redo log）来保证持久性；

（三）事务隔离级别  
1、支持四种不同的事务隔离级别，默认的事务隔离级别是REPEATABLE-READ（可重复读）：

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交（read-uncommitted）</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>不可重复读（read-committed）</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>可重复读（repeatable-read）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>串行化（serializable）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

（四）INNODB存储引擎的锁  
1、支持行级锁，写不阻塞读；不同行间的写相互不阻塞；并发性能好  
2、行锁是加在索引上的（或者说行级锁是在索引上实现的），如果更新字段上没有索引则行锁会转换为表级锁。

（五）INNODDB数据块缓存池  
1、数据的读写需要经过缓存（数据的读写不是直接读写磁盘文件，而是先将文件数据加载到内存，再进行读写（更新后产生脏页），再根据配置的策略定期将脏页刷新到磁盘）  
2、innodb的数据是以整页（page,16K）为单位，读取到缓存中；  
3、缓存中的数据以LRU策略换出；  
4、IO效率高

（六）INNODB数据文件存储结构  
1、innodb存储类型的表，是索引组织表（聚簇表），它的整个表数据就是一个索引表。表数据是根据主键排序，如果没有索引则检查唯一索引键，用唯一键做索引；如果没有主键也没有唯一索引键，则会自动内部分配一个rowID来做索引。  
2、innodb结构的表是索引结构的，数据节点每页是16K。  
3、innodb结构的表是索引组织表（聚簇表），根据主键寻址速度很快。  
主键值递增的insert插入效率较好，主键随机insert插入操作效率差。因此，innodb表必须制定主键，建议使用自增数字。

（七）INNODB数据持久化与事务日志  
1、事务日志（redo log）实时持久化，  
2、内存变化数据（脏数据）增量异步刷出到磁盘文件；  
3、实例故障靠重放日志恢复；  
4、性能好，可靠，恢复快

### 二、MYISAM存储引擎 ###

（一）特点  
1、前身叫ISAM存储引擎；  
2、MYISAM存储引擎的数据组织形式是一种堆表，和索引组织表相区别（innodb的数据组织形式是索引组织表）。  
例如插入一条数据id=2(id为主键，表里已经存在id=1和id=3数据)，  
（1）myisam存储引擎：如果插入id=2,数据的插入位置和id=1 id=3无关系，也就是无序的；  
堆表的插入特性是无序的，其他的数据库例如oracle PostgreSQL都是以堆表形式插入数据的；  
（2）INNODB存储引擎：如果插入id=2，会插入到id=1和id=3之间，因为innodb是索引组织表是有有序的；  
索引组织表的最大特点是根据主键去查询效率非常快。  
3、MYISAM存储引擎不支持事务；  
4、myisam存储引擎的缓存（）不缓存数据，只缓存索引。数据缓存是交给操作系统的内存来缓存的。  
这种会有问题：如果查询一个大表，会消耗大量操作系统内存，如果表中碎片很大可能会浪费大量操作系统内存。  
5、锁粒度较大：使用的是读写锁（读的时候不允许写，写的时候不允许读，只有读的时候允许读）  
6、数据文件可以直接 拷贝，偶尔可能会用上。但是线上现在不建议使用该引擎了  
因为：  
a、不支持事务；  
b、锁粒度太大并发性能差；  
c、只缓存索引不缓存数据，对系统内存影响较大有风险；  
d、8.0取消了MYISAM引擎；

### 三、MEMORY存储引擎 ###

1、数据全内存存放，无法持久化；  
2、性能较高；  
3、不支持事务；  
4、适合偶尔作为临时表使用；  
create tmpporary table tmp(…)

### 四、BLACLHOLD存储引擎 ###

1、数据不作任何存储；  
2、利用mysql replicate，充当日志服务器，在mysql replicate环境里充当代理主。

一般用BLACKHOLD作为日志服务器，记录数据库的所有变更。向其他从服务器分发日志。

### 五、TOKUDB存储引擎 ###

1、分形树存储结构；  
2、支持事务；  
3、行锁；  
4、压缩效率较高；  
5、适合大批量insert的场景（吞吐量/io效率高）  
6、备份比较麻烦，只有企业版有在线备份工具，社区版没有在线备份工具；

### 六、MYSQL CLUSTER ###

1、多主分布式集群；  
2、数据节点间冗余，高可用；  
3、支持事务；  
4、设计上易于扩展；  
5、面向未来，线上慎用；

### 问题1、为什么Innodb表需要主键？ ###

1）innodb表数据文件都是基于主键索引组织的，没有主键，mysql会想办法给我搞定，所以主键必须要有；
      2）基于主键查询效率高；
      3）其他类型索引都要引用主键索引；

### 问题3、为什么不建议Innodb表主键设置过长？ ###

因为辅助索引都保存引用主键索引，过长的主键索引使辅助索引变得过大；

### 问题2：为什么建议InnoDB表主键是单调递增？ ###

如果InnoDB表主键是单调递增的，可以使用改进后的B+tree分裂策略，显著减少B-Tree分裂次数和数据迁移，从而提高数据插入效率。
    
               不仅如此，它还大大提高索引页空间利用率。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pqY2phdmE_size_16_color_FFFFFF_t_70]: /images/20220204/e5dcb6fda5fa4c83b4e48951d0c3b774.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pqY2phdmE_size_16_color_FFFFFF_t_70 1]: /images/20220204/997e87cb06ee449fa08961360c615e97.png