数据库总结

1 导致索引失效

• 左前缀匹配原则
  • mysql 会一直向右匹配直到遇到范围查询（>,<,between,like）， =，in 是可以乱序的。
  • 联合索引最多只能包含16列
    • 每个表 最多16个索引
  • 如 创建一个联合索引, 那 这个索引的任何前缀都会用于查询, (col1, col2, col3)
    • 这个联合索引的所有前缀 就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索引查询
    • (col2), (col3), (col2, col3) 都不会启用索引去查询.
    • (col1, col3)会启用(col1)的索引查询
• 独立的列，索引不能参与计算
• like禁止全模糊或者左模糊
• 查询where on 条件数据类型不匹配

2 建立索引注意事项

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

3 查询

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

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

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

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

  • 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

• 使用ISNULL()函数 来判断是否为NULL

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

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

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

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

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

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

  • 参考
  • link
  • explain
    • image.png
      • id 查询中select 子句或者操作表顺序，数字越大权重越大，相同则按先后顺序
      • slect_type 查询类型
      • table 正在访问的表
      • partitions 匹配的分区
      • type 数据访问的类型
        • system 表中只有一条记录
        • const 表示通过索引一次就找到了， 通过主键或者唯一索引，只匹配一条记录
        • eq_ref 唯一性索引扫描 对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
        • ref 非唯一性索引扫描，返回匹配某个单独值的所有行
          • 本质上也是一种索引访问，返回所有匹配某个单独值的行 然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体
        • range 只检索给定范围的行
          • 使用一个索引来选择行，key列显示使用了哪个索引 一般就是在你的where语句中出现between、<>、in等的查询
        • index 全索引扫描
        • all 遍历全表以找到匹配行
          • index all 区别 ndex只遍历索引树，通常比All快 因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的
      • possible_keys 可能用到的索引，不一定用到
      • key 实际用到的索引
      • key_len 索引使用的字节数
      • ref 显示索引的哪一列被使用了
      • rows 大致估算查询记录，需要读取的行数
        • 估算 SQL 要查找到结果集需要扫描读取的数据行数.
      • filtered 查询 占这表数据的百分比
      • extra 额外信息

• 在代码中写分页逻辑时，若为count 为0，应该直接返回，避免执行后面的分页语句

• 数据订正（特别是删除、 修改记录操作） 时，要先 select，避免出现误删除，确认无误才能执行更新语句

4 建表

• 小数类型为 decimal，禁止使用 float 和 double。
  • float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不 正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
• 如果存储的字符串长度几乎相等，使用 char 定长字符串类型
• varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率。
• 表必备三字段： id, gmt_create, gmt_modified
  • id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1
  • gmt_create, 类型 datetime 类型，数据库主动创建
  • gmt_modified，类型 datetime 类型，数据库被动更新
• 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表
  • 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
• 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检 索速度。

5 InnoDB 与 MyISAM 区别

• 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+树索引
  • 聚集索引
  • 辅助索引
  • 非聚集索引
• 哈希索引
• 倒排索引 实现全文检索

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