业界公认MySQL单表容量在1千万以下是最佳状态，因为这时它的BTREE索引树高在3~5之间

思考：为什么MySQL单表1千万的索引树高是3~5？

建表优化

字段设计基本原则：
1、满足需求的情况下尽可能选择小的数据类型和指定短的长度
2、拆出变长大字段到单独的表中

小字段主要有两个影响（InnoDB默认数据页大小16K）：
1、相同表记录数的情况下，索引KEY越小，索引树高度就越小，可以减少索引查询次数
2、数据行存储的数据越少，每张数据页就能存更多的数据行，查询的时候减少跨页查询，也就是减少了一次磁盘IO

跨页查询分两种情况：
1、数据页与数据页的跨页查询（对应基本原则1）
2、数据页与溢出页的跨页查询（对应基本原则2）

行格式：

Compact行溢出：

Dynamic行溢出：

什么情况下会溢出
原则：只要一行记录的总和超过8k，就会溢出。

变长大字段类型包括blob、text、varchar

索引优化

索引的主要功能是查找和排序，索引优化是最基础的优化手段，也是程序员的必须掌握的基本知识，是本文的重点篇幅

索引分类

数据库版本：MySQL 8.0.17
CREATE TABLE `indexs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键索引',
  `unique_index` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '唯一索引',
  `normal_index` int(11) NOT NULL DEFAULT '0' COMMENT '普通索引',
  `union_index_a` int(11) NOT NULL DEFAULT '0' COMMENT '联合索引a',
  `union_index_b` int(11) NOT NULL DEFAULT '0' COMMENT '联合索引b',
  `union_index_c` int(11) NOT NULL DEFAULT '0' COMMENT '联合索引c',
  `prefix_index` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '前缀索引',
  `fulltext_index` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '全文索引',
  `func_index` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '函数索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`unique_index`),
  KEY `normal_index` (`normal_index`),
  KEY `prefix_index` (`prefix_index`(10)),
  KEY `union_index_abc_desc` (`union_index_a`,`union_index_b`,`union_index_c` DESC),
  KEY `func_index` ((date(`func_index`))),
  FULLTEXT KEY `fulltext_index` (`fulltext_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

索引类型	100万数据	1000万数据
主键索引	1ms	1ms
唯一索引	1-2ms	1-5ms
普通索引	1-2ms	5-10ms
联合索引a	1-2ms	5-10ms
联合索引ab	1-2ms	5-10ms
联合索引abc	1-2ms	5-10ms
主键range	2-5ms	100-200ms
唯一range	2-5ms	100-200ms
普通range	5-10ms	100-200ms

空间索引

geohash长度	1	2	3	4	5	6	7	8	9
km误差	±2500	±630	±78	±20	±2.4	±0.61	±0.076	±0.01911	±0.00478

