mysql总结

超、凢脫俗 2022-10-16 10:27 165阅读 0赞

###    一、存储引擎 ###

（1）两种存储引擎

MyISAM：专注性能，主键索引和辅助索引是独立的  
InnoDB：专注事务，支持事务、行锁、外键、聚集索引

（2）MyISAM 和 InnoDB 的区别

1、InnoDB支持事务，MyISAM不支持

2、InnoDB支持外键，而MyISAM不支持

3、InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高

4、InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5、Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高

### 二、主键与索引 ###

**（1）索引类型**

B+树索引、Hash索引

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB: 其数据文件本身就是索引文件。其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，+这也是和MyISAM不同的地方。

**（2）联合索引**

1、联合索引是两个或更多个列上的索引，只能左侧匹配。

2、利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

**（3）什么情况下应不建或少建索引**

1、表记录太少

2、经常插入、删除、修改的表

3、数据重复且分布平均的表字段

4、经常和主字段一块查询但主字段索引值比较多的表字

**（4）使用数据索引能提高效率**

1、数据索引的存储是有序的

2、在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

3、极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

**（5）B+树索引和哈希索引的区别**

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的。

1、哈希索引的优势

等值查询，没有大量重复键值（哈希碰撞问题），哈希索引具有绝对优势

2、哈希索引不适用的场景

不支持范围查询

不支持索引完成排序

不支持联合索引的最左前缀匹配规则

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引

**（6）B树和B+树**

1、区别

1）B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。

2）B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接

所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

2、B+比B树更适合实际应用中操作系统的文件索引和数据库索引

1）B+的磁盘读写代价更低。

B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小。

2）B+-tree的查询效率更加稳定。

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

**（7）主键生成方式**

Mysql自增长主键策略

时间戳相关生成策略（数据库无关，移植性较好 ，但长度太长。雪花算法）

UUID、GUID（移植性较好，但没有排序，无法保证趋势递增、查询的效率较低）

每次取主键最大值+1做为新的主键（主键长度可控，移植性较好 ，并发写会造成主键冲突）

单独建一个存放主键的表（并发问题、性能影响较大）

**（8）为什么用自增列作为主键**

1、使用自增列(INT/BIGINT类型)做主键，写入顺序是自增的，和B+数叶子节点分裂顺序一致

2、不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；

### 三、数据库锁 ###

**（1）锁的粒度划分**

表级锁、行级锁、页级锁

MyISAM采用表级锁(table-level locking)。

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

**（2）表级锁**

Mysql中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

**（3）行级锁**

Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

*1、两阶段锁协议*

InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放

*2、InnoDB的三种行锁算法*

Record lock：单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：临键锁，record+gap 锁定一个范围，包含记录本身

*3、优缺点*

优点：

1）当在许多线程中访问不同的行时只存在少量锁定冲突。

2）回滚时只有少量的更改

3）可以长时间锁定单一的行。

缺点：

1·）比页级或表级锁定占用更多的内存。

2）当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

3）如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

4）用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

**（4）MVCC（行锁）**

*1、锁实现*  
LBCC基于锁的并发控制、MVCC多版本并发控制

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

MVCC只在读提交和可重复读隔离级别下工作

*2、MVCC最大的好处*

读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

*3、MVCC读操作（RR级别下防止幻读）*

快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）

当前读 (current read)（临键锁）：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录

**（5）死锁和死锁检测**

并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

死锁两种处理策略：

1、直接进入等待，直到超时。这个超时时间可以通过参数 innodb\_lock\_wait\_timeout 来设置。  
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑。

**（6）锁类型**  
共享/排它锁(Shared and Exclusive Locks)：S、X  
意向锁(Intention Locks)：表级锁，IS、IX  
记录锁(Record Locks)：行级锁  
间隙锁(Gap Locks)：行级锁  
临键锁(Next-key Locks)：行级锁、记录锁与间隙锁的组合  
插入意向锁(Insert Intention Locks)：间隙锁(GapLocks)的一种  
自增锁(Auto-inc Locks)：表级锁

IS与IX兼容，X与IS、IX互斥，S与IX互斥、与IS兼容

### 四、事务 ###

（1）事务特性ACID

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性： 执行事务前后，数据保持一致；

隔离性： 并发访问数据库时，一个用户的事物不被其他事物所干扰，各并发事务之间数据库是独立的；

持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响。

（2）InnoDB四种事务隔离级别

读未提交(脏读)、读提交(不可重复读)、可重复读(幻读)、串行化

Mysql 默认采用的 REPEATABLE\_READ隔离级别

Oracle 默认采用的 READ\_COMMITTED隔离级别.

### 五、表分区 ###

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

（1）表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

（2）表分区的好处

1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

4、避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

（3）分区表的限制因素

一个表最多只能有1024个分区

MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

分区表中无法使用外键约束

MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

（4）如何判断当前MySQL是否支持分区？

命令：show variables like '%partition%' 运行结果:

have\_partintioning 的值为YES，表示支持分区。

（5）MySQL支持的分区类型

RANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。

HASH分区 ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

KEY分区 ：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的

### 六、不走索引 ###

1、在索引字段上运算、函数操作

2、隐性转换、JOIN操作主键和外键的数据类型不相同

3、非左Like

4、OR操作符，其中一个条件没有索引、非主键列和不同索引（取决于MySQL查询优化器）

5、GROUP BY、ORDER BY子句子句，WHERE条件不包含索引列

6、联合索引，没有使用索引前缀，使用了联合索引的全部列，但索引键不是AND操作

反向查询区分情况

7、not , not in, not like, <>, != ,!>,!< ，不包含null

8、null值

### 七、MySQL优化方法 ###

1、IN包含的值不应过多  
2、SELECT语句指明字段  
3、只需要一条数据，limit 1  
4、排序没有用到索引，尽量少排序  
5、限制条件中其他字段没有索引，少用or  
6、用union all代替union  
7、不使用ORDER BY RAND()  
8、区分in和exists， not in和not exists  
9、分段查询  
10、避免where 子句中对字段进行 null 值  
11、不建议使用%前缀模糊查询  
12、where子句中对字段进行表达式  
13、避免隐式类型转换  
14、联合索引来说，要遵守最左前缀法则  
15、联合索引范围查询索引字段失效  
16、JOIN优化：尽量使用inner join，避免left join、小表去驱动大表、STRAIGHT\_JOIN用索引

**MySQL优化**

1.  开启查询缓存，优化查询
2.  explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的
3.  当只要一行数据时使用limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据
4.  为搜索字段建索引
5.  使用 ENUM 而不是 VARCHAR。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是VARCHAR
6.  Prepared StatementsPrepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
    
    Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
7.  垂直分表
8.  选择正确的存储引擎

四、CHAR 和 VARCHAR

1、CHAR 和 VARCHAR 类型在存储和检索方面有所不同  
2、CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255 当 CHAR  
值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。