内容简介：2019年05月16日，周四2019年05月21日，周四2019年05月30日，周二
关 于 叶 问
《叶问》是知数堂新设计的互动栏目，不定期给大家提供技术知识小贴士，形式不限，或提问、或讨论均可，并在当天发布答案，让大家轻轻松松利用碎片时间就可以学到最实用的知识点。
2019年05月14日，周二
#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（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），还需要再加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
2019年05月16日，周四
举几个你遇到的 MySQL 中的隐式转换案例
1.表中定义字符类型，where条件传入数字类型，如：
code varchar(10), SQL: select * from t where code=1
2.关联字符类型不一致(驱动表int传入被驱动表字符串中关联)，如：
t1.a int,t2.a varchar(10), SQL: select * from t1 left join t2 on t1.a=t2.a
3.关联两表字符集类型不一致(驱动表大字符集传入被驱动表小字符集关联)，如：
t1 utf8mb4, t2 utf8, SQL: select * from t1 left join t2 on t1.a=t2.a
4.关联两表字符校对规则不一致，如：
t1表coll为utf8_general_ci,t2表为ucs2_general_ci,SQL: select * from t1,t2 where t1.a=t2.a
5.字符串被截断(严格来说不算隐式转换，但容易被忽略)，如SQL:
where a='1aaa'这个条件也能查到a=1的记录
判断出现类型转换方法：
1.在命令行查看执行计划，通过show warnings可以观察到
due to type or collation conversion on field
2.若发生类型转换，一般来说无法使用索引，执行计划的key那列通常是NULL
2019年05月21日，周四
一、备份目的 备份的主要目的是确保数据安全。在数据文件出现损坏或者误操作时用于数据恢复 二、全备策略 2.1、目前备份策略对于数据量小的库（数据量在50G以下），可以采用逻辑全备的方式(例如mysqldump或mydumper) 2.2、对于数据量大（数据量在50G以上）的库，一般采用xtrabackup进行物理备份 2.3、无论物理备份还是逻辑备份，尽可能在专属从库上执行，尽可能不要在主库上进行备份 2.4、执行备份前，最好先确认数据库中当前没有未结束的大事务，或者未结束的大select 2.5、除了上述备份外，同时也要备份binlog文件 2.6、建议在业务低峰期(例如凌晨)每天做全量备份 2.7、备份文件同时最好放在远程备份服务器上，不要只放在本地 三、增量备份 3.1、xtrabackup支持在全备基础上的增量备份 3.2、或者通过binlog备份做增量 四、其他策略 4.1、如有条件，可以配置延迟从库，在出现数据误删需要快速恢复的情况下，那么可以从延迟从库立刻恢复数据 4.2、对于超大的库（如日志库），可以直接用主从的方式代替备份，master出问题，立即将业务切换到slave上 五、备份文件有效性测试 5.1、除了备份策略以外，还需要验证备份的有效性，定期检验备份集 5.2、备份集检验包含备份有效性和备份完整性监测，通过是否能够恢复备份集并抽样访问表数据来检验备份的有效性，通过数据探针的方式来检验备份完整性（例如：备份前往某个表中插入特定标记数据，恢复后检查该数据是否存在）
2019年05月28日，周二
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阶段下推到引擎层去执行），并不是将多个索引下推到引擎层过滤，需要注意区别
2019年05月30日，周二
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个字符重复率很高或者需要利用覆盖索引优化的场景
扫码加入QQ技术交流群
（群号： 793818397）
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Inside Larry's and Sergey's Brain
Richard Brandt / Portfolio / 17 Sep 2009 / USD 24.95
You’ve used their products. You’ve heard about their skyrocketing wealth and “don’t be evil” business motto. But how much do you really know about Google’s founders, Larry Page and Sergey Brin? Inside......一起来看看 《Inside Larry's and Sergey's Brain》 这本书的介绍吧!
