一、性能相关

1 导致索引失效

不符合左前缀匹配原则
- mysql 会一直向右匹配直到遇到范围查询（>,<,between,like），联合索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
- 联合索引最多只能包含16列
  - 每个表最多16个索引
- 如创建一个联合索引, 那这个索引的任何前缀都会用于查询, (col1, col2, col3)
  - 这个联合索引的所有前缀就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索引查询
  - (col2), (col3), (col2, col3) 都不会启用索引去查询.
  - (col1, col3)会启用(col1)的索引查询
- mysql 5.7 及以下，联合索引，对最后一个索引列进行排序，只能升序，不能降序。
  - 有做一个评论业务，mysql5.7 要求时间倒叙，就是把联合索引最后一个列，按时间戳的负数存的实现的。
索引列不能参与计算
like禁止全模糊或者左模糊
查询where on 条件数据类型不匹配

2 建立索引

1. 注意事项1

尽量选择区分度高的列作为索引
- 区分度的公式是count(distinct col)/count(*)
- 也可以在一些区分度不高的地方建立索引
  - 例如任务表。
对 where,on,group by,order by 中出现的列使用索引
在varchar 字段上建立索引时，必须指定索引长度，而且要注意区分度
尽量的扩展索引，不要新建索引
- 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
组合索引区分度高的在最左边

2. 注意事项2

创建索引之前，分析sql 的查询频率和效率
数据库多种索引类型，选择合适的索引类型
不要选择区分度不高的字段做索引
联合索引多个列
联合索引可以避免会表。
创建过多索引，占用大量的磁盘空间
索引类的长度越长，效率越低

3 查询

1 避免 select * ，需要哪个列查哪个列

尽量用到覆盖索引，索引中就有你的全部数据。explain你的查询可以看到 extra : Using index
- 避免回表

2 order by 注意利用索引的有序性，避免出现（file_sort ）外部排序

实践
- 业务需要评论时间倒序，使用dateline bigint 评论时间字段
- mysql 5.6/5.7 不支持联合索引倒序，数据库存 dateline 的相反数。就可以直接asc
  - 本来查询条件是下面所有字段
    - first 通过dc层数据过滤掉
    - status 通过 dateline<= 0 过滤掉。当其他状态的时候顺便把 dateline 变成大于0

create table if not exists zhiya_forum.post_comment
(
    id bigint not null comment 'id'
        primary key,
    post_id bigint default 0 not null comment '帖子Id',
    author_id bigint default 0 null comment '评论人Id',
    dateline bigint default 0 not null comment '评论时间',
    first tinyint default 0 not null comment '是否是首条评论(0:否,1:是)',
    status int default 0 not null comment '状态(1:正常,2:审核中,4:已删除)',
    deleted tinyint default 0 not null comment '是否已删除(0:未删除,1:已删除)'
    ...
)
comment '帖子评论表' charset=utf8mb4;


 create index idx_post_id_author_id_deleted_dateline
    on post_comment (post_id, author_id, deleted, dateline);

create index idx_post_id_deleted_dateline
    on post_comment (post_id, deleted, dateline);

-- 执行计划    
explain
select * from post_comment where post_id = 5198700340765002879 and author_id=5168092810391368748  and deleted = 0 and  dateline < 0 order by dateline ,id limit 5 ;

+--+-----------+------------+-----+-------------------------------------------------------------------+--------------------------------------+-------+----+----+---------------------+
|id|select_type|table       |type |possible_keys                                                      |key                                   |key_len|ref |rows|Extra                |
+--+-----------+------------+-----+-------------------------------------------------------------------+--------------------------------------+-------+----+----+---------------------+
|1 |SIMPLE     |post_comment|range|idx_post_id_deleted_dateline,idx_post_id_author_id_deleted_dateline|idx_post_id_author_id_deleted_dateline|26     |NULL|79  |Using index condition|
+--+-----------+------------+-----+-------------------------------------------------------------------+--------------------------------------+-------+----+----+---------------------+

3 用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写
例子快速定位需要获取的 id 段，然后再关联： SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

4 limit 优化，如果limit语句保护 ordery by 来作为分页查找，可以先获取上一页最大/最小值作为查询条件。看具体情况

create table xiexiaoping_tc_user_sign
(
    id          bigint auto_increment
        primary key,
    uid         bigint                                    not null comment '用户uid',
    integral    bigint unsigned default 0                 null comment '获取的积分',
    create_time timestamp       default CURRENT_TIMESTAMP not null comment '签到日期',
    update_time timestamp       default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '最后更新时间'
)comment '用户签到记录表';
explain  select id,create_time from xiexiaoping_tc_user_sign order by  id asc  limit 1,20;


