mysql索引表 MySQL高级应用

前言

提示：本文主要是通过咕泡学院课程学习，本人将记录的笔记进行整理，图片皆来自课程PPT。本文介绍MySQL的一些原理，适用于有一定数据库使用经验的人观看，如有错误欢迎指正。

一、MySQL是什么？

MySQL是目前最流行的关系型数据库之一，MySQL因为其速度、可靠性和适应性而备受关注。

二、MySQL常用的存储引擎

1、ISAM：

优点：读取操作的速度很快。

缺点：没有事务、没有索引、没有字段管理。

2、MyISAM：

优点：ISAM扩展格式、提供ISAM里所没有的索引和字段管理、表格锁定的机制、插入数据快，空间和内存使用比较低。

缺点：重要缺陷就是不能在表损坏后恢复数据、适用于完整性、并发性要求比较低。

3、HEAP：

优点：只驻留在内存里的临时表格、速度比ISAM和MyISAM快。

缺点：管理的数据不稳定、关机前没保存将全部丢失。

4、InnoDB：

优点：对数据库ACID事务的支持、实现了SQL标准的四种隔离级别、支持崩溃修复能力和并发控制、提供了行级锁和外键约束、MySQL运行时Innodb会在内存中建立缓冲池，用于 缓冲数据和索引。

缺点：不支持FULLTEXT类型的索引，而且它没有保存表的行数。

5、MEMORY：

优点：数据全部放在内存中、处理速度快。

缺点：需要足够的内存、内存出现异常就会影响数据、安全性不高、不能建立太大的表。

三、InnoDB内存架构（5.5.5以上默认Engine是Innodb，其他版本默认是MyISAM）

基于磁盘的存储引擎，数据都是存在磁盘上的。InnoDB将数据从磁盘加载到内存，有一个最小的逻辑单位——页，每一次读取一页的数据到内存。每一页为16KB的大小。

InnoDB会将一次读取页的数据存放到一块专用的内存中（buffer pool），下一次读取数据的时候会先去buffer pool里查看，是否已经在里面了，如果在，则直接读取，无需再去磁盘读取，以减少IO带来的性能消耗。当要修改数据的时候，就直接修改buffer pool里的数据，此时内存中的数据和磁盘里的数据就不一致了，内存中的页就叫做脏页。InnoDB后台有专门的线程，会专门把脏页的数据刷到磁盘（刷脏）。修改了一批数据之后，一次性把这些数据刷到磁盘，减少了磁盘IO。

架构图：

内存结构：

异常恢复图示：

Log Buffer保存页中修改的记录，防止刷脏到一半导致数据丢失甚至是损坏。支持crash safe崩溃恢复的功能，redo log的数据缓存在Log Buffer中，redo log有一个固定的大小，超过这个大小会把之前的数据覆盖。

四、InnoDB磁盘结构

分为以下几个空间：

系统表空间：默认情况下，所有的表都会公用系统表空间。

独占表空间：开启之后，一个表占用一个空间，记录数据和索引的信息。

通用表空间

临时表空间

Redo log：持久性。

Undo 表空间：原子性，记录事务发生之前的状态，用于回滚操作，是逻辑日志。

双写缓冲：

当InnoDB往OS写数据的时候，如果写了一半，挂了就会导致部分写失效，会造成数据丢失，数据丢失就可能导致文件损坏，此时拿着损坏的文件做奔溃恢复是没有意义的，所以使用了双写缓冲的技术，分别记录到内存和磁盘里，由于是顺序写入，所以对性能不会带来太大的开销。如果写一半挂了，就可以拿这个页的副本（缓冲）来还原内容，然后再用redo log去恢复数据。

主从复制原理：

更新语句执行流程：

五、索引

一、本质：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据，类似书的目录。

二、innoDB索引类型：

1、普通索引：没有限制。

2、唯一索引：字段值不允许重复。

3、主键索引：是一种特殊的唯一索引，在唯一索引的条件下增加了不允许这个字段的值为null。

4、全文索引：大文本中匹配字符可以使用全文索引，只有文本类型的字段才可以创建（CHAR VARCHAR text）。

三、思考：用什么数据结构存储索引是最合适的呢？

1、平衡二叉树（AVL Tree）存索引

