本文的内容是自己的学习记录,摘自B站尚硅谷mySql优化专题视频教程,只摘取了自己觉得必要的点,后期会继续补充。链接为:https://www.bilibili.com/video/BV1KW411u7vy?p=1
一、关于Explain查看mysql执行计划
explain之type(访问类型):
从最好到最差依次是:
system>const>eq_ref>ref>range>index>ALL
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。
如:将主键置于where列表中,MySql就能将该查询转换为一个常量
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref:非唯一性索引扫描,返回匹配某个单独值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,
它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引
一般就是在where语句中出现了between,<,>,in等的查询
这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引
index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比All快,因为索引文件通常比数据文件小。
也就是说,虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的。
all:Full Table Scan,将遍历全表以找到匹配的行
备注:一般来说,得保证查询至少达到range级别,最好能达到ref
二、sql优化
1.全值匹配
2.联合索引:最佳左前缀法则,中间索引不能断,如果断了,只能匹配之前的部分索引
3.不能再索引上做任何操作(计算、函数(自动或手动)类型转换),如果做了会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列:例如 select * from table where col1=1 and col2 > 2 and col3 =3,
此时索引只用到了col1和col2,没有用到col3,col2用于排序,col1用于查找;如果此时把*替换成具体的字段,那么col2不会用到
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少Select *
6.mysql在使用不等于(!=或<>)的时候无法使用索引,会导致全表扫描
7.is null, is not null无法使用索引
8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
9.字符串不加单引号,索引失效
10.少用or,用它来连接时会索引失效一般性建议:
1.对于单键索引,尽量选择针对当前query过虑性更好的索引
2.在选择组合索引的时候,当前Query中过虑性最好的字段在索引字段顺序中,位置越靠前越好
3.在选择组合索引的时候,尽量选择可以包含当前query中的where子句中更多字段的索引
4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
优化总结口诀:
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
小表驱动大表
left join 右表建索引;
right join 左表建索引;
三表查询:小表驱动大表,从最小表开始建索引
三、数据库锁优化建议
行锁:
1、尽可能让所有数据建索都通过索引来完成,避免无索引行锁升级为表锁)(字符串类型的索引,字段要加上单引号,不然不仅用不到索引,且行锁会变表锁)
2、合理设计索引,尽量缩小所得范围
3、尽可能较少检索条件,避免间隙锁
4、尽量控制事务大小,减少锁定资源量和时间长度
5、尽可能低级别事务隔离