explain select id,create_time from xiexiaoping_tc_user_sign where id > 5186995987949814399 order by  id asc limit 20
-- 结果
id/select_type/table/type/possible_keys/key/key_len/ref/rows/Extra
1,SIMPLE,xiexiaoping_tc_user_sign,index,null, PRIMARY, 8,null,18,null    
1,SIMPLE,xiexiaoping_tc_user_sign,range,PRIMARY,PRIMARY,8,null,16,Using where


-- 实际
SELECT `id`,`business_code`,`field_name`,`content`,`status`,`modify_time` "
FROM `i18n_text`  WHERE `id`>#{id}  AND `status`=1  ORDER BY `id`  LIMIT #{pageSize}

5 UNION 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

如果没有 ALL，mysql 会给临时表加上 DISTINCT 选项，对临时表数据做唯一性检查

6 避免使用大表的JOIN，mysql优化器对JOIN优化策略过于简单

尽量使用小表驱动大表的方式进行查询
- 如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表
- select name from A where id in (select id from B);

7 sql性能优化至少达到range级别。

8 当某一列全是NULL，count(col) 返回的结果是0，但sum(col)返回结果是NULL，使用sum() 时需要注意NPE 问题（空指针异常）

9 count(distinct col) 计算除NULL以外的不重复行号,count(*) 来统计行

10 判断是否存在没有索引的列，不要用count(*)，用limit 1, 当数据量超过 1一亿条才有效果。

数据量小的时候cunt(*) 有优化

- 有索引 结果一样的。
explain  select count(*) from `i18n_text` where `business_code` = 'ty_app_user'  and `field_name` = 'uploadIdError';

+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+
|id|select_type|table    |type |possible_keys                                            |key                        |key_len|ref        |rows|Extra      |
+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+
|1 |SIMPLE     |i18n_text|const|uk_field_name_business_code,idx_business_code_modify_time|uk_field_name_business_code|388    |const,const|1   |Using index|
+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+

explain SELECT 1 FROM `i18n_text` WHERE  `business_code` = 'ty_app_user'  and `field_name` = 'uploadIdError' LIMIT 1 ;

+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+
|id|select_type|table    |type |possible_keys                                            |key                        |key_len|ref        |rows|Extra      |
+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+
|1 |SIMPLE     |i18n_text|const|uk_field_name_business_code,idx_business_code_modify_time|uk_field_name_business_code|388    |const,const|1   |Using index|
+--+-----------+---------+-----+---------------------------------------------------------+---------------------------+-------+-----------+----+-----------+

- 没有索引 的列，也是一样的。

11 禁止超过3个表join，join字段，数据类型必须一致

12 慢查询日志定位到sql，然后explain 去分析

13 其他

在代码中写分页逻辑时，若为count 为0，应该直接返回，避免执行后面的分页语句
数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句

4 建表

1. 所有字段均定义为NOT NULL 除非你真的想存NULL

浪费存储空间，因为InnoDb需要额外一个字节存储。
默认值NULL过多会影响优化器选择执行计划。

2. 字段类型在满足需要条件下越小越好，使用unsigned存储非负整数，实际使用时存储负数的场景不多。

3. 使用timestamp 存储时间

timestamp
- 内存
  - 4个字节
  - TIMESTAMP(6) 占用6字节
- 带有时区属性
  - set time_zone= '-08:00'
    - 就可以看到列timestamp 的时间变化。
- TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07
DATETIME
- 内存
  - 占用8个字节
  - DATETIME(6) 也是占用8字节

 register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

4. 使用 varchar(M) 存储变成字符串。 M指的是字符数

5. 使用 unsigned int 存储IPv4 地址而不是char(15),这种方式只能存储IPv4,存储不了ipv6

6. 使用 decimal 存储精确浮点数，用float类型可能会存在数据误差

小数类型为 decimal，禁止使用 float 和 double。
- float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

金融业务可以用 long 精确到分， decimal
- 如果只是做加减可以用long
  - 也可以结果用long ，运算用decimal
- 如果做乘除，可以用 decimal

7. 少用blob text

8. 如果存储的字符串长度几乎相等，使用 char 定长字符串类型

9. varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

10. 表必备三字段： id, gmt_create, gmt_modified

id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1
gmt_create, 类型 datetime 类型，数据库主动创建
gmt_modified，类型 datetime 类型，数据库被动更新

