Mysql总结

Mysql总结

1.mysql体系结构和存储引擎的介绍

1.1 mysql的体系结构

连接层： 最上层是一些客户端的链接服务，主要完成关于连接处理，用户的功能，比如授权认证，相关安全操作，服务器也会对每个安全接入的客户验证他所具有的操作权限。

服务层： 第二层架构主要完成大多数核心服务的功能，比如sql接口，完成缓存的查询，sql的分析和优化，部分内置函数的执行，所有跨存储引擎的功能都在这一层实现，比如存储过程，函数等。

引擎层： 存储引擎真正实现了mysql中数据的存储与读取，服务器通过调用服务api和存储引擎进行通信，不同的存储引擎具有不同的功能，可以根据自己的需要选取对应的存储引擎。

存储层： 主要是将数据存储在文件系统之上，并完成与存储引擎之间的交互。

1.2 mysql存储引擎

1.2.1 innodb存储引擎：

特点：

1.DML操作遵循ACID模型，支持事务

2.锁级别是行级锁，可以很好的提高并发情况下的访问性能

3.支持外键，能很好的保存数据的完整性

innodb的存储结构图以及关系：

名词解释：

tablespace： 表空间

segment： 段，属于表空间下，一个表空间下有3个段

extent： 区，一个段空间下有四个区空间

page： 页，一个区包含64页，一页的大小是16k

row： 行

从此可以看出如果一行的数据大小为1k，则一页可以存储16行数据，如果每行的数据更大或者更小则依此类推，由此可以推断出mysql每页，每个区能存储多少数

据。

1.2.2 myisam存储引擎：

myisam存储引擎是mysql早期的存储引擎，在mysql5.5之后更换为innodb存储引擎

特点：

不支持外键，不支持事务，不支持行锁，支持表锁，访问速度快

1.2.3 memory存储引擎：

memory存储引擎表中的数据是临时存放在内存中的，由于可能会受到断电，硬件问题的影响，所以只会将这些表当做临时表或者缓存来使用

特点：

内存存放，采用hash索引，查找效率快，但是不支持范围查询

1.2.4 存储引擎的选择：

1.如果对数据的一致性，完整性，对事务的一致性完整性要求比较高,在高并发情况下需要保持数据的一致性，同时还需要加上其他操作，比如修改删除新增等，这

个时候就使用innodb存储引擎，比较适合核心数据

2.如果数据以读写和插入为主，很少有删除和更新操作，同时对数据的完整性要求不高，这个时候就可以使用myisam存储引擎

3.至于menory存储引擎，由于现在有redis的存在，都是在内存中进行存储和操作，而且redis效率更快适用范围更广，所以memory存储引擎基本没有人使用了

2.mysql索引介绍以及索引优化

2.1 mysql索引介绍

2.1.1 什么是索引？

索引： 能够帮助mysql快速获取有效数据的数据结构， 是存储引擎用于快速找到记录的一种数据结构

优点： 提高了数据的检索效率，降低了磁盘io操作，降低了cpu使用率

缺点： 增加了磁盘空间的占用率，同时降低了增加删除修改的效率

2.1.2 索引的分类

按数据结构区分：

1.B+Tree索引：

b+tree索引支持大部分存储引擎

B+Tree索引和B-Tree索引介绍：

b-tree：叶子节点和非叶子节点都存放值，叶子节点存放的key值只允许存在4个，当大于4个时，就会向上裂变，同时可能会造成单向链表的出现

b+tree：只会在叶子节点上存放值，同时存放的值会形成有序链表，访问效率较高，提高区间的访问性能，因为mysql的innodb存储引擎

采用的是逻辑存储结构，其中页中最多存放16k数据，这样mysql在页中存放的数据就会更多。

Innodb存储引擎为什么采用B+Tree索引而不采用B-Tree索引？

1.B+Tree索引相对于B-tree索引，产生的二叉树的层级更少，效率更高，而且B-Tree索引产生的二叉树可能会出现形成单向链表的情况

