wangzhongyang
commented
3 years ago MySQL索引规范
-
控制索引数量： 【强制】单张表中索引数量不超过7个 【强制】单个索引中的字段数不超过5个 【建议】对字符串使用前缀索引，前缀索引长度不超过8个字符 【强制】避免冗余索引（eg：index(a,b,c)、index(a,b)、index(a)）和重复索引
-
主键准则 【强制】表必须有主键 【强制】不使用更新频繁的列作为主键 【建议】尽量不选择字符串列作为主键 【建议】不使用UUID MD5 HASH这些作为主键（数值太离散了） 【强制】默认使非空的唯一键作为主键 【建议】建议选择自增或发号器
-
常见索引列建议 【建议】SELECT、UPDATE、DELETE语句的WHERE条件列 【建议】ORDER BY、GROUP BY、DISTINCT的字段 【强制】多表join的关联列 【建议】优先考虑覆盖索引 【建议】索引要综合评估数据密度和分布以及考虑查询和更新比例
-
联合索引的顺序 【建议】区分度最高的放在联合索引的最左侧（区分度=count(distinct col)/count(col)） 【建议】尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好） 【建议】使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
-
索引禁忌 【建议】不在低基数列上建立索引，例如“性别” 【强制】不在索引列进行数学运算和函数运算 【强制】禁止使用物理外键 外键用来保护参照完整性，可在业务端实现 对父表和子表的操作会相互影响，降低可用性
-
索引命名 【强制】非唯一索引必须以 idx_字段1_字段2命名 【强制】唯一索引必须以uk_字段1_字段2命名， 【强制】索引名称必须全部小写 【强制】索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率 【建议】能使用唯一索引就要使用唯一索引，提高查询效率 【建议】学会使用explain，如果发现索引选择性差，利用查询优化器合理选择索引
-
强制读取 写数据库 /FORCE_MASTER/和/FORCE_SLAVE/ 如：/FORCE_MASTER/ select * from tb;
-
其他
- 同一字段将OR改为IN，OR的效率为O(n)，IN的效率为O(log N)，N越大差距越大，当N很大时，OR会慢很多. 另外在使用IN的时候注意控制IN中的N个数.
- 避免负向查询（!=,<>, NOT IN ,NOT EXISTS, NOT LIKE）和前缀模糊查询(LIKE ‘%string’、LIKE ‘string’)，不能使用索引。目标表超过 10w 行的，禁止使用前缀模糊查询，前端传过来的“%”和“”需要经过转义
- 使用合理的分页方式以提高分页的效率，数据量很大的情况下，避免使用 offset,limit，这种方式会导致大量的 io，建议修改为WHERE id> LAST_ID ORDER BY id LIMIT N，其中 LAST_ID指代上一页最后一条数据的 id
- 关联查询，遵循小结果集驱动大结果集的原则，MySQL 关联查询的本质是以驱动表作为循环的基础，因此驱动表查询的结果行数越小，循环的次数越小。多使用EXPLAIN分析 SQL 执行计划
-
进阶
- 用LEFT JOIN替代NOT IN。示例：
## not in SELECT col1, col2, col3 FROM tableA WHERE col1 NOT IN ( SELECT col1 FROM tableB );
left join
SELECT col1, col2, col3 FROM tableA a LEFT JOIN tableB b ON a.col1 = b.col1 WHERE b.col1 IS NULL;
2. INSERT 主键冲突 a. INSERT：已存在则报错 a. INSERT IGNORE INTO: 已存在则忽略。在插入时如遇到主键冲突或唯一索引冲突时，会跳过，但是自增 id 的值会增加（不改变原有记录） b. REPLACE INTO: 已存在则替换。遇到主键冲突或唯一索引冲突时，会覆盖原有记录，自增 id 会增加。 3. GROUP_CONCAT 组内排序，对结果进行切割，可以解决每个班级第N名的问题。通过减少子排序打到优化的目的 4. 用户变量@（临时变量） 列出每个同学的全校成绩排名 不借助用户变量的情况： ```sql SELECT a.*, ( SELECT count( score ) FROM students b WHERE b.grade > a.grade ) + 1 AS rank FROM students a;但上述返回的其实是包含并列的排名，假如不考虑并列呢？那我们直接先排序，然后按照顺序挨个标排名就可以了，这时候我们就可以借助用户变量来辅助标记了。
SELECT *, @rank := @rank + 1 FROM students, ( SELECT @rank := 0 ) ORDER BY score DESC- COALESCE 代替if
- 使用主库读的方法有 3 种：
- 直接使用主库连接地址新建连接，该连接下的所有请求都只在主库上执行，因此如使用此方法，一定要确保只有关键业务使用了该连接，用完立即释放，避免其他读业务拖垮主库。
- 包含了事务的所有请求只会路由到主库（包括事务读），如开启事务，一定要控制事务即开即用，只保证事务内只包含读取关键数据的逻辑，其他逻辑严禁写在事务中。
- 在使用读写分离的连接地址时，可通过Hint格式将查询语句强制转发至主实例执行。RDS读写分离支持的Hint格式为/```sql
# *FORCE_MASTER*/，指定后续SQL到主实例执行。如 /*FORCE_MASTER*/ SELECT * FROM table_name;
- 用LEFT JOIN替代NOT IN。示例：
存储引擎有哪些，InnoDB和MyISAM区别、使用场景
大部分内容来自官网MySQL8.0文档，不是的将做标记
存储引擎
InnoDB和MyISAM区别
SQL语句性能分析：explain
输出列字段意义
分段式事务
ACID 模型
innodb MVCC模型（多版本并发控制）
InnoDB对MVCC的实现
记录中的三个隐藏字段、undo日志、Read View来实现的索引优化 什么情况下不能使用到索引
!=或函数like或regexp时左边有通配符order by中，排序条件是查询条件表达式binary log 日志格式及优缺点
now()InnoDB架构
InnoDB内存结构
InnoDB磁盘结构
MySQL锁
select ... for shareselect ... for updateInnoDB事务
autocommit设置InnoDB备份与恢复
mysqlbackup，正在复制InnoDB时，仍然可以对表进行读写复制(replication 主从)
MySQL 的主从 (Master-Slave) binlog 复制机制是 slave 拉取 (pull) 的模式。
优势
复制方法
建议基于使用binary log行的
复制实现
复制安全
主从同步解决方案
组复制(group replication)
能够创建高弹性、高可用、容错的复制拓扑。具有在单主模式(一台接受写和更新)下运行或多主模式(所有服务器都可以写和更新)部署。包含自动脑裂保护机制。每个服务器都有自己的完整数据副本，实现最终一致性。服务器间通过TCP连接，用于内部通信及信息传递。
组复制背景
group_replication_set_as_primary()指定某个成员为mastern <= 2 * f + 1，否则将阻塞要求与限制
一致性保证
这里特指保持全局同步一致性
分布式恢复
每当新成员加入或重新加入复制组时，它必须赶上组成员在加入之前或离开时应用的事务，这个过程称为分布式恢复。
组复制的安全性
MySQL InnoDB Cluster
集群概况
MySQL数据同步方案
阿里Canal
ELK