## 巧妙的使用 Explain
看一條 SQL 語句的性能,可以使用 explain 關鍵字查看語句性能,這里說一下其中的 type 字段的部分含義:
* all,即全表掃描,說明這個 SQL 語句沒有使用到索引,有可能是表本身沒有創建索引,也可能是因為 SQL 語句導致沒有使用索引
* range,說明使用的是有范圍的索引掃描,性能優于 index
* index,這里說明使用了索引,這種情況下,如果 extra 列中的值為 Using index,這種情況是索引覆蓋,索引覆蓋的意思是,我們想要查詢的數據,索引中已經都存在啦,這種情況下就不需要再回表取數據了
* ref,說明條件列使用了索引,但是不是主鍵和 unique,所以這里即使使用了索引,索引值不唯一,有重復的情況
* eq_ref,相對于 ref 來說就是使用的是唯一索引,對于每個索引鍵值,只有唯一的一條匹配記錄
* const/system,單表中最多只有一條匹配行,查詢起來非常迅速,所以這個匹配行中的其他列中的值可以被優化器在當前查詢中當做常量來處理。例如根據主鍵或者唯一索引進行的查詢
* index_merge,說明使用了 MySQL 的索引合并的優化方法,當使用合并索引的時候,就需要檢查一下我們所創建的索引是否為多個單列索引。
## 如何有效的優化索引
### 使用索引的時候,索引必須作為獨立的列出現
作為獨立的列的意思是,索引不能作為表達式的一部分,也不能作為函數的參數出現,否則索引會失效,原因是 MySQL 無法自動解析表達式以及參數,所以也就無法使用索引了,索引失效還有以下幾種情況:
* 條件中使用 is null 或者 is not null 會導致索引失效,原因是索引中不會存儲 null。
* 使用 %like,因為 MySQL 是左匹配,使用模糊查詢時如果以%開頭會導致全表查詢。
* 使用多列索引的時候,如果索引順序不是建立索引的順序,或者跳過第一個索引直接使用后面的索引,也會導致索引失敗,原因依然是 MySQL 是左匹配。
* 條件中包含or時,只有所有列都是單獨索引時才會使用索引。
## 如何創建有效的索引
### 如果需要索引很長的字符串,此時需要考慮前綴索引
前綴索引即選擇所需字符串的一部分前綴作為索引,這時候,需要引入一個概念叫做索引選擇性,索引選擇性是指不重復的索引值與數據表的記錄總數的比值,可以看出索引選擇性越高則查詢效率越高,當索引選擇性為1時,效率是最高的,但是在這種場景下,很明顯索引選擇性為1的話我們會付出比較高的代價,索引會很大,這時候我們就需要選擇字符串的一部分前綴作為索引,通常情況下一列的前綴作為索引選擇性也是很高的。
#### 如何選擇前綴
計算該列完整列的選擇性,使得前綴選擇性接近于完整列的選擇性。
### 使用多列索引
盡量不要為多列上創建單列索引,因為這樣的情況下最多只能使用一星索引,這樣的話,不如去創建一個全覆蓋索引,在多列上創建單列索引大部分情況下并不能提高 MySQL 的查詢性能,MySQL 5.0 中引入了合并索引,在一定程度上可以表內多個單列索引來定位指定的結果,但是 5.0 以前的版本,如果 where 中的多個條件是基于多個單列索引,那么 MySQL 是無法使用這些索引的,這種情況下,還不如使用 union
### 選擇合適的索引列順序
經驗是將選擇性最高的列放到索引最前列,可以在查詢的時候過濾出更少的結果集
但這樣并不總是最好的,如果考慮到 group by 或者 order by 等情況,再比如考慮到一些特別場景下的 guest 賬號等數據情況,上面的經驗法則可能就不是最適用的
### 覆蓋索引
所謂覆蓋索引就是指索引中包含了查詢中的所有字段,這種情況下就不需要再進行回表查詢了
MySQL 中只能使用 B-Tree 索引做覆蓋索引,因為哈希索引等都不存儲索引的列的值,覆蓋索引對于 MyISAM 和 InnoDB 都非常有效,可以減少系統調用和數據拷貝等時間。
Tips:減少 select * 操作
### 使用索引掃描來做排序
MySQL 生成有序的結果有兩種方法:通過排序操作,或者按照索引順序掃描;使用排序操作需要占用大量的 CPU 和內存資源,而使用 index 性能是很好的,所以,當我們查詢有序結果時,盡量使用索引順序掃描來生成有序結果集。
#### 怎樣保證使用索引順序掃描:
* 索引列順序和 ORDER BY 順序一致
* 所有列的排序方向一致
* 如果關聯多表,那么只有當 ORDER BY 子句引用的字段全部為第一張表時,才能使用索引做排序,限制依然是需要滿足索引的最左前綴要求。
### 壓縮索引
* 上一篇將索引結構的文章提到了,MyISAM 中使用了前綴壓縮技術,會減少索引的大小,可以在內存中存儲更多的索引,這部分優化默認也是只針對字符串的,但是可以自定義對整數做壓縮。
* 這個優化在一定情況下性能比較好,但是對于某些情況可能會導致更慢,因為前綴壓縮決定了每個關鍵字都必須依賴于前面的值,所以無法使用二分查找等,只能順序掃描,所以如果查找的是逆序那么性能可能不佳。
### 減少重復、冗余以及未使用的索引
* MySQL 的唯一限制和主鍵限制都是通過索引實現的,所以不需要在同一列上增加主鍵、唯一限制再創建索引,這樣是重復索引
* 再舉個例子,如果已經創建了索引(A,B),那么再創建索引(A)的話,就屬于重復索引,因為 MySQL 索引是最左前綴,所以索引(A,B)本身就可以使用索引(A),但是創建索引(B)的話不屬于重復索引
* 盡量減少新增索引,而應該擴展已有的索引,因為新增索引可能會導致 INSERT、UPDATE、DELETE 等操作更慢
* 可以考慮刪除沒有使用到的索引,定位未使用的索引,有兩個辦法,在 Percona Server 或者 MariaDB 中打開 userstates 服務器變量,然后等服務器運行一段時間后,通過查詢 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查詢到每個索引的使用頻率
## 索引和鎖
之前講索引結構的時候說過,InnoDB 支持行鎖和表鎖,默認使用行鎖,而 MyISAM 使用的是表鎖,所以使用索引可以讓查詢鎖定更少的行,這樣也會提升查詢的性能,如果查詢中鎖定了1000行,但實際只是用了100行,那么在 5.1 之前都需要提交事務之后才能釋放這些鎖,5.1 之后可以在服務器端過濾掉行之后就釋放鎖,不過依然會導致一些鎖沖突。
## 減少索引和數據碎片
首先我們需要了解一下為什么會產生碎片,比如 InnoDB 刪除數據時,這一段空間就會被留空,如果一段時間內大量刪除數據,就會導致留空的空間比實際的存儲空間還要大,這時候如果進行新的插入操作時,MySQL 會嘗試重新使用這部分空間,但是依然無法徹底占用,這樣就會產生碎片。
產生碎片帶來的后果當然是,降低查詢性能,因為這種情況會導致隨機磁盤訪問。
可以通過 OPTIMIZE TABLE 或者重新導入數據表來整理數據。
## 總結
數據庫的索引這部分要講的話實在是太多了~絕大部分情況都需要結合實際情況,如果我們可以更多的了解數據庫索引本身的一些原理,那么對于優化會有一些幫助~巧妙地使用 explain 分析自己所寫的 SQL 語句,可以更好的進行優化。
- 前言
- 讀者須知
- 第一章 Linux
- HTTP
- 簡介
- 狀態碼
- 特點
- URL
- Request
- Response
- 請求方式
- 工作原理
- 生命周期
- GET和POST區別
- 組成
- 端口
- 命令
- 常用命令
- chmod命令詳解
- ubuntu apt-get命令
- 用戶和用戶組
- Nginx
- 四個基本功能
- 進程
- 進程管理[ps命令]
- 進程管理[top命令]
- 進程管理[kill命令]
- 進程管理[進程優先級]
- 進程管理[netstat命令]
- 定時任務
- crontab
- 實現每秒執行
- >/dev/null 2>&1說明
- 文件管理
- 工作管理
- 資源管理
- 第二章 NGINX
- 介紹
- 入門
- 特性
- 安裝啟動
- 基礎必會
- 常用功能
- 反向代理
- 負載均衡
- 正向代理
- HTTP服務器
- 動靜分離
- 技能點匯總
- 顯示亂碼
- 打開目錄瀏覽功能
- 錯誤碼原因和解決方案
- location用法
- 常用正則
- rewrite
- 全局變量
- if語句塊
- https
- php后端處理(fast-cgi)
- flag標志位
- 過期功能
- gzip壓縮
- 會話保持時間
- 配置nginx worker進程最大打開文件數
- sendfile
- 單個工作進程的最大連接數
- 選擇事件驅動模型
- 隱藏ngxin版本號
- 網絡連接的優化
- 緩存原理及機制
- 限流
- 日志配置
- 灰度發布
- 配置一鍵生成
- 第三章 MySQL
- 入門
- 簡介
- 術語
- 特點
- 三范式
- 8.0 新特性
- 數據類型
- 數據類型詳解
- 常用函數
- 命令速查
- MyISAM與InnoDB區別
- 服務器構成
- 事務
- 本質
- 特性
- 分類
- 隔離級別
- PHP中使用事務實例
- MVCC
- 問題和解決
- 調優原則
- 分布式事務
- 索引
- 簡介
- 索引的分類
- 創建索引
- 刪除索引
- 哈希索引
- btree索引和hash索引的區別
- 單列索引和多列索引
- 索引優化
- 查看SQL語句對索引的使用情況
- 鎖
- 技能點
- 開發規范
- 導入導出數據庫
- blob和text的區別
- char與varchar類型區別
- SQL查詢語句優化
- 事務隔離和鎖操作需要在語言級別來做嗎
- 58到家數據庫30條軍規解讀
- 數據遷移
- SKU數據庫設計
- RBAC數據庫設計
- 第四章 Redis
- 入門
- 簡介
- 應用場景
- 安裝啟動
- 生命周期
- 事務
- 配置項
- 緩存
- 數據持久化
- 安全
- 數據類型
- string
- hash
- list
- set
- zset
- php代碼實戰
- 字符串緩存實戰
- 隊列實戰
- 發布訂閱實戰
- 計數器實戰
- 排行榜實戰
- 字符串悲觀鎖實戰
- 事務的樂觀鎖實戰
- 高級應用
- 分片機制
- 主從復制
- 緩存問題
- 解決 Redis 并發競爭 Key 問題
- 淘汰策略
- 第五章 PHP
- composer
- 什么是composer
- composer常用概念解析
- 使用composer的正確姿勢
- 消息隊列
- 為何使用消息隊列
- Beanstalkd
- PSR規范
- PSR-0
- PSR-1
- PSR-2
- PSR-3
- PSR-4
- OOP基礎
- 面向對象概念
- 類和對象
- 類
- 操作對象成員
- this使用
- 構造方法和析構方法
- 封裝
- __set(),__get(),__isset(),__unset()四個方法的應用
- 繼承
- 重載新的方法(parent::)
- 訪問類型(public,protected,private)
- final關鍵字的應用
- static和const關鍵字的使用(self::)
- static關鍵字
- __toString()方法
- 克隆對象__clone()方法
- __call()處理調用錯誤
- 抽象方法和抽象類(abstract)
- 接口(interface)
- 多態
- 把對象串行化serialize()方法,__sleep()方法,__wakeup()方法
- 自動加載類 __autoload()函數
- OOP進階
- 語法糖
- 異常處理
- 后期靜態綁定
- 后期靜態綁定在框架的運用
- 代碼優化思路
- Closure(閉包)
- 巧用PHP內置方法
- 數組操作的奇技淫巧
- 設計模式
- 單例模式(Singleton Pattern)
- 工廠模式(Factor Pattern)
- 建造者模式(Builder Pattern)
- 原型模式(Prototype Pattern)
- 適配器模式(Adapter Pattern)
- 裝飾器模式(Decorator Pattern)
- 代理模式(Proxy Pattern)
- 外觀模式(Facade Pattern)
- 橋接模式(Bridge Pattern)
- 組合模式(Composite Pattern)
- 享元模式 (Flyweight Pattern)
- 策略模式 ( Strategy Pattern )
- 模板模式 (Template Pattern)
- 觀察者模式 (observer Pattern)
- 迭代模式(Iterator Pattern)
- 責任鏈模式(Chain of Responsibility Pattern)
- 命令模式 (Command Pattern)
- 備忘錄模式(Memento Pattern)
- 狀態模式 (State Pattern)
- 訪問者模式(Visitor Pattern)
- 中介者模式(Mediator Pattern)
- 解釋器模式(Interpreter Pattern)
- 數據映射模式(Data Mapper Pattern)
- 注冊樹模式(Registry Pattern)
- 空對象模式(Null Object Pattern)
- 搜索引擎
- Elasticsearch
- 安裝
- 入門
- 實踐
- 集群
- 查詢
- API
- 接口調用
- cURL
- Guzzle
- RPC
- yar
- session
- 概念
- 客戶端實現形式
- cookie與session的區別
- Cookies的安全性
- JWT
- 組成
- 入門
- 應用
- 知識點
- 常見
- $_SERVER
- php的引用
- 第六章 技術棧擴展
- 使用第三方靜態資源服務
- 七牛對象存儲實戰
- 七牛對象存儲之客戶端上傳
- aliyunOSS服務端文件上傳
- aliyunOSS客戶端文件上傳
- 第三方支付
- 微信支付
- 支付寶支付
- SEO排名影響因素
- PHP架構師之路
- CTO職能
- web宏觀分析
- 常見的企業軟件系統
- 負載的優化思路
- 從容應對負載并發的前期準備
- 第七章 網絡安全
- XSS
- CSRF
- DDoS
- SQL注入
- 停用js
- 文件上傳
- 點擊劫持
- APT
- 會話劫持
- 第八章 運維
- devops
- devops簡介
- 常用工具
- 搭建運行環境
- Centos7 lnmp環境搭建
- ubuntu lnmp環境搭建
- Apache多站點配置
- docker
- 輕松使用和理解docker
- lnamp產品級環境搭建
- lnamp產品級環境搭建【第二版】
- 基于 Docker 容器的沙盒化評測系統
- vagrant
- vagrant入門
- vagrant之Vagrantfile
- vagrant之集成jenkins
- homestead
- gitlab
- gitlab簡介
- webhook
- ssh堡壘機
- 第九章 測試
- 壓力測試
- 單元測試
- 第十章 團隊協作
- 軟件開發模式
- 邊做邊改模型
- 瀑布模型
- 迭代模型
- 快速原型模型
- 增量模型
- 螺旋模型
- 敏捷軟件開發
- 演化模型
- 噴泉模型
- 智能模型
- 混合模型
- 模型對比
- TDD
- git
- git_入門
- git_使用
- git_進階
- git workflow
- git_高級
- git_小技巧
- okr工作法
- API接口文檔管理系統
- 敏捷協作工具
- 第十一章 技術燈塔
- github項目
- 社區好貨
- 紙質書
- 第十二章 代碼之外
- 面試官的角度看面試
- 程序員的壯年思考