很多知识点出自《高性能mysql》

B树与B+树

在磁盘设计中每经过一个节点就是一次io操作，io操作是会影响性能的，所以磁盘存储的数据结构设计最终的目的就是减少io

B树

B+树是B-树的加强版，B+树的枝节点不存储行数据，只是存储主键的索引和引用，叶子节点才是真正的保存数据的，所以枝节点能存放更多的索引，节点也会相应的减少，树的高度也随之变低，磁盘io读写也就进一步减少，并且叶子节点的数据都是顺序的,叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表，所以磁盘由随机读取变为了顺序读取，提升了磁盘的性能，所以B+树作为innodb的磁盘存储结构比B-树的读写效率更高，查询效率更好。

B+树

B+树的优势：

一般来说B+Tree比BTree更适合实现外存的索引结构，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，在数据库中一页默认为16k。索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中能存放的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的索引键。

MySql的索引

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引

• 聚簇索引:聚簇索引的存储了表中所有的主键和行数据,聚集索引查询效率更高，但是写入性能差，因为写入新数据需要调整索引的位置
• 非聚簇索引:数据存储和索引分开，叶子节点存储对应的行，需要二次查找，通常称为[二级索引]或[辅助索引]

InnoDB——聚簇索引

• 索引值不能重复，不能为空。InnoDB是以主键为基础的，如果我们没有指定主键，InnoDB会创建一个隐式的主键
• 聚簇索引的主索引的叶子结点存储的是键值对应的行数据和主键键值，辅助索引的叶子结点存储的是键值对应的数据的主键键值和对应的主索引的键值。而枝节点只存放主键键值和引用因此主键的值长度越小越好，类型越简单越好，这样树的一个枝节点能存放的数据量就越多。
• 聚簇索引的数据是根据主键的顺序保存，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
• 使用主索引的时候，尽量使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据
• 聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕

聚簇索引B+树

聚簇索引B+树和对应的辅助索引

MyISAM——非聚簇索引(辅助索引)

• MyISAM存储引擎采用的是非聚簇索引，主索引不允许重复，不允许空值，叶子结点的key都存储指向键值对应的数据的物理地址。
• 非聚簇索引的数据表和索引表是分开存储的。
• 非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

• 只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
  非聚簇索引

InnoDB和MyISAM

联合索引的B+树

联合索引的B+树

覆盖索引：

创建联合索引

这个查询使用了覆盖索引

如果把上述查询东西改为select *就不走覆盖索引了，而先走辅助索引然后利用辅助索引查出来的数据去查主索引，因为主索引存储了对应表的所有行数据

Hash表索引

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like ‘%xxxxx%’,'%xxxxx'模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制
ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

使用所有的 一些规则

选用主索引时最好选择散列性好，字段长度小，并且有顺序性递增的字段。散列性差的字段主主索引时会造成数据库无法通过一次索引找到对应的行数据，因为主索引存在大量重复值，这时数据库必须对行数据再进行一次扫描，另外有可能造成页分裂。无序性的字段作为主索引会造成多次的页分裂。字段长度大的数据在支节点占用太多空间，这样会造成支支节点过多，使io的次数变多

慢查询优化

慢查询日志

通过手动开启慢查询输出日志可以查看有问题的SQL

• 设置开启慢查询日志的输出
  set global slow_query_log=NO
• 设置慢查询日志输出的位置
  set global slow_query_log_file=/xxxxx/xxxxx.log
• 设置记录的慢查询是执行时间超过多少秒的SQL，这里单位是秒
  set global slow_query_time=xxxxx.xxx
• 开启记录没有使用索引的SQL
  set global slow_queries_not_using_indexes=NO
• 使用mysql自带的慢查询分析工具，分析慢查询日志
  mysqldumpslow /xxxx/xxxxx.log

实际操作

mysqldumpslow 分析慢查询工具

由于开启慢查询日志，线上的日志可能会特别大，直接看文本是不切实际的，使用mysqldumpslow 工具可以分析统计日志文件形成报表，方便我们查看

