MySQL开发优化细节

1.char 与 varchar，实际开发中针对于相对固定的字段应该采用char来进行存储，原因在于char是定长字符串，存取效率会优于varchar挺多
   相当于空间换时间

2.mysql中字符集以及对应的校对规则
   2.1：show variables  like '%character%'  查看服务端，客户端，结果集，连接成，系统元数据对应的字符集，根据交互流程会进行对应
     字符集转换，一般 charset_client 和 charset_conn 以及 charset_result 的字符集默认一致，字符集转换就是根据字符串内容，从要
      转换成的字符集内部获取对应的二进制数据进行存储
   2.2:  show collation  查看不同字符集对应的校对规则，default列指定是否默认使用，charset列指定校队规则对应的字符集，不同字符集之间
     不会有相同的校对规则，校对规则有着命名约定，名称结尾为，_ci指定大小写敏感，_cs大小写不敏感，_bin二元

3.关于索引失效场景，在高版本中小部分失效场景已经修复了，如 null空值处理，or的处理，在高版本中针对于or采取了合并索引的方法
   var引号忘记写导致的锁升级在高版本中也已经修复。

4.针对于mysql不等于!=/<>的场景，我们只需要 Sname>'赵雷' or Sname <'赵雷'就能够继续使用到索引，因为mysql中不同字符集是有着不同的collation校对规则的，
   根据校对规则能够对字符串进行排序比较

5.索引选择性，指的是不重复索引列个数/总列数，计算得到的值 0-1 ，索引选择性越高则查询效率越高，因为mysql在查找时会过滤掉更多的行
   计算sql：select count( distinct 列名) / count(*) from 表名

6.前缀索引，一般针对于text，clob，长度较大的varchar，因为mysql不允许索引这些列的完整长度，使用前缀索引我们需要确保较高的索引选择性，性能和业务方面进行综合
   考量，同时前缀索引也不能太长，使用了前缀索引的列，无法进行覆盖索引，order by ，group by。因为前缀索引树中存放的索引值只是索引列数据前缀并不完整。
   适用场景为字段内容后几位为相对固定数据时，如 @xx.com等这类字段。

7.能用连表查询就不要用子查询，因为子查询会新创建一张临时表，而临时表的创建和销毁都会占用一定的系统资源以及时间。

8.为什么不用join太多的表，阿里巴巴规范中表示join不要超过三张表，一方面是join采用的是nested loop不管怎么优化还是会影响一定的性能，另一方面连接缓冲区
   （join buffer）会占用更多的内存，如果不可避免的需要查询多张表，可以将数据异构到es中进行查询。

9.为什么排序是无过滤不索引，这是处于性能考虑的，因为非主键索引，会存在回表操作，也就是没检索一条记录，回表查询一次，这个读操作是随机io，通常会比顺序
   全表扫描还要慢，因此尽可能保证过滤索引和order by 索引是一个组合，这样就能减少磁盘io次数，组合索引也会进行ICP优化，减少引擎层和服务层交互。

10.union优化，union会先创建一张临时表，然后将各个查询中的数据填充到这张临时表中，再进行查询，所以有多优化手段都会失效，我们优化这类sql，一般两点
     1.手工将where等过滤筛选等手段下移到各个子查询中，2.能用union all就不要用 union，因为union会使用distinct对临时表数据进行唯一性约束，代价很高

11.explain中type字段表示查询访问类型，有一个ref_or_null 类型，表示使用索引列匹配某个值 or  索引列 is null，其余merge_index就是合并索引，表示查询
     使用了两个以上的索引，unique_subquery 通过唯一键或主键与子查询进行关联，子查询返回不重复的集合，index_subquery通过索引列与子查询进行关联
     子查询返回重复的集合，上述两种类型索引都是建立在子查询上的
     性能排比（从最优到最差）
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 
     
12.组合索引的效率要高于索引合并

13.当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化，所以分布式系统使用uuid作为主键值需要慎重，对空间影响问题，可以使用twitter雪花算法实现的分布式ID，全局唯一，单表有序，或者使用mycat+数据表进行预加载实现。

14.mysql查询优化器，当他认为使用全表扫描要比使用索引快，他则不会使用索引，不过滤不索引就是这个原因

15.mysql索引下推优化，mysql5.6添加的，一般explain的extra字段提示using index condition就是使用了索引下推，其实就是数据的过滤筛选交给了存储引擎
    去完成，减少了磁盘io次数，以前都是会从引擎层返回给服务层，由服务层再去进行查询筛选。（也叫ICP优化）

16.线上死锁问题的排查
     1.通过show variables like 'innodb_status_output_locks' 查看对应系统变量是否开启，开启后可以看到哪个事务对哪些记录加了哪些锁
        set global innodb_status_output_locks=on  开启系统变量（排查完问题后记得关闭）

     2.show engine innodb status \G 查看事务加锁情况

     
     合理运用这些命令就能够快速定位线上的死锁问题并进行处理和修复。

17.主从同步延迟的原因：已知从机通过sql线程监听主机binlog日志中指定的position位置来进行数据同步，假设多个sql在从机
     上进行读操作时加了表锁或行锁（读写分离情况），或者进行写操作的时间稍长（单纯主从，未读写分离），就会导致主从同步延迟。
     解：项目中关键业务，读写都操作主机，非关键业务采用读写分离     

18.不停机扩容如何实现?（也可以说是不宕机进行数据迁移）
      1.建立好新库的库表结构，变更老项目的增删改代码，在写入旧库的同时也会写入新库（多数据源或者自定义数据源都可以）
      2.使用定时作业将旧库数据同步到新库，记录存在就更新，不存在就添加。
      3.数据同步没问题之后，把对数据的读操作切换到新库
      4.旧库不再写入新的数据
      5.经过一段时间，确定旧库没有请求之后，就可以下线旧库了

19.like百分号导致的索引失效问题，目前的解决方案还是在like之前过滤过部分数据以及来降低性能开销所带来的损失

20.针对于项目中无法避免出现了text类型字段，我们可以将库中text字段类型抽取封装成单个表，避免字段内容太大，影响所在表性能