【mysql数据库优化】

2024-10-09 10:31:11

sql优化：
1.MYSQL逻辑分层：连接层服务层引擎层存储层
InnoDB(默认) ：事务优先（适合高并发操作；行锁）
MyISAM ：性能优先（表锁）

2.sql的执行顺序：
SQL ：
编写过程：
select dinstinct ...from ....join ...on ...where ...group by ...having ...order by ...limit ...

解析过程：
from ... on... join ...where ...group by ...having ...select dinstinct ...order by... limit ...
3.增加索引
create index ...
4.最佳左前缀
a.保持索引的定义和使用的顺序一致性
b.索引需要逐步优化
c.将含In的范围查询放到where条件的最后，防止失效。
5.多表操作遵循小表驱动大表原则
a.小表驱动大表
select ...where 小表.x10=大表.x300 ;
b.索引建立经常使用的字段上
6.避免索引失效的一些原则(不跨列、不操作、不用!=、不用or、like用常量开头)
(1)复合索引
a.复合索引，不要跨列或无序使用（最佳左前缀）
b.复合索引，尽量使用全索引匹配

(2)不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
select ..where A.x = .. ; --假设A.x是索引
不要：select ..where A.x3 = .. ;

(3)复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。
-- 复合索引中如果有>，则自身和右侧索引全部失效。
-- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。
-- 复合索引全部失效
-- 我们学习索引优化，是一个大部分情况适用的结论，但由于SQL优化器等原因该结论不是100%正确。
-- 一般而言，范围查询（> < in），之后的索引失效。

(4)补救。尽量使用索引覆盖（using index）
select a,b,c from xx..where a= .. and b =.. ;

(5) like尽量以“常量”开头，不要以'%'开头，否则索引失效
explain select ...... where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖挽救一部分。

(6) 尽量不要使用类型转换（显示、隐式），否则索引失效

(7) 尽量不要使用or，否则索引失效
explain select from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

7.一些其他的优化方法
(1)exist和in
select ..from table where exist (子查询) ;
select ..from table where 字段 in (子查询) ;

如果主查询的数据集大，则使用In ,效率高。
如果子查询的数据集大，则使用exist,效率高。

exist语法：将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据则校验成功），
如果复合校验，则保留数据；

select tname from teacher where exists (select * from teacher) ;
--等价于select tname from teacher

select tname from teacher where exists (select * from teacher where tid =9999) ;

in:
select ..from table where tid in (1,3,5) ;

（2）order by 优化
using filesort 有两种算法：双路排序、单路排序（根据IO的次数）
MySQL4.1之前默认使用双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段）
--IO较消耗性能
MySQL4.1之后默认使用单路排序：只读取一次（全部字段），在buffer中进行排序。但种单路排序会有一定的隐患（不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。
注意：单路排序比双路排序会占用更多的buffer。
单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小： set max_length_for_sort_data = 1024 单位byte

如果max_length_for_sort_data值太低，则mysql会自动从单路->双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：
a.选择使用单路、双路；调整buffer的容量大小；
b.避免select * ...
c.复合索引不要跨列使用，避免using filesort
d.保证全部的排序字段排序的一致性（都是升序或降序）

最新文章

热门文章