11. 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

12 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

5 InnoDB 与 MyISAM 区别

场景
- MyISAM 适合插入少，查询多。
- InnoDB 适合高并发，大数据量，支持事务，技术方案很成熟。
1 InnoDB支持事务，MyISAM不支持事务
2 InnoDB支持外键，MyISAM不支持
3 InonDb 聚集索引，MySAM非聚集索引
- 聚集索引(数据存储方式) 叶子节点存储数据行，据行的物理顺序与列值（一般是主键列）的逻辑顺序一致
- 辅助索引需要两次查询，先查询到主键，然后通过主键查到数据，数
- 非聚集索引叶子节点存储数据行对于页的指针
4 InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁
InnoDB
- InnoDB 大多数情况使用B+树建立索引，B+树索引能找到数据行对应的页，b+树索引可以分为：
  - 聚集索引
    - 聚集索引中存放着一条行记录的全部信息
    - 正常的表应该有且仅有一个聚集索引（绝大多数情况下都是主键）
  - 辅助索引
    - 只包含索引列和一个用于查找对应行记录的『书签』( InnoDB 中这个书签就是当前记录的主键)

6 分布式事务

2PC
- 请求提交阶段
  - 协调器向所有参与者发送事务请求，询问是否可以执行事务，然后各个参与者响应YEs/No
- 提交阶段
  - 协调器向所有参与者发出提交指令，参与者提交失败/超时则回滚
- 不足
  - 提交协议是阻塞协议，如果事务协调器宕机，某些参与者将无法解决他们的事务问题
3PC
- 提交请求阶段
- 预提交都确认预提交，进入三阶段
- 提交只要预提交成功，则一定要保证真实提交成功，即使协调器下一阶段不可用，一般是通过重试补偿的策略

7 索引类型

b+树索引
- 聚集索引
- 辅助索引
- 非聚集索引
哈希索引
倒排索引实现全文检索

7.1 mysql为什么用B+ 树实现索引

叶子节点指针相连，B+树更适合范围查询
非叶子节点只保持关键字跟指针，B+数可以容纳更多关键字。
b+树查询效率稳定，任何关键字的查询都是从跟节点到叶子节点，效率一样。

二、问题总结

1. 在读多写少的网络环境下，MySQL 如何优化数据查询方案

解决方案
- 热点数据缓存命中率足够高通过 Redis 缓存降低 DB 压力
- 非热点数据读写分离
  - 读写分离前提 MySQL 集群拆分成“主 + 从”结构的数据集群
MySQL 主从复制的原理
- 过程
  - MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应
  - 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
  - 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性
- 问题
  - 一个主库一般只跟 2～3 个从库
    - 因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽
  - MySQL 主从复制还有哪些模型
    - 同步复制
      - 事务线程要等待所有从库的复制成功响应。
    - 异步复制
      - 事务线程完全不等待从库的复制成功响应
    - 半同步复制
      - 不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行
  - 主从复制延迟导致的查询异常解决
    - 使用数据冗余
      - 连主库的服务，异步调用连从库服务，把所有信息都传给从库，避免从从库中查询
    - 使用缓存
      - 写入主库同时，写入redis 缓存。
    - 直接查询主库
    - 分库，将一个主库拆分为4个主库，每个主库的写并发就500/s，此时主从延迟可以忽略。
    - 最基本的，从库，并行复制。但是意义不大。
  - 代码实现主库和从库的数据库访问
    - 把所有数据源配置到配置文件，代码逻辑切换数据源。
      - 缺点 sql路由侵入代码逻辑，复杂工程不利于代码维护
    - 独立部署的代理中间件，如 MyCat
      - 缺点有一定的性能损耗，需要中间建技术沉淀

2. 写多读少：MySQL 如何优化数据存储方案

问题
- 数据库写入请求量过大，导致系统出现性能与可用性问题
- 解决
  - 对存储数据做分片
  - 数据库做“分库分表”
    - 垂直拆分
    - 水平拆分
    - 垂直水平拆分
    - 垂直水平拆分
- 如何确定分库还是分表
  - 分表
    - 当数据量过大造成事务执行缓慢时，就要考虑分表。减少每次查询数据总量是解决数据查询缓慢的主要原因（查询指事务中的查询和更新操作）
  - 分库
    - 为了应对高并发，一个数据库实例撑不住，就把并发请求分散到多个实例中去。