索引存在磁盘中，访问一次磁盘块就要进行一次IO操作，InnoDB一次读取会读取16K的数据，但是上图展示，一个磁盘块存放的数据远远达不到16K的大小，浪费了大量的空间。并且访问一个磁盘块就要操作一次IO，数据大的时候就要操作很多次，大大降低了性能。

解决方案：让一个节点存储更多的数据，让节点的度数增多，就由二叉树变为“多叉树”（多路平衡查找树）。

2、多路平衡查找树（Balanced Tree，B树）

如果是无序的插入，会导致不断的分裂与合并，B树的效率已经很高了，但是InnoDB的索引还不是用这种数据结构，而是用了B+树。

3、加强版多路平衡查找树（B+Tree）

数据只存储在叶子节点上，叶子节点之间是一条链表，这就保证了当区间检索的时候，无需回到根节点再往下找，而是直接通过这个链表就可以找到所有的数据

假设一条记录1KB，一个叶子节点存储16条数据，一个bigint为8个字节+指针6个字节=14字节

特点：

1、B Tree能解决的问题，B+Tree都能解决

2、扫库、扫表能力更强

3、磁盘读写能力更强

4、排序能力更强

5、效率更加稳定

InnoDB 索引使用的数据结构：

1、hash

时间复杂度是O（1）

不足：

无序，排序很复杂。

需要进行hash计算，根据key计算value的方式，只能进行等值查询，不能进行区间查询。

若重复的值很多，就会导致很多的hash冲突。

我们并不能自己去创建hash的索引，只用innoDB自己创建的才可以。

2、B+Tree

我们在InnoDB引擎下创建的索引就是使用的B+Tree。

四、InnoDB索引：

.frm：表结构定义的文件，所有的表都有这个文件。

只有一个文件（除了.frm）

.idb：数据和索引都存在这里。

主键索引：

辅助索引：

除了主键索引之外的索引叫做辅助索引或者二级索引。

聚集索引：决定数据存放的顺序的索引。

1、如果有主键索引，根据主键作为聚集索引查找值。

2、如果没有主键，根据找到的第一个唯一索引且不为null的作为聚集索引查找数据。

3、没有索引，InnoDB为每一列都设置了一个隐藏的熟悉ROWID，使用ROWID作为聚集索引。

五、MyISAM索引：

一个表有两个个文件（除了.frm）

.MYI：索引文件。

.MYD：数据文件。

主键索引：

辅助索引：

六、索引的创建和使用原则

一、列的离散度：

公式：count(distinct(column_name)):count(*)；列的不重复的数比所有行数。

从上公式可以看出，列的不重复率越高，离散度就越高。在离散度不高的字段上建立索引，由于重复值比较多，所以扫描的行数也会比较多，当重复值很高的时候，查询的时候可能会放弃索引。所以，索引应该尽可能创建在离散多高的列上。

二、联合索引的最左匹配原则：

在建立联合索引的时候，要将使用最多的字段放在最左边，而且中间不能中断；比如说你建立了一个联合索引A B C，那么查询的时候，使用A，A B，A B C做查询条件是可以使用到索引的（A B C的顺序没关系，OPTIMIZER优化器会自动调整这个顺序）mysql索引表，但是如果用B，C，B C，A C做查询条件，那么将用不到索引。

三、回表：

当使用主键索引的时候，只需要遍历一棵B+树，但是如果使用了辅助索引，则需要遍历两棵B+树，多扫描一棵B+树的过程就叫做回表。所以回表会带来额外的性能消耗。

覆盖索引：当除了创建了主键索引还创建了其他的字段的索引的时候，如果查询的字段刚好在这个索引里存在，则可以直接获取字段值，而不需要去回表，这个现象就称为覆盖索引。举个例子：建立了一个联合索引，字段非别是A和B，然后查询：select A,B from xxx where A='xxx' and B='xxx'; 由于A和B就在这个辅助索引树中，所以无需回表。但是如果是select B from xxx where B='xxx';这种情况，优化器去遍历索引的时候，开销比较小的话，也会使用索引。EXPLAIN解析查询语句中出现Using index就说名使用到了覆盖索引

四、创建索引的技巧：

1、在用于where判断，order排序和join的（on）字段上创建索引。

2、索引的个数不要过多，过多索引占用空间，合理创建索引。

3、区分度（散列度）低的字段，例如性别，不要创建索引。

4、频繁更新的值，不要作为主键或者索引，更新会导致B+树的调整，带来大量磁盘页的碎片

