这篇文章主要讲解了“怎么掌握MySQL查询优化和分库分表”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么掌握mysql查询优化和分库分表”吧！
网站设计制作过程拒绝使用模板建站；使用PHP+MYSQL原生开发可交付网站源代码;符合网站优化排名的后台管理系统；成都网站建设、网站制作收费合理；免费进行网站备案等企业网站建设一条龙服务.我们是一家持续稳定运营了十余年的创新互联网站建设公司。
重庆分公司
重庆分公司
重要）通过用户反馈获取存在性能问题的SQL；
通过慢查日志获取存在性能问题的SQL；
实时获取存在性能问题的SQL；
相关配置参数：
slow_query_log # 启动停止记录慢查日志，慢查询日志默认是没有开启的可以在配置文件中开启(on) slow_query_log_file # 指定慢查日志的存储路径及文件，日志存储和数据从存储应该分开存储 long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001秒(1毫秒)比较合适 log_queries_not_using_indexes #是否记录未使用索引的SQL
常用工具：mysqldumpslow和pt-query-digest
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log
SELECT id,user,host,DB,command,time,state,info FROM information_schema.processlist WHERE TIME>=60
查询当前服务器执行超过60s的SQL，可以通过脚本周期性的来执行这条SQL，就能查出有问题的SQL。
重要）重点！！！）
上图原文连接
发送SQL语句。
查询缓存，如果命中缓存直接返回结果。
SQL解析，预处理，再由优化器生成对应的查询执行计划。
执行查询，调用存储引擎API获取数据。
返回结果。
第一阶段：
相关配置参数：
query_cache_type # 设置查询缓存是否可用 query_cache_size # 设置查询缓存的内存大小 query_cache_limit # 设置查询缓存可用的存储最大值（加上sql_no_cache可以提高效率） query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单
缓存查找是利用对大小写敏感的哈希查找来实现的，Hash查找只能进行全值查找（sql完全一致），如果缓存命中，检查用户权限，如果权限允许，直接返回，查询不被解析，也不会生成查询计划。在一个读写比较频繁的系统中，建议关闭缓存，因为缓存更新会加锁。将query_cache_type设置为off,query_cache_size设置为0。这个阶段包括了多个子过程：
一条查询可以有多种查询方式，查询优化器会对每一种查询方式的（存储引擎）统计信息进行比较，找到成本最低的查询方式，这也就是索引不能太多的原因。 1、统计信息不准确
2、成本估算与实际的执行计划成本不同
3、给出的最优执行计划与估计的不同
4、MySQL不考虑并发查询
5、会基于固定规则生成执行计划
6、MySQL不考虑不受其控制的成本，如存储过程，用户自定义函数
查询优化器：对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划，查询优化器会对一些查询进行改写
可以优化的sql类型
1、重新定义表的关联顺序；
2、将外连接转换为内连接；
3、使用等价变换规则；
4、优化count(),min(),max()；
5、将一个表达式转换为常数；
6、子查询优化；
7、提前终止查询，如发现一个不成立条件(如where id = -1)，立即返回一个空结果；
8、对in()条件进行优化；
set profiling = 1; #启动profile,这是一个session级的配制执行查询 show profiles; # 查询每一个查询所消耗的总时间的信息 show profiles for query N; # 查询的每个阶段所消耗的时间
启动监控和历史记录表：use performance_schema
update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%'; update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';
利用主从复制，先对从服务器进入修改，然后主从切换
（推荐）
添加一个新表（修改后的结构），老表数据导入新表，老表建立触发器，修改数据同步到新表， 老表加一个排它锁（重命名）， 新表重命名， 删除老表。
修改语句这个样子：
alter table sbtest4 modify c varchar(150) not null default ''
利用工具修改：
子查询改写为关联查询：
分担读负载 可通过 一主多从，升级硬件来解决。
拆分简单,不允许跨库。但并不能减少写负载。
该方式只能在一定时间内减少写压力。
以上两种方式只能暂时解决读写性能问题。
对一个库中的相关表进行水平拆分到不同实例的数据库中
分区键要能尽可能避免跨分区查询的发生
分区键要尽可能使各个分区中的数据平均
感谢各位的阅读，以上就是“怎么掌握mysql查询优化和分库分表”的内容了，经过本文的学习后，相信大家对怎么掌握mysql查询优化和分库分表这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