垂直拆分
- 垂直拆分分库
  - 根据数据的业务相关性进行拆分
    - 如一个数据库里面既存在商品数据，又存在订单数据，那么垂直拆分可以把商品数据放到商品库，把订单数据放到订单库。一般情况，垂直拆库常伴随着系统架构上的调整
  - 优点
    - 把不同的业务数据进行隔离，让系统和数据更为“纯粹”，更有助于架构上的扩展
  - 不足
    - 不能解决某一个业务的数据大量膨胀的问题
      - 如如商品系统接入了一个大客户的供应链，对于商品数据的存储需求量暴增
      - 把数据拆分到多个数据库和数据表中，也就是对数据做水平拆分
- 垂直拆分分表
  - 把一个有很多字段的表给拆分成多个表
    - 将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去
  - 优点
    - 因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好
水平拆分
- 把单一库表数据按照规则拆分到多个数据库和多个数据表中
- 拆分的规则
  - 哈希分片
    - 缺点
      - 无法避免单一热点问题
    - 使用
      - userId md5加密结果hash后取模
        
        最终采用的是
      - userId hash后取模
        
        hash 冲突
      - userId直接取模
        
        出现id经常为偶数
  - 范围分片
    - 是按照某一个字段的区间来拆分
      - 比如按照商品的所属品类进行分片， Range 分片就可以加入对于业务的预估
    - 也有热点数据问题问题
      - 垂直扩展
        
        提升单机处理能力，增强硬件
      - 分片元数据
        
        分片的规则记录在一张表里面，每次执行查询的时候，先去表里查一下要找的数据在哪个分片中

3. 分库分表

1 分库分表过程

1 选定分片键
- 如用户表就用户id
2 如果是client层如shardingjdbc，修改代码。
3 之前库的数据迁移

2. 如何把单库表的系统迁移到分库分表上。

停机迁移方案
- 过程
  - 网站挂个公告
  - 写好一个导数的一次性工具，然后将单库单表的数据读出来，写到分库分表里面去。
- 回滚方案
  - 如果数据迁移失败，改回之前配置，恢复服务。
- 优点
  - 简单
双写迁移方案
- 描述线上系统里面，之前写库的地方，增删改操作，除了对老库删改，都加上新库的增删改。
  - 同时写两个库，老库和新库。
- 过程
  - 写一个导数据工具，跑起来读老库写新库，写的时候
    - 一条数据新库是否存在
      - 存在根据 gmt_modified 这类字段判断这条数据最后修改的时间
      - 不存在直接写入新库
    - 导完一轮之后，自动校验每条老库新库每条数据是否一致，反复循环。最终一致。
    - 重新部署一次，仅仅用基于分库分表的新代码。
  - image.png
  - 不足
    - 迁移代码和业务代码强耦合了，并且这些业务接口由于双写会导致耗时有所增长。
  - 优化
    - 这个可以通过订阅表的binlog，来进一步优化
      - 可参考 alibaba canal
        
        canal 要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费
实践
- 32 * 32 来分库分表，每个库里一个表分为32张表，一共就是 1024 张表。
- 策略
  - 定位库
    - id % 32 库
  - 定位表
    - id/32 % 32 表

3. 动态扩容缩容的分库分表

原理
- 58沈剑数据库秒级平滑扩容架构方案
- 原理成倍扩容，避免数据迁移。
  - n % 3 = 1 的数据, 会变成 n % 6 = 4
  - n % 3 = 2 的数据，会变成 n % 6 = 5
过程
1. 现状
  - image.png
    - 两个库，如何平滑扩容，增加实例数，降低单库量。
      2.扩容过程
  1. 修改配置
    - image.png
    1. 数据库实例所在的机器做双虚ip，原来%2=0的库是虚ip0，现在增加一个虚ip00，%2=1的另一个库同理
    2. 修改服务的配置，将2个库的数据库配置，改为4个库的数据库配置，修改的时候要注意旧库与辛苦的映射关系：
      - %2=0的库，会变为%4=0与%4=2;
      - %2=1的部分，会变为%4=1与%4=3;
      - 这样修改是为了保证，拆分后依然能够路由到正确的数据。
  2. reload配置，实例扩容
    - image.png
    - 服务层reload配置
    - 整个过程可以逐步重启，对服务的正确性和可用性完全没有影响：
      - 即使%2寻库和%4寻库同时存在，也不影响数据的正确性，因为此时仍然是双主数据同步的
      - 服务reload之前是不对外提供服务的，冗余的服务能够保证高可用
  3. 收尾工作，数据收缩
    - image.png
    1. 把双虚ip修改回单虚ip
    2. 解除旧的双主同步，让成对库的数据不再同步增加
    3. 增加新的双主同步，保证高可用
    4. 删除掉冗余数据，例如：ip0里%4=2的数据全部干掉，只为%4=0的数据提供服务
