目录

  • #1709 - Index column size too large. The maximum column size is 767 bytes.是什么原因导致的，如何处理？
  • 举几个你遇到的MySQL中的隐式转换案例
  • 你觉得理想的MySQL备份策略应该是怎样的，实际上你真正的备份策略又是怎样的，为什么会有不同，出于什么原因呢？
  • MySQL执行sql时一个表只能用到一个索引吗？
  • MySQL的前缀索引有什么特点？使用上有什么使用场景和限制么?

一、#1709 - Index column size too large. The maximum column size is 767 bytes.是什么原因导致的，如何处理？（一）原因
1、对于行格式为REDUNDANT和COMPACT的InnoDB表来说，索引的最大长度为767字节
2、行格式为DYNAMIC和COMPRESSED格式的InnoDB表最大索引长度允许达到3072字节（注：8.0版本之前，要求innodb_file_format=Barracuda，且innodb_large_prefix=1。 且8.0以后该参数已废弃）
3、注意，即便设置innodb_large_prefix=1，但若行格式是REDUNDANT和COMPACT时调整无效，且5.7.6版本以前该参数默认是关闭的，5.7.7版本后默认开启。8.0后该参数也已废弃
4、索引长度计算规则：
4.1 一般地，key_len等于索引列类型字节长度，例如tinyint类型为1字节，int类型为4字节，bigint为8字节
4.2 如果是字符串类型，还需要同时考虑字符集因素(latin1为1字节/gbk为2字节/utf8为3字节/utf8mb4为4字节)，例如：CHAR(30) UTF8则key_len至少是90字节
4.3 如果是日期时间型还需要考虑精度值（5.6.4版本以后），如datetime为5字节+精度值，timestamp为4字节+精度值
4.4 若该列类型定义时允许NULL，还需要再加1字节
4.5 若该列类型为变长类型，例如 VARCHAR（TEXTBLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），还需要再加2字节

（二）处理方法
1、降低索引长度，采用部分索引而不是整列索引，如：create index idx_xx on t(code(30))2、修改innodb_file_format为Barracuda，同时修改表的row format为DYNAMIC，使得最大索引长度增加到3072字节，如：alter table t row_format=dynamic

二、举几个你遇到的MySQL中的隐式转换案例
1.表中定义字符类型，where条件传入数字类型，如：code varchar(10), SQL: select * from t where code=12.关联字符类型不一致(驱动表int传入被驱动表字符串中关联)，如：t1.a int,t2.a varchar(10), SQL: select * from t1 left join t2 on t1.a=t2.a3.关联两表字符集类型不一致(驱动表大字符集传入被驱动表小字符集关联)，如：t1 utf8mb4, t2 utf8, SQL: select * from t1 left join t2 on t1.a=t2.a4.关联两表字符校对规则不一致，如：
t1表coll为utf8_general_ci,t2表为ucs2_general_ci,SQL: select * from t1,t2 where t1.a=t2.a5.字符串被截断(严格来说不算隐式转换，但容易被忽略)，如SQL:where a='1aaa'这个条件也能查到a=1的记录
判断出现类型转换方法：
1、在命令行查看执行计划，通过show warnings可以观察到
due to type or collation conversion on field
2、若发生类型转换，一般来说无法使用索引，执行计划的key那列通常是NULL

三、你觉得理想的MySQL备份策略应该是怎样的，实际上你真正的备份策略又是怎样的，为什么会有不同，出于什么原因呢？（一）备份目的
备份的主要目的是确保数据安全。在数据文件出现损坏或者误操作时用于数据恢复

（二）全备策略
1、目前备份策略对于数据量小的库（数据量在50G以下），可以采用逻辑全备的方式(例如mysqldump或mydumper)
2、对于数据量大（数据量在50G以上）的库，一般采用xtrabackup进行物理备份
3、无论物理备份还是逻辑备份，尽可能在专属从库上执行，尽可能不要在主库上进行备份
4、执行备份前，最好先确认数据库中当前没有未结束的大事务，或者未结束的大select
5、除了上述备份外，同时也要备份binlog文件
6、建议在业务低峰期(例如凌晨)每天做全量备份
7、备份文件同时最好放在远程备份服务器上，不要只放在本地

（三）增量备份
1、xtrabackup支持在全备基础上的增量备份
2、或者通过binlog备份做增量

（四）其他策略
1、如有条件，可以配置延迟从库，在出现数据误删需要快速恢复的情况下，那么可以从延迟从库立刻恢复数据
2、对于超大的库（如日志库），可以直接用主从的方式代替备份，master出问题，立即将业务切换到slave上

（五）备份文件有效性测试
1、除了备份策略以外，还需要验证备份的有效性，定期检验备份集
2、备份集检验包含备份有效性和备份完整性监测，通过是否能够恢复备份集并抽样访问表数据来检验备份的有效性，通过数据探针的方式来检验备份完整性（例如：备份前往某个表中插入特定标记数据，恢复后检查该数据是否存在）

四、MySQL执行sql时一个表只能用到一个索引吗？
答：并不是，以下几种情况一个表可能会使用多个索引（一）index merge
在执行计划的type列显示index_merge，key显示了使用的索引，key_len包含了这些索引列表的最长部分，根据Extra列的显示可以将index merge分为三类
1、Using intersect(...)，对应的算法为Index Merge Intersection，如：c1、c2是两个单列索引，SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2;
2、Using union(...)，Index Merge Union，如：c1、c2、c3是三个单列索引，SELECT * FROM t1 WHERE c1 = 1 OR c2 = 2 OR c3 = 3;
3、Using sort_union(...)，Index Merge Sort-Union，如：c1、c2是两个单列索引，SELECT * FROM t1 WHERE c1 < 10 OR c2 < 20;

（二）表自关联
表自关联同样可以用到多个索引，如：
c1、c2是两个单列索引，SQL如下：select * from t as t1 join t as t2 on t1.c1=t2.c2 where t1.c2=1;
补充：
有很多同学答了ICP，ICP是指将单个索引内无法参与index key的索引条件下推到引擎层进行过滤（也就是index filter阶段下推到引擎层去执行），并不是将多个索引下推到引擎层过滤，需要注意区别

五、MySQL的前缀索引有什么特点？使用上有什么使用场景和限制么?（一）特点：
1、只能对列的部分长度建立索引，减少索引的大小即key_len，达到节省空间、提高查询效率的目的
2、可以通过length函数计算前n个字符长度的记录条数占比来决定前缀索引的长度，一般来说能够覆盖80%-90%即可
例如，一个表总数是100万，我们统计字符串前缀长度小于15个字符的数量是80万，那么前缀索引长度可能15个字符长度基本就够用了select count(*) from t where length(c1)<=15;
(注意，这个SQL效率很低，不要在线执行)

（二）不足、缺点：
1、前缀索引无法使用覆盖索引的特性，因此必须回表获取数据
2、无法利用前缀索引完成分组/排序

（三）使用场景：
1、适合乱序数字或前n个字符可选性高的情况或者是text/blob这种无法整列创建索引的大字段
2、不适合于前n个字符重复率很高或者需要利用覆盖索引优化的场景