這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於常用查詢優化的相關問題，下面一起來看一下，希望對大家有幫助。
推薦學習：mysql影片教學
#在程式上線運行一段時間後，一旦資料量上去了，或多或少會感覺到系統出現延遲、卡頓等現象，出現這種問題，就需要程式設計師或架構師進行系統調優工作了，其中，大量的實踐經驗表明，調優的手段儘管有很多，但涉及到SQL調優的內容仍然是非常重要的一環，本文將結合實例，總結一些工作中可能涉及到的SQL最佳化策略；
可以說，對於大多數系統來說，讀多寫少一定是常態，這表示涉及到查詢的SQL是非常高頻的操作；
前置準備，給一張測試表添加10萬條數據
使用下面的預存程序為單表造一批數據，將表換成自己的就好了
create procedure addMyData() begin declare num int; set num =1; while num <= 100000 do insert into XXX_table values( replace(uuid(),'-',''),concat('测试',num),concat('cs',num),'123456' ); set num =num +1; end while; end ;
然後呼叫該預存程序
call addMyData();
本篇準備了3張表，分別為學生（student）表，班級（class）表，帳戶(account)表，各自有50萬，1萬和10萬條數據用於測試;
分頁查詢是開發中常會遇到的，有一種情況是，當分頁的數量非常大的時候，查詢的時候往往非常耗時，例如查詢student表，使用下面的sql查詢，耗時達到0.2秒；
實務經驗告訴我們，越往後，分頁查詢效率越低，這就是分頁查詢的問題所在， 因為，當在進行分頁查詢時，如果執行 limit 400000,10 ，此時需要 MySQL 排序前4000 10 寫 錄，僅傳回400000 - 4 00010 的記錄，其他記錄丟棄，查詢排序的代價非常大
1)#一般分頁查詢時，透過建立覆蓋索引能夠比較好地提高效能，可以透過覆寫索引加子查詢形式進行最佳化；
SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;
执行上面的sql，可以看到响应时间有一定的提升；
2）对于主键自增的表，可以把Limit 查询转换成某个位置的查询
select * from student where id > 400000 limit 10;
执行上面的sql，可以看到响应时间有一定的提升；
2、关联查询优化
在实际的业务开发过程中，关联查询可以说随处可见，关联查询的优化核心思路是，最好为关联查询的字段添加索引，这是关键，具体到不同的场景，还需要具体分析，这个跟mysql的引擎在执行优化策略的方案选择时有一定关系；
2.1 左连接或右连接
下面是一个使用left join 的查询，可以预想到这条sql查询的结果集非常大
select t.* from student t left join class cs on t.classId = cs.id;登入後複製为了检查下sql的执行效率，使用explain做一下分析，可以看到，第一张表即left join左边的表student走了全表扫描，而class表走了主键索引，尽管结果集较大，还是走了索引；
针对这种场景的查询，思路如下：
- 让查询的字段尽量包含在主键索引或者覆盖索引中；
- 查询的时候尽量使用分页查询；
关于左连接（右连接）的explain结果补充说明
- 左连接左边的表一般为驱动表，右边的表为被驱动表；
- 尽可能让数据集小的表作为驱动表，减少mysql内部循环的次数；
- 两表关联时，explain结果展示中，第一栏一般为驱动表；
2.2 关联查询关联的字段建立索引
看下面的这条sql，其关联字段非表的主键，而是普通的字段；
explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;登入後複製
通过explain分析可以发现，左边的表走了全表扫描，可以考虑给左边的表的tenant_name和user表的account 各自创建索引；
create index idx_name on tenant(tenant_name);
create index idx_account on `user`(account);
再次使用explain分析结果如下
可以看到第二行type变为ref，rows的数量优化比较明显。这是由左连接特性决定的，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引 。
2.3 内连接关联的字段建立索引
我们知道，左连接和右连接查询的数据分别是完全包含左表数据，完全包含右表数据，而内连接（inner join 或join） 则是取交集（共有的部分），在这种情况下，驱动表的选择是由mysql优化器自动选择的；
在上面的基础上，首先移除两张表的索引
ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;使用explain语句进行分析
然后给user表的account字段添加索引，再次执行explain我们发现，user表竟然被当作是被驱动表了；
此时，如果我们给tenant表的tenant_name加索引，并移除user表的account索引，得出的结果竟然都没有走索引，再次说明，使用内连接的情况下，查询优化器将会根据自己的判断进行选择；
3、子查询优化
子查询在日常编写业务的SQL时也是使用非常频繁的做法，不是说子查询不能用，而是当数据量超出一定的范围之后，子查询的性能下降是很明显的，关于这一点，本人在日常工作中深有体会；
比如下面这条sql，由于student表数据量较大，执行起来耗时非常长，可以看到耗费了将近3秒；
select st.* from student st where st.classId in ( select id from class where id > 100 );登入後複製
通过执行explain进行分析得知，内层查询 id > 100的子查询尽管用上了主键索引，但是由于结果集太大，带入到外层查询，即作为in的条件时，查询优化器还是走了全表扫描；
针对上面的情况，可以考虑下面的优化方式
select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;
子查询性能低效的原因
- 子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询；
- 子查询结果集存储的临时表，不论是内存临时表还是磁盘临时表都不能走索引 ，所以查询性能会受到一定的影响；
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
使用mysql查询时，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好，尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代；
一个真实的案例
在下面的这段sql中，优化前使用的是子查询，在一次生产问题的性能分析中，发现某个tenant_id下的数据达到了35万多，这样直接导致某个列表页面的接口查询耗时达到了5秒左右；
找到了问题的根源后，尝试使用上面的优化思路进行解决即可，优化后的sql大概如下，
4、排序（order by）优化
在mysql，排序主要有两种方式
- Using filesort : 通过表索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort
buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序；- Using index : 通过有序的索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高；
对于以上两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index
4.1 使用age字段进行排序
由于age字段未加索引，查询结果按照age排序的时候发现使用了filesort，排序性能较低；
给age字段添加索引，再次使用order by时就走了索引；
4.2 使用多字段进行排序
通常在实际业务中，参与排序的字段往往不只一个，这时候，就可以对参与排序的多个字段创建联合索引；
如下根据stuno和age排序
给stuno和age添加联合索引
create index idx_stuno_age on `student`(stuno,age);
再次分析时结果如下，此时排序走了索引
关于多字段排序时的注意事项
1）排序时，需要满足最左前缀法则,否则也会出现 filesort；
在上面我們建立的聯合索引順序是stuno和age，也就是stuno在前面，而age在後，如果查詢的時候調換排序順序會怎麼樣呢？透過分析結果發現，走了filesort；
2）排序時，排序的類型保持一致
##在保持欄位排序當順序不變時，預設情況下，如果都依照升序或降序時，order by可以使用index，如果一個是升序，另一個是降序會如何呢？分析發現，這種情況下也會走filesort；5、分組（group by）優化group by 的最佳化策略和order by 的最佳化策略非常像，主要列舉以下幾個要點：
group by 即使沒有篩選條件用到索引，也可以直接使用索引；
5.1 給group by的欄位新增索引如果欄位未加索引，分析結果如下，這種結果效能顯然很低效- group by 先排序再分組，遵照索引建立的最佳左前綴法則；
- 當無法使用索引列時，增大max_length_for_sort_data 和sort_buffer_size 參數的設定；
- where效率高於having，能寫在wheree的限定條件就不要寫在having中了；
- 減少使用order by，能不排序就不排序，或將排序放到程式去做。 Order by、groupby、distinct這些語句較為耗費CPU，資料庫的CPU資源是極為寶貴的；
- 如果sql包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢；
為stuno加入索引之後
給stuno和age新增聯合索引
#如果不遵循最佳左前綴，group by 效能將會比低效
遵循最佳左前綴的情況如下
6、count 最佳化count() 是一個聚合函數，對於傳回的結果集，一行行判斷，如果count 函數的參數不是NULL ，累計值就加1，否則不加，最後回傳累計值；
用法：count（*）、count（主鍵）、count（欄位）、count（數字）如下列舉了count的幾種寫法的詳細說明經驗值總結
#用法 count（主鍵）##說明 count(*) InnoDB 會遍歷整張表，把每一行的主鍵id值都取出來，返回給服務層，服務層拿到主鍵後，直接按行進行累加(主鍵不可能為null)； count（字段） InnoDB不會把全部欄位取出來，而是專門做了最佳化，不取值，服務層直接按行進行累加; count（數字） 沒有not null 約束: InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，服務層判斷是否為null，不為null，計數累加，有not null 約束：InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，直接按行進行累加； InnoDB 引擎遍歷整張表，但不取值。服務層對於傳回的每一行，放一個數字「1」進去，直接按行進行累加；
##################### ########依照效率排序來看，count(字段) < count(主鍵id) < count(1) ≈ count(*)，所以盡量使用count(*)####### ###推薦學習：###mysql影片教學######以上是MySql常用查詢最佳化策略詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！