- 总结
  - 方案能够实现n库扩2n库的秒级、平滑扩容，增加数据库服务能力，降低单库一半的数据量，
  - 其核心原理是：成倍扩容，避免数据迁移。
案例
- 一开始每个表拆分到 32个库，每个库32张表，总共 1024表。
  - 如果每个库正常承载 1000 写并发， 32 个库就可以承载 32000 的写并发。
- 路由规则
  - id % 32 = 库
  - id / 32 % 32 = 表
优点
- 不需要对数据迁移。

4. 数据库中间件

代理层 mybat
- 需要部署运维成本高。
- 对各个项目都是透明的。
客户端层 sharding-jdbc
- 不用部署，运维成本低
- 不需要代理层的二次转发请求，性能高
- 如果遇到升级各个系统都重新升级再发布。

5. 跨库join的问题

垂直分库

全局表
- 所有模块都需要依赖的一些字典表，每个库都保存一份
- 这类数据很少发生修改。
字段冗余
- 空间换时间体现
- 但是冗余字段修改了后，是否要同步更新？看业务要求了
数据同步
- 定时A库中的tab_a表和B库中tbl_b有关联，可以定时将指定的表做同步。
- 需要在性能影响和失效影响中取的平衡。
业务系统组装
- 可以先查询符合条件的id，再业务服务获取数据组装。

6. 分库分表后如何进行分页查询

背景
- 一张 10亿数据的订单表，根据id hash 存储在N台mysql 节点中，设计一套方案，以time字段的顺序将分页查询的结果展示到前端
结果

id	time	普通字段
主键，分片键	二级索引	普通字段

思考方向

引入ES，由ES完成分页查询
新建（time，id）映射表，将非分片键的查询转换成分片键查询。
合并所有分片的返回结果，在服务端进行筛选。

方案1

image.png

4. 常见问题

1. 如何平滑添加字段，加索引

直接 alter table add column
- 项目早期，数据量极少，不会出现锁冲突。
提前预留了扩展字段，或者留一个扩展字段，使用key/value 方式存储，新增字段时，直接加一个key 就好了。
- 操作空间浪费，预留多少很难控制，扩展差
新增一张表（增加字段），迁移原表数据，再重新命名新表作为原表。
- 例如使用 INSERT INTO ... SELECT 语句将旧表的数据复制到新表中。
  - 如果要保证一致。
    - 在复制数据之前，先暂停旧表的写入操作。可以使用ALTER TABLE命令将旧表设置为只读模式，例如：ALTER TABLE old_table_name SET READ ONLY;。
    - 使用INSERT INTO ... SELECT语句将旧表的数据复制到新表中。
    - 完成数据复制后，再恢复旧表的写入操作。可以使用ALTER TABLE命令将旧表设置为可读写模式，例如：ALTER TABLE old_table_name SET WRITEABLE;。
    - 如果需要确保数据的一致性，可以在复制数据的过程中使用事务。例如，可以使用MySQL的START TRANSACTION和COMMIT语句来确保数据的完整性。
- 删除旧表并将新表重命名为旧表的名称。这种方法可以确保在加字段的过程中，原始表仍然可用。
在线DDL工具
- 一些数据库管理工具提供了在线DDL功能，可以在不锁定表的情况下添加字段。

总结
- 线上一般用2，3，4种。

3. mysql经验

1. 单机mysql 性能

一个 mysql 实例一般硬件大概支持 2000 的读写。主从同步大概20ms，30ms。

2. 大型项目的join操作

95% 都是单边增删查改，如果有join逻辑，放到java代码里来做。
sql 越简单，后续迁移分库分表，读写分离，成本越低。

3. 建立一个varchar(300) 的索引失败，为什么。

索引存在页中，一页默认16k
- B+ 树结构，每个叶子节点上包含两条记录（否则退化成链表）
- 所以一个记录最多不能超过8k。
- 又因为辅助索引，包含了主键索引/下级辅助索引，单个索引只有 4k
- 又因为需要预留和辅助空间，扣掉后不能超过 3500 取整数是（1024 * 3）
- 因为mysql 行格式
  - 一种是767
  - 两种支持 3072
