Mysql 进阶

Mysql 进阶

1.存储引擎

mysql存储引擎有多种，作用域是表。我们最常用的就是innodb，和myisam，也可以自定义存储引擎,在mysql5.5版本后默认的存储引擎就是innodb，早器版本的存储的引擎是myisam。

**mysql的体系结构：**连接层、服务层、引擎层、存储层。

1.1 innodb

innodb支持事务，支持行锁，支持外键。

innodb的持久化文件：在8.0之后是xxx.ibd，存储该表的表结构和数据和索引。在之前是frm、sdi分别存储表结构和数据。

innodb逻辑存储结构：

tablespace ==》 segment ==》 extent(1M) ==》 page（16k） == 》row

1.2 myisam

早期默认的存储引擎，特点是不支持事务和外键，支持表锁不支持行锁，访问速度快。持久化有三种文件格式。使用场景：很少更新的数据，日志数据。

1.3 memory

数据是存储在内存中的，所以只能作为临时表或者缓存使用。支持hash索引。持久化只有一种文件格式。使用场景：缓存，但是表大小有限制。

2.索引

索引是一种可以保证数据库高效检索的数据结构。

优点：查询效率高。缺点：增删慢，需要维护索引文件，增加存储空间。

2.1索引的数据结构

主要了解的索引数据结构有：b+tree索引（都支持），hash索引（memoty存储引擎支持）。

我们正常所指的所以的数据结构就是innodb的b+tree数据结构。

2.1.1 b+tree

特点：

1.一个节点拥有多个元素。（为了结构遍历的树高的问题）

2.所有的元素都会出现在叶子节点，非叶子节点都是冗余索引。

3.叶子节点会形成一个有序的双向链表。

2.1.2 hash

特点：

只能用于等值匹配，无法完成排序，查询效率高，只需要检索一次就可以了。

2.3索引的分类

主键索引、唯一索引、常规索引、全文索引（查找的是索引中的关键字，而不是索引值）。

聚集索引（主键索引）：索引和数据存放在一起。

二级索引：数据和索引分开，叶子节点存放的是主键信息。

回表查询就是先到二级索引中获取到主键值，然后到聚集索引中获取的这一行的数据。

2.4sql执行优化

2.4.1 sql执行频率

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______';

2.4.2 慢查询日志

慢查询日志默认是关着的，需要修改my.cnf文件

1. # 开启慢查询日志开关
2. slow_query_log=1
3. # 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
4. long_query_time=2

更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log

2.4.3 sql性能分析

2.4.3.1 profile

show profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数，能看到当前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：
SET profiling = 1;
查看所有语句的耗时：
show profiles;
查看指定query_id的SQL语句各个阶段的耗时：
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.4.3.2 explain

EXPLAIN 各字段含义：

• id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）
• select_type：表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
• possible_key：可能应用在这张表上的索引，一个或多个
• Key：实际使用的索引，如果为 NULL，则没有使用索引
• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

2.5索引的使用

失效场景：

如果是联合索引，那么范围查询（>,<）的右边索引会失效。使用大于等于就不会。

在索引列上进行运算也会索引失效。

字符串类型的字段不加单引号，索引会失效。

模糊查询，头部进行模糊匹配索引会失效。

or链接的时候，如果前面有索引，后面没有索引，那么都会失效。如果两个都有索引，则不会。

有的时候，mysql根据数据分布会评估放弃走索引，全表扫描会更快。（大于等于）

索引使用：

sql提示使用哪个索引

explain select * from tb_user use index(idx_user_pro) where profession="软件工程";

覆盖索引：查询返回的列在索引中可以找到。所以尽量减少select*，会导致回表操作。执行计划中，extra中可以看出。

前缀索引：针对varchar和text，可以只将索引的前n个字符建立索引。减少磁盘io。

单列索引和联合索引的选择。更推荐联合索引

建立索引原则：

数据量大的表且查询频繁。

where、order by、group by 后面建立索引。

字符串建议考虑前缀索引。

尽量使用联合索引，使用到覆盖索引，减少回表。

2.6 sql优化

1.批量插入、手动提交事务、主键顺序插入；大数据量插入，使用load指令。

数据组织方式：

页是innodb管理的最小单元，一个页为16kb。

页分裂，页合并（删除不是真的删除，一个页超过百分之50时候就会寻求合并）。有序自增主键，减少页分裂。

order by，group by 优化：将字段建立联合索引索引。

limit优化，可以使用子查询优化。

计数优先使用：count（*）和count（1）

更新的时候，where根据索引字段更新，这样开启的是行锁，否则为表锁。

3.锁

3.1表级锁

表锁：共享锁、独占锁
表级锁：
读锁：
不会阻止其它客户端的读，但是会阻止其它客户端的写。
写锁：
其它的客户端既不能写也不能读。

元数据锁：避免dml和ddl冲突。
意向锁：加上行锁的时候，同时对这张表加上意向锁。

行级锁：
在innodb中应用
间隙锁，防止可重复读中的幻读；临建锁，

共享锁和排他锁：共享锁和共享锁是兼容的，但是排他锁不兼容。
select 不会加任何锁。 insert、update、delete 会加上排他锁。

innodb引擎
表空间==》段==》区==》页==》行
表空间就是.ibd文件
段，数据段就是索引的叶子节点
区，每个区大小为1m；页是16k。

内存结构；缓冲池，减少磁盘io。

4.事务

ACID 原子性，一致性，隔离性，持久性。

读取未提交（脏读）、读取已提交（不可重复读）、可重复读（幻读）、串行化。

原子性，一致性，持久性 是通过redolog、undo log 实现。

隔离性是通过锁和mvcc实现的。

4.1redolog

重做日志，记录事务提交时数据页的物理修改，是事务持久性的保证。

增删改的时候，在缓存中修改产生脏页，然后记录到redolog buff中，同时记录redo log 文件到磁盘（顺序io），commit时候，持久化到磁盘，commit失败就会使用到redo log 文件持久化。

4.2undo log

回滚日志，记录数据修改前的信息。

保证了事务的回滚，比如insert就会记录delete；update也会记录update。

insert会在事务提交的时候删除，update和delete不会。

undo log 版本链

4.3 mvcc（多版本并发控制）

当前读就是读取最新版本的记录，select … lock in share mode

快照读就是读取记录可见版本

4.3.1 隐藏字段

DB_TRX_ID:最近修改的事务id；

DB_ROLL_PTR：回滚指针，指向上一个版本数据的undo log磁盘地址；

DB_ROW_ID:隐藏主键（有主键则不会出现）

4.3.2 readview

是快照读sql执行mvcc提取数据时的当时活跃事务（未提交的）id。也就是undo log 版本链中版本

用当前事务的id去比较，是否可以访问该版本。（脏读、不重复读、幻读的原理）