作者:Sky.Jian版权声明 链接:http://isky000.com/database/mysql-performance-tuning-index | del.icio.us | Twitter it | Hosted On DreamHost 可以通过我的折扣码 iMySQLer 获得优惠折扣 MySQL 数据库性能优化之表结构,这是 MySQL数据库性能优化专题 系
作者:Sky.Jian
版权声明
链接:http://isky000.com/database/mysql-performance-tuning-index |
del.icio.us |
Twitter
it |
Hosted On
DreamHost 可以通过我的折扣码 iMySQLer 获得优惠折扣
MySQL 数据库性能优化之表结构,这是 MySQL数据库性能优化专题 系列的第三篇文章:MySQL 数据库性能优化之索引优化
大家都知道索引对于数据访问的性能有非常关键的作用,都知道索引可以提高数据访问效率。
为什么索引能提高数据访问性能?他会不会有“副作用”?是不是索引创建越多,性能就越好?到底该如何设计索引,才能最大限度的发挥其效能?
这篇文章主要是带着上面这几个问题来做一个简要的分析,同时排除了业务场景所带来的特殊性,请不要纠结业务场景的影响。
-
索引为什么能提高数据访问性能?
很多人只知道索引能够提高数据库的性能,但并不是特别了解其原理,其实我们可以用一个生活中的示例来理解。我们让一位不太懂计算机的朋友去图书馆确认一本叫做《MySQL性能调优与架构设计》的书是否在藏,这样对他说:“请帮我借一本计算机类的数据库书籍,是属于 MySQL 数据库范畴的,叫做《MySQL性能调优与架构设计》”。朋友会根据所属类别,前往存放“计算机”书籍区域的书架,然后再寻找“数据库”类存放位置,再找到一堆讲述“MySQL”的书籍,最后可能发现目标在藏(也可能已经借出不在书架上)。
在这个过程中: “计算机”->“数据库”->“MySQL”->“在藏”->《MySQL性能调优与架构设计》其实就是一个“根据索引查找数据”的典型案例,“计算机”->“数据库”->“MySQL”->“在藏” 就是朋友查找书籍的索引。
假设没有这个索引,那查找这本书的过程会变成怎样呢?朋友只能从图书馆入口一个书架一个书架的“遍历”,直到找到《MySQL性能调优与架构设计》这本书为止。如果幸运,可能在第一个书架就找到。但如果不幸呢,那就惨了,可能要将整个图书馆所有的书架都找一遍才能找到我们想要的这本书。
下面我们通过上面这个生活中的小示例,来分析一下索引,看看能的出哪些结论?
-
索引有哪些“副作用”?
- 图书的变更(增,删,改)都需要修订索引,索引存在额外的维护成本
- 查找翻阅索引系统需要消耗时间,索引存在额外的访问成本
- 这个索引系统需要一个地方来存放,索引存在额外的空间成本
-
索引是不是越多越好?
- 如果我们的这个图书馆只是一个进出中转站,里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”,那我们的索引就只会不断的修改,而很少会被用来查找图书
所以,对于类似于这样的存在非常大更新量的数据,索引的维护成本会非常高,如果其检索需求很少,而且对检索效率并没有非常高的要求的时候,我们并不建议创建索引,或者是尽量减少索引。 - 如果我们的书籍量少到只有几本或者就只有一个书架,索引并不会带来什么作用,甚至可能还会浪费一些查找索引所花费的时间。
所以,对于数据量极小到通过索引检索还不如直接遍历来得快的数据,也并不适合使用索引。 - 如果我们的图书馆只有一个10平方的面积,现在连放书架都已经非常拥挤,而且馆藏还在不断增加,我们还能考虑创建索引吗?
所以,当我们连存储基础数据的空间都捉襟见肘的时候,我们也应该尽量减少低效或者是去除索引。
- 如果我们的这个图书馆只是一个进出中转站,里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”,那我们的索引就只会不断的修改,而很少会被用来查找图书
-
索引该如何设计才高效?
- 如果我们仅仅只是这样告诉对方的:“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”,结果又会如何呢?朋友只能一个大类区域一个大类区域的去寻找“数据库”类别,然后再找到 “MySQL”范畴,再看到我们所需是否在藏。由于我们少说了一个“计算机类”,朋友就必须到每一个大类去寻找。
所以,我们应该尽量让查找条件尽可能多的在索引中,尽可能通过索引完成所有过滤,回表只是取出额外的数据字段。 - 如果我们是这样说的:“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书,叫做《MySQL性能调优与架构设计》,看是否在藏”。如果这位朋友并不知道计算机是一个大类,也不知道数据库属于计算机大类,那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中,然后从包含 “MySQL” 书籍中再看有哪些是“数据库”范畴的(有可能部分是讲述PHP或者其他开发语言的),然后再排除非计算机类的(虽然可能并没有必要),然后才能确认。
所以,字段的顺序对组合索引效率有至关重要的作用,过滤效果越好的字段需要更靠前。 - 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index
Scan Or Full Table Scan))
所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。 - 如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类,抑或者图书馆的索引系统中这两个类别属性并没有关联关系,又会怎样呢?也就是说,朋友得到的是2个独立的索引,一个是告知“计算机”这个大类所在的区域,一个是“数据库”这个小类所在的区域(很可能是多个区域),那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找,那这样的效率实际过程中也会比较低下。
所以,在实际使用过程中,一次数据访问一般只能利用到1个索引,这一点在索引创建过程中一定要注意,不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。
- 如果我们仅仅只是这样告诉对方的:“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”,结果又会如何呢?朋友只能一个大类区域一个大类区域的去寻找“数据库”类别,然后再找到 “MySQL”范畴,再看到我们所需是否在藏。由于我们少说了一个“计算机类”,朋友就必须到每一个大类去寻找。
看完这些分析,我想大家应该了解索引优化的一些基本思路了吧