- 又因为 utf8 三个字节表示一个字符， 767 = 256 * 3 - 1，最多表示255个字符。
可以修改 innodb_large_prefix 参数
- 是否允许单列的索引长度超过767字节，有ON和OFF两个取值

3. mysql 怎么查看连接池是否已满。

查看连接数配置
- show variables like '%max_connections%'
查看当前连接数
- show full processlist

4. mysql深分页怎么优化

1 用延迟关联子查询优化超大分页场景
2 假如是自增id，让前台把除了基本分页参数pageNo,pageSize外上一页最大id 带过来。
- 不支持随机跳页
3 利用es 做分页，直接把id 关联出来。

create index `test_index_str(2000)_index`
    on test_index (str(300));

 错误信息 ：Specified key was too long; max key length is 767 bytes

5. mysql 怎么计算n 层树高有多少条记录

计算平均数据长度

show table status like 'index_test_big';
+--------------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------------+--------+--------------+-------+
|Name          |Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time        |Update_time|Check_time|Collation      |Checksum|Create_options|Comment|
+--------------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------------+--------+--------------+-------+
|index_test_big|InnoDB|10     |Dynamic   |3775|52            |196608     |0              |147456      |0        |5772          |2023-10-05 16:33:56|NULL       |NULL      |utf8_general_ci|NULL    |              |       |
+--------------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------------+--------+--------------+-------+
 Avg_row_length 就是每条数据的平均 52

根据B+数，计算有多少页，每一页能存放最大数量

mysql 一页默认大小是 16k。每一页存放 16k / 52 = 315
1层能存放的最大关键字是：1170
- 一个关键字包含（关键字（主键bigint 类型 8字节）+指针（6字节））总共14字节
- 16k / 14 b = 1170
B+树 n个关键字有n个孩子。所以二层最大 1170 个孩子。
- 3层有 1170 * 1170 个页。每一页能存放 index_test_big 表数据 11701170315= 4亿多。

4 mysql 细节

1. 过期读

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。

1.1 应用

使用数据冗余
- 连主库的服务，异步调用连从库服务，把所有信息都传给从库，避免从从库中查询
使用缓存
- 写入主库同时，写入redis 缓存。

1.2 mysql 层面方案

强制走主库方案
- 强制走主库方案其实就是，将查询请求做分类。
  - 通常情况下，我们可以将查询请求分为这么两类：
    - 对于必须要拿到最新结果的请求，强制将其发到主库上。
    - 对于可以读到旧数据的请求，才将其发到从库上。
- 问题
  - 所有查询都不能是过期读的需求。
sleep方案；
- 主库更新后，读从库之前先sleep一下。
- 具体
  - 具体的方案就是，类似于执行一条select sleep(1)命令。
    - 例如商品平台，发布了商品，有个几秒延迟提示，就达到sleep 的目的。
- 这个sleep方案确实解决了类似场景下的过期读问题。但，从严格意义上来说，这个方案存在的问题就是不精确。
  - 如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒；
  - 如果延迟超过1秒，还是会出现过期读。
- 问题
  - 可以解决类似Ajax场景下的过期读问题，但是不靠谱。
判断主备无延迟方案；
- 实现
  - 我们知道show slave status结果里的seconds_behind_master参数的值，可以用来衡量主备延迟时间的长短。
  - 第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求。
  - seconds_behind_master的单位是秒，如果你觉得精度不够的话，还可以采用对比位点和GTID的方法来确保主备无延迟
- 对比位点确保主备无延迟：
  - show slave status结果
    - image.png
    - Master_Log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点；
    - Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点。
  - 如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成。
- 对比GTID集合确保主备无延迟
  - Auto_Position=1 ，表示这对主备关系使用了GTID协议。
  - Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合
  - Executed_Gtid_Set，是备库所有已经执行完成的GTID集合。
  - 如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
- 对比位点和对比GTID这两种方法，都要比判断seconds_behind_master是否为0更准确。
配合semi-sync方案；
- 要解决这个问题，就要引入半同步复制，也就是semi-sync replication。
- semi-sync做了这样的设计
  1. 事务提交的时候，主库把binlog发给从库；
  2. 从库收到binlog以后，发回给主库一个ack，表示收到了；
  3. 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。
- 问题
  - 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
  - 持续延迟的情况下，可能出现过度等待的问题。
等主库位点方案；

select master_pos_wait(file, pos[, timeout]);
# 逻辑
1. 它是在从库执行的；
2. 参数file和pos指的是主库上的文件名和位置；
3. timeout可选，设置为正整数N表示这个函数最多等待N秒。