//查看工具的命令
mysqldumpslow --help

下面列出比较常用的，也是最实用的

-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time（按平均锁定时间排序）
                ar: average rows sent（按平均行数排序）
                at: average query time（按平均查询时间排序）
                 c: count（sql次数）
                 l: lock time（锁定时间）
                 r: rows sent（行数）
                 t: query time （查询时间） 
  -r           reverse the sort order (largest last instead of first)（倒序）

例如

//按照时间进行排序，排序是从大到小排序
mysqldumpslow -s t /xxxx/xxx.log
//按照次数进行排序
mysqldumpslow -s c /xxxx/xxx.log

SQL语句的优化方案

join分析

一、小表驱动大表优于大表驱动小表

先了解在join连接时哪个表是驱动表，哪个表是被驱动表：

• 1.当使用left join时，左表是驱动表，右表是被驱动表
• 2.当使用right join时，右表是驱动表，左表是被驱动表
• 3.当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

例如: A是小表，B是大表
　　使用left join 时，则应该这样写select * from A a left join B b on a.code=b.code
　　A表时驱动表，B表是被驱动表

测试：A表140多条数据，B表20万左右的数据量
　　select * from A a left join B b on a.code=b.code
　　执行时间：7.5s

　　select * from B b left join A a on a.code=b.code
　　执行时间：19s

二、在join中驱动表的索引会失效，只有被驱动表的索引起效果，所以给被驱动表建立索引

在以小表驱动大表的情况下，再给大表建立索引会大大提高执行速度，驱动表的索引会失效。
假如驱动表A有100条数据，驱动表B有1000条数据 并且B表的字段s有索引，现在执行这样的语句：

select * from A a left join B b on (a.s = b.s)；

这条语句会执行多少次查询呢？答案是：200次。
首先遍历A表，把A表的100条数据拿到B表一一查询，由于B表的s字段是有索引的，所以每一次都能查到一一对应的数据，所以需要100次查询。也就是100+100=200。
但是如果被驱动表B没有的s字段没有索引，那么就变成了全表扫描：100*1000=100000。性能就会变得及其低下。所以使用join查询时,被驱动表务必加上索引，否则性能会急剧下降。

三、在排序时使用索引字段效率会大幅度提升

• 1、在进行排序时如果排序字段是索引字段，那么排序将会扫描索引完成排序，而索引本来就是有序的，不需要扫描表，所以效率将会非常快

• 2、而不使用索引字段排序的情况，需要回表扫描然后开辟额外的内存空间进行排序，如果内存空间不足还会生成临时文件进行落盘增加物理磁盘IO,极大的影响了SQL效率

using filesort

using filesort有两种算法，一个是常规排序法，一个是优化排序法

using filesort的常规排序法执行步骤：

实例：

排序缓冲区

using filesort的优化排序法执行步骤：

using filesort的优化排序法执行步骤

using filesort什么时候执行常规排序和优化排序：

当排序数量小4096B时使用的优化排序法，否则使用常规排序

以下是联合索引时排序是否走索引的情况

先建立一个联合索引
索引 key a_b_c(a,b,c)

• 使用索引的左端字段排序时能走索引，并且排序的升降序要一致

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

• 如果WHERE使用了索引的最左端,则ORDER BY 也能使用索引

WHERE a = const  ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const  ORDER BY b,c
WHERE a = const AND b > const ORDER BY b,c

• 以下情况不能使用索引排序

WHERE a > const  ORDER BY b,c/*范围排序不能使用索引*/
ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
WHERE g = const ORDER BY b,c    /*丢失a索引*/
WHERE a = const ORDER BY c     /*丢失b索引*/
WHERE a = const ORDER BY a,d /*d不是索引的一部分*/

四、禁止使用Select *，只查自己需要的字段

1、在数据库查询出来的数据是需要网络传输到客户端的，查询出来的数据字段越多数据量就越多，占用网络带宽
2、在排序时如果没有走索引的情况下，数据库是需要另外开辟空间进行排序的，字段越多数据量大导致内存不足就需要进行创建临时文件进行落盘增加物理磁盘IO
3、很有可能会使覆盖索引失效

