MySQL常见问题及处理

InnoDB

性能优秀主键所有速度快于其他索引，支持事务，支持行级锁

MyISM

不支持事务，支持表级锁

索引

  1. 大大减少服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随即I/O变顺序I/O
  4. 大大提高查询速度，降低写速度，占用磁盘

种类

主键索引，组合索引，唯一索引，普通索引

原则

  1. 索引列是出现在where子句 中的列，或连接子句中的列
  2. 索引列的基数越大效果越好
  3. 对字符串进行索引，应该制订一个前缀的长度，可以节省大量的索引空间
  4. 根据情况创建复合索引，
  5. 避免创建过多的索引
  6. 选择字节较短的列作为索引，离散度较高的列作为索引

注意

  1. A or B 两个列必须都有索引，才会用到索引
  2. 列类型的字符串查询时一定要加双引号，否则索引失效
    eg:请描述创建组合索引的方案。

优化

  1. 优化查询过程中的数据访问
  2. 优化长难的查询语句
  3. 优化特定类型的查询语句
  4. 分表

分析：

1
2
3
set profiling = 1
show profiles
explain

方案：

  1. 多表关联查询指定查询列明（A.id，A.name）
  2. 查询数据量较多，使用limit解决
  3. 重复查询相同的数据，可以缓存数据，下次直接读取缓存
  4. 查询时是否在扫描额外的记录，如果存在就创建索引覆盖扫描
  5. 适当修改表的范式（第一，二，三范式）
  6. 尽可能的少查询请求（如果数据量很大，分解为多个查询也是有必要的）
  7. count() 中的会忽略所有的列，直接统计所有的列，因此不要使用count（列名）
  8. 关联查询时确定ON或者USING子句的列上有所以，避免全表扫描
  9. groupBy和orderBy中只有一个表的列，这样mysql才有可能使用索引
  10. 如果不需要orderBy，进行groupBy时使用orderBy NULL，mysql不会在进行文件排序

安全：

sql注入

分析

1
2
user/delete/?id=1 or 1=1
delete from user where id=1 or 1=1

方案

  1. sql语句预处理（prepare）
  2. 写入数据库的数据进行特殊字符的转译
  3. 错误信息不要返回给用户，记录到错误日志