Mysql 连接数 性能 表结构变更 锁竞争等问题

• 连接数满问题

  • max_connections: 数据库允许的最大连接数
  • 如果超过则后续的用户无法登陆
  • 并发的用户活动数越大，用户线程占用的内存越多
  • 大基线 512，需纳入应用小基线

• 性能问题

  • 分析思路
    • 操作系统层面
    • 线程
    • SQL：慢查询日志
    • TOP SQL
    • Explain
  • 程序效率问题
    • 执行计划： Explain
    • SHOW CREATE TABLE xxx
    • 无法利用索引
      • 无法利用索引的场景
        • 查询条件最左以通配符%开始， eg：where a like ‘%xxx’
        • 查询条件使用IS NULL 或 IS NOT NULL
        • 查询条件在字段上进行数学运算或函数运算（包括隐式数据转换）
        • 查询条件使用负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。
        • 复合索引（是从第一个索引列开始，从左到右依次匹配；一旦某个字段不能匹配，则立即停止匹配，后边的索引字段不会再匹配）
    • 全表扫描
    • 大表没有自增列作为主键

• 表结构变更问题

  • 现象：给表新增字段耗时比较长，数据量越大耗时越长
  • 原因：
    • 针对表级的操作（除了TRUNCAT和索引维护）内部相当于重新建表，执行时间与数据量成正比。
    • 内部原理（1. 以新表结构建新表，2. 将旧表数据select insert 到新表，3. 将新表名字改成旧表名字替换旧表）
  • 对同一张表的多次ALTER操作，应合并为一次操作。
  • 应评估DDL操作是否满足停机时间窗口

• 锁竞争问题

  • 行锁
    • 触发条件
      • 不同线程INSERT/UPDATE/DELETE的数据有重叠
      • 先执行的线程没有及时commit
      • 在这种情况下，后执行的线程就需要等待，如果等待时间超过innodb_lock_wait_timeout设置的时间就会报“Lock wait timeout exceeded” 错误
    • 在这种情况下mysql会回滚当前SQL
  • 死锁
    • 触发条件
      • 不同线程INSERT/UPDATE/DELETE的数据有重叠
      • 两个线程之间的执行顺序有交叉
      • 两个线程处理的主键顺序相反
    • Mysql会将其中一个线程的整个事务（挑更新记录较少的）都回滚，并在mysql.err中记录死锁信息。
  • 元数据锁
    • 触发条件
      • DDL与同表其他SQL并发执行
    • Mysql等待lock_wait_timeout,如果锁未释放，等待方报错:1205(HY000)
    • show processlist 发现 waiting for table metadata lock
  • 表级锁
    • 隐式锁
      • REPEATABLE-READ
    • 显式锁
      • FLUSH TABLES WITH READ LOCK
      • LOCK TABLES xx READ/WRITE