MySQL基础知识、索引、锁、InnoDB和MyISAM-Java基础

喜欢ヅ旅行 2021-12-03 05:25 299阅读 0赞

1、MySQL基础

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70][]

2、MySQL优化

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 1][]

### 1、索引 ###

MySQL基本存储结构是页，记录存在页里面（ex.InnoDB）。

加速数据库的检索速度；如果表常有插入、更新、删除操作就不要建立索引。索引会降低插入、删除、修改等维护任务的速度。

索引需要占用物理和数据空间；

索引的最左最右匹配原则；

索引有聚集索引和非聚集索引；

MySQL支持Hash索引和B+树索引；

*  数据结构考虑：B+树、hash索引；
 *  物理存储角度：聚集索引、非聚集索引；
 *  逻辑角度：普通索引或单例索引、唯一索引、多列索引、复合索引、全文索引、主键索引、
 *  各个数据页可以组成一个双向链表；

每个数据页中的记录又可以组成一个单向链表；

*  每个数据页会为存储它里面的记录生成一个页目录，通过主键查找某个记录的时候，使用二分法快速定位；
 *  以其他列（非主键）作为搜索条件，只能从最小记录开始遍历单链表的每条记录；

普通的select查找就是：

定位记录所在的页，需要遍历双向链表，找到页；

从所在页内中查找相应的记录；由于不是根据主键查询，只能遍历所在的页的单链表；所以慢；

**1）索引提高检索速度**

索引是将**无序数据变成了有序（相对）**来实现加快查询速度。没有索引是遍历双向链表来定位对应的页，建立索引之后通过目录定位页。

其实底层是B+树，实现方式B+树，使得我们能够快速查找。

**2）索引降低增删改的速度**

B+树是平衡树，是一个空树或者左右两个子树的高度差绝对值不超过1；检索复杂度O(longn)。

增删改会破坏B+树原有结构，要保持平衡，就必须旋转等操作，增加了额外开销，使得速度变慢；

**3）哈希索引**

哈希索引就是采用一定的**哈希算法**，把键值换算成新的哈希值，检索的时候不需类似B+树那样从根节点到叶子进行逐级查找，只需要一次哈希算法，立即定位到相应的位置，速度非常快；

本质就是：**键值换算成新的哈希值**，根据哈希值进行定位；

局限：哈希索引页没办法利用索引完成排序；

*  不支持最左匹配原则；
 *  大量重复键值情况下，哈希索引效率也是极低的-》哈希碰撞；
 *  不支持范围查找；

**4）InnoDB支持哈希索引**

主流支持B+树多，InnoDB自适应哈希索引；

**5）聚集和非聚集索引**

聚集索引就是以主键创建的索引；

非聚集就是以非主键创建的索引；

区别：

*  聚集索引在叶子节点存储的是**表中的数据**；
 *  非聚集索引在叶子节点存储的是**主键和索引列**；
 *  使用非聚集索引查询出数据时，拿到叶子上的主键再去查找到想要查找的数据。

非聚集索引也叫二级索引，非聚集索引未必是单列的，可以多列创建索引。

创建多个单例（非聚集）索引时，会生成多个索引树（过多创建索引会占用磁盘空间）

覆盖索引：

*  不是聚集索引，叶子节点存储的时主键+列值；
 *  最终还要回表，通过主键再查找一次，会较慢；
 *  覆盖索引就是把要查询到的列和索引对应，实现不回表；

**6）最左匹配原则**

列的排序顺序决定了可命中索引的列数；即最左优先原则；

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

7）in、=自动优化顺序

不需要考虑顺序，mysql会自动优化顺序，尽可能匹配索引列；

**8）索引失效**

*  or的每一列都加上索引；
 *  多列索引必须符合最左前缀原则
 *  like以%开头的查询
 *  列类型为字符串，需要将数据使用引号引用起来；
 *  如果全表扫描更快，则不使用索引；

**9）索引维护**

维护表和索引的主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

**a、找到并修复损坏的表**

mysql本身的缺陷或者操作系统的问题导致索引损坏。

运行chaeck table来检查是否发生表损坏；使用repair table来修复损坏的表；或者通过alter来重建表。也可以使用离线工具，实现表的恢复；

**b、更新索引统计**

mysql的查询优化器会通过两个api来了解存储引擎的索引值的分布信息，决定如何使用索引。records\_in\_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少记录；