MySQL使用的是GPL許可證。 1)GPL許可證允許自由使用、修改和分發MySQL,但修改後的分發需遵循GPL。 2)商業許可證可避免公開修改,適合需要保密的商業應用。

選擇InnoDB而不是MyISAM的情況包括:1)需要事務支持,2)高並發環境,3)需要高數據一致性;反之,選擇MyISAM的情況包括:1)主要是讀操作,2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用,如電商平台,而MyISAM適合讀密集型且無需事務的應用,如博客系統。

在MySQL中,外鍵的作用是建立表與表之間的關係,確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性,使用時需注意性能優化和避免常見錯誤。

MySQL中有四種主要的索引類型:B-Tree索引、哈希索引、全文索引和空間索引。 1.B-Tree索引適用於範圍查詢、排序和分組,適合在employees表的name列上創建。 2.哈希索引適用於等值查詢,適合在MEMORY存儲引擎的hash_table表的id列上創建。 3.全文索引用於文本搜索,適合在articles表的content列上創建。 4.空間索引用於地理空間查詢,適合在locations表的geom列上創建。

toCreateAnIndexinMysql,usethecReateIndexStatement.1)forasingLecolumn,使用“ createIndexIdx_lastNameEnemployees(lastName); 2)foracompositeIndex,使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees(lastName,firstName,firstName);” 3)forauniqe instex,creationexexexexex,

MySQL和SQLite的主要區別在於設計理念和使用場景:1.MySQL適用於大型應用和企業級解決方案,支持高性能和高並發;2.SQLite適合移動應用和桌面軟件,輕量級且易於嵌入。

MySQL中的索引是數據庫表中一列或多列的有序結構,用於加速數據檢索。 1)索引通過減少掃描數據量提升查詢速度。 2)B-Tree索引利用平衡樹結構,適合範圍查詢和排序。 3)創建索引使用CREATEINDEX語句,如CREATEINDEXidx_customer_idONorders(customer_id)。 4)複合索引可優化多列查詢,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5)使用EXPLAIN分析查詢計劃,避

在MySQL中使用事務可以確保數據一致性。 1)通過STARTTRANSACTION開始事務,執行SQL操作後用COMMIT提交或ROLLBACK回滾。 2)使用SAVEPOINT可以設置保存點,允許部分回滾。 3)性能優化建議包括縮短事務時間、避免大規模查詢和合理使用隔離級別。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。

mPDF
mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

SublimeText3 Linux新版
SublimeText3 Linux最新版

記事本++7.3.1
好用且免費的程式碼編輯器

DVWA
Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中