資料庫學習之八：mysql 基礎優化-索引管理

来源:https://www.cnblogs.com/cuiyongchao007/archive/2020/05/08/12853069.html
-Advertisement-
Play Games

八、mysql 基礎優化 索引管理 1、課程大綱 索引介紹 索引管理 2、執行計劃獲取及分析 mysql mysql資料庫中索引的類型介紹 BTREE:B+樹索引 （主要） HASH:HASH索引 FULLTEXT:全文索引 RTREE:R樹索引 索引管理： 索引建立的在表的列上（欄位）的。 在wh ...


八、mysql 基礎優化-索引管理

1、課程大綱

索引介紹

索引管理

2、執行計劃獲取及分析

mysql資料庫中索引的類型介紹
BTREE:B+樹索引 （主要）
HASH:HASH索引
FULLTEXT:全文索引
RTREE:R樹索引
------
索引管理：
索引建立的在表的列上（欄位）的。
在where後面的列建立索引才會加快查詢速度。
索引分類：
- 主鍵索引
- 普通索引****
- 唯一索引
添加索引
alter table test add index index_name(name);
create index index_name on test(name);
查詢表是否有索引信息：
DESC stu；看他的key列值
mysql> explain select * from stu;
mysql> explain select * from stu where stu_name='zhangsan';
查看到的type不同。
----------------------------------------------------------

索引及執行計劃
索引基本管理：

創建和刪除：
alter table stu add index idx_name(stu_name);
alter table stu drop index idx_name;
或者

create index inx_name on stu(stu_name);
drop index inx_name on stu;

查詢索引設置
desc stu;

主鍵索引： 唯一、非空
走主鍵索引的查詢效率是最高的，我們儘量每個表有一個主鍵，並且將來查詢的時候計量以主鍵為條件查詢

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `test1` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
增加自增主鍵
alter table test1 change id id int(4) primary key not null auto_increment;

首碼索引：
create index index_name on stu(stu_id(8));

聯合索引：
where a女生 and b身高165 and c身材好

index(a,b,c)

特點：首碼生效特性。

a,ab,abc，ac   可以走索引。
b bc c 不走索引。

原則：把最常用來作為條件查詢的列放在前面。

走索引：
select  * from people where a='nv' and b>=165 and tizhong<=120;
select  * from people where a='nv' and b>=165;
select  * from people where a='nv';
select  * from people where a='nv' and tizhong<=120;

alter table stu add index minx(gender,age);
唯一性索引：
create unique index index_name on test(name);

3、explain 調取語句的執行計劃

主要是判斷語句是否走索引

explain select stu_name,gender,age from stu where gender='F' and age <20;

mysql> explain select name,gender,age from test where gender='F' and age <20;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | inx_test      | inx_test | 7       | NULL |    1 | Using index condition |

type : 表示MySQL在表中找到所需行的方式，又稱“訪問類型”，

常見類型如下:

ALL,index, range, ref, eq_ref, const, system, NULL

從左到右，性能從最差到最好

ALL：
Full Table Scan， MySQL將遍歷全表以找到匹配的行

如果顯示ALL，說明：
查詢沒有走索引：
	1、語句本身的問題
	2、索引的問題，沒建立索引

index：Full Index Scan，index與ALL區別為index類型只遍歷索引樹
例子：
explain select count(*) from stu ;

range:索引範圍掃描，對索引的掃描開始於某一點，返回匹配值域的行。
顯而易見的索引範圍掃描是帶有between或者where子句裡帶有<,>查詢。
where 條件中有範圍查詢或模糊查詢時
>  < >= <=   between  and   in ()   or
like 'xx%'

當mysql使用索引去查找一系列值時，例如IN()和OR列表，也會顯示range（範圍掃描）,當然性能上面是有差異的。

ref：使用非唯一索引掃描或者唯一索引的首碼掃描，返回匹配某個單獨值的記錄行

where stu_name='xiaoming'

explain select *  from stu  where stu_name='aa';

eq_ref：類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，
就是多表連接中使用primary key或者 unique key作為關聯條件	

join條件使用的是primary key或者 unique key

