深圳企业网站建设,长春网站建设推广优化,制作小程序的平台,12306网站 花了多少钱建设基础数据准备二#xff1a;五百万数据插入上面插入几条测试数据#xff0c;在使用索引时还需要插入更多的数据作为测试数据#xff0c;下面就通过存储过程插入500W条数据作为测试数据三#xff1a;使用索引和不使用索引的比较没有添加索引前一个简单的查询用了1.79秒创建索…基础数据准备二五百万数据插入上面插入几条测试数据在使用索引时还需要插入更多的数据作为测试数据下面就通过存储过程插入500W条数据作为测试数据三使用索引和不使用索引的比较没有添加索引前一个简单的查询用了1.79秒创建索引然后再查询可以看到耗时0.00秒这就是索引的威力四explain命令explain命令用于查看sql执行时是否使用了索引是优化SQL语句的一个非常常用而且非常重要的一个命令, 上面中的key字段表示查询使用到的索引即使用了idx_username索引id: SELECT识别符。这是SELECT的查询序列号select_type: 查询类型simple: 简单表即不适用表连接或者子查询primary: 主查询即外层的查询subquery: 子查询内层第一个SELECT结果不依赖于外部查询dependent subquery: 子查询内层第一个select: 依赖于外部查询union: UNION语句中第二个SELECT开始后面所有SELECTunion result union 中合并结果DERIVEDtable查询的表partitionstype扫描的方式all表示全表扫描all : 全表扫描index: 扫描所有索引range: 索引范围扫描,常见于 、、、between、const: 表最多有一个匹配行, 常见于根据主键或唯一索引进行查询system: 表仅有一行(系统表)。这是const联接类型的一个特例refpossible_keys: 该查询可以利用的索引可能同一个查询有多个索引可以使用如果没有任何索引显示nullkey: 实际使用到的索引从Possible_key中所选择使用索引当有多个索引时mysql会挑出一个最优的索引来使用key_len: 被选中使用索引的索引长度ref:多表连接时的外键字段constrows: 估算出结果集行数该sql语句扫描了多少行可能得到的结果,MySQL认为它执行查询时必须检查的行数filtered:Extra额外重要的信息no tables: Query语句中使用FROM DUAL 或不含任何FROM子句using filesort : 使用文件排序最好能避免这种情况Using temporary: 某些操作必须使用临时表常见 GROUP BY ; ORDER BYUsing where: 不用读取表中所有信息仅通过索引就可以获取所需数据;Using join buffer (Block Nested Loop)Using index conditionUsing sort_union(索引名)查看索引的使用情况show status like ‘Handler_read%’;Handler_read_key: 越高越好Handler_read_rnd_next:越低越好查询优化器重新定义表的关联顺序(优化器会根据统计信息来决定表的关联顺序)将外连接转化成内连接(当外连接等于内连接)使用等价变换规则(如去掉11)优化count()、min()、max()子查询优化提前终止查询in条件优化mysql可以通过 EXPLAIN EXTENDED 和 SHOW WARNINGS 来查看mysql优化器改写后的sql语句五走索引的情况和不走索引的情况1. in走索引in操作能避免则避免若实在避免不了需要仔细评估in后边的集合元素数量控制在1000个之内。2. 范围查询走索引3. 模糊查询只有左前缀使用索引4. 反向条件不走索引 ! 、 、 NOT IN、IS NOT NULL5. 对条件计算(使用函数或者算数表达式)不走索引使用函数计算不走索引无论是对字段使用了函数还是值使用了函数都不走索引解决办法通过应用程序计算好将计算的结果传递给sql而不是让数据库去计算id是主键id/10使用了算数表达式不走索引6. 查询时必须使用正确的数据类型如果索引字段是字符串类型那么查询条件的值必须使用引号否则不走索引7. or 使用索引和不使用索引的情况or 只有两边都有索引才走索引如果都没有或者只有一个是不走索引的8. 用union少用or尽量避免使用or因为大部分or连接的两个条件同时都进行索引的情况几率比较小应使用uninon代替这样能走索引的走索引不能走索引的就全表扫描。9. 能用union all就不用unionunion all 不去重复union去重复union使用了临时表应尽量避免使用临时表10. 复合索引对于复合索引如果单独使用右边的索引字段作为条件时不走索引的。即复合索引如果不满足最左原则leftmost不会走复合索引11. 覆盖索引覆盖索引是select的数据列只用从索引中就能够取得不必读取数据行换句话说查询列要被所建的索引覆盖。当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。它包括在查询里的Select、Join和Where子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段也即索引包含了查询正在查找的数据)覆盖索引根据关键字就能够直接获取查询所需要的所有数据不必要读取数据行的数据所有数据是指where、select从句、order by、 group by从句的值如果索引字段是字符串那么查询条件必须加引号但是如果查询的列都在索引中即使不满足走索引的条件此时也会使用索引。示例中order_code666666,是数字类型没有加引号按说是不走索引的但是select * 而test表只有两个字段id和order_code而这两个字段都创建了索引这种情况也会走索引12. order bymysql有两种排序方式通过有序索引顺序扫描直接返回有序数据通过explain分析显示Using Index,不需要额外的排序操作效率比较高。通过对返回数据进行排序也就是Filesort排序所有不是通过索引直接返回排序结果的都叫Filesort排序。Filesort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进行排序如果内存装载不下就会将磁盘上的数据进行分块再对各个数据块进行排序然后将各个块合并成有序的结果集order by 使用索引的严格要求索引的顺序和order by子句的顺序完全一致索引中所有列的方向(升续、降续)和order by 子句完全一致当多表连接查询时order by中的字段必须在关联表中的第一张表中如果有 order by 的场景请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分并且放在索引组合顺序的最后避免出现 file_sort 的情况影响查询性能。正例:where a? and b? order by c; 索引:a_b_c反例:索引中有范围查找那么索引有序性无法利用如:WHERE a10 ORDER BY b; 索引 a_b 无法排序order by如果根据多个值进行排序那么排序方式必须保持一致要么同时升续要么同时降续排序方式不一致不走索引13. group by默认情况下group by column有两个作用第一个就是根据指定的列进行分组第二作用group by 不但分组而且还为分组中的数据按照列来排序如果分组的字段创建了索引那么排序也没什么毕竟排序走索引也很快但是如果group by指定的列没有走索引而我们通常情况下只对分组中的数据进行统计例如对分组中的数据求和通常顺序无关紧要此时就要关闭group by 的排序功能使用Order By NULL;来关闭排序功能避免排序对性能的影响。14. 分页limit分页查询一般会全表扫描优化的目的应尽可能减少扫描第一种思路在索引上完成排序分页的操作最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id覆盖索引效率高些第二中思路limit m,n 转换为 n之前分页查询是传pageNo页码, pageSize分页数量当前页的最后一行对应的id即last_row_id以及pageSize这样先根据条件过滤掉last_row_id之前的数据然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列如果用于重复的列那么分页数据将不准确当只一行数据使用limit 1多表连接查询连接条件(也就是外键必须创建索引否则大数据查询直接卡死)如果全表扫描比使用索引快就不会使用索引比如 表的数量很少或者满足条件的数据量比较大也不走索引, 查询数据库记录时查询到的条目数尽量小当通过索引获取到的数据库记录 数据库总记录的1/3时SQL将有可能直接全表扫描索引就失去了应有的作用。where条件将能过滤掉多的条件写在前面过滤掉少部分的数据写在后面这样先排除一大部分不满足条件的数据然后剩下一小部分数据然后再从中找出满足条件的记录数据类型不匹配是不会走索引例如对字符串类型创建索引where条件值却没有使用引号就不走索引,join语句中join条件字段类型不一致的时候MYSQL无法使用索引15 in和exists查询所有下过订单的用户(tbl_user 500w条数据tbl_order 3条数据), 有两种方式一种使用in另一种使用exists但是两者效率相差很大in的伪代码SELECT * FROM A WHERE id IN (SELECT a_id FROM B) 当A表中的数据量远大于B表中的数据量时使用in查询用户id大于10的用户的订单信息从以上逻辑可以看到exists每次循环一下外表都要查询一下内表即使外表数量很少但是每循环一次外表都要去查询一个大表这样的效率是不高的除非子查询走索引最好走覆盖索引。SELECT * FROM B o WHERE exists(SELECT 1 FROM A WHERE o.user_id 10) 网上说当B表的数据量小A表的数据量很大时用exists。上面那个例子没有测试出来实际优化时in和exists具体那个好根据具体执行情况来选择也不能一味的就说某种情况下用这个就比那个快16. 强制索引当查询时不走索引时可以通过force index 强制mysql使用指定的索引一般情况下如果mysql不走索引它是认为全表扫描会更快些可以通过强制走索引看一下查询时间如果强制索引效果更好查询速度更快就使用强制索引如果强制索引没有明显效果就没必要使用了。mysql强制使用索引:force index(索引名或者主键PRI)-- 强制使用主键索引select * from table force index(PRI);-- 强制使用索引idx_xxxselect * from table force index(idx_xxx);-- 强制使用索引PRI和idx_xxxselect * from table force index(PRI,idx_xxx);mysql禁止某个索引ignore index(索引名或者主键PRI)-- 禁止使用主键select * from table ignore index(PRI)-- 禁止使用索引idx_xxxselect * from table ignore index(idx_xxx);-- 禁止使用索引PRI,idx_xxxselect * from table ignore index(PRI,idx_xxx);六其它优化禁止使用select *需要什么字段就去取哪些字段超过三个表禁止join。需要join的字段数据类型必须绝对一致;多表关联查询时保证被关联的字段需要有索引。说明:即使双表 join 也要注意表索引、SQL 性能。尽可能避免复杂的join和子查询。尽量使用左右连接少使用内连接。永远小结果集驱动大结果集(这点mysql会自动优化)不要使用count(列名)或 count(常量)来替代 count()count()是SQL92定义的标准统计行数的语法跟数据库无关跟 NULL和非NULL无关。说明:count(*)会统计值为NULL 的行而count(列名)不会统计此列为NULL值的行。不得使用外键与级联一切外键概念必须在应用层解决。禁止使用存储过程存储过程难以调试和扩展更没有移植性。避免使用存储过程、触发器。使用表连接来优化子查询。尽量避免使用子查询建议将子查询转换成关联查询子查询的效率并没有连接join查询快(并不绝对)连接查询之所以更有效率一些是因为mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。对于多表连接如果连接条件创建索引效率更高。对于列类型是字符串值必须使用单引号括住如果没有引号引住就不走索引 结论字符串必须使用‘’引住拒绝大SQL拆分成小SQL优先优化高并发的SQL而不是执行频率低某些“大”SQL在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON 在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。减少 IO 次数IO永远是数据库最容易瓶颈的地方这是由数据库的职责所决定的大部分数据库操作中超过90%的时间都是 IO 操作所占用的减少 IO 次数是 SQL 优化中需要第一优先考虑当然也是收效最明显的优化手段。降低 CPU 计算除了 IO 瓶颈之外SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后降低 CPU 计算也就成为了我们 SQL 优化的重要目标大表的数据修改最好要分批处理例如1000万行的记录删除更新 100万行记录可以一次只删除更新5000行记录暂停几秒然后再执行剩下的数据。如何修改大表的表结构先在从服务器上修改然后将从服务器改为主服务器然后再从主服务器上修改然后再切换回来此种方式需要切换主从有一定的风险。在主服务器上创建一张新表将老表的数据迁移到新表创建一个触发器将老表的新数据同步到新表中对老表加一个排它锁重新命名新表和老表然后删除掉老表(整个操作过程比较复杂可以通过工具来实现)读者福利马上又是新的一年的“金三银四”了想沉淀学习明年准备跳槽的朋友们一定需要全面的面试资料和进阶学习渠道小编这里有专门整理的java面试集锦关注私信小编回复“资料”即可获得免费领取方式~
