无锡网站搜索引擎优化,wordpress 常量,宁波制作网站公司,cpa没有网站怎么做优化所需知识点#xff08;必须掌握#xff09;
索引篇 explain命令 重点#xff1a;这是后续分析是否使用索引以及使用是否恰当的工具 作用#xff1a;查看sql的执行计划#xff0c;可以看sql语句是否使用了索引#xff0c;索引的使用情况#xff0c;以及sql的性能。 …优化所需知识点必须掌握
索引篇 explain命令 重点这是后续分析是否使用索引以及使用是否恰当的工具 作用查看sql的执行计划可以看sql语句是否使用了索引索引的使用情况以及sql的性能。 使用方法explain查询语句例如explain select * from test 字段解释只讲需要用到的
字段含义id一条sql语句表的执行顺序(id相同从上到下顺序执行。id不同值越大越先执行)type表示连接类型性能有好到差连接类型为 null,system,const,eq_ref,ref,range,index,allpossible_key可能应用在这张表上的索引(一个或多个)key实际使用的索引如果为null则没有使用索引Extra字段含义 Using index 性能好不需要回表查询 Using where 性能好不需要回表查询 Using index condition 需要回表查询 Using temporary 在查询过程中使用了临时表 Using filesort 使用了额外的排序操作来满足 ORDER BY 子句 索引使用原则 重点这决定了你创建哪些索引可以提升性能 针对数据量大并且查询比较频繁的表建立索引针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。尽量选择区分度高的列例如身份证作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间 避免回表提高查询效率要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。
索引失效场景 重点创建了索引但是失效了得不偿失 联合索引中出现范围查询(,)范围查询右侧的列索引失效解决方案使用、不要在索引列上进行运算操作 索引将失效字符串类型字段使用时不加引号索引将失效如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会 被用到。 优化实战
代码展示 SELECTDISTINCTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN 离异WHEN mbi.marriage 3 THEN 丧偶ELSE END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,-- mbi.car,-- mbi.house,-- CASE-- WHEN mbi.income 3 THEN 3K以上-- WHEN mbi.income 4 THEN 3K-5K-- WHEN mbi.income 5 THEN 5K-8K-- WHEN mbi.income 6 THEN 8K-12K-- WHEN mbi.income 7 THEN 12K-20K元-- WHEN mbi.income 8 THEN 20K-50K元-- WHEN mbi.income 9 THEN 50K以上-- ELSE NULL-- END AS income,CASEWHEN EXISTS (SELECT 1 FROM lspace_member.sea_customer_listWHERE member_id u.idAND tenant_id u.tenant_idAND shop_id u.shop_id) THEN 1ELSE 0 END as isSea,CASEWHEN EXISTS (select 1 from lspace_member.blank_member where member_id u.id) THEN 1ELSE 0 END as isBlank,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1) IN (NULL, https://static.lspace.vip/images/my/boy.jpg, https://static.lspace.vip/images/my/girl.jpg)THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.user uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_idu.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId u.idif testmemberListReq.isMyCustomer 1inner join lspace_member.service_case sc on sc.worker_id#{memberListReq.workerId} and sc.member_id u.id and sc.case_type1/ifif testmemberListReq.isMyCustomer 0left join lspace_member.service_case sc on sc.member_id u.id and sc.case_type1/ifleft JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_idu.id and uvq.status2left JOIN lspace_member.service_period sp on sp.member_idu.id and uvq.order_idsp.order_idWHEREu.sex #{memberListReq.sex}AND u.sex IS NOT NULLif testmemberListReq.homeTowns ! null and !memberListReq.homeTowns.isEmpty()and mbi.hometownCode inforeach itemitem indexindex collectionmemberListReq.homeTowns open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.workCitys ! null and !memberListReq.workCitys.isEmpty()and mbi.workCity inforeach itemitem indexindex collectionmemberListReq.workCitys open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.minAge ! nullAND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) gt; #{memberListReq.minAge}/ifif testmemberListReq.maxAge ! nullAND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) lt; #{memberListReq.maxAge}/ifif testmemberListReq.text ! null and memberListReq.text ! AND(uicm.code LIKE CONCAT(%,#{memberListReq.text}, %)oru.phone#{memberListReq.text})/ifgroup byu.idORDER BYuicm.score DESC,CASEWHEN MIN(sp.start_time) IS NOT NULL THEN MIN(sp.start_time)ELSE u.gmt_createEND DESC,u.id ASClimit #{offest},#{size}
第一步连表优化 根据阿里巴巴的《Java 开发手册》中的 SQL 设计规范关于 SQL 查询中涉及的表的数量并没有具体的硬性限制但是在实践中阿里规范建议尽量避免过多的表连接建议连表数量小于3张。 上面这段sql主要是为了查询出符合条条件的用户。这是优化前代码链表7张非必要链表2张。根据业务需求可以看看你业务需求中是不是连的表都必须连很影响性能能拆则拆。我根据业务分析后我发现有两张表可以拆出来拆
拆后代码 SELECTDISTINCTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN 离异WHEN mbi.marriage 3 THEN 丧偶ELSE END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1) IN (NULL, https://static.lspace.vip/images/my/boy.jpg, https://static.lspace.vip/images/my/girl.jpg)THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.user uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_idu.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId u.idif testmemberListReq.isMyCustomer 1inner join lspace_member.service_case sc on sc.worker_id#{memberListReq.workerId} and sc.member_id u.id and sc.case_type1/ifif testmemberListReq.isMyCustomer 0left join lspace_member.service_case sc on sc.member_id u.id and sc.case_type1/ifleft JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_idu.id and uvq.status2left JOIN lspace_member.service_period sp on sp.member_idu.id and uvq.order_idsp.order_idWHEREu.sex #{memberListReq.sex}AND u.sex IS NOT NULLif testmemberListReq.homeTowns ! null and !memberListReq.homeTowns.isEmpty()and mbi.hometownCode inforeach itemitem indexindex collectionmemberListReq.homeTowns open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.workCitys ! null and !memberListReq.workCitys.isEmpty()and mbi.workCity inforeach itemitem indexindex collectionmemberListReq.workCitys open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.minAge ! nullAND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) gt; #{memberListReq.minAge}/ifif testmemberListReq.maxAge ! nullAND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) lt; #{memberListReq.maxAge}/ifif testmemberListReq.text ! null and memberListReq.text ! AND(uicm.code LIKE CONCAT(%,#{memberListReq.text}, %)oru.phone#{memberListReq.text})/ifgroup byu.idORDER BYuicm.score DESC,CASEWHEN MIN(sp.start_time) IS NOT NULL THEN MIN(sp.start_time)ELSE u.gmt_createEND DESC,u.id ASClimit #{offest},#{size}
第二步SQL优化 在SQL语句中少做运算可提高新增而且可以有效避免索引失效所以尽量不要在SQL做运算。 这三种第一种在java代码中预先计算生日后作为条件搜索避免了函数运算和索引失效所以做了第二步优化 1. mbi.birthday 2000-01-08 2. FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) 25 3.mbi.birthday DATE_SUB(CURDATE(), INTERVAL 25 YEAR) 优化后代码 SELECTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN 离异WHEN mbi.marriage 3 THEN 丧偶ELSE END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1) IN (NULL, https://static.lspace.vip/images/my/boy.jpg, https://static.lspace.vip/images/my/girl.jpg)THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id u.idAND type 16AND STATUS 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.user uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_idu.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId u.idif testmemberListReq.isMyCustomer 1inner join lspace_member.service_case sc on sc.worker_id#{memberListReq.workerId} and sc.member_id u.id and sc.case_type1/ifif testmemberListReq.isMyCustomer 0left join lspace_member.service_case sc on sc.member_id u.id and sc.case_type1/ifleft JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_idu.id and uvq.status2left JOIN lspace_member.service_period sp on sp.member_idu.id and uvq.order_idsp.order_idWHEREu.sex #{memberListReq.sex}AND u.sex IS NOT NULLif testmemberListReq.homeTowns ! null and !memberListReq.homeTowns.isEmpty()and mbi.hometownCode inforeach itemitem indexindex collectionmemberListReq.homeTowns open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.workCitys ! null and !memberListReq.workCitys.isEmpty()and mbi.workCity inforeach itemitem indexindex collectionmemberListReq.workCitys open( separator,close)#{item,jdbcTypeINTEGER}/foreach/ifif testmemberListReq.minBirthday ! null and memberListReq.minAge!0AND mbi.birthday lt; #{memberListReq.minBirthday}/ifif testmemberListReq.maxBirthday ! nullAND mbi.birthday gt; #{memberListReq.maxBirthday}/if
!-- if testmemberListReq.minAge ! null--
!-- AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) gt; #{memberListReq.minAge}--
!-- /if--
!-- if testmemberListReq.maxAge ! null--
!-- AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) lt; #{memberListReq.maxAge}--
!-- /if--if testmemberListReq.text ! null and memberListReq.text ! AND(uicm.code LIKE CONCAT(%,#{memberListReq.text}, %)oru.phone#{memberListReq.text})/ifgroup byu.idORDER BYuicm.score DESC,MIN(sp.start_time) DESC,u.gmt_create desc,u.id ASClimit #{offest},#{size}
第三步加索引 根据索引篇的知识点在区分度高数据列多条件搜索排序这个上面创建索引。创建后使用explain命令逐一分析创建的索引是否有效是否被使用。在进行调整 根据我的业务需求我添加了3个索引来满足我的业务需求
CREATE INDEX idx_workCity ON lspace_member.memberbaseinfo (workCity);
CREATE INDEX idx_hometown ON lspace_member.memberbaseinfo (hometownCode);CREATE INDEX idx_code ON lspace_diga.user_id_code_mapping (code);
第四步java代码优化 根据感觉简单优化了一下代码对效率其实影响不太大。大家也自我感觉优化一下吧特别涉及是for循环for循环中有查询的重点关注一下 总结
根据以上方式速度快了将近一半300ms左右。主要也是对SQL的优化合理的使用索引恰当的链表方式可以大大提高性能。
