建筑工程发布网站,宁波专业网站建设怎么做,公司网站建设工作方案,美篇相册制作免费下载app一、SQL查询优化(重要)1.1 获取有性能问题SQL的三种方式通过用户反馈获取存在性能问题的SQL#xff1b;通过慢查日志获取存在性能问题的SQL#xff1b;实时获取存在性能问题的SQL#xff1b;1.1.2 慢查日志分析工具相关配置参数#xff1a;slow_query_log # 启动停止记录慢… 一、SQL查询优化(重要)1.1 获取有性能问题SQL的三种方式通过用户反馈获取存在性能问题的SQL通过慢查日志获取存在性能问题的SQL实时获取存在性能问题的SQL1.1.2 慢查日志分析工具相关配置参数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-digestpt-query-digest --explain h127.0.0.1,uroot,ppssWord slow-mysql.log1.1.3 实时获取有性能问题的SQL(推荐)SELECT id,user,host,DB,command,time,state,infoFROM information_schema.processlistWHERE TIME60查询当前服务器执行超过60s的SQL可以通过脚本周期性的来执行这条SQL就能查出有问题的SQL。1.2 SQL的解析预处理及生成执行计划(重要)1.2.1 查询过程描述(重点)通过上图可以清晰的了解到MySql查询执行的大致过程发送SQL语句。查询缓存如果命中缓存直接返回结果。SQL解析预处理再由优化器生成对应的查询执行计划。执行查询调用存储引擎API获取数据。返回结果。1.2.2 查询缓存对性能的影响(建议关闭缓存)第一阶段相关配置参数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 第二阶段MySQL依照执行计划和存储引擎进行交互这个阶段包括了多个子过程 一条查询可以有多种查询方式查询优化器会对每一种查询方式的(存储引擎)统计信息进行比较找到成本最低的查询方式这也就是索引不能太多的原因。1.3 会造成MySQL生成错误的执行计划的原因 1、统计信息不准确 2、成本估算与实际的执行计划成本不同 3、给出的最优执行计划与估计的不同 4、MySQL不考虑并发查询 5、会基于固定规则生成执行计划 6、MySQL不考虑不受其控制的成本如存储过程用户自定义函数1.4 MySQL优化器可优化的SQL类型查询优化器对查询进行优化并查询mysql认为的成本最低的执行计划。为了生成最优的执行计划查询优化器会对一些查询进行改写 可以优化的sql类型 1、重新定义表的关联顺序 2、将外连接转换为内连接 3、使用等价变换规则 4、优化count(),min(),max() 5、将一个表达式转换为常数 6、子查询优化 7、提前终止查询如发现一个不成立条件(如where id -1)立即返回一个空结果 8、对in()条件进行优化1.5 查询处理各个阶段所需要的时间1.5.1 使用profile(目前已经不推荐使用了)set profiling 1; #启动profile,这是一个session级的配制执行查询show profiles; # 查询每一个查询所消耗的总时间的信息show profiles for query N; # 查询的每个阶段所消耗的时间1.5.2 performance_schema是5.5引入的一个性能分析引擎(5.5版本时期开销比较大)启动监控和历史记录表use performance_schemaupdate setup_instruments set enabledYES,TIME YES WHERE NAME LIKE stage%;update set_consumbers set enabledYES,TIME YES WHERE NAME LIKE event%; 1.6 特定SQL的查询优化1.6.1 大表的数据修改 1.6.2 大表的结构修改对表中的列的字段类型进行修改改变字段的宽度时还是会锁表无法解决主从数据库延迟的问题利用主从复制先对从服务器进入修改然后主从切换(推荐)添加一个新表(修改后的结构)老表数据导入新表老表建立触发器修改数据同步到新表 老表加一个排它锁(重命名) 新表重命名 删除老表。 修改语句这个样子alter table sbtest4 modify c varchar(150) not null default 利用工具修改 1.6.3 优化not in 和 查询 子查询改写为关联查询SELECT customer_id,first_name,last_name,email FROM customerWHERE customer_idNOT IN (SELECT customer_id FROM payment) 改写后SELECT a.customer_id,a.first_name,a.last_name,a.email FROM customer aLEFT JOIN payment b ON a.customer_id b.customer_idWHERE b.customer_id IS NULL二、分库分表2.1 分库分表的几种方式分担读负载 可通过 一主多从升级硬件来解决。2.1.1 把一个实例中的多个数据库拆分到不同实例(集群)拆分简单,不允许跨库。但并不能减少写负载。2.1.2 把一个库中的表分离到不同的数据库中该方式只能在一定时间内减少写压力。以上两种方式只能暂时解决读写性能问题。2.1.3 数据库分片对一个库中的相关表进行水平拆分到不同实例的数据库中2.1.3.1 如何选择分区键分区键要能尽可能避免跨分区查询的发生分区键要尽可能使各个分区中的数据平均2.1.3.2 分片中如何生成全局唯一ID使用auto_increment_increment和auto_increment_offset参数使用全局节点来生成ID在Redis等缓存服务器中创建全局ID(推荐)完唐成勇https://segmentfault.com/a/1190000013781544
