咱們500萬條數據測試一下，如何合理使用索引加速？

来源:https://www.cnblogs.com/jiagooushi/archive/2022/07/22/16505475.html
-Advertisement-
Play Games

5 如何合理使用索引加速 tips： 500萬條建表sql參照網盤sql腳本 [root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql 索引是資料庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助用戶解 ...


5 如何合理使用索引加速

tips：

500萬條建表sql參照網盤sql腳本

[root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql

索引是資料庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助用戶解決大多數的MySQL的性能優化問題。

5.1 驗證索引提升查詢效率

在我們準備的表結構product_list 中， 一共存儲了 500多萬記錄；

mysql> select count(1) from product_list;
+----------+
| count(1) |
+----------+
|  5072825 |
+----------+
1 row in set (1.71 sec)

mysql>

1） 根據ID查詢

SELECT * FROM product_list WHERE id = 121926;

file

查詢速度很快， 接近0s ， 主要的原因是因為id為主鍵， 有索引；

2). 根據store_name進行精確查詢

執行用時4分鐘

SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

file

查看SQL語句的執行計劃 ：

explain SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

file

處理方案 ， 針對store_name欄位， 創建索引 ：

create index product_list_stname on product_list(store_name);

file

索引創建完成之後，再次進行查詢 ：

SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

file
通過explain ， 查看執行計劃，執行SQL時使用了剛纔創建的索引

-- 查看SQL語句的執行計劃
explain SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

file

5.2 索引的使用

5.2.1 準備環境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name`  varchar (100) not null,
	`nickname` varchar (50),
	`password` varchar (60),
	`status`  varchar (1) not null,
	`address`  varchar (100) not null,
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿裡巴巴','阿裡小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程式員','黑馬程式員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

5.2.2 避免索引失效

組合索引(name,status,address)

1) 全值匹配

對索引中所有列都指定具體值。

-- 全值匹配
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
ken_len = 3 * N + 2;
-- name varchar(100)  	==302
-- status varchar(1)  	==5
-- address varchar(100) ==302

file

2) 最左首碼法則

如果索引了多列，要遵守最左首碼法則。指的是查詢從索引的最左前列開始，並且不跳過索引中的列。

匹配最左首碼法則，走索引：

explain select * from tb_seller  where name='小米科技';

file
違反最左首碼法則 ， 索引失效：

explain select * from tb_seller  where status='1';
explain select * from tb_seller  where status='1'  and  address='北京市';

file

如果符合最左法則，但是出現跳躍某一列，只有最左列索引生效：

explain select * from tb_seller  where name='小米科技'  and  address='北京市';

file

3) 範圍查詢右邊的列

-- 使用範圍查詢的情況，右邊的列失效 
explain select * from tb_seller  where name='小米科技' and status='1'  and  address='北京市';
explain select * from tb_seller  where name='小米科技' and status>'1'  and  address='北京市';

file

根據前面的兩個欄位name ， status 查詢是走索引的， 但是最後一個條件address 沒有用到索引。

4) 禁止列運算

-- 不要在索引列上進行運算操作， 索引將失效。
explain select * from tb_seller  where substring(name,3,2) ='科技';

file

5) 字元串不加單引號

造成索引失效。

-- 字元串不加單引號,造成索引失效。
explain select * from tb_seller  where name='科技' and status='0';
explain select * from tb_seller  where name='科技' and status=0;

file
由於，在查詢時，沒有對字元串加單引號，MySQL的查詢優化器，會自動的進行類型轉換，造成索引失效。

file

6) 儘量使用覆蓋索引

避免select *

儘量使用覆蓋索引（只訪問索引的查詢（索引列完全包含查詢列）），減少select * 。

-- 儘量使用覆蓋索引
explain select * from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status  from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status,address  from tb_seller  where name='科技' and status='0'  and  address='西安市';

file
如果查詢列，超出索引列，也會降低性能。

explain select status,address ,password  from tb_seller  where name='科技' and status='0'  and  address='西安市';

TIP : 
	
    using index ：使用覆蓋索引的時候就會出現

    using where：在查找使用索引的情況下，需要回表去查詢所需的數據

    using index condition：查找使用了索引，但是需要回表查詢數據

    using index ; using where：查找使用了索引，但是需要的數據都在索引列中能找到，所以不需要回表查詢數據

7) 合理使用or條件

用or分割開的條件， 如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

示例，name欄位是索引列 ， 而createtime不是索引列，中間是or進行連接是不走索引的 ：

explain select * from tb_seller where name='黑馬程式員' or createtime = '2088-01-01 12:00:00';

file

8) 合理使用like查詢

以%開頭的Like模糊查詢，索引失效。

-- 如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。
explain select * from tb_seller  where name like '黑馬程式員%';
explain select * from tb_seller  where name like '%黑馬程式';
explain select * from tb_seller  where name like '%黑馬程式員%';

file

解決方案 ： 通過覆蓋索引來解決

explain select sellerid from tb_seller  where name like '%科技%';
explain select sellerid,name from tb_seller  where name like '%科技%';
explain select sellerid,name,status,address  from tb_seller  where name like '%科技%';

file

9) 合理評估索引執行

如果MySQL評估使用索引比全表更慢，則不使用索引。

-- 如果MySQL評估使用索引比全表更慢，則不使用索引。
create index idx_seller_addr on tb_seller(address);
explain select * from tb_seller  where address='北京市';
explain select * from tb_seller  where address='西安市';

file

10) is NULL和 is NOT NULL

有時索引失效。

-- is  NULL和 is NOT NULL 
explain select * from tb_seller  where name  is null;
explain select * from tb_seller  where name  is not null;

file
解決方案：把null值設置一個預設值

11) in和not in

in 走索引， not in 索引失效。

-- in 走索引， not in 索引失效。
explain select * from tb_seller  where sellerid in('oppo','xiaomi','sina');
explain select * from tb_seller  where sellerid not in  ('oppo','xiaomi','sina');

file

12) 單列索引和複合索引

儘量使用複合索引，而少使用單列索引 。

創建複合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相當於創建了三個索引 ： 
	name
	name + status
	name + status + address

創建單列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

資料庫會選擇一個最優的索引（辨識度最高索引）來使用，並不會使用全部索引 。

5.3 查看索引使用情況

show status like 'Handler_read%';	
show global status like 'Handler_read%';	

mysql> show status like 'Handler_read%';	
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 18      |
| Handler_read_key      | 19      |
| Handler_read_last     | 0       |
| Handler_read_next     | 5072825 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 269     |
+-----------------------+---------+
7 rows in set (0.02 sec)

mysql> 

Handler_read_first：索引中第一條被讀的次數。如果較高，表示伺服器正執行大量全索引掃描（這個值越低越好）。

Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好）。

Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。

Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。

Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵。這個值較高，意味著運行效率低，應該建立索引來補救。

Handler_read_rnd_next：在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。

本文由傳智教育博學谷 - 狂野架構師教研團隊發佈
如果本文對您有幫助，歡迎關註和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力
轉載請註明出處！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • python中5個帶key的內置函數
    day03 java入門02 1.JDK的安裝與卸載 卸載JDK 刪除JDK的安裝目錄 刪除JAVA_HOME 刪除path下關於Java 的目錄 命令行視窗輸入java -version測試 安裝JDK 百度搜索JDK8，找到下載地址 同意協議 下載電腦對應的版本 雙擊安裝JDK 記住安裝路徑 配 ...
  • 【Python爬蟲】論壇發帖輔助腳本
    因為論壇發帖手動插入鏈接太費時間，所以我自己用Python爬蟲寫了一個小腳本，用來自動上傳圖片到圖床，然後返回鏈接，我們只需直接複製鏈接就可以啦 因為不同圖床需要提交的POST表單不一致，所以在這裡我用ImgURL圖床做示範，如果大家有需求，可自行修改代碼。 使用腳本的時候，我們只需把需要上傳的圖片 ...
  • 內部類和外部類變數重名時--如何訪問變數呢？
    java日誌體系 日誌的主流體系 日誌的用途 軟體開發中，我們經常需要去調試程式，做一些信息，狀態的輸出便於我們查詢程式的運行狀況。為了讓我們能夠更加靈活和方便的控制這些調試的信息，所以我們需要專業的日誌技術。java 中尋找bug 會需要重現。調試也就是debug 可以在程式運行中暫停程式運行，可 ...
  • 畢業設計經典案例：Python實現疫苗接種資料庫管理
    那一年過年前，疫情開始爆發，對全國人民的生活和工作造成了嚴重的影響。但憑藉著國家強盛的實力，新冠疫苗也很快的被技術人員研發出來，人們通過接種新冠疫苗來抵禦新冠病毒的危害。本次通過接種新冠疫苗的這個數據統計，來設計“疫苗接種資料庫”，存儲人們接種疫苗的相關信息，並實現“增刪改查”等基本操作以及其他的拓 ...
  • 20220722-Java中this關鍵字
    this關鍵字知識總結 學習資源：B站韓順平老師Java入門教學 ==代碼示例1== public class This01 { public static void main(String[] args) { Dog d1 = new Dog("小黃", 3); d1.info(); System ...
  • 三天學會使用MyBatis框架，絕對乾貨，只實戰，不學究！
    2022最新版Mybatis框架教程來咯！開肝！ 快速開啟你的 MyBatis之旅！ 通過本課程的學習，可以在最短的時間內學會使用持久層框架MyBatis，在該視頻中沒有廢話，都是乾貨，該視頻的講解不是學術性研究，項目中用什麼，這裡就講什麼，如果您現在項目中馬上要使用MyBatis框架，那麼您只需要 ...
  • python利用BUG讓它免費啦~你在不知道就要後悔啦~
    前言 嗨嘍~大家好呀，這裡是魔王吶 VIP音樂下載不了？只能試聽？甚至聽不了？ 那麼今天我就來教你怎麼用Python白嫖VIP音樂~ 所需準備 第三方庫: requests >>> pip install requests 開發環境: 版 本： python 3.8 編輯器：pycharm 2021 ...
  • HttpClient使用簡介說明
    多商戶商城系統，也稱為B2B2C（BBC）平臺電商模式多商家商城系統。可以快速幫助企業搭建類似拼多多/京東/天貓/淘寶的綜合商城。 多商戶商城系統支持商家入駐加盟，同時滿足平臺自營、旗艦店等多種經營方式。平臺可以通過收取商家入駐費，訂單交易服務費，提現手續費，簡訊通道費等多手段方式，實現整體盈利。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類