5、复合索引把散列值（区分度）高的值放在前面

6、创建复合索引，而不是修改单列索引

7、过长字段，怎么建立索引？

可以使用前缀索引，截取数据的前缀来作为索引。长度、存储空间和区分度之间的关系，取的前缀越长，那么占用的存储空间就越大，取的前缀太短，又会导致区分度过低

8、为什么不建议用无序的值（例如身份证号、UUID）作为索引？

无序的值插入，如果当前页写满了，会导致页的分裂，B+树数据结构的分裂。最好使用自增有序的列作为索引

五、什么时候用不到索引？

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式。

2、字符串不加引号，出现隐式转换。

3、like条件中前面带%。

4、负向查询能用到索引吗？（如：， !=， NOT IN）

不一定，取决于优化器。通过计算公式，计算出的结果，如果使用索引效率高则使用，使用索引效率不高则不使用。

七、事务

一、四大特性

1、原子性A：逻辑单位，一系列操作不可拆分，要么都成功要么都失败。InnoDB通过undo log实现事务回滚

2、一致性C：数据在操作之前和操作之后是一致的。比如转账，A转1000块给B，A减少了1000而B只增加了500，这就不满足一致性了

3、隔离性I：多个事务之间互不干扰

4、持久性D：不会因为数据库重启或者奔溃而导致数据丢失。（InnoDB使用redo log实现奔溃后数据恢复，保证数据的持久性）

二、并发带来的问题

脏读：一个事务读取到了另一个事务未提交的数据，导致前后读取的数据不一致。

不可重复读：一个事务读到了另一个事务提交的数据，两次读取的数据不一致。

幻读：一个事务查询数据，另一个事务提交了一条，第一个事务再次查询多出来了数据，像是出现了幻觉一样。

总结：事务并发的三大问题都是读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。

三、事务隔离级别（）SQL92标准）

未提交读：未解决任何并发问题。

已提交读：解决脏读问题。

可重复读：解决不可重复读问题。

串行化：解决所有问题。

MySQL InnoDB对事务隔离级别的支持程度：

四、事务隔离级别解决方案

1、在读取数据之前加锁，阻止其他事务对数据进行修改（LBCC）

InnoDB锁类型：

行锁级别：

1、共享锁（读锁）：多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改，修改可能会造成死锁。

加锁方式：select * from xxx where id=1 LOCK IN SHARE MODE;

释锁方式：commit/rollback;

2、排它锁（写锁）：排它锁不能与其他锁并存，如一个事务获取 一个数据行的排它锁，其他事务就不能再获取该行的锁（共享锁、排它锁），只有该获取排它锁的事务是可以对数据行进行读取和修改。

加锁方式：

自动：delete/update/insert 默认加锁

手动：select * from xxx where id=1 FOR UPDATE;

释锁方式：commit/rollback;

行锁原理：锁住的是索引。

行锁算法：

1、区间划分

2、记录锁，锁定记录

3、间隙锁，锁定范围

锁定了一个区间后，在这个区间操作数据是不能成功的，解决了幻读的问题。

4、临键锁，锁定范围加记录

表锁级别：

意向共享锁。

意向排它锁。

意向锁是有存储引擎自己维护的，用户无法手动操作意向锁。

意向共享锁和意向排它锁是加表锁的一个标志，一旦这个表有这个标志则加表锁一定失败，无需再去检索全表，来查找是否有数据行被别的事务所锁定，提高了性能

生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定的级别的一致性读取（MVCC）。

每次开启一个事务都会记录一个版本号，在我这个事务创建之前的数据可以查看到，在这个事务之后做的删除新增修改的数据在这个事务中查看不到。

五、事务隔离级别的选择：

1、RR的间隙锁会导致锁定范围的扩大

2、条件列未使用到索引，RR锁表，RC锁行

3、RC的“半一致性”读可以增加update操作的并发性

4、InnoDB事务隔离级别的实现：

八、mysql优化

一、MySQL优化思路与工具：

1、优化层次

2、连接数

连接数越多，带来服务端上下文切换的额外消耗，所以不是连接数越多越好。一般设置为服务器所在机器的 核心数*2+1。所以要合理配置连接数。（默认是151，最大是10万）

连接数（too many connections）超过了服务器设置的max connections的值：

服务端处理：增加连接数。

客户端处理：释放连接数、使用连接池。

