拟定网站优化方案,建设个网站要多少钱,wordpress ssl nginx,做php网站曾几何时#xff0c;我信誓旦旦的认为只要 SQL 写的好#xff0c;面试过程永不倒。结果在一次又一次的征途中#xff0c;「最终还是以完败落下帷幕」。结果都源于注重使用而忽略原理#xff0c;从而不知也不了解「SQL」优化应如何面对。读文的你#xff0c;如今可有这样…曾几何时我信誓旦旦的认为只要 SQL 写的好面试过程永不倒。结果在一次又一次的征途中「最终还是以完败落下帷幕」。结果都源于注重使用而忽略原理从而不知也不了解「SQL」优化应如何面对。读文的你如今可有这样的沧桑1、SQL 执行原理过程分析「话说开篇就谈 SQL 的执行原理不太好吧」「错那是你 矮了心胸狭隘了」。这都是优化的根基呀SQL 语句最终落地执行的场景如果都不明白那根本就能拥有优化的思路。那 SQL 优化的定义是什么「简单点就是以最快的时间获取到需要的结果。那怎么快如何快」首先来看看 「MySQL」 检索数据原理过程。例select * from table id 5 and name zhazha假如。ID 为「主键索引」那么 SQL 执行过程可分为两步优化器调用存储引擎提供的 API 接口通过主键索引搜索 ID 5 的记录读取并载入到 MySQL 服务层内部(即内存)在服务层中获取的记录要一一和 name 字段进行对比。一旦记录满足条件就开始逐步发送给客户端。每一性的结果集都通过 MySQL 协议进行封包并写入缓存区然后再批量传输。故此 「SQL 优化」 提速应从三方面入手「扫描的行数使用索引减少扫描行记录」「返回的行数通过 where 条件减少不必要行的判断。一般 MySQL 有三种应用 where 条件」ps: 指定 where 条件字段为 索引列针对聚集索引这种情况在索引中使用 where 条件来过滤不匹配的记录。这是在存储引擎层完成的。使用索引覆盖扫描(在 Extra 列中出现 Using index 情况)来返回记录直接从索引中过滤不需要的记录并返回命中的结果。where 条件将在 MySQL 服务器层完成但无须再回表查询记录。从数据表中返回数据然后过滤不满足条件的记录(在 Extra 列中出现 Using Where)。where 条件将在 MySQL 服务器层 完成MySQL 需先从数据表读出记录然后过滤。在这里条件的选择将决定服务器层过滤多少数据。比如用户表有 10000 条记录每个用户的 「email」 是唯一的如果用户使用 「email」 作为用户名登陆的话大多数可能会这些写。SELECT * FROM t_user WHERE email?;但上面的语句实现了查询 email 对应的用户信息但由于 email 这一列没有加索引会导致全表扫描。然后在服务层里面再进行 where 条件的一一对比。如何改呢 SELECT * FROM t_user WHERE email? LIMIT 1;加上 LIMIT 1只要找到了对应的一条记录就不会继续向下扫描。所以在写 SQL 条件语句时可查看针会对多少数据结果集。「响应时间即服务时间和排队的时间。像 I/O 执行、锁等待消耗的时间」可似 我还是有几个疑问就下面数据库响应为什么要一条条结果发送呢不是一起批量响应结果更为方便吗结果集以每条记录发送后面又怎么成为批量传输了那如果是 join 怎么执行的呀SQL 语句怎么来呢骚年就你这个发问真乃骨骼惊奇、万中挑一的编程奇才。实力雄厚这门东西都是指日可待。到这里是不是应该来个赞呢「数据库传输问题」MySQL 逐步返回结果有两个好处:服务器端无须存储太多结果也就不会因为要返回太多结果而 消耗太多内存。这样的处理也让 MySQL 客户端第一时间获的返回的结果。「数据批量传输」主要因为 MySQL 协议采用 TCP 协议传输而 TCP 是一个流式协议传输类似于你打开水龙头后水就一直流出来。所以数据发送前都会先写入到网卡的缓冲区里面满了之后才会一并的发给客户端。而 MySQL 协议封包为的就是防止数据出现丢或和数据不一致问题。「join 查询问题」首先我们得明白数据库是如何进行关联查找对于联合查询MySQL 先将一系列的单个查询结果放到一个临时表中然后再重新读出临时表数据来完成联合查询。「注以 关联表 和 主表 说明当前表 为查询的主表关联表为 join 连接的表」MySQL 先根据 where 条件在主表中找到满足条件的记录然后循环取出单条数据并嵌套循环到下一个表中寻找匹配的行依次下去直到找到所有关联表中匹配的行为止。然后根据各个表匹配到的行返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行如果最后一个关联表无法找到更多的行MySQL 就返回到上一层次关联表看是否能够找到更多的匹配记录依此类推迭代执行。注在 MySQL 的概念中每个查询都是一次关联。所以读取结果临时表也是一次关联。综上关联查询实际就是一个分解查询的关系用主表关联字段的只做为条件去找到关联表中满足数条件得数据。这也是为什么要 join 字段上建立索引的原因像什么 「order by limit group by」等函数实际上都是在拿到每个查询结果集之后或者之前在索引里面行处理。你所写的条件会根据表的执行顺序来进行使用但有时优化器会更改执行顺序。但我想给关联表加入限制条件呢你直接在 on 字段后面 用逻辑连接符号加入 where 即可。例下ON a.id b.id and b.name 吒吒辉2、前方高能预警怎么优化 SQL这方向怎么选到现在大家应该对 SQL 优化有了属于自己的侧重点吧。根据业务定位到时间消耗最大并优化试问这样你还不能写出符合你业务的语句吗❝在这里请给我来一个点赞和关注吧救救在下吧❞首先定位到某些业务查询很慢然后再拆解到底是哪个部分最耗时间。响应时间上面谈到响应时间服务时间排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间。可能是等 I/O 操作完成也可能是等待行锁一般最常见和重要的等待是 I/O 和锁等待但是实际情况更加复杂你免 SQL 语句中的执行函数和其它内存上的分配等等吧。所以针对一个查询很慢语句首先得看这个语句是查询上的问题还是服务器的问题。如果是查询语句慢那你优化 SQ 语句。如果是服务器上整体程序都比较慢大多数都是服务器负责太高。那要怎么做呢使用 SHOW GLOBAL STATUS通过 SHOW GLOBAL STATUS查看每秒的查询数(Queries)、Threads* connected 和 Threads* running (表示当前正在执行查询的线程数)。进而定位 mysql 工作线程是否达到瓶颈。这三个数据的趋势对于服务器级别偶尔停顿的敏感性很高。一般发生此类问题时根据原因的不同和应用连接数据库方式的不同每秒的查询数一般会下跌。所以我们可写个脚本每秒去获取数据库的执行状态从而定位目前数据库的负载能力。使用 SHOW PROCESSLIST通过 SHOW PROCESSLIST 找到数据库下面每个工作线程的执行状态如果某一业务下执行比较慢那么它的工作线程一定长时间处于查询状态(query),这时你就需要针对线程所在业务的 SQL 进行优化。「主要列含义」id 列:一个标识你要 kill 一个语句连接就有用它。user 列: 显示当前用户如果不是 root这个命令就只显示你权限范围内的 sql 语句。command 列:显示当前连接的执行的命令一般就是休眠(sleep)查询(query)连接(connect)其他查询锁住(Locked)。state 列:显示使用当前连接的 sql 语句的状态很重要的列请注意state 只是语句执行中的某一个状态一个 sql 语句以查询为例可能需要经过 copying to tmp tableSorting resultSending data 等状态才可以完成。使用慢日志查询如果要通过查询日志发现问题需要开启慢查询日志并在全局级别设置 「long_query_time 为 0」 , 还一个 log_output 参数要注意它有 table 和 file 选择分别代表日志存储为数据表还是文件。并设置并且要确认所有的连接都采用了新的设置。这可能需要重置所有连接以使新的全局设置生效;随着服务器运行过久日志文件可能达到几百 G这时候如果打开文件查找就不是一件理智的事情。可以根据 MySQL 每秒将当前时间写入日志中的模式统计每秒的查询数量:awk /^# Time:/{print $3, $4, c;c0}/^# User/{c} slow-query.1og如果发现 mysql 吞吐量有高低起伏直接根据对应的时间点看到详细的记录进而分析问题。针对优化之前首先得看到 MySQL 的性能是否为服务资源不够处理什么业务线程执行的慢这个慢操作产生的原因是什么等确定要优化的问题。所以 show global status 和 show processlist 都是很好的手段。减少扫描的行要想减少扫描的行就得看到有哪些途径可以减 MySQL 查询的行「索引」 就是选择对索引原理不了解可以去看 互联网大厂面试谈索引就直逼这些底层难的是我不懂这些原理当你查询中用到了索引字段那么一般都会使用到索引这里列举不会使用索引的方式更重要的是明白索引的底层结构是什么。索引走不走还是得看字段在 where 条件后上的搭配与索引原理。因为多条件会涉及到「索引合并优化」注索引合并优化(Index merge optimization)当查询中单张表可以使用多个索引时同时扫描多个索引并将扫描结果进行合并。OR使用它的前提条件你需保证 OR 两边的字段都需要建立索引。如果不保证将退化为全表扫描。如查询 SELECT * FROM T1 WHERE a1xxx OR a2xxx如果 c1 和 c2 列上分别有索引可以按照 c1 和 c2 条件进行查询再将查询结果合并(union)操作得到最终结果。「使用了索引合并有什么特点」通过 explain 对 sql 语句进行分析如果使用了索引合并那么会在 type 列会显示 index_mergekey 列会显示出所有使用的索引。为什么当 where 字段列一旦其中有一个不是索引列。就不走索引扫描呢因为全表只扫一次而你索引是根据字段的索引树找的索引最终就会合并全表和索引方式查找的的结果返回给客户端。但扫描的方式就终确认为全表方式。以下为匹配记录:如下是索引使用情况很多人说 OR 需要用 union all 或者 in 优化如果都知道 OR 的索引触发条件还需进行结果搜索合并与 in 的条件限制吗你细品inin 是查询上面的一种组合方式根据里面其字段值进行搜索但如果这个组合条件过多就导致优化器没办法很好的使用索引并且使用 in 的字段需建立索引这样才能提升查找效率。如EXPLAIN SELECT * FROM users WHERE id in (5,8,9) AND name in (Prof. Chase McKenzie II,Mr. Porter Prosacco IV) AND remember_token IN (Wjz2H8WrpK, 5WYeM5FPKf);有索引无索引直接全表扫描EXPLAIN SELECT * FROM users WHERE name in (Prof. Chase McKenzie II,Mr. Porter Prosacco IV) AND remember_token IN (Wjz2H8WrpK, 5WYeM5FPKf);范围匹配范围条件的查询无法在使用范围列后面的其他的索引列。一般可以优化为多个等值查询。即转为 in 的过列字段进行值的匹配。! 和如果索引字段使用到 ! 和 操作符那么查询引擎将放弃使用索引而进行全表扫描。因为优化器会权衡查找的行数如果取反那么剩下的数据查找的次数可能接近于全表如果是二级索引还有回表的影响更会加大 MySQL 的查找次数。返回的行数返回行数主要由不同类型的查询把数据从磁盘读取到服务层后使用 where 后面的条件或者查询相关语句来进行限制。count使用 count 计数时不要在括号里面指定一列来统计行数直接采用 count(*)性能会更好。这就有点难为人。为什么呢因为 MySQL 确认括号内的表达式值不可能为空时实际上就是在统计行数。当我们使用 COUNT()的时候这种情况下通配符并不会像我们猜想的那样扩展成所有的列实际上它会忽略所有的列而直接统计所有的行数。都说「MyISAM 的 COUNT()函数总是非常快,不过这是有前提条件的即只有没有任何 WHERE 条件的 COUNT(*) 才非常快」因为此时无须实际地去计算表的行数。MySQL 可以利用存储引擎的特性直接获得这个值。如果 MySQL 知道某列 col 不可能为 NULL 值那么 MySQL 内部会将 COUNT(col)表达式优化为 COUNT(*)。那如何优化反向操作统计 ID 大于 5 的城市。可以像下面这样来写这个查询:mysql SELECT COUNT(*) FROM world.City WHERE ID 5;如果将条件反转一下先查找 ID 小于等于 5 的城市数然后用总城市数一一减就能得到同样的结果却可以将扫描的行数减少到 5 行以内:SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*) FROM world.City WHERE ID 5;关联查询确保 ON 或者 USING 子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表 A 和表 B 用列 C 关联的时候如果优化器的关联顺序是 B、A,那么就不需要在 B 表的对应列上建索引。没有用到的索引只会带来额外的负担。一般来说 除非有其他理由否则只需要在关联顺序中的第二个表的相应列上创建索引。确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列这样 MySQL 才有可能使用索引来优化这个过程。子查询关于子查询优化就是尽可能使用关联查询代替Group by 和 DISTINCT MySQL 都使用同样的办法优化这两种查询事实上MySQL 优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化这也是最有效的优化办法。在 MySQL 中当无法使用索引的时候GROUP BY 使用两种策略来完成:使用临时表或者文件排序来做分组 对于任何查询语句这两种策略的性能都有可以提升的地方。可以通过使用提示 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来让优化器按照你希望的方式运行。如果需要对关联查询做分组(GROUP BY)并且是按照查找表中的某个列进行分组通常采用查找表的标识列分组的效率会比其它列更高。例如下面的查询效率不会很好;SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name;如果查询按照下面的写法效率则会更高:SELECT actor.first name, actor.last name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor.id) GROUP BY film_actor.actor.id;使用 actor.actor_id 列分组的效率甚至会比使用 film_actor.actor_id 更好。如果没有通过 ORDER BY 子句显式地指定排序列当查询使用 GROUP BY 子句的时候结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序而这种默认排序又导致了需要文件排序则可以使用 ORDER BY NULL, 让 MySQL 不再进行文件排序。也可以在 GROUP BY 子句中直接使用 DESC 或者 ASC 关键字使分组的结果集按需要的方向排序。Limit在系统中需要进行分页操作的时候我们通常会使用 LIMIT 加上偏移量的办法实现同时加上合适的 ORDER BY 子句。如果有对应的索引通常效率会不错否则MySQL 需要做大量的文件排序操作。一个非常令人头疼的问题就是在偏移量非常大的时候例如可能是 LIMIT 10000,20 这样的查询这时 MySQL 需要查询 10020 条记录然后只返回最后 20 条前面 10000 条记录都将被抛弃这样的代价非常高。如果所有的页面被访问的频率都相同那么这样的查询平均需要访问半个表的数据。要优化这种查询要么是在页面中限制分页的数量要么是优化大偏移量的性能。优化此类分页查询的个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样的效率会提升非常大。考虑下面的查询:SELECT f1lm_id, description FROM sakila.fi1m ORDER BY title LIMIT 50, 5;如果表非常大可改写成这样SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5 ) AS limt USING(film_id);这里的“延迟关联”将大大提升查询效率它让 MySQL 扫描尽可能少的页面获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的 LIMIT 子句。LIMIT 和 OFFSET 的问题其实是 OFFSET 的问题它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置那么下次就可以直接从该书签记录的位置开始扫描这样就可以避免使用 OFFSET。例如若需要按照租借记录做翻页那么可以根据最新一条租借记录向后追溯这种做法可行是因为租借记录的主键是单调增长的。首先使用下面的查询获得第一组结果:SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;假设上面的查询返回的是主键为 16049 到 16030 的租借记录那么下一页查询就可以从 16030 这个点开始:SELECT * FROM sakila. rental WHERE rental id 16030 ORDER BY rental id DESC LIMIT 20;这样无论翻页到多么后面其性能都会很好。还可以包括预先计算的汇总表或者关联到一个冗余表冗余表只包含主键列和需要做排序的数据列。设置最后一页分页的时候常用的技巧是在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS 提示这样就可以获得去掉 LIMIT 以后满足条件的行数因此可以作为分页的总数。看起来MySQL 做了一些非常“高深”的优化像是通过某种方法预测了总行数。但实际上MySQL 只有在扫描了所有满足条件的行以后才会知道行数所以加上这个提示以后不管是否需要MySQL 都会扫描所有满足条件的行然后再抛弃掉不需要的行而不是再满足 LIMIT 的行数后就终止扫描。所以该提示的代价可能非常高。一个更好的设计是将具体的页数换成“下一页”按钮假设每页显示 20 条记录那么我们每次查询时都是用 LIMIT 返回 21 条记录并只显示 20 条如果第 21 条存在那么我们就显示“下一页”按钮否则就说明没有更多的数据也就无须显示“下一页”按钮了。另一种做法是先获取并缓存较多的数据例如 缓存 1000 条然后 每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略如果结果集少于 1000就可以在页面上显示所有的分页链接因为数据都在缓存中所以这样做性能不会有问题。如果结果集中于 1000,则可以在页面上设计一个额外的“找到的结果多于 1000 条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率要高很多。UNIONMySQL 总是通过创建并填充临时表的方式来执行 UNION 查询。因此很多优化策略在 UNION 查询中都没法很好地使用。经常需要手工地将 WHERE、LIMIT ORDERBY 等子句“下推”到 UNION 的各个子查询中以便优化器可以充分利用这些条件进行优化(例如直接将这些子句冗余地写一份到各个子查询)。总结SQL 优化的重心由 3 方面消耗的时间来决定响应时间、扫描时间、查找时间。扫描查询主要由索引提速如果一些其它条件(order by )与索引列相关那可保证扫描的数据行更少这样就不用在服务层做 where 处理。MySQL 优化器会自行优化 where 条件让执行条件显得更简单。且编写 where 条件也需考虑条件对结果数据的处理。服务器负载也是很关键的如果整体负载过高那数据库性能肯定会直线下降。如有帮助欢迎关注莲花童子哪吒