CREATE TABLE nodes (  
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键索引',
    `geom` geometry NOT NULL,
    `geohash` VARCHAR(10) AS (st_geohash(geom, 6)) VIRTUAL,
    PRIMARY KEY (`id`),
    SPATIAL KEY idx_nodes_geom (geom),
    key idx_nodes_geohash(geohash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

n := time.Now().Nanosecond()
longitude := fmt.Sprintf("116.%d", n)
latitude := fmt.Sprintf("39.%d", n)
sql := fmt.Sprintf("insert into nodes (geom) value(ST_GeometryFromText('POINT(%s %s)'))", longitude, latitude)

SELECT id, geohash, ST_Distance_Sphere(Point(116.4909484, 39.4909484), geom) as distance, ST_AsText(geom) geom FROM nodes WHERE geohash IN ('wtmknk','wtmkn6','wtmkne','wtmkn5','wtmknh','wtmkns','wtmknd','wtmkn4','wtmkn7') ORDER BY distance LIMIT 10;

LBS应用大多基于MongoDB数据库，MongoDB内置的geoindex非常好用，外加分片特性非常适合于LBS这样的应用，简单的业务用MySQL可以用用（todo）

函数索引

降序索引

没想到应用场景，时间字段排序大部分业务场景都是倒序查询

联合索引最左匹配原则：

index(a, b, c)	索引是否被使用
a = 5	使用到a
a = 5 and b = 6	使用到a,b
a = 5 and b = 6 and c = 7	使用到a, b, c
b = 6 或 b = 6 and c = 7 或 c = 7	使用不到
a = 5 and c = 7	使用到a
a = 5 and b > 6 and c = 7	使用到a,b
a = 5 and b = 6 order by c	使用a, b查找，c排序
a = 5 and b > 6 order by c	使用a, b
a = 5 and b = 6 group by c	使用a, b查找，c分组
a = 5 and b > 6 group by c	使用a, b
a = 5 and b like "%6" and c = 7	使用到a
a = 5 and b like "6%" and c = 7	使用到a, b, c

思考：为什么会有这种现象发生？（下图有问题，还未更正）

a：234455555567
b：644566667766
c：919647890619
使用索引的前提条件：数据有序
a有序可以使用a，b有序可以使用b，c有序可以使用c，order by和group by能使用索引的前提也是数据有序
a一直有序，所以一直可以使用a
a = 5, b = 666677 b有序，所以可以使用b
a = 5 and b = 6, c = 4789，c有序，所以可以使用c
a > 4, b = 66667766, b无序，所以不能使用
再从索引查找过程来理解，a不确定，b = 6,看上图，568节点往哪个分支走呢？左边也有6，右边也有6

哪些情况需要创建索引

1、主键自动创建主键索引
2、频繁作为查询条件的字段
3、查询时与其他表关联的字段
4、查询时需要排序的字段（访问建立索引的字段将大大提高排序速度）
5、查询时统计或分组的字段（本质上分组的过程就是排序）

哪些情况不要创建索引

1、表记录太少（多少为少，1000以下）
2、频繁更新的字段不适合的字段（索引提高了查询速度，但同时也会降低更新的速度，更新操作不单单是更新了记录还要更新索引）
3、where条件用不到的字段
4、数据重复且平均分布的字段，如性别

索引失效的几种情况

1、计算、函数、类型转换（包括隐式转换），会导致索引失效
分析：字段类型是字符串，未加引号导致了隐式的类型转换；函数：left()、 right()、substring()、substring_index()、mid()、substr()
2、联合索引未满足最左匹配原则，导致索引部分失效 （下文详解）
3、like查询是以%开头，导致索引失效
4、查询条件包含!=、<>、or，可能导致索引失效
分析：where a != 1优化器认为查询结果大概率是表的大部分数据，走索引还要回表，不如直接全表扫描
5、索引字段上使用is null， is not null，可能导致索引失效
分析：null值不会像其他值一样出现在索引树的叶子节点上
6、优化器估计使用全表扫描要比使用索引快，则不使用索引
分析：当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

打开慢查询日志，记录慢查询sql

多慢才算慢，大部分业务场景range范围查询避免不了，针对索引的优化也是达到range级别是个平衡点，所以根据根据上文索引查询时间，慢查询时间比较合理的数值：

数据量	慢查询时间
1000万	100-200ms
100万	5-10ms

vi /etc/my.cnf.d/mysql-server.cnf
在[mysqld] 项目下添加如下配置
slow_query_log = 1
slow-query-log-file = /var/lib/mysql/mysql_slow.log
long_query_time = 0.01
log-queries-not-using-indexes = true

systemctl restart mysqld

执行时间最慢的10条的sql：mysqldumpslow -s t -t 10 /var/lib/mysql/mysql_slow.log

找到慢sql，怎么优化？
explain：分析sql索引使用情况详见 EXPLAIN详解
show profiles：SET profiling = 1; 分析sql执行过程（实际工作中用的不多）

COUNT(*)查询

COUNT(常量) 和 COUNT(*)表示的是直接查询符合条件的数据库表的行数，会统计NULL的行
COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数

COUNT(*)优化：选择最小的二级索引来进行count的查询优化，如果没有二级索引才会选择聚簇索引

优化LIMIT分页

LIMIT M,N性能问题：全表扫描，Limit原理是从结果集的M位置处取出N条输出,其余抛弃
1、利用二级索引快速分页
ids = select id from articles order by created_at desc limit 10000,10
select * from articles where id in (ids)
缺点：LIMIT M,N问题已经存在，只是减缓，1000万数据1-2秒
2、利用数据的首尾记录有由全索引扫描转为范围扫描
15 文章21 2020-01-01 19:00:00
12 文章20 2020-01-01 18:00:00
11 文章18 2020-01-01 16:00:00

9  文章17 2020-01-01 16:00:00
8  文章15 2020-01-01 15:00:00
6  文章14 2020-01-01 14:00:00

4  文章13 2020-01-01 14:00:00
3  文章12 2020-01-01 13:00:00
1  文章11 2020-01-01 00:00:00
注意：11、9和6、4时间相同

第一页：select * from articles order by created_at desc, id desc limit 3
第二页：select * from articles where created_at <= '2020-01-01 16:00:00' and (created_at < '2020-01-01 16:00:00' or id < 11) order by created_at desc, id desc limit 3

上一页：select * from articles where created_at >= '2020-01-01 16:00:00' and (created_at > '2020-01-01 16:00:00' or id > 9) order by created_at desc, id desc limit 3
当前第二页：select * from articles where created_at <= '2020-01-01 16:00:00' and (created_at < '2020-01-01 16:00:00' or id < 11) order by created_at desc, id desc limit 3
下一页：select * from articles where created_at <= '2020-01-01 14:00:00' and (created_at < '2020-01-01 14:00:00' or id < 6) order by created_at desc, id desc limit 3

缺点：只能上一页、下一页，没有页码

索引选择性是什么？

索引的选择性（Selectivity），指的是不重复的索引值（也叫基数，Cardinality）和表记录数（#T）的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0-1 ，当选择性越大，越接近1，索引价值也就越大。
索引选择性（Index Selectivity）= 基数（Cardinality）/ 总行数（#T）
SQL = SELECT COUNT(DISTINCT(字段))/COUNT(*) AS Selectivity FROM 表名;

索引选择性与前缀索引

select count(distinct left(prefix_index, 1))/count(*) as sel1, count(distinct left(prefix_index, 2))/count(*) as sel2, count(distinct left(prefix_index, 3))/count(*) as sel3, count(distinct left(prefix_index, 4))/count(*) as sel4 from indexs

sel1	sel2	sel3	sel4
0.0000	0.0002	0.0034	0.0241

单列索引 VS 组合索引 （高并发倾向建立组合索引）
当执行查询时，MySQL只能使用一个索引。如果有三个单列的索引，MySQL会试图选择一个限制最严格的索引。即使是限制最严格的单列索引，它的限制能力也肯定远远低于这三个列上的多列索引。

索引下推技术：

没有索引下推查询过程：
存储引擎层在索引中把符合union_index_a=4366964的一条数据找出来，再回表查这一行记录的全部数据，再返回给server层判断where条件，把符合union_index_c=1562544的数据再过滤出来；
索引下推查询过程：
存储引擎层在索引中把符合union_index_a=4366964的一条数据找出来，判断数据是否符合union_index_c=1562544条件，不符合则过滤，再回表查这一行记录的全部数据，再返回给server层判断where条件；
索引下推技术：减少回表次数，减少存储引擎层和server层的数据传输

代码优化

SQL预编译(Prepared)

1、即时SQL
一条SQL在DB接收到最终执行完毕返回，大致的过程如下：
1. 词法和语义解析；
2. 优化SQL语句，制定执行计划；
3. 执行并返回结果；
如上，一条SQL直接是走流程处理，一次编译，单次运行，此类普通语句被称作 Immediate Statements （即时SQL）。
2、预编译SQL
但是，绝大多数情况下，某一条SQL语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，在SQL语句整个执行过程中，Optimizer是最耗时的。
所谓预编译语句就是将此类SQL语句中的值用占位符替代，可以视为将SQL语句模板化或者说参数化，一般称这类语句叫Prepared Statements。

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程。

此外预编译语句能防止SQL注入（思考原因）

大部分编程语言都支持预编译，可以通过SQL预编译提高数据库执行效率

使用ORM框架避免关联查询，规范API接口
详见正确理解ORM

拆表优化

MySQL单表容量在1千万效率最好，超过1千万需要拆表

水平拆表

单表的数据能保持在一定的量级，有助于性能的提高
切分的表结构相同，应用层改造较少，只需要增加路由规则即可

问题：拆开多少张表合适？10张，50张，100张？
分析：主要还是看业务增长量，比如用户订单表，一天10万订单，100天数据量达到1000万，4张表可以支撑1年，8张表2年，以此类推（分表数量保持4的倍数，原因下文再说）

冷热数据分离
将热数据剥离开来，减少热数据表的数据量，保证热数据的读写性能，冷数据相对来说访问量少，可以再业务上做针对的优化，如下图京东按时间维度拆分历史订单数据

垂直拆分
页溢出在字段很多的情况下，通过大表拆小表，更便于开发与维护，也能避免跨页问题，MYSQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的开销。

拆分依据：
1、将长度较短，访问频率较高的属性尽量放在一个表里，这个表称为主表
2、将字段较长，访问频率较低的属性尽量放在一个表里，这个表称为扩展表
如果1和2都满足，还可以考虑第三点：
（3）经常一起访问的属性，也可以放在一个表里

主机优化

主要针对my.cnf配置的优化

主要是dba的活，专业的事留给专业的人，列举比较重要、好理解的的参数

innodb_buffer_pool_size参数
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
show global variables like 'innodb_buffer_pool_size';
用于innodb 数据和索引的缓存，默认128M，innodb 最重要的性能参数。
建议值：不超过物理内存的80%（如果数据量小，可以是数据量+10%，数据量20G，物理内存是32G，这时候可以设置buffer pool为22G。）
innodb_buffer_pool_size=1G

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，
则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值

SELECT @@innodb_buffer_pool_chunk_size/1024/1024/1024;  //   查看每个缓存池的大小
show global variables like 'innodb_buffer_pool_chunk_size';
show global variables like 'innodb_buffer_pool_instances'; // 缓存池的数量 逻辑CPU数量

参数 innodb_dedicated_server=ON来让MySQL自动探测服务器的内存资源，确定innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数的取值


innodb_flush_log_at_trx_commit参数
show global variables like 'innodb_flush_log_at_trx_commit'; 默认是1
控制事务的提交方式，控制日志刷新到硬盘的方式
0: 由mysql的main_thread每秒将存储引擎log buffer中的redo log写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。（速度快，不安全）
1：每次事务提交时，将存储引擎log buffer中的redo log写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。（安全）
2：每次事务提交时，将存储引擎log buffer中的redo log写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

sync_binlog参数
show global variables like 'sync_binlog'; 默认是1
0：这时候的性能是最好的，但是风险也是最大的。因为一旦系统crash，在binlog_cache中的所有binlog信息都会被丢失
1：表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据，设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多

innodb_flush_log_at_trx_commit=1 和 sync_binlog=1 是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，
binary log只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双1模式会导致频繁的io操作，因此该模式也是最慢的一种方式。

"双1模式"适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单、交易、充值、支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是 innodb_flush_log_at_trx_commit=2，sync_binlog=N (N为500 或1000)

这就是MySQL著名的"双1模式"

思考：主从复制，在半同步下并且不允许退化为异步复制的情况下使用innodb_flush_log_at_trx_commit=2，sync_binlog=N的方案是不是可以保证性能又能保证数据不丢失？靠谱不？

主从分离（针对读的优化）

扩展阅读：

基于biglog日志点复制
当发生故障，需要主从切换，需要找到binlog和position点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。
基于GTID的复制（MySQL>=5.7推荐使用）
GTID 全局事务ID（Global Transaction ID），是一个已提交事务的编号，并且是一个全局唯一的编号。
代替了基于binlog和position号的主从复制同步方式

MySQL的三种复制方式
1、asynchronous 异步复制
2、semisynchronous 半同步复制
3、lossless replication 无损复制 增强版的半同步复制

半同步复制 与异步复制不同的是，采用半同步复制机制时，Slave会向Master发送一个ACK确认消息，同时Master的commit操作也会被阻塞，只有收到了Slave的确认消息之后，Master才会把数据的修改结果返回给客户端。

binlog格式：
1、row level: 仅保存记录被修改细节，不记录sql语句上下文相关信息优点 (默认)
2、statement level: 每一条会修改数据的sql都会记录在binlog中
3、mixed level: 以上两种level的混合使用经过前面的对比，可以发现row level和statement level各有优势，如能根据sql语句取舍可能会有更好地性能和效果；Mixed level便是以上两种leve的结合。

并行复制：（解决主从复制数据延迟问题）
库间并发 
库间并发的理论依据是这样的 ---- 一个实例内可能会有多个库(schema)，不同的库之间没有什么依赖关系，所以在slave那边为
每一个库(schema)单独起一个SQL线程，这样就能通过多线程并行复制的方式来提高主从复制的效率。
这个理论听起来没问题，但是事实上一个实例也就一个业务库，所以这种库间并发就没什么作用了；也就是说这个方式的适用场景比较少，针对这个不足直到"组提交"才解决！
组提交
组提交的理论依据是这样的 --- 如果多个事务他们能在同一时间内提交，这个就间接说明了这个几个事务锁上是没有冲突的，
也是就说他们各自持有不同的锁，互不影响；逻辑上把几个事务看一个组，在slave以"组"为单位分配给SQL线程执行，这样多个SQL线程就可以并行跑了；而且不在以库为并行的粒度，效果上要比"库间并发"要好一些。
WriteSet
WriteSet是站在"组提交"的基础之间建立起来的，在在master上做的自适应打包分组

mysql8.0主从同步：
1、binlog format：row level
2、基于GTID的复制
3、无损复制
4、WriteSet并行复制

show global variables like 'query_cache_type';
主：query_cache_type=OFF
mysql8.0已经废弃查询缓存这个功能

写库拆分（针对写的优化）

误区：主主复制提高不了写速度，只能解决主单点问题，还没见过这种架构应用在实际工作中

二叉树分库分表
主要解决扩容数据库服务器时避免按行进行数据拆分，以2的倍数扩容数据库主机数量，先按库拆分，再按表拆分，每次只需要转移1/2的数据；
常规拆数据过程：

二叉树分库分表拆数据过程：

按8个库16张表的规则分order表

// 用户数据库、表映射关系

$userId = 用户id;
// 一个数据库实例
$conn = 数据库连接;
$conns[0] = $conn;

// 两个数据库实例
$conn1 = 数据库连接1;
$conn2 = 数据库连接2;
$conns[0] = $conn1;
$conns[1] = $conn2;

$dbConn, $dbName, $tableName = getConn($userId, $conns)

func getConn($userId, $conns) {
    $dbCount = 8;
    $tableCount = 16;
    $dbHostCount = len($conns);
    $dbIndex = $userId % $dbCount %  $dbHostCount;
    $tableIndex = $userId / 10 % $tableCount + 1;
    $dbConn = $conns[$dbIndex - 1]
    $dbName = "db_" + $dbIndex
    $tableName = "order_" + $tableIndex
    return $dbConn, $dbName, $tableName
}

分表数量	插入速度
1	14978
2	17665
3	19333
4	19985
5	20183

总结：单表插入主要是建数据速度有瓶颈，多表插入的时候主要是磁盘IO有瓶颈，多表保持3-5张比较合适

网上有文章说分到最后可以一库一表，通过我们分表压测的结果来判断，一库一表发挥不出mysql全部的性能，一库四表一个小集群比较合适，
8个库16张表的话，最后可以分到32个小集群，每个集群2万的插入QPS，32*2 = 64万插入QPS；

估算索引树高：

假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数
说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

Innodb表空间page size的选择（没懂，todo）

Innodb page size 可以选择 8K、 16K、 32K、 64K
从空间和内存利用的角度来讲，page size越大越好。但是从checkpoint的角度来讲恰恰相反，page size越小，性能越好

为了保证数据的一致性，数据库采用检查点（checkpoint）机制将内存中的脏页刷新到磁盘中，同时清空旧的物理日志。

局部性原理和磁盘预读

局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。 
预读的长度一般为页的整倍数，页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储称为一页（在许多操作系统，页的大小通常为4k）