2.B-tree不论叶子节点还是非叶子节点都会存放值，这样会导致一页中存放的存储的键值对减少，指针跟着减少，需要增加大量数据的情况下，树的高度变高，降

低性能，增大内存耗用，b+tree在树的只会在叶子节点上面存放值，并且会形成双向链表，b-tree在一页中，存放的键值对和层深更深，查询效率相比于b+tree将

键值对都存放在叶子节点比起来会更慢。

附上更详细的解释，B-Tree索引到B+Tree索引的演变历史：https://blog.csdn.net/qq_42561919/article/details/120517300

2.R-Tree索引:

适合地理位置查找

3.Hash索引：

hash索引根据内部的hash算法计算出对应的hash值，然后将数据插入到指定的槽位，如果两个数据的hash值一样，这个时候就会产生hash冲突，然后形成链

表，处理方式和hashmap的处理方式一样。

优点：

1.适合精确匹配

2.查询效率很快，通常高于b+tree（不通常情况下指的是产生hash碰撞，形成很长的链表）

缺点：

1.无法利用hash进行排序操作

2.hash索引只能用in，=来进行匹配，没办法使用between和<,>来进行匹配，

支持hash索引的存储引擎有innodb，memory存储引擎，innodb存储引擎具有在特定情况下自适应构建hash索引的功能，hash索引是innodb存储引擎在特定情况下自动构建的。

什么情况下innodb会自动构建hash索引？

二级索引中的热点key(连续访问三次以上)会被创建hash索引，自适应哈希索引通过缓冲池的B+树构造而来。

查看自适应hash索引是否被打开–show variables like ‘%ap%hash_index’;

4.Full-Text：

倒排索引，适合文本查找

按字段特性区分：

1.主键索引（又称聚集索引）

2.普通索引（针对单个字段创建的索引）

3.组合索引（针对多个字段创建的索引）

2.2 mysql优化

2.2.1：mysql索引优化：

1.开启mysql的慢查询日志

1.1 在MySQL的配置文件my.cnf或者my.ini中添加如下配置：

slow_query_log = 1 #开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log #日志文件路径
long_query_time = 3 #超过3s即为慢查询
log_queries_not_using_indexes = 1 #记录未使用索引的查询

在开启mysql慢查询配置后，可以定期dump出对应的日志文件，查看存在哪些慢sql，然后进行优化

2.使用explain关键字来分析慢sql

名词解析：

table: 查询的哪张表

type: 最重要的列，显示使用的是哪种类型，从最好到最差的依为:const>eq_reg>ref>range>index>all，一般达到range级别就能满足要求，最好是达到ref级别

possible_keys: 显示可能用到的索引，一般是应用在这张表中的索引，如果为null，没有可能的索引，可以为相关的域从where语句中选择一个合适的语句

key: 实际使用的索引，如果为null，则没有使用索引，在极少情况下会出现使用mysql强制索引的情况，这种情况下可以使用use indeex(indexname)来强制使用

一个索引，或者使用ignore index(indexname)来强制忽略mysql的索引

key_len: 使用的索引长度，在不丢失精度的情况下，长度越短越好

ref: 显示索引的哪一列被引用了，如果可能的话，是一个常数

rows: mysql认为必须检查的用来返回数据的行数

Extra: 关于mysql解析返回的额外信息，返回信息的相关解析:返回值可能会有， Using index和using filesort：

Using index和using filesort解释：

Using index： 直接通过索引进行返回数据，效率高

Using filesort: 将数据放在缓冲区进行排序，可能会用到磁盘文件，性能会降低

既然能分析出什么情况下使用了索引，那么什么情况下会产生索引失效？

1.违反了最左前缀匹配原则

2.数据库没有统一字符编码为utf8mb64

3.查询语句中使用了is not null，!=,<>,in等符号

4.where条件的索引进行了算术运算

5.对索引字段使用了范围查询，比如> <

6.like语句中，%在左边会导致索引失效

