索引的优化、SQL慢查询的优化和表的优化
sql执行顺序:
from
on
join
where
group by(开始使用select中的别名,后面的语句中都可以使用)
avg,sum…
having
select
distinct
order by
开启慢sql查询,查看有哪些sql比较慢:slow_query_log=1
- select少用*,解析时会去数据字典查询所有的列替换 *,消耗时间
- from语句是从后往前,数据量少的表放后面
- where子句是从前往后,将能过滤掉最大数据的记录条件写再
- 频繁作为查询条件的字段建索引,如where group by
- 为经常最为表连接的字段上加索引
- 使用复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高
- in 主表与子查询表做笛卡尔积 适用子查询数据少的
- exit 遍历主表 对子查询结果检测行的存在 适用主表数据少的
查看执行计划:
type: 从最好到最差的连接类型为const、eq_reg、ref、range、index、ALL
possible_keys: 显示可能应用在这张表中的索引
key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
Extra: 关于MYSQL如何解析查询的额外信息Using where,Using index等
索引:
类型:主键,普通,唯一,复合,全文(text)
方法:
hash: 由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤
B-Tree: 适用于范围查询
个数: 5-6个左右最佳,太多会影响性能,索引会增加写操作的成本(每次都会重建索引)
mysql会根据哪个索引过滤到的数据多使用哪个
一个表只会使用一个索引
对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引,窄索引效率高
避免在经常修改的列上建索引
失效:
1.前模糊查询
2.复合索引,没有用到左前缀原则
3.重复数据较多的列 比如sex
4.查询的数量是表的大部分,应该是30%以上。
5.对索引列进行运算或使用函数
6.索引列有太多null值
7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引,比如数据均匀分布
分库分表 读写分离