const、system：當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些類型訪問。
如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量

 explain  select * from city where id=1;

NULL：MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，
	  例如從一個索引列里選取最小值可以通過單獨索引查找完成。

mysql> explain select name,population from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
	  
Extra:

Using temporary
Using filesort
Using join buffer

排序   order by  ,group by ,distinct,排序條件上沒有索引
explain select * from city where countrycode='CHN' order by population;
在join 的條件列上沒有建立索引

4、資料庫索引的設計原則：

一、資料庫索引的設計原則：

	為了使索引的使用效率更高，在創建索引時，必須考慮在哪些欄位上創建索引和創建什麼類型的索引。
那麼索引設計原則又是怎樣的?(儘量使用主鍵索引和唯一性索引。)

1．選擇唯一性索引
	唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。
例如，學生表中學號是具有唯一性的欄位。為該欄位建立唯一性索引可以很快的確定某個學生的信息。
如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

主鍵索引和唯一鍵索引，在查詢中使用是效率最高的。

2．為經常需要排序、分組和聯合操作的欄位建立索引
經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間。
如果為其建立索引，可以有效地避免排序操作。

3．為常作為查詢條件的欄位建立索引
如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。因此，
為這樣的欄位建立索引，可以提高整個表的查詢速度。

select count(DISTINCT population ) from city;
select count(*) from city;

4．儘量使用首碼來索引
如果索引欄位的值很長，最好使用值的首碼來索引。例如，TEXT和BLOG類型的欄位，進行全文檢索
會很浪費時間。如果只檢索欄位的前面的若幹個字元，這樣可以提高檢索速度。

------------------------以上的是重點關註的，以下是能保證則保證的--------------------

5．限制索引的數目
索引的數目不是越多越好。每個索引都需要占用磁碟空間，索引越多，需要的磁碟空間就越大。
修改表時，對索引的重構和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

6．儘量使用數據量少的索引
如果索引的值很長，那麼查詢的速度會受到影響。例如，對一個CHAR（100）類型的欄位進行全文
檢索需要的時間肯定要比對CHAR（10）類型的欄位需要的時間要多。

7．刪除不再使用或者很少使用的索引
表中的數據被大量更新，或者數據的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理
員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

5、索引的開發規範

不走索引的情況：
重點關註：
1) 沒有查詢條件，或者查詢條件沒有建立索引 

select * from tab;   全表掃描。
select  * from tab where 1=1;

在業務資料庫中，特別是數據量比較大的表。
是沒有全表掃描這種需求。

1、對用戶查看是非常痛苦的。
2、對伺服器來講毀滅性的。

（1）select * from tab;

SQL改寫成以下語句：
selec  * from tab  order by  price  limit 10      需要在price列上建立索引

（2）
select  * from  tab where name='zhangsan'          name列沒有索引

改：
	1、換成有索引的列作為查詢條件
	2、將name列建立索引
	
2) 查詢結果集是原表中的大部分數據，應該是30％以上。 

查詢的結果集，超過了總數行數30%，優化器覺得就沒有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有索引

select * from tab  where id>500000;

如果業務允許，可以使用limit控制。

怎麼改寫 ？
結合業務判斷，有沒有更好的方式。如果沒有更好的改寫方案
儘量不要在mysql存放這個數據了。放到redis裡面。

3) 索引本身失效，統計數據不真實 

索引有自我維護的能力。
對於表內容變化比較頻繁的情況下，有可能會出現索引失效。

4) 查詢條件使用函數在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等) 
例子： 
錯誤的例子：select * from test where id-1=9; 
正確的例子：select * from test where id=10;

5)隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤. 
由於表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number類型以where條件傳給資料庫,
這樣會導致索引失效. 錯誤的例子：select * from test where tu_mdn=13333333333; 
正確的例子：select * from test where tu_mdn='13333333333'; 
------------------------
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| telnum | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> 

------------------------

6) 
<>  ，not in 不走索引