7.查询的字段直接查询的全表的字段，然后可能会触发回表，导致索引失效

8.使用的or字段中存在不是索引的字段

9.order by语句中存在不是索引的字段或者没有遵循最左前缀匹配原则

什么是最左前缀匹配原则？

最左优先，以最左边的为起点任何连续的索引都能匹配上。

（1）如果第一个字段是范围查询需要单独建一个索引；

（2）在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

3.索引优化的方法：

1.针对查询次数较多的字段创建合适的索引

2.在某些特定的情况下可以强制使用索引，在select语句后面添加上force index_name

3.order by字段中最好使用索引字段，同时遵循最左前缀匹配原则，同时保持和索引的升序降序结构一致

4.遵循最左前缀匹配原则

5.尽量避免全表扫描，查询的字段中尽量使用索引字段，而不是使用*，查询时使用 * 可能会出发回表操作。

6.代码层面可以添加一层查询阈值的操作，控制每次load数据的大小，比如设置本次查询数量为1000条，按批次进行查询操作。

什么是回表？

回表（英文名：“Lookup” 或 “Ref”）是数据库中一种查询优化方式，通常用于处理使用非聚集索引（Non-Clustered Index）的查询。当使用非聚集索引查询时，

数据库引擎首先通过该索引找到匹配的行，然后使用行指针（Row Pointer）到表中查找相应的行数据。这个过程中就需要进行一次额外的访问（即“回表”），以

获取完整的行数据。

2.2.2 插入优化

插入时尽量使用批量插入，插入的主键尽量是有序的，这样就可以避免在创建索引的时候去查找索引的位置，出现大批量数据时，可以使用load指令进行插入

，如果出现大批量数据，尽量手动开启事务，然后使用insert批量插入方法的语句进行插入。

2.2.3 主键优化

主键插入尽量使用顺序插入，尽量使用自增主键，但是现在基本使用的都是uuid作为主键，但是对于只需要展示的数据就可以使用自增主键作为主键，设计表时可以采用其他字段的键作为主键，同时添加冗余字段关联其他表

2.2.4 update优化

update语句更新时最好是根据索引字段来进行更新，mysql中的行锁是根据索引来的行锁，如果update的条件如果不根据索引来，可能会引发锁表，更新时最好

根据聚集索引来进行更新。

2.2.5 order by优化

order by的字段尽量使用索引字段，如果存在多个索引字段，需要遵循最左前缀匹配法则，创建order by需要的索引时可以指定升序或者降序，

Create index idx_user_age_phone_ad on tb_user (age asc,phone desc);

创建索引的时候也可以指定升序降序，如果不指定是升序还是降序，使用的都是升序作为索引，查询的时候也需要覆盖索引，出现特定情况，比如组合排序中一个

升序一个降序，需要像上面那样创建特殊的索引，如果不可避免的出现using filesort，再出现大数据量的情况下，可以适当增大排序缓冲区大小，

sort_buffer_size(默认为256k)

2.2.6 group by优化

使用group by时，尽量使用索引字段，同时也要遵循最左前缀匹配法则

2.2.7 limit 优化

通过覆盖索引加子查询的方式来完成查询(这里假设user表有1000万条数据)

Select s.* from tb_user s,(select id from tb_user limit 2000000,10) a where s.id = a.id

2.2.8 count 的优化

Innodb可能会遍历整张表，myisam会自动对表的数据进行计数放在磁盘上，效率这个是高于innodb很多的

Count(主键)

存储引擎会遍历整张表，将每条数据的id取出来进行统计，返回给服务层

Count(字段)

存储引擎也会遍历整张表，将count的字段取出来进行统计计数，返回给服务层，如果该字段为null则不会计数

Count(1)

存储引擎会遍历整张表，查询每条数据但是不取值，返回给服务层进行+1，这样来进行统计

Count()

存储引擎会遍历整张表，innodb存储引擎做了优化，并不会取出所有数据，直接在服务层按照行累加

