sql数量从上⽽下分配_sql优化的⼀般策略sql 优化的⼀般策略：索引优化，sql改写，参数优化，优化器
索引优化
以select * from vvshop_order.vv_order where receive_phone='151011324532'; 为例分析
explain select * from vv_order where order_no=23;
结果：
分析：可以看到该sql扫描全表 30 多万记录，可以通过添加索引优化
alter table vv_order add index orderno_idx(order_no);
注意点：
当传⼊的数据类型和库表数据类型不⼀致时，索引会失效sql语句时间范围查询
不要为每个查询字段建⽴单独的索引，应该根据实际需要建⽴单列索引或者组合索引
通过explain+extended 检查sql的执⾏计划，是否使⽤索引，是否发⽣隐式转换
避免在查询条件中使⽤函数
sql 改写
分页优化
原sql select * from buyer where sellerid=100 limit 100000，5000, limit M, N 写法中，M越⼤，性能越差，可以改写为
select t1.* from buyer t1,
(select id from buyer sellerid=100 limit 100000，5000) t2
where t1.id=t2.id;
⼦查询优化
1. 查询数量较多时，in改为exist，或者优化为如下的形式
SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
p_no = p_no;
2. 避免查询返回所有字段，只返回需要的字段数据
不使⽤ select * or 改写为 in
or的效率事n，in的效率是log(n)，控制in数量在200以内
不使⽤函数和触发器，通过应⽤程序实现 少⽤join，保证字段类型⼀直再join或⽐较 连续数值 使⽤ between
参数优化
优化器
其他
影响in是否⽣效的因素
1. eq_range_index_dive_limit 参数
sqlserver查询前10条记录
默认为10
eq_range_index_dive_limit = 0 只能使⽤index dive
0 < eq_range_index_dive_limit <= N 使⽤index statistics
eq_range_index_dive_limit > N 只能使⽤index dive
字段
1. 根据实际使⽤情况设置字段类型
2. 单表不要有太多字段，建议20以内
mysql语句的执行顺序
进程的基本状态3. 避免使⽤null字段，优化较难且额外占⽤索引空间
4. ⽤整型来存IP
系统参数调优
基准测试⼯具
1. sysbench：模块化，跨平台以及多线程的性能测试⼯具
2. iibench-mysql：基于java的插⼊性能测试⼯具
3. tpcc-mysql：Percona 开发的TPC-C 测试⼯具
这⾥介绍⼀些⽐较重要的参数：
back_log
backlog值指出在MySQL暂时停⽌回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到maxconnections时，新来的请求将会被存在堆栈中，以等待某⼀连接释放资源，该堆栈的数量即backlog，如果等待连接的数量超过backlog，将不被授予连接资源。可以
从默认的50升⾄500
wait_timeout
数据库连接闲置时间，闲置连接会占⽤内存资源。可以从默认的8⼩时减到半⼩时
index函数返回什么
maxuserconnection
最⼤连接数，默认为0⽆上限，最好设⼀个合理上限thread_concurrency：并发线程数，设为CPU核数的两倍
skipnameresolve
禁⽌对外部连接进⾏DNS解析，消除DNS解析时间，但需要所有远程主机⽤IP访问
keybuffersize
索引块的缓存⼤⼩，增加会提升索引处理速度，对MyISAM表性能影响最⼤。对于内存4G左右，可设为256M或384M，通过查询show status like'keyread%'，保证keyreads / keyreadrequests在0.1%以下最好
innodbbufferpool_size
缓存数据块和索引块，对InnoDB表性能影响最⼤。通过查询show status like 'Innodbbufferpoolread%'，保证(Innodbbufferpoolreadrequests – Innodbbufferpoolreads)/ Innodbbufferpoolreadrequests 越⾼越好
innodbadditionalmempoolsize
InnoDB存储引擎⽤来存放数据字典信息以及⼀些内部数据结构的内存空间⼤⼩，当数据库对象⾮常多的时候，适当调整该参数的⼤⼩以确保所有数据都能存放在内存中提⾼访问效率，当过⼩的时候，MySQL会记录Warning信息到数据库的错误⽇志中，这时就需要该调整这个参数⼤⼩
innodblogbuffer_size
InnoDB存储引擎的事务⽇志所使⽤的缓冲区，⼀般来说不建议超过32MB
querycachesize
缓存MySQL中的ResultSet，也就是⼀条SQL语句执⾏的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引⽤了该表的select语句在Query Cache中的缓存数据失效。
所以，当我们的数据变化⾮常频繁的情况下，使⽤Query Cache可能会得不偿失。根据命中率(Qcachehits/(Qcachehits+Qcache_inserts)*100))进⾏调整，⼀般不建议太⼤，256MB可能已经差不多了，⼤型的配置型静态数据可适当调⼤.
可以通过命令show status like 'Qcache_%'查看⽬前系统Query catch使⽤⼤⼩
readbuffersize
kotlin项目实战MySql读⼊缓冲区⼤⼩。对表进⾏顺序扫描的请求将分配⼀个读⼊缓冲区，MySql会为它分配⼀段内存缓冲区。如果对表的顺序扫描请求⾮常频繁，可以通过增加该变量值以及内存缓冲区⼤⼩提⾼其性能
sortbuffersize
MySql执⾏排序使⽤的缓冲⼤⼩。如果想要增加ORDER BY的速度，⾸先看是否可以让MySQL使⽤索引⽽不是额外的排序阶段。如果不能，可以尝试增加sortbuffersize变量的⼤⼩
readrndbuffer_size
MySql的随机读缓冲区⼤⼩。当按任意顺序读取⾏时(例如，按照排序顺序)，将分配⼀个随机读缓存区。进⾏排序查询时，MySql会⾸先扫描⼀遍该缓冲，以避免磁盘搜索，提⾼查询速度，如果需要排
序⼤量数据，可适当调⾼该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过⼤。
record_buffer
每个进⾏⼀个顺序扫描的线程为其扫描的每张表分配这个⼤⼩的⼀个缓冲区。如果你做很多顺序扫描，可能想要增加该值
threadcachesize
保存当前没有与连接关联但是准备为后⾯新的连接服务的线程，可以快速响应连接的线程请求⽽⽆需创建新的
table_cache
类似于threadcachesize，但⽤来缓存表⽂件，对InnoDB效果不⼤，主要⽤于MyISAM
References
阿⾥云慢SQL优化挑战⼤赛分析
SQL优化器原理 - 查询优化器综述
MYSQL查询SQL语句性能优化⽅法
MySQL--eq_range_index_dive_limit参数学习
MySQL SQL优化之in与range查询【转】
本⽂由 歧途⽼农 创作，采⽤ CC BY 4.0 CN 协议 进⾏许可。 可⾃由转载、引⽤，但需署名作者且注明⽂章出处。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。