整理一下一些琐碎的不熟悉的知识点

1.sql执行顺序

  • from/join 确定数据源
  • where 过滤行
  • group by 分组
  • 聚合函数 SUM/COUNT
  • HAVING 过滤分组
  • select 选择列
  • distinct 去重
  • order by 排序
  • limit/offset 分页

2. mysql默认端口号：3306

3.字段类型

  • char/varchar
    char：定长，会填充空格达到指定长度，适合存储长度较短或者长度近似的字符串
    varchar：变长，用1到2个额外字节存储字符串长度，适合存储长度不确定或者差异较大的字符串
  • decimal：定长型，可以存储精确的小数值； float/double：存储近似的小数值

4.NULL

  • NULL代表一个不确定的值，不等于任何值
  • 任何值与NULL比较（= != > <）的结果都是NULL;判断一个值是否为NULL，要用IS NULL/IS NOT NULL判断
  • 聚合函数SUM/AVG/MIN/MAX会忽略NULL值，COUNT(*)会统计所有行数包括NULL，COUNT(列名)不统计NULL

5.innodb和myisam

  • innodb支持行锁；提供事务管理；支持外键；支持mvcc；数据也是索引结构（myisam中数据和索引分开）；支持安全恢复（redo log）

6.行级锁

  • 加在索引字段上，对当前操作的行进行加锁。存储引擎层实现。
  • 执行update、delete假设where没有命中索引或者索引失效，会扫描全表对所有行加锁。
  • 行锁：1.记录锁：单个行记录上的锁；2.间隙锁：锁定一个范围 3.nextkeylock(1+2)：锁住记录+范围，解决幻读。默认可重复度，默认nextkeylock。假设命中唯一索引会降级为record lock。

7.共享锁/排他锁：

  • 共享/读锁/s锁：锁兼容；
  • 排他/写锁/x锁：锁不兼容；

8.意向锁：表级锁。数据引擎维护的，用户无法手动操作。

  • 意向共享锁is锁：有意对某些记录加共享锁s锁，先获得is锁；
  • 意向排他锁ix锁：有意对某些记录加排他锁x锁，先获得的ix锁
  • 意向锁之间互相兼容。

9.执行计划EXPLAIN

10.sql优化：

  • 慢sql定位：mysql慢查询日志、explain命令分析
  • 索引优化、表结构优化（范式、反范式）、sql优化（避免select *、连接查询代替子查询）
  • 架构优化：读写分离、分库分表、冷热分离、redis缓存
  • 其他：连接池配置（大小、超时时间）、硬件配置

11.子查询：子查询性能差的原因：子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

12.覆盖索引

  • 包含了完整的数据行，所有列数据，可以直接返回结果；
  • 二级索引：只包含id+索引列数据，还需要用id回表查询其他字段。

13.mysql连接池

  • 在启动的适合创建一定数量的数据库连接，并保存在一个池中，应用程序可以动态地从池中申请、使用和释放连接，而不是每次和数据库进行交互时再创建新的连接。
  • 避免每次执行sql时需要重新建立tcp连接以及mysql的认证，复用连接，减少性能开销。