效率对比

Count(主键)<count(字段)<count(1)≈count()

3.mysql事务和锁的介绍

3.1 mysql事务：

事务：

一组在数据库中执行的操作，它们必须要么全部成功执行，要么全部不执行

事务的特性：

原子性：

事务的原子性指的是构成事务的所有操作要么全部执行成功，要么全部执行失败，不会出现部分执行成功，部分执行失败的情况。

例如在转账业务中，张三向李四转账 100 元，于是张三的账户余额减少 100 元，李四的账户余额增加 100 元。在开启事务的情况下，这两个操作要么全部执行成

功，要么全部执行失败，不可能出现只将张三的账户余额减少 100 元的操作，也不可能出现只将李四的账户余额增加 100 元的操作。

一致性：

事务的一致性指的是事务在执行前和执行后，数据库中已存在的约束不会被打破。

比如余额必须大于等于 0 就是一个约束，而张三余额只有 90 元，这个时候如果转账 100 元给李四，那么之后它的余额就变成了 -10，此时就破坏了数据库的约

束。所以数据库认为这个事务是不合法的，因此执行失败。

隔离性：

事务的隔离性指的是并发执行的两个事务之间互不干扰，也就是说，一个事务在执行过程中不会影响其它事务运行。

持久性：

事务的持久性指的是事务提交完成后，对数据的更改操作会被持久化到数据库中，并且不会被回滚。

例如张三向李四转账，在同一事务中执行扣减张三账户余额和增加李四账户余额操作。事务提交完成后，这种对数据的修改操作就会被持久化到数据库中，且不会

被回滚，因为已经被提交了，而回滚是在事务执行之后、事务提交之前发生的。

所以数据库的事务在实现时，会将一次事务中包含的所有操作全部封装成一个不可分割的执行单元，这个单元中的所有操作必须全部执行成功，事务才算成功。只

要其中任意一个操作执行失败，整个事务就会执行回滚操作，即自动回滚（当然也可以手动回滚）。但执行成功之后，就无法再回滚了，因为事务已经结束了。

3.1.1 事务并发产生的问题：

1.更新丢失：同时有多进程更新同一条数据，导致前几个进程更新到的数据背覆盖，称为更新丢失

2.脏读：一个事务A正在处理一条数据，在处理过程中，事务开启，这个时候另外一个事务B来读取该条数据，获取到数据之后并进行后续操作，依赖该条数据，这

个时候如果不控制就产生了B事务读到脏数据的操作

3.幻读：一个事务A在根据条件读取到的数据原本只有一条，但是在经过一段时间后去读取，这段时间经过其他事务插入了相同条件的数据，然后就A事务就读出了

多条数据，这种情况就是幻读

4.不可重复读：一个事务在之前读取出数据过一段时间之后再去读这个数据，发现数据丢失或者已经修改了，这种被称为不可重复读

3.1.2 mysql事务的隔离级别：

1.读未提交（read commit），允许读取尚未提交的数据变更，可能造成脏读、不可重复读、幻读

2.读已提交（read uncommit），允许读取并发事务已经提交的数据，可以避免脏读，但是可能造成不可重复、幻读。

3.可重复读（repeatable read），对同一字段多次读取的结果都是一致

4.串行化（Serializable），事务串行执行，每个事务之间互不干预。

3.1.3 如何解决事务产生的并发问题？

可以考虑采用加锁的方式，为了解决mysql事务产生的并发问题，mysql引入了锁机制

3.2 mysql锁

为什么mysql要使用锁？

为了解决并发是数据访问的一致性，有效性等问题

3.2.1：全局锁

性能较差，在使用时，整个数据库就只能进行只读操作，一般在备份的时候使用

全局锁的使用：

1.手动开启： flush table with read lock；

2.全局锁执行备份操作： mysqldump -h[ip地址] -uroot -p**** tableName > dump.sql

​ 使用不加锁的方式来执行备份操作：mysqldump --single-transaction -h[ip地址] -uroot -p**** tableName > dump.sql