### MyISAM和InnoDB ###

1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。只支持表锁；

2、InnoDB：支持事务安全的引擎，支持**外键、行锁、事务**是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。支持表锁和行锁；只有通过索引条件检索数据才使用行锁，否则使用表锁；其**行锁基于索引**；

## 锁 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 2][]

对于更新、删除、插入，InnoDB自动加排他锁；

MyISAM支持select自动加读锁，执行更新、删除、插入，自动加写锁；

### 1）表锁 ###

表锁：开销小，加锁快，不会死锁，锁定力度大，锁冲突概率高，并发度低；

行锁：开销大，加锁慢，会死锁，锁粒度小，锁冲突概率低，并发度高；

在表读锁和表写锁的环境下：**读读不阻塞，读写阻塞，写写阻塞**！

*  读锁和写锁互斥，读写操作串行；
 *  写锁优先于读锁；
 *  写锁和读锁优先级是通过参数调节：max\_write\_lock\_count和low\_priority\_updates；
 *  MyISAM之间查询和插入并发；concurrent\_insert来指定模式；如果没有空洞行，允许一个进程读表，另一个进程写表；INnoDB不支持；

### 2）行锁 ###

InnoDB支持行锁；

主要两大类型的行锁：

**共享锁（S锁）：**允许一个事务读一行，阻止其他事务获得相同数据集的排他锁；即**读锁**，读锁共享，多个客户可以同时读取，但不允许其他客户修改；

**排他锁（X锁）：**允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁；即**写锁**，写锁会阻塞其他的写锁和读锁；

InnoDB为允许行锁和表锁共存，实现多粒度锁机制；所以内部存在意向锁：

意向共享锁（IS）：事务给数据加行共享锁前，必须先取得IS；

意向排他锁（IX）：事务打算给数据加行锁，先取得IX锁；

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> InnoDB </td> 
 <td style="vertical-align:top;width:264.55pt;"> MyISAM </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 事务，支持4个事务隔离级别：回滚、崩溃修复、多版本并发的事务安全 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 管理非事务表，不支持事务 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 适合大量插入或更新 行级锁适合高并发，但需确保查询时通过索引完成 数据更新较为频繁的场景 数据一致性要求较高 硬件内存大，可缓存来提高内存利用率，减少磁盘IO 避免全表扫描，会使用表锁 尽可能缓存索引和数据，提高响应速度 主键尽可能小，避免主键更新，带来数据移动 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 适合高速存储和检索、全文搜索，适合查找 低并发 数据修改相对较少，以读为主 数据一致性要求不是非常高 高并发用排队机制 启动延迟插入改善批量写入性能 充分利用Query Cache极大提高访问效率 Count只有全表扫描高效 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 不支持全文索引 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 英文全文搜索 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 表锁+行锁 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 表锁 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 基于磁盘资源表空间数据文件和日志文件，表大小受限于操作系统文件的大小 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 存储为三个文件：.frm文件存储表定义，数据文件扩展名.MYD，索引文件扩展名.MYI </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 不易于跨平台转移 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 易于跨平台转移 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 索引组织表使用聚集索引，索引就是数据，顺序存储。可以缓存索引，也能缓存数据 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 堆组织表使用的是非聚集索引、索引和文件分开，随机存储，只能缓存索引 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:261.25pt;"> 读写阻塞与事务隔离级别有关 </td> 
 <td style="vertical-align:top;width:264.55pt;"> 读写互相阻塞，写阻塞读，读阻塞写，读不阻塞读 </td> 
 </tr> 
 </tbody> 
</table>

详情链接：[https://cloud.tencent.com/developer/article/1170837][https_cloud.tencent.com_developer_article_1170837]

**扫码关注一起随时随地学习！！！就在洋葱攻城狮，更多精彩，等你来！！**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70]: /images/20211203/ee274760982b4f5cae4b79ab6970519f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 1]: /images/20211203/a8ccac348f3041ab85fbf656672986aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 2]: /images/20211203/f3db56356f454322b7969ce2eecef751.png
[https_cloud.tencent.com_developer_article_1170837]: https://cloud.tencent.com/developer/article/1170837
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ljeTA3MDY_size_16_color_FFFFFF_t_70 3]: /images/20211203/e7c7d6e56d4d4f61990c51985c4d8d28.png