一、sql优化
explain详解
type保证达到range级别,最好达到ref
1、满足最左前缀法则
2、不对索引进行操作(计算、函数、类型转换)
3、尽量使用覆盖索引,不使用 select * 语句
4、不使用不等于(!=或者<>),not in ,not exists
5、避免在where子句中对字段进行 null 值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
6、like查询不要以通配符’%'开头
以通配符’%'开头不会使用索引,如果非要使用,使用覆盖索引或者使用搜索引擎
- 索引下推
Extra的值为Using index condition,表示使用了索引下推
假设有联合索引(name,age)SELECT * from user where name like '张%' and age=20
(1)没使用索引下推过程
二级索引中查询name是张开头的记录,然后依次回表查询age为20的记录
(2)使用索引下推过程
二级索引中查询name是张开头的记录,查询到马上判断是否是age为20的,都匹配上再回表查询记录完整数据,减少回表次数提高效率
7、字符串不加单引号索引失效(类型转换)
8、少用or或in
数据量大的时候会走索引,不然会选择全表扫描
9、范围查询尽量拆分成多段
可能因为单次查询数据量太大不走索引
10、尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。
注意:union all的前提条件是两个结果集没有重复数据。
11、当只需要一条数据的时候,使用limit 1
这是为了使EXPLAIN中type列达到const类型
12、必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索sql语句,但是可能它所采用的索引并不是我们想要的。这时就 可以采用force index来强制优化器使用我们制定的索引。
13、Order by与Group by优化
Extra | 含义 |
---|---|
Using index | 覆盖索引 |
Using filesort | 用的是外部排序不是索引排序,数据较小时从内存排序,否则在磁盘排序。 |
- Using filesort的情况需要进行索引优化
order by满足两种情况会使用Using index。
(1)order by语句使用索引最左前列。
(2)where子句与order by子句条件列组合满足索引最左前列。
- filesort文件排序方式
mysql通过max_length_for_sort_data变量和所需要查询的的字段总大小选择使用哪种排序方式,总长度比变量大使用双路排序,比变量小使用单路排序。可以通过修改max_length_for_sort_data(默认1024字节)变量的值主动控制使用哪种排序。可以通过trace工具查看sort_mode,看使用的是哪种排序方式
排序方式 | 过程 |
---|---|
单路排序 | 一次性取出满足条件行的所有字段,然后在sort buffer中进行排序后返回。(<sort_key, additional_fields>) |
双路排序 (回表排序模式) | 先根据条件取出相应的排序字段和ID,然后在sort buffer中进行排序,排序完后根据id回表查询到其它需要的字段后返回。(<sort_key, rowid>) |
14、count(*)查询优化
查询速度对比
count(*) ≈ count(1) > count(索引字段) > count(主键 id) > count(普通字段)
二级索引比主键索引小,所以更快
存储引擎 | count(*)优化 |
---|---|
myisam | 在磁盘上维护了表总行数,直接取,查询不需要优化 |
innodb | 查询count需要实时计算,可以优化 1)show table status 可以查询到表总行数的估计值 2)将总数维护到Redis里 难实现 3)增加数据库计数表 插入或删除表数据行的时候同时维护计数表,让他们在同一个事务里操作 |
15、分页查询优化
- 原理
'limit 1000,10’是查询1010条数据,然后舍弃前1000条,返回后10条,查询数据多的大表执行效率很低- 优化
(1)对于自增且连续的主键排序的分页查询
‘limit 100000,10’可以优化成’id>100000 limit 10’,使用索引,并且扫描行数减少
(2)对于非主键字段排序的分页查询select * from employees ORDER BY name limit 90000,5;
优化成
select * from employees e inner join (select id from employees order by name limit 90000,5) ed
16、Join查询优化
假设两个表t1和t2,t1有100行,t2有10000行,a是索引字段,b是非索引
- 表关联两种算法
Nested-Loop Join嵌套循环连接算法 | Block Nested-Loop Join基于块的嵌套循环连接算法 |
---|---|
关联字段是索引字段 | 关联字段不是索引字段 |
select * from t1 inner join t2 on t1.a= t2.a; join连接过程 (1)选择数据少的t1表作为驱动表(选择见下面说明) (2)先在t1中根据t1的过滤条件查询出一条数据 (3)取出索引a,在t2表中查询满足两表关联的条件的数据(因为是索引,索引查询很快,即使t2表很多数据,也只用扫描1条数据) (4)合并t1和t2数据,返回 (5)重复2,3,4步骤 整个过程扫描了100+100*1=200行 | select * from t1 inner join t2 on t1.b= t2.b; join连接过程 (1)选择数据少的t1表作为驱动表(选择见下面说明) (2)t1表数据全放到join_buffer中 (3)t2表依次取出来跟join_buffer数据对比 (4)合并t1和t2数据,返回 整个过程扫描了10000+100=10100行,在buffer中还需要100*10000=100万次判断 |
- join_buffer内存不够时
分段放,先放一部分,查询出结果,清空buffer接着放,再次从t2表获取数据和buffer对比,会多扫描一次t2表- 驱动表选择
left join左边是驱动表
right join右边是驱动表
inner join mysql自动判断,小表作为驱动表- 大表小表判断
不是根据表数据量大小,是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的表作为驱动表- 优化
(1)关联字段加索引,让mysql做join操作时尽量选择NLJ算法
(2)小表驱动大表,如果知道哪个是小表可以用straight_join固定连接驱动方式,避免mysql自己判断
(3)sql复杂的转移到java代码里实现,方便横向拓展
17、in和exsits优化
- 原则
小表驱动大表
select * from A where id in (select id from B)
B驱动A
select * from A where exists (select 1 from B where B.id = A.id)
A驱动B
- 结论
当B表的数据集小于A表的数据集时,in优于exists
当A表的数据集小于B表的数据集时,exists优于in
二、索引设计优化
1、先写sql再建索引
等sql编写完,根据sql建索引,尽量覆盖80%sql条件
2、不要在小基数字段上建立索引
如性别字段,只有两个值,没法快速的二分查找,不如全表扫描了。
3、长字符串采用前缀索引
varchar(255)这种大字段,对这个字段的前20个字符建立索引,每个值的前20个字符放在索引树里。
过程:先根据字段前20个去索引树匹配,查询出来回表聚簇索引,得到完整字段,再匹配,得到结果返回。
注意:order by和group by无法使用此索引。
三、数据库设计优化
1、确定合适的大类型:数字、字符串、时间、二进制;
2、确定具体的类型:有无符号、取值范围、变长定长等
类型 | 选择建议 |
---|---|
数字 | 1、如果确定没有负数,指定为UNSIGNED无符号类型,容量大一倍。 2、建议使用TINYINT代替ENUM、BITENUM、SET。 3、不要用INT(10)指定字段显示宽度,直接用INT。 4、DECIMAL最适合保存准确度要求高,而且用于计算的数据,比如价格。注意长度设置。 5、建议使用整形类型来运算和存储实数。 6、能用整数尽量用整数,速度快,能使用AUTO_INCREMENT自增。 |
日期 | 1、用DATE、TIME、DATETIME来存储时间,而不是使用字符串。 2、数据格式为TIMESTAMP和DATETIME时,用CURRENT_TIMESTAMP作为默认,操作数据时mysql自动修改。 3、TIMESTAMP是UTC时间戳,与时区相关,DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数,与时区无关,你存了什么,读出来就是什么。 4、TIMESTAMP比DATETIME更节约空间,但是TIMESTAMP有时间上限问题,看情况选择。 |
字符串 | 1、字符串的长度相差较大用VARCHAR(变长);字符串短,且所有值都接近一个长度用CHAR(定长,不足会后面补空格,查询时会去掉空格)。 2、要用来计算的数字不要用VARCHAR类型保存,可能影响到计算的准确性和完整性。 3、尽量少用BLOB和TEXT,如果实在要用可以考虑将BLOB和TEXT字段单独存一张表,用id关联。 4、BLOB系列存储二进制字符串,与字符集无关。TEXT系列存储非二进制字符串,与字符集相关。 5、BLOB和TEXT都不能有默认值 |