3.关闭全局锁：unlock tables;

3.2.2 mysql表锁

表锁的分类：

读锁(read lock) ： 读锁情况下，所有客户端都可以查询，当前客户端进行增删改回报错，其他客户端进行增删改操作会阻塞，直到当前客户端的锁解锁

读锁的使用： lock tables tableName read; 释放锁: unlock tables;

写锁（write lock）: 写锁又名排他锁/独占锁，写锁情况下，当前客户端可以查询，可以执行DML语句，其他客户端不能执行DML语句

写锁的使用：lock tables tableName wirte; 释放锁：unlock tables;

3.2.3 mysql元数据锁（meta read lock MDL）

元数据指的是表结构，元数据锁就是指不能对表结构进行修改。再mysql5.5版本中引入了元数据锁(MDL)。

元数据锁的查看：select * from performance_schema.metadata_locks

3.2.4 意向锁

意向锁： 主要解决innodb中行锁和表锁的冲突，mysql在对对行加锁时会先尝试添加意向锁，添加表锁时会优先去查看是否存在意向锁，提高了mysql添加表

锁时查询表中行锁情况的的效率

意向共享锁 （IS）:与表锁的排他锁（write）互斥，与表锁的共享锁（read）兼容

意向排他锁 （IX）:与表锁的排他锁（write）和共享锁（read）都互斥

mysql意向锁的查看：select object_schema,object_name index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

3.2.5 mysql行级锁：

行锁： 指针对某一行记录进行加锁，锁的力度是最小的，行锁又分为共享锁和排他锁，间隙锁和临键锁，共享锁和共享锁之间不互斥，和排他锁之间互斥，排他锁和共享锁以及其他

排他锁之间都是互斥的

间隙锁： 锁的是两条记录之间的间隙，不包含当前记录的值

临键锁： 锁的是两天记录之间的间隙，包含当前记录的值，为了避免多个事务之间出现幻读的现

mysql死锁出现的情况，该如何避免死锁？

mysql死锁出现的情况： 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处

于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

举例：项目中存在两个事务，事务A和事务B，事务A和事务B分别执行的操作为：

事务A：修改a表id = 1的数据之后再修改a表name=zhangsan的数据

事务B：修改a表name=zhangsan的数据之后再修改a表id=1的数据

这时候两个事务并发执行，事务A首先持有id=1这行数据的行锁，等待执行修改name=zhangsan这条数据，然而这时事务B持有name=zhangsan这行的行锁，但

是等id=1的行锁释放，两个事务互相等待对方事务释放锁，所以就造成了死锁。

该如何避免死锁这种情况？：让不同的session加锁有次序，引用上面的举例就是保持两个事务对表的更新操作顺序保持一致

3.2.6 mysql锁的优化措施：

1.尽量减少锁定范围

减少锁定范围可以有效地减小锁冲突的概率，提高并发性。例如，在执行UPDATE或DELETE操作时，可以只锁定需要修改的行，避免锁定整个表的操作。

2.使用合适的数据类型

MySQL支持多种数据类型，在并发访问时，使用合适的数据类型可以减少锁定时间，提高效率。例如，在业务允许情况下，可以使用整型替代字符型，因为整型

的比较速度远低于字符型的比较速度。

3.使用批处理操作

批处理操作可以减少SQL语句执行的次数，减少锁定时间，提高效率。例如，在执行INSERT或UPDATE语句时，可以将多条数据一次性提交到MySQL中。

4.合理使用索引

索引可以提高查询的速度，减少锁定时间。在MySQL中，索引分为聚集索引和非聚集索引两种类型，聚集索引是指按照主键建立的索引，非聚集索引是指建立在

非主键字段上的索引。

5.合理使用事务

事务可以保证数据的一致性，但对性能有一定的影响。为了提高性能，在业务允许情况下，可以将多个操作拆分成多个小事务，降低锁定的范围和时间，提高并发性。