五、order by,group by,distinct尽量使用索引

除了order by会排序外,group by（group by在Mysql8的中进行了优化，内部已经不排序了）和distinct是内部先进行排序的，所以最好的优化方案就是尽量的使它们走索引

group by:

索引：

mysql5.6 group by会默认排序

mysql8group by分组时默认不排序

出现了using temporary和using filesort的分组都需要进行优化

group by的三种类型

group by的三种类型

六、尽量使用子关联查询代替使用子查询。

尽量将子查询转化为join,子查询产生的结果集无法使用索引，并且子查询的结果集会产生临时表，如果子查询数量过多生成大量的临时表，严重消耗cup io资源。

但是现实中子查询是很多业务必不可少的，现在mysql的优化器对子查询的优化也有相应的支撑，有些子查询效果不一定比关联查询差，所以还以实际的执行计划去定

七、分页优化

八、union优化

九、复合索引的优化

在通常我们查询时，sex(男，女)，flag(0，1)之类的是不适合建立索引的，这类的散列性太差，但是很多查询又用到它们，我们可以给它们建立一个复合索引(sex,flag),每一次使用时必须遵守最左前缀原则，这里通常可以这么用：例如我们要查一个带上flag又不带sex字段的查询，我们可以写成这样绕过sex字段，又符合了最左前缀

select xxx from xxx where sex in ('男','女') and flag=xxx

十、范围查询优化

范围查询(between,<,>)会使右边的索引条件失效，所以在sql中尽可能的把范围查询放在最后面

十一、其他方案

• 1、不要在数据库进行运算，比如：触发器，存储过程、函数。应当用后端代码实现
• 2、主键推荐使用自增的，不推荐使用字符串作为索引，主键不应该被修改。选择自增主键作为索引时插入数据时都是按照索引的顺序插入的，索引不需要移动数据和销毁额外的性能维护索引数据，而频繁修改索引会造索引数据频繁移动产生索引碎片得不到一个结构顺序紧凑的索引结构。如果我们没有选择自增主键也没有指定其他字段当主键，innodb会选择一个rowid作为内置主键，往后我们插入的数据的主键都是按照这个rowid自增生成
• 3、事务简单，甚至不要事务
• 4、使用or代替in,用union代替or,使用union all代替union(union有去重的开销)
• 5、limit优化：偏移量越大，执行越慢

select * from a where id in (select id from a where id>10000) limit0,10
select * from a limit10000,10
第一个sql优于第二个sql

分析sql的执行计划

id表示查询的顺序：

• 当id相同时，sql是由上至下执行，应该由上至下分析
• 当id不同时，其id越大则表示执行的优先级越高，应该从id高的到id低的分析
• 当id为null时，其代表了是union查询的结果
• 出现两组id,应该id大的一组优先权高

当id相同时，sql是由上至下执行，应该由上至下分析

当id不同时，其id越大则表示执行的优先级越高

出现两组id,应该id大的一组优先权高

各个select_type的含义

table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表

type

mysql查询的方式，这是Mysql查询性能好坏最重要的指标

keys

• possible_index:查询出可能会用到的索引
• key:查询时实际用到的索引
• key_len:查询实际使用索引的最大长度，与字段的数据类型有关

ref

常见的有：const，func，null，字段名。
4、当使用常量等值查询，显示const，
1、当关联查询时，会显示相应关联表的关联字段
2、如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
3、其他情况null

extra

这一列包含的是不在其他列显示的额外信息。

• using index:这个说明MySQL使用覆盖索引，避免了回表操作，效率不错。
• using where:查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
• using filesort:表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。
• using temporay:表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
• Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
• Impossible where：表示在我们用不太正确的where语句，导致没有符合条件的行。

索引的优化

哪些字段适合创建索引、哪些不适合的：