# 结果
这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。
当然，除了正常返回一个正整数M外，这条命令还会返回一些其他结果，包括：
1. 如果执行期间，备库同步线程发生异常，则返回NULL；
2. 如果等待超过N秒，就返回-1；
3. 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0。

可以解决半同步复制的问题
- 流程
  1. trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position；
  2. 选定一个从库执行查询语句；
  3. 在从库上执行select master_pos_wait(File, Position, 1)；
  4. 如果返回值是>=0的正整数，则在这个从库执行查询语句；
  5. 否则，到主库执行查询语句。
- 问题
  - 如果所有的从库都延迟超过1秒了，那查询压力不就都跑到主库上了
  - 按照我们设定不允许过期读的要求，就只有两种选择，
    - 一种是超时放弃，
    - 一种是转到主库查询。

等GTID方案。

是全局事务标识。它具有全局唯一性，一个事务对应一个GTID

MySQL中同样提供了一个类似的命令：
 select wait_for_executed_gtid_set(gtid_set, 1);
 
#这条命令的逻辑是：
等待，直到这个库执行的事务中包含传入的gtid_set，返回0；
超时返回1。

如果你的数据库开启了GTID模式，对应的也有等待GTID的方案。
- 流程
  1. trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
  2. 选定一个从库执行查询语句；
  3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
  4. 如果返回值是0，则在这个从库执行查询语句；
  5. 否则，到主库执行查询语句。
- 怎么能够让MySQL在执行事务后，返回包中带上GTID呢
  - 你只需要将参数session_track_gtids设置为OWN_GTID，
  - 然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可

3. InnoDB自增列重复值问题

当自增id操作最大值的时候，也会重复
如果表对象，从内存中置换出来，id 也可能重复

3.1 复现问题

use test;
drop table if exists t1;
create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
select * from t1;
# 输出
+--+-+
|id|a|
+--+-+
|1 |2|
|2 |2|
|3 |2|
+--+-+

delete from t1 where id=2;
delete from t1 where id=3;
select * from t1;

# 关闭mysql，再启动mysql,然后再插入一条数据

# 我们看到插入了（2,2），而如果我没有重启，插入同样数据我们得到的应该是（4,2)。 上面的测试反映了mysqld重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况。
insert into t1 values (null,2);
select * FROM T1;

# 输出
+--+-+
|id|a|
+--+-+
|1 |2|
|2 |2|
+--+-+

自增id重复利用在某些场景下会出现问题。
- 假设t1有个历史表t1_history用来存t1表的历史数据
- 那么mysqld重启前,ti_history中可能已经有了（2,2）这条数据，而重启后我们又插入了（2，2），当新插入的(2,2)迁移到历史表时，会违反主键约束。

3.2 原因分析


show create table t1;

# +-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# |Table|Create Table                                                                                                                                                      |
# +-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# |t1   |CREATE TABLE `t1` (                                                                                                                                               |
# |     |`id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                             |
# |     |`a` int(11) DEFAULT NULL,                                                                                                                                         |
# |     |PRIMARY KEY (`id`)                                                                                                                                                |
# |     |) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8                                                                                                             |
# +-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

AUTO_INCREMENT
- 建表时可以指定 AUTO_INCREMENT值，不指定时默认为1，
- 这个值表示当前自增列的起始值大小，如果新插入的数据没有指定自增列的值，那么自增列的值即为这个起始值。
- 对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。
- show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。
mysqld 重启后，如何得到AUTO_INCREMENT呢？
- mysql采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT。而这种方法就是造成自增id重复的原因。
MyISAM自增值没有这个问题
- myisam会将这个值实时存储在.MYI文件中（mi_state_info_write）。mysqld重起后会从.MYI中读取AUTO_INCREMENT值（mi_state_info_read)

3.3 问题修复

修复
1. 将AUTO_INCREMENT最大值持久到frm文件中。
  - 第一种方法直接写文件性能消耗较大，这是一额外的操作，而不是一个顺带的操作。
2. 将 AUTO_INCREMENT最大值持久到聚集索引根页trx_id所在的位置
  - 采用第二种方案。为什么选择存储在聚集索引根页页头trx_id，页头中存储trx_id,只对二级索引页和insert buf 页头有效（MVCC)。而聚集索引根页页头trx_id这个值是没有使用的，始终保持初始值0。
  - 正好这个位置8个字节可存放自增值的值。我们每次更新AUTO_INCREMENT值时，同时将这个值修改到聚集索引根页页头trx_id的位置。

