sql优化方案:
- 索引优化
- sql优化
- 设计优化
- 配置优化
- 架构优化
- 硬件优化
索引的优势和劣势:
优势:
- 通过建立唯一索引或者主键索引,保证数据中每一行数据的唯一性。
- 提高检索的数据,减少表的检索行数
- 加速表与表的直接相连
- 在分组和排序字句进行数据检索时,可以减少分组和排序所消耗的时间
- 建立索引,在查询中使用索引,可以提高性能
劣势:
- 索引文件会占用物理空间
- 创建索引和维护索引会耗费时间(随数量增加而增加)
- 对表数据进行添加更新删除的时候,索引也需要维护
索引分类:
- 主键索引
- 普通索引
- 唯一索引
- 联合索引
- 全文索引
创建索引的原则:
- 字段的辨识度要高
- 条件字段优先加索引(where on)
- 在order by和group by后面的字段可以添加索引
- 回表查询,如果联合字段唯一性强
- 查询中存在热点数据,数据不经常变化,考虑缓存
索引失效的原因:
- 查询条件没有用到索引
- 对索引字段进行处理了
- 左侧模糊匹配查询
- 数据频繁的增删
- 关键字or可能会导致索引失效
- 联合索引不遵循最左匹配原则
- 查询的列为空
慢查询日志:
是mysql提供的一种日志记录,用来记录mysql中响应时间超过阈值的语句
- show variables like '%slow_query_log%'
- 开启
- set global slow_query_log=1;
- 只对当前数据库生效,如果重启后,则会失效
- 如果想永久生效,必须修改配置文件
- slow_query_log=1
- slow_query_log_file=地址
Explain
- 查看表的读取顺序
- 数据读取操作的操作类型
- 查看哪些索引可以使用
- 查看哪些索引被实际使用
- 查看表之间的引用
- 查看每张表有多少行被优化器执行
结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
- system:系统表中的一行数据
- const:通过索引可一次直接找到的数据
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录和他匹配(主键或者唯一索引扫描)
- ref:非唯一性索引扫描
- range:给定范围扫描
- index:只遍历索引树
- all:全表
possible_keys :可能用在这个表的索引
key:实际使用的索引
key——len:索引使用的字节数
ref:索引是否被引入到,到底引用到了哪几个索引
row:大概读取行数
filtered :满足条件的比例
Extra:语句中出现了Using Filesort 和 Using Temporary说明没有使用到索引
出现 impossible where说明条件永远不成立