• 1、字段值散列度大，唯一性高的字段。例如：状态字段可能只是两个值这种唯一性太差的值不适合当索引。散列性越差，说明重复数据越多，如果作为索引，占据的叶子节点量庞大，此时索引扫描跟全表扫描区别不大
• 2、如果非要为这些唯一性性差的字段创建索引，可以尝试使用复合索引
• 3、更新太频繁的字段不适合创建索引，因为每次更新字段，索引都会重新调整排序io消耗巨大
• 4、频繁使用where、order by、group by、distinct、join 建立索引或者联合索引
• 5、建立联合索引，应当优先左侧优化，如：散列性最好、使用最频繁、字段长度最小符合这些条件的优先放在联合索引的左侧

在 MySQL 中，下列几种情况下有可能使用到索引。

• 索引一是否被用到举例说明如下。
  有几个原则：

1、 复合索引左前缀和中间不断原则，复合索引的前缀不能丢失，中间索引不能断，
例如：key(a,b,c),where a b c、where a b、where a、where a f b 都能用到索引，
where a c,where b c、where c、where b都用不到索引

2、不能在索引上进行特殊操作。例如：函数、计算、类型转换等

3、在联合索引中使用范围（between,<,>,in等）会导致右边的索引失效，
例如：where a=1 and b>1 order by c desc b这个字段的条件是一个范围，
导致右边c字段索引失效。解决办法：把b从联合索引移除，只留下a c

4、在索引中使用(!=,<>,is null,%xx,%xx%)会导致索引失效,
注意(is not null)在mysql8以下的版本会使索引失效

5、索引是字符串，查询时却不用单引号括起来会导致索引失效

6、使用or时，查询的字段不是索引字段就会失效，or中的条件不是索引字段也会失效，
例如，key g,key(a,b,c),   select a b where a or b、select a g where a or g   都走索引，
select f a g where a or g、select a g where a or g or f就不走索引了

7、禁止使用子查询，将子查询转化为join,子查询产生的结果集无法使用索引，
并且子查询的结果集会产生临时表，如果子查询数量过多生成大量的临时表，严重消耗cup io资源

首先按 company_id，moneys 的顺序创建一个复合索引，具体如下:

create index ind_sales2_companyid_moneys on sales2(company_id,moneys);
create index ind_company2_name on company2(name);
create index ind_sales_year on sales(year);

• 然后按 company_id 进行表查询，具体如下:

可见虽然在 moneys 上面建有复合索引，但是由于 moneys不是联合索引的第一列，那么在查询中 这个索引也不会被 MySQL 采用。

• 对于使用 like 的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会 被使用，来看下面这个个执行计划

可以发现第一个例子没有使用索引，而第二例子就能够使用索引，区别就在于“%”的位置 不同，前者把“%”放到第一位就不能用到索引，而后者没有放到第一位就使用了索引。 另外，如果如果 like 后面跟的是一个列的名字，那么索引也不会被使用。
如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%…%’。

• 如果列名是索引，使用column_name is null可以走索引。如下例中查询name为null 的记录就用到了索引，但是如果是name is not null，not in,<>都是使用不到索引

• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引， 那么涉及到的索引都不会被用到，例如:

可见虽然在 year 这个列上存在索引 ind_sales_year，但是这个 SQL 语句并没有用到这个索引， 原因就是 or 中有一个条件中的列没有索引。

• 如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引 起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为，MySQL 默认把输入的 常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的， 但是 SQL 语句中的条件值 294 是一个数值型值，因此即便在 name 上有索引，MySQL 也不能 正确地用上索引，而是继续进行全表扫描。

从上面的例子中可以看到，第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name，那么虽然在 name 列上有索引，但是也没有用到;而第二个 SQL 语句就可以正确使 用索引。

其他SQL优化

INSERT

如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大
缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语 句快(在一些情况中几倍)。下面是一次插入多值的一个例子:

insert into test values(1,2),(1,3),(1,4)...

GROUP BY

提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多.