4 mysql 没有命中我们期望的索引

采用 force index 强行选择一个索引，最好用配置中心配置，可以调整。不然上线调整不够敏捷。
可以考虑修改语句，引导 MySQL 使用我们期望的索引
可以新建一个更适合的索引，来提供给优化器选择，或删除误用的索引。
- 删除误用的索引有时候业务可能真的不需要这个索引

三、mysql 系统信息查询

1. mysql 额外信息查询

参考
- 阿里巴巴java开发手册
- MySQL索引原理及慢查询优化

数据库总结

一、性能相关

1 导致索引失效

2 建立索引

1. 注意事项1

2. 注意事项2

3 查询

1 避免 select * ， 需要哪个列查哪个列

2 order by 注意利用索引的有序性，避免出现 （file_sort ）外部排序

3 用延迟关联或者子查询优化超多分页场景

4 limit 优化，如果limit语句保护 ordery by 来作为分页查找，可以先获取上一页最大/最小值作为查询条件。 看具体情况

5 UNION 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

6 避免使用大表的JOIN，mysql优化器对JOIN优化策略过于简单

7 sql性能优化 至少达到range级别。

8 当某一列全是NULL，count(col) 返回的结果是0，但sum(col)返回结果是NULL， 使用sum() 时需要注意NPE 问题（空指针异常）

9 count(distinct col) 计算除NULL以外的不重复行号,count(*) 来统计行

10 判断是否存在 没有索引的列，不要用count(*)， 用limit 1, 当数据量超过 1一亿条 才有效果。

11 禁止超过3个表join，join字段，数据类型必须一致

12 慢查询日志 定位到sql，然后explain 去分析

13 其他

4 建表

1. 所有字段均定义为NOT NULL 除非你真的想存NULL

2. 字段类型在满足需要条件下越小越好，使用unsigned存储非负整数，实际使用时存储负数的场景不多。

3. 使用timestamp 存储时间

4. 使用 varchar(M) 存储变成字符串。 M指的是字符数

5. 使用 unsigned int 存储IPv4 地址而不是char(15),这种方式只能存储IPv4,存储不了ipv6

6. 使用 decimal 存储精确浮点数，用float类型可能会存在数据误差

7. 少用blob text

8. 如果存储的字符串长度几乎相等，使用 char 定长字符串类型

9. varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率。

10. 表必备三字段： id, gmt_create, gmt_modified

11. 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

12 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检 索速度。

5 InnoDB 与 MyISAM 区别

6 分布式事务

7 索引类型

7.1 mysql为什么用B+ 树实现索引

二、问题总结

1. 在读多写少的网络环境下，MySQL 如何优化数据查询方案

2. 写多读少：MySQL 如何优化数据存储方案

3. 分库分表

1 分库分表过程

2. 如何把单库表的系统 迁移到分库分表上。

3. 动态扩容缩容的分库分表

4. 数据库中间件

5. 跨库join的问题

垂直分库

6. 分库分表后如何进行分页查询

思考方向

方案1

4. 常见问题

1. 如何平滑添加字段， 加索引

3. mysql经验

1. 单机mysql 性能

2. 大型项目的join操作

3. 建立 一个varchar(300) 的 索引失败， 为什么。

3. mysql 怎么查看连接池是否已满。

4. mysql深分页怎么优化

5. mysql 怎么计算n 层树高有多少条记录

4 mysql 细节

1. 过期读

1.1 应用

1.2 mysql 层面方案

3. InnoDB自增列重复值问题

3.1 复现问题

3.2 原因分析

3.3 问题修复

4 mysql 没有命中我们期望的索引

三、mysql 系统信息查询

1. mysql 额外信息查询

1 避免 select * ，需要哪个列查哪个列

2 order by 注意利用索引的有序性，避免出现（file_sort ）外部排序

4 limit 优化，如果limit语句保护 ordery by 来作为分页查找，可以先获取上一页最大/最小值作为查询条件。看具体情况

7 sql性能优化至少达到range级别。

8 当某一列全是NULL，count(col) 返回的结果是0，但sum(col)返回结果是NULL，使用sum() 时需要注意NPE 问题（空指针异常）

10 判断是否存在没有索引的列，不要用count(*)，用limit 1, 当数据量超过 1一亿条才有效果。

12 慢查询日志定位到sql，然后explain 去分析

9. varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

12 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

2. 如何把单库表的系统迁移到分库分表上。

1. 如何平滑添加字段，加索引

3. 建立一个varchar(300) 的索引失败，为什么。