EXPLAIN SELECT * FROM teltab WHERE telnum   <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');
------------
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tab where telnum <> '1555555';
-----
單獨的>,<,in 有可能走，也有可能不走，和結果集有關，儘量結合業務添加limit
or或in  儘量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum   IN ('110','119');
改寫成：

EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

-----------------------------------
7)   like "%_" 百分號在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'   走range索引掃描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引

%linux%類的搜索需求，可以使用elasticsearch

%linux培訓%

8) 單獨引用複合索引里非第一位置的索引列. 
列子：
複合索引：

DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
走索引的情況測試：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';    ----->部分走索引
不走索引的：
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex='m';

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

-Advertisement-
Play Games
更多相關文章
  • 企業級虛擬化實戰之KVM——虛擬機遷移
    遷移概述 系統的遷移是指把源主機上的 操作系統 和 應用程式 移動到目的主機，並且能夠在目的主機上正常 運行 在沒有虛擬機的時代，物理機之間的遷移依靠的是系統備份和恢復技術。在源主機上實時備份操作系統和應用程式的狀態，然後把存儲介質連接到目標主機上，最後在目標主機上恢復系統。隨著虛擬機技術的發展，系 ...
  • 快速升級Zabbix 5.0 版本
    Zabbix 5.0 增加了很多新功能，如：垂直菜單、隱藏菜單、用戶界面中的測試項目、限制代理檢查、查找並替換預處理步驟 ES7支持等等...快來部署體驗一把嘗鮮體驗 Zabbix 5.0 吧 升級須知 升級要求 PHP版本 PHP版本已從最低的5.4.0 升級到 7.2.0 資料庫版本 MySQL ...
  • Linux環境變數
    在bash shell中，環境變數分為兩類： 全局變數 局部變數 全局環境變數 全局環境變數對於shell會話和所有生成的子shell都是可見的。例如 : 局部環境變數 局部環境變數只能在定義它們的進程中可見。Linux也定義了標準的局部環境變數。 使用 命令可以顯示該進程的所有環境變數，包括 局部 ...
  • MySQL資料庫備份與恢復
    [TOC] 前言 1.備份數據的意義 運維工作的核心簡單概括起來就是兩件事：第一個是保護公司的數據，第二個是讓網站能夠7 24小時提供服務。 雖然這兩件事情都很重要，但是相比較而言，丟失一部分數據和讓網站7 24小時提供服務，哪個更重要呢？ 對於絕大多數企業來講，失去數據就相當於失去商機，失去產品， ...
  • 第五天MYSQL
    2020 5/9 十九、分頁查詢 應用場景：當要顯示的數據，一頁顯示不全，需要分頁提交sql請求 語法：(執行順序已標出) SELECT 查詢列表 ⑦每執行一步都會生成一個虛擬的結果集 FROM 表名 ① [join type join 表2 ② on 連接條件 ③ where 篩選條件 ④ gro ...
  • 資料庫學習之十一：mysql 備份恢復
    十一、mysql 備份恢復 課程大綱 運維工作的核心簡單概括就兩件事： 第一個是保護公司的數據。 第二個是讓網站能7 24小時提供服務(用戶體驗)。 1、備份的類型 冷備份：關閉數據、停止業務 溫備份：枷鎖備份 熱備份：線上備份，不會影響業務。 2、備份方式 邏輯備份： 基於sql語句的備份： ①m ...
  • 資料庫學習之十：mysql日誌管理
    十、mysql日誌管理 課程大綱 1、日誌的類型簡介 mysql show variables like '%log_error%'；在配置文件中指定錯誤日誌位置。 mysql show variables like '%gen%'; 一般日誌查詢 二進位日誌，記錄修改記錄。 | 日誌文件 | 選項 ...
  • 資料庫學習之九：mysql 存儲引擎
    九、mysql 存儲引擎 1、課程大綱 2、mysql存儲引擎介紹 3、mysql存儲引擎種類 4、資料庫的存儲引擎 存儲引擎查詢 存儲引擎的配置： 配置存儲引擎： 5、innodb體繫結構： 表空間： 共用表空間：主要存放系統元數據 獨立表空間：主要存放用戶數據 邏輯概念，數據存儲的概念 表空間數 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類