post/mysql-ehancement/

[utterances-bot]

MySQL优化笔记 - Wallis

MySQL 分层、存储引擎 mysql 分层 连接层 提供与客户端连接的服务 服务层 提供各种用户使用的接口 提供 SQL 优化器（MySQL Query Optimizer） 引擎层 提供各种存

https://bomir.top/post/mysql-ehancement/

[Bicomir]

mysql8去掉了mysql服务层(Server)的查询缓存模块， 本来之前的设计是如果胖到相同的SQL查询命中缓存，能加快查找的效率。

弃用的原因在于： 查询缓存命中率太低， mysql8版本之后会失效；

[Bicomir]

Mysql调优问题 1.性能监控； 2.schema与数据类型优化； 3.执行计划； 4.通过索引进行优化； 5.查询优化； 6.分区表； 7.服务器参数设置； 8.mysql集群；

[Bicomir]

mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.03 sec)

mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.04 sec)

mysql> show profile for query 1; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000031 | | Executing hook on transaction | 0.000002 | | starting | 0.000004 | | checking permissions | 0.000003 | | Opening tables | 0.000023 | | init | 0.000004 | | System lock | 0.000010 | | optimizing | 0.000003 | | statistics | 0.000009 | | preparing | 0.000010 | | executing | 0.000040 | | end | 0.000002 | | query end | 0.000002 | | waiting for handler commit | 0.000005 | | closing tables | 0.000004 | | freeing items | 0.000041 | | cleaning up | 0.000006 | +--------------------------------+----------+ 17 rows in set (0.05 sec)

[Bicomir]

为啥主键是一般选择自动递增的？ 涉及的要点是索引的维护过程， 索引是一个B+树， B+树的层数和元素值的添加和删除会影响到整个树的结构或者层数会影响到整个树的结构或者层数。

 1）  如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

       这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

2） 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置：此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE 来重建表并优化填充页面。

3）因此， 在使用 InnoDB 存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

[Bicomir]

mysql调优 索引 MVCC 存储引擎 事务 主从复制 读写分离 分库分表 锁 日志系统（Redo和Undo） redo log（前滚日志）， crash safe 如果发生异常情况，就算数据没有持久化成功，只要日志持久化成功，也会恢复； undo log(回滚日志)， 保证原子性， 也能保证MVCC；

当使用其他索引的时候， 叶子结点中并不包含整行的数据值，需要去主键索引中进行数据查找的过程叫做回表；

CBO： 基于成本的优化； RBO： 基于规则的优化；

一致性非锁定读 一致性锁定读

存储引擎： 不同的数据文件在磁盘的不同组织方式；

局部性原理：数据和程序都有聚集成群的趋势， 分为空间局部性和时间局部性； 磁盘预读： 内存跟磁盘在进行交互的时候要保证每次读取需要一个逻辑单位，而这个逻辑单位叫做页，或者叫datapage, 一般大小都是4k或者8k, 在进行读取的时候一般都是4k的整数倍，InnoDB每次读取16Kb的数据；

[Bicomir]

-- 创建索引的字段是长了好，还是短了好？ 短了好，原因是， 在层数不变的情况下，可以存储更多的数据量；

-- 我们在创建表的时候是用代理主键还是用自然主键？ 代理主键表示和业务无关的字段， 自然主键表示和业务有关的字段； 能使用代理主键要尽可能多的使用代理主键；

-- 主键设置好之后，要不要自增？ 在满足业务的情况下尽可能自增，不自增会增加索引的维护成本；

-- 在分布式应用场景中， 自增id还适合用吗？ 不适用，雪花算法 或 自定义id;

-- 存储引擎 InnoDB， myisam, memmory 1.innodb支持事务， myisam不支持； 2.innodb支持外键， myisam不支持； 3.InnoDB支持表锁和行锁， 但是myisam支持表锁； 4.InnoDB在5.6版本之后支持全文索引； 5.InnoDB索引的叶子结点直接存放数据，而myisam存放的是地址；

[Bicomir]

回表

索引覆盖

最左匹配

索引下推

聚簇索引 和 非聚簇索引 （数据和索引是否是聚集存储的），InnoDB里面。.frm和.idb, InnoDB中主键索引是聚簇索引， 必须要包含一个主键列， key如果在创建表的时候指定了主键，那么key就是主键， 如果没有主键， 那么key就是唯一键，如果唯一键也没有， 那么key就是6字节的row id.