低效:
SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB
HAVING JOB ='PRESIDENT' OR JOB ='MANAGER'
高效:
SELECT JOB , AVG(SAL) FROM EMP WHERE JOB ='PRESIDENT'
OR JOB ='MANAGER' GROUP BY JOB

嵌套查询

尽量使用JOIN代替子查询，使用JOIN时
子查询产生的结果集无法使用索引，并且子查询的结果集会产生临时表，如果子查询数量过多生成大量的临时表，严重消耗cup io资源
如果一定要使用子查询进行复制的sql嵌套，应当尽量缩小内表，也就是先把子查询的表按条先进行筛选然后在进行查询关联这样关联的范围就会变小

MySql表字段优化

char和varchar

• char：定长，一般用于固定长度的数据存储 ，例如：身份证号，手机号，电话，密码等
  CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，M代表了可以存储的字符的个数而不是字节数，当存储的字符不足指定的M值时会充空格以达到指定的长度，比如定义 char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。。当检索时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。由于是固定长度，更新时不需要调整存储空间，所以在CHAR字段上的索引效率级高

• varchar：不定长，VARCHAR(M)，M取值可以为0~65535之间，M代表了可以存储的字符的个数而不是字节数，整体最大长度是65,532字节，在utf-8中一个字符占用磁盘空间的3个字节，另加一个字节或两个字节来记录长度(如果列声明的长度超过255，否则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留。varchar存储变长数据，但是VARCHAR可以节约内存块的长度和磁盘空间的大小，检索上可以提升性能，但存储效率没有CHAR高。

char尽量存储一些定长的数据，比如身份证、电话号码、姓名等，char因为是固定了长度所以可以避免不定长度字符串的空间调节。

varchar根据实际存储的数据分配最终的存储空间，由于可以自动调节长度，所以可以节省磁盘空间，但是并不能调节内存空间，内存空间是定长的时候固定的，但是如果进行字段更新时会做空间调节操作，额外消耗性能，如果扩展长度时页空间不足还会通过页的分裂来达到存储的目的，进一步降低性能，所以在创建varchar不要使用默认的255，而根据需要的中文个数创建，过大的字符长度会消耗过多的缓冲池内存和B+树结构叶子节点的增多

char和varchar相同点：
char(n)，varchar(n)中的n都代表字符的个数
超过char，varchar最大长度n的限制后，字符串会被截断。

varchar定长的意义：
使用VARCHAR(100)与VARCHAR(200)存储的90字符的效果是的相同吗?结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时，分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为其为根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。

由于B+树的特点所以设计字段时尽量设计的小，够用就好了，这样可以减少磁盘的io次数，有以下方案：

• 1.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
• 2.尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
• 3.少用text类型，尽量用varchar代替text，char虽然比varchar快，但是char是定长，varchar是可扩展长度的，更适合代替text类型的场景
• 4.使用枚举或整数代替字符串类型
• 5.日期尽量使用TIMESTAMP代替DATETIME
• 6.单表不要有太多字段，建议在20以内
• 7.用整型来存IP，如：使用函数相互转化inet_aton('255.255.255.255')=4294967295,inet_ntoa(4294967295)='255.255.255.255'

内存优化

https://www.cnblogs.com/ilifeilong/p/7224678.html

缓冲池：

innodb是基于磁盘存储的，为提升性能，innodb会在内存中有一个缓冲池，每次读书数据会将数据放到缓冲池中缓存，修改数据也是先对缓冲池进行修改，然后按照一定的频率刷回硬盘，当查询命中内存中的缓冲池时就不用查询磁盘。

在专用数据库服务器上，通常将多达80％的物理内存分配给InnoDB缓冲池。因为InnoDB的存储引擎的工作方式总是将数据库文件按页读取到缓冲池，每个页16k默认（innodb_page_size=16k），在MySQL 5.7中增加了32KB和64KB页面大小的支持，通过innodb_buffer_pool_size设置缓冲池大小，通过innodb_page_size设置页的大小，设置innodb_buffer_pool_size时，操作以块（chunk）形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值128M，也就是说每个缓冲池实例大小默认是128M，innodb_buffer_pool_size=innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的倍数，如果配置innodb_buffer_pool_size为不等于innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于或不小于指定缓冲池大小的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。
例如：

show variables like 'innodb_buffer_pool_size'   //查询innodb_buffer_pool_size的大小
SET GLOBAL innodb_buffer_pool_size=16106127360 //设置innodb_buffer_pool_size的大小

LRU

InnoDB将buffer pool作为一个list管理，基于LRU算法。当有新的页要读入到buffer pool的时候，buffer pool就将最近最少使用的页从buffer pool中驱逐出去（类似redis的内存淘汰）并且将新页加入到list的中间位置，这就是所谓的“中点插入策略”。一般情况下list头部存放的是热数据就是所谓的young pages（最近经常访问的数据），list尾部存放的就是old pages（最近不被访问的数据），默认3/8的list信息是作为old list，这些信息是被驱逐的对象，通过innodb_old_block_pct设置比例，默认是37也就是3/8，每次插入新页都会从倒数的3/8的位置插入，倒数的3/8的页是要被刷出缓冲池的

• 1、3/8的list信息是作为old list，这些信息是被驱逐的对象。
• 2、list的中点就是我们所谓的old list头部和new list尾部的连接点，相当于一个界限。
• 3、新数据的读入首先会插入到old list的头部。
• 4、如果是old list的数据被访问到了，这个页信息就会变成new list，变成young page，就会将数据页信息移动到new sublist的头部。

innodb_old_blocks_time，单位是毫秒，默认是1000，可以设置当页在磁盘中被读出后经过多长时间才能被加入的缓冲池。如果增大这个值的话，就会让buffer pool里面很多页信息变老的速度变快，因为这些数据不会很快被内存中擦除的话，就会变成热数据而挤掉原有缓存的数据。

缓冲池实例

并且缓冲池是支持多实例的，也就是一个MySql实例支持多个缓冲池以减少并发操作中内存结构的争用，对应内存比较大的64位服务器，可以设置为8个，通过show engine innodb status查询，通过innodb_buffer_pool_instances设置缓冲池的实例数

show engine innodb status //查询缓冲池实例
SET GLOBAL innodb_buffer_pool_instances=8 // 设置缓冲池的实例数

MySql的优化极限

当面对百万级别的数据量级时，就算是Mysql调优大湿都不可能调得动了，这时就到了MySql的性能极限了，请放弃在MySql的关联查询吧，尽量利用缓存和队列

这时一个优秀的架构就比极限调优更胜百倍，MySql在一个不错的物理机器上最多能承载3500-4500的QPS

读多写少用缓存，写多读少用队列，那么高并发用哪种？当然是两种一起用：

• 1、高并发读数据：利用redis将热点数据进行缓存，将绝大部分的读压力让redis承担，以减轻mysql的读压力
• 2、高并发写数据：当大量的写请求到达Mysql时，将写流量先进队列，通过队列慢慢的来对MySql进行写操作，缓冲了MySql的写压力

MySQL的并发控制优化

LBCC，基于锁的并发控制，Lock Based Concurrency Control。

使用锁的机制，在当前事务需要对数据修改时，将当前事务加上锁，同一个时间只允许一条事务修改当前数据，其他事务必须等待锁释放之后才可以操作。性能太差

MVCC，多版本的并发控制，Multi-Version Concurrency Control。

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突。使用锁和锁协议来实现相应的隔离级别来进行并发控制会因为锁会造成事务阻塞。而多版本并发控制使得对同一行记录做读写的事务之间不用相互阻塞等待，提高了事务的并发能力，可以认为MVCC是一种解决读写阻塞等待的行级锁。

MVCC的数据库表中每一行数据都可能存在多个版本，对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，通过读写数据时读不同的版本来避免加锁阻塞

• 1、MVCC只支持(已提交读)和(可重复读)隔离级别。
• 2、MVCC能解决脏读、不可重复读问题，不能解决幻读问题。
• 3、MVCC是用来解决读写操作之间的阻塞问题。

隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

• DB_TRX_ID：数据行版本号：大小为6byte，记录最近修改(修改/插入)事务ID，记录创建这条记录/最后一次修改该记录的事务ID
• DB_ROLL_PTR：删除版本号：大小为7byte，记录回滚指针，指向当前记录行的undo log信息(指向该数据的前一个版本数据)
• DB_ROW_ID：行数据隐式id：大小为6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID
  产生一个聚簇索引

ReadView

read view是读视图，其实就相当于一种快照，里面记录了系统中当前活跃事务的ID以及相关信息，主要用途是用来做可见性判断，判断当前事务是否有资格访问该行数据。read view有多个变量：

• trx_ids: 它里面的trx_ids变量存储了活跃事务列表，也就是Read View开始创建时其他未提交的活跃事务的ID列表。例如事务A在创建read view(快照)时，数据库中事务B和事务C还没提交或者回滚结束事务，此时trx_ids就会将事务B和事务C的事务ID记录下来。

假设当前事务生成了一个ReadView，trx_ids列表里的事务id为[60,100]。

1、如果你要访问的记录版本的事务id为50，比当前列表最小的id 60还小，那说明这个事务在ReadView生成之前就提交了，所以对当前活动的事务来说是可访问的。
2、如果你要访问的记录版本的事务id为70,发现此事务在列表id最大值和最小值之间，那就再判断一下 70 这个id是否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问。
3、如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问。

Undo log

Undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以通过回滚指针顺着undo log链找到满足其可见性条件的记录行版本。

在InnoDB里，undo log分为如下两类：
①insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
②update undo log : 事务对记录进行delete和update操作时产生的undo log，在事务回滚时需要

实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

插入：获取最新的事务版本号n,保存n到对应行的行版本号

删除：获取最新的事务版本号n，保存到对应行的删除版本号

修改：变为insert和delete操作的组合，先获取最新的事务版本号n，然后进行数据行拷贝，插入拷贝的数据，保存n到新插入数据行行版本号的字段中，然后保存n到旧的数据行的删除版本号字段中

查询：获取最新的事务版本号n,查询行版本号小于或者等于n的行数据，防止读到其他事务提交的数据

MVCC实现过程原理主要的原理：

参考：https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc

版本记录都是去版本链里面找的，然后根据不同隔离级别生成的ReadView就会有所不同

• 例如：在一个读已提交或者是重复读的级别事务中
  

  有一个事务id为100的事务，修改了name,使得的name等于小明2，但是事务还没提交。则此时的版本链如下：
  此时另一个事务发起了select 语句要查询id为1的记录，那此时生成的ReadView 列表就是[100]。最新版本100是活跃事务不能访问，那就得使用版本链去找了，首先找100的下个版本name为小明1的记录，发现trx_id是60，小于列表中的最小id,所以可以访问，直接访问结果为小明1。那这时候我们把事务id为100的事务提交了，并且新建了一个事务id为110也修改id为1的记录，name修改为小明3，并且不提交事务

  这时候之前那个select事务又执行了一次查询,要查询id为1的记录。这时候会发生两种情况

• 1、已提交读隔离级别
  会重新一个生成一个ReadView，那你的活动事务列表中的值就变了，变成了[110]，通过版本链查trx_id对比，查到的只能是小明2。
• 2、可重复读隔离级别
  ReadView还是第一次select时候生成的ReadView,也就是列表的值还是[100]。所以select的结果是小明1。所以第二次select结果和第一次一样，所以叫可重复读！

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

当前读
需要特别注意的是在MVCC下的可重复读在读操作是防止了幻读，读操作下完全就是按照ReadView进行的快照读。但是对于会对数据的操作例如：

select * from .... where ... for update 
select * from .... where ... lock in share mode 
update .... set .. where ... 
delete from. . where ..

都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。如果事务中都使用快照读，那么就不会产生幻读现象，但是快照读和当前读混用就会产生幻读。