3、架构优化

单台服务器：

服务端：

1、单台数据库满足不了读写访问的需求，使用数据库集群部署。

2、集群带来的问题，多个数据库节点的数据如何保持一致？

复制技术（replication）：

、

、

写的请求都通过master节点，读的请求通过slaver节点

基于主从复制的读写分离：

一定程度的减轻单台服务器的访问的压力，但是会有主从复制一致性的问题（有一定的延迟）。

客户端：

配置多数据源，可以使用一些中间件（如MyCat）帮助选择数据源

分库分表：

单表数据过大：

垂直分库：

减少数据库并发访问压力。

水平分库分表：解决存储瓶颈。

带来的问题：数据源的选择。可以使用一些中间件（如MyCat）

4、慢查询日志

默认关闭（因为会消耗一些性能）

mysqldumpslow工具来统计慢查询日志

profile工具也可以查看查询的相关信息，show profiles;查看所有查询的情况；show profile;查看最后执行的一条的情况；show profile for id;查看查询出来的指定一条的执行情况

mysql命令：show processlist;查看每一个连接的情况，每一个客户端的连接，对于服务端来说就是一个线程，这个命令就可以查看到每一个线程的状态。

5、执行计划

explain关键字分析执行情况，从id大的开始执行，如果id一样则从上往下执行。数据量会影响执行顺序，由笛卡尔积决定执行顺序，是一种小表驱动大表的思想。

explain关键字分析相关字段说明：

（1）select_type查询类型：

SIMPLE：简单查询，不包含子查询的查询。

PRIMARY：包含子查询的主查询。

SUBQUERY：内存的子查询。

DERIVED：衍生查询，用到临时表的查询，会把数据临时存在临时表中的查询。

UNION：用到了UNION的查询。

UNION RESULT：代表的是UNION的结果。

（2）type

const：使用了主键索引或唯一索引查到一条数据（好的类型）。

system：查询系统表的时候只有一条数据（用得少）。

eq_ref：关联查询的时候用到了主键索引或唯一索引，对于前面的每个结果动能匹配到后面的结果的时候，除了const之外最好的访问类型。

ref：查询用到了非唯一索引。

range（优化最好能达到range及以上的级别）：对索引的范围访问（between and ,> ,< ），一定用到了索引。

index：拿到索引的所有的值。

all：全表扫描，没有用到索引。

null：没有访问表，直接得到结果。

（3）possible_keys：可能用到的索引。

（4）key：实际用到的索引。

（5）key_len：使用了索引的长度（了解）。

（6）ref：执行过滤的时候使用了哪一个常量或者哪一个字段一起去筛选。

（7）rows：预估要扫描多少行数据（不是精确的值）。

（8）filtered：存储引擎返回数据给SERVER之后，SERVER还要做过滤，剩下有多少满足条件，是一个百分比，高一点比较好（说明在存储引擎层就做好了过滤）。

（9）Extra（额外的信息）

Using INDEX：用到了覆盖索引。

Using where：返回给SERVER层的时候，不是索所用的记录都符合条件，还要在SERVER过滤。

Using filesort：不能使用索引排序，还要用到其他额外的做排序（需要优化）。

Using temporary：用到临时表，需要把结果缓存在内存里。如：对非索引列使用distinct的时候；对非索引列做group by的时候，等等。

6、存储引擎与表结构的优化

存储引擎：

查询插入非常多的用MyIsam。

对数据的一致性要求高的，有并发的、更新多的操作用InnoDB。

临时数据可以用memory。

表结构：

数据量大的表，可以按日期拆分表。

字段定义原则：可以存储数据的最小数据类型。

NOT NULL：一定不允许为null的数据，定义成NOT NULL，或者定义default默认数据。

外键、触发器、视图开发人员不建议使用，约束尽量在代码里做好约束。

大文件、图片：存到文件服务器上，数据库只存URI，一般是相对地址。

总结：

上图所示：从上往下成本增加，收效减小。

不能忽略业务上的优化：

1、如支付宝鼓励用余额宝支付，而不是银行卡。因为使用内部的数据库会更快，用外部接口性能消耗更大。

2、服务降级，高峰期关闭其他业务，已达到降低数据库压力的效果。

3、双十一提前预售，实现分流。

4、mysql满足不了需求了可以使用搜索引擎（ES）大数据，NoSQL等。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!