索引是滿足某種特定查找算法的數(shù)據(jù)結(jié)構(gòu)，而這些數(shù)據(jù)結(jié)構(gòu)會以某種方式指向數(shù)據(jù)，從而實現(xiàn)高效查找數(shù)據(jù)。
創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站設(shè)計制作、成都做網(wǎng)站與策劃設(shè)計,洪湖網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:洪湖等地區(qū)。洪湖做網(wǎng)站價格咨詢:18982081108
具體來說 MySQL 中的索引，不同的數(shù)據(jù)引擎實現(xiàn)有所不同，但目前主流的數(shù)據(jù)庫引擎的索引都是 B+ 樹實現(xiàn)的，B+ 樹的搜索效率，可以到達二分法的性能，找到數(shù)據(jù)區(qū)域之后就找到了完整的數(shù)據(jù)結(jié)構(gòu)了，所有索引的性能也是更好的。
在涉及LBS的服務(wù)開發(fā)過程中，經(jīng)常需要存儲地理空間的位置并進行一定計算（附近商家等需求），本文主要介紹mysql對于LBS的支持。
Mysql的空間擴展主要提供一下幾個方面的功能：
其中前兩點對InnoDB，MyISAM，NDB，ARCHIVE等mysql存儲引擎都支持，第三點只有對InnoDB和MyISAM的支持，由于InnoDB的支持行鎖以及事務(wù)的特性，現(xiàn)在基本上已經(jīng)是默認存儲引擎了，所以本文以下內(nèi)容都默認使用InnoDB。
創(chuàng)建空間列以及空間索引的語句如下：
Mysql的空間數(shù)據(jù)類型與OpenGIS的數(shù)據(jù)類型相對應(yīng)。
Mysql的空間數(shù)據(jù)有不同表示格式，其中咱能看懂的也就第一種
因為上文提到了SRID，這里說下什么是SRID，SR是指Spatial Reference，也就是我們常說的空間參考系，mysql支持卡迪爾坐標系和地理坐標系，其中地理坐標系又有好多種，下面說幾種常用的空間參考系
Mysql的所有空間坐標系都存在表 mysql.st_spatial_reference_system 中，這個表是隱藏的，看不見的，但是你可以通過 infomation_shcema.st_spatial_reference_system 中查看參考系的信息，這個表就是 mysql.st_spatial_reference_system 的一個視圖的實現(xiàn)。
mysql的空間索引的數(shù)據(jù)結(jié)構(gòu)是R樹，R樹實際上就是多維的B樹，B樹的數(shù)據(jù)結(jié)構(gòu)在我的另一篇博客中有介紹，這里就不展開了，說幾點在應(yīng)用的時候需要注意的。
最后轉(zhuǎn)一篇博文
常見的索引類型：哈希表、有序數(shù)組、搜索樹。
mysql之普通索引和唯一索引。
執(zhí)行查詢的語句是 select id from T where k=5
這個查詢語句在索引樹上查找的過程，先是通過 B+ 樹從樹根開始，按層搜索到葉子節(jié)點，也就是圖中右下角的這個數(shù)據(jù)頁，然后可以認為數(shù)據(jù)頁內(nèi)部通過二分法來定位記錄。
InnoDB的索引組織結(jié)構(gòu)：
change buffer:持久化的數(shù)據(jù)。InnoDB將更新操作緩存在 change buffer中,也就是說,change buffer 在內(nèi)存中有拷貝，也會被寫入到磁盤，主要節(jié)省的則是隨機讀磁盤的IO消耗。
change buffer 只限于用在普通索引的場景下，而不適用于唯一索引.
merge:將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程。
merge執(zhí)行流程：
1、從磁盤讀入數(shù)據(jù)頁到內(nèi)存
2、從change buffer里找出這個數(shù)據(jù)頁的change buffer記錄，依次應(yīng)用，得到新版數(shù)據(jù)頁
3、寫redo log,這個redo log包含了數(shù)據(jù)的變更和change buffer的變更。
change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無限增大。change buffer 的大小，可以通過參數(shù) innodb_change_buffer_max_size=50 表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
如果要在這張表中插入一個新記錄 (4,400) 的話，InnoDB 的處理流程是怎樣的。
第一種情況是，這個記錄要更新的目標頁在內(nèi)存中
這時，InnoDB 的處理流程如下：
第二種情況是，這個記錄要更新的目標頁不在內(nèi)存中
這時，InnoDB 的處理流程如下：
mysql insert into t(id,k) values(id1,k1),(id2,k2); 當前 k 索引樹的狀態(tài)，查找到位置后，k1 所在的數(shù)據(jù)頁在內(nèi)存 (InnoDB buffer pool) 中，k2 所在的數(shù)據(jù)頁不在內(nèi)存中。
分析這條更新語句，你會發(fā)現(xiàn)它涉及了四個部分：內(nèi)存、redo log（ib_log_fileX）、 數(shù)據(jù)表空間（t.ibd）、系統(tǒng)表空間（ibdata1）。這條更新語句做了如下的操作（按照圖中的數(shù)字順序）：
帶change buffer的更新過程：
select * from t where k in (k1, k2) ,如果讀語句發(fā)生在更新語句后不久，內(nèi)存中的數(shù)據(jù)都還在，那么此時的這兩個讀操作就與系統(tǒng)表空間（ibdata1）和 redo log（ib_log_fileX）無關(guān)了.
理解Mysql索引的原理和數(shù)據(jù)結(jié)構(gòu)有助于我們更好的使用索引以及進行SQL優(yōu)化，索引是在存儲引擎層面實現(xiàn)的，所以不同的引擎實現(xiàn)的索引也有一定的區(qū)別，但是在生產(chǎn)環(huán)境中，我們最常用的就是InnoDB引擎和B樹索引，OK，那本文要討論的重點也同樣是 InnoDB引擎下的B樹索引 。
我們建立一個表來進行測試，表的DDL如下所示，我們要關(guān)注的是表t_book上的主鍵索引id和name author publish_date三列組成的索引test_index。
Mysql中的B樹索引是使用B+樹實現(xiàn)的，關(guān)于B+樹的數(shù)據(jù)結(jié)構(gòu)個人認為美團點評技術(shù)博客中Mysql索引原理及慢查詢優(yōu)化一文中介紹的非常詳實，B+樹的數(shù)據(jù)結(jié)構(gòu)如下圖所示。
圖中淺藍色塊即磁盤塊，根節(jié)點磁盤塊中存儲17和35兩個數(shù)據(jù)，其中指針P1指向小于17的數(shù)據(jù)，指針P2指向大于17小于35的數(shù)據(jù)，指針P3指向大于35的數(shù)據(jù)。顯然通過B+樹索引查詢數(shù)據(jù)與B+樹的高度有關(guān)，如上圖的B+樹索引查找一個葉子節(jié)點的數(shù)據(jù)只需要三次磁盤IO，對于Mysql來說三層的B+樹可以索引上百萬的數(shù)據(jù)，這對于查詢效率的提升是巨大的。
總結(jié)起來Mysql中B樹索引有以下關(guān)鍵特點：
Mysql中的B樹索引有兩種數(shù)據(jù)存儲形式，一種為聚簇索引，一種為二級索引。
InnoDB一般會使用表的主鍵來作為聚簇索引，如果一個表沒有主鍵（不建議這么玩）InnoDB會選用一個唯一非空索引來代替，如果沒有這樣的索引，InnoDB會隱式建立一個聚簇索引。聚簇的含義即是數(shù)據(jù)行和相鄰的鍵值緊湊的存儲在一起，占據(jù)一塊連續(xù)的磁盤空間，因此通過聚簇索引訪問數(shù)據(jù)可以有效減少隨機IO，通常使用聚簇索引查找比非聚簇索引查找速度更快。以我們建立的表t_book為例，聚簇索引即為自增主鍵id，其B樹索引數(shù)據(jù)結(jié)構(gòu)可以用下圖來表示。
聚簇索引有以下關(guān)鍵特點：
InnoDB的B樹索引中除了聚簇索引，就都是二級索引了，二級索引的含義是索引的葉子節(jié)點除了存儲了索引值，還存儲了主鍵id，在使用二級索引進行查詢時，查找到二級索引B樹上的葉子節(jié)點后還需要去聚簇索引上去查詢真實數(shù)據(jù)，但是這里有一種特殊情況，即查詢所需的所有字段在二級索引中都可以獲取，此時就不需要再去回表查數(shù)據(jù)了，這種情況就是索引覆蓋（EXPLAIN中EXTRA列中會出現(xiàn)USING INDEX，本文只關(guān)注索引結(jié)構(gòu)，不詳細討論索引覆蓋等技術(shù)的使用，如果深入理解索引的數(shù)據(jù)結(jié)構(gòu)，索引覆蓋等技術(shù)也沒有那么神秘）。
在我們的測試表t_book中，test_index即為二級索引，由于我們把除了主鍵id所有的列都作為一個聯(lián)合索引，所以在這個表上的查詢都可以使用索引覆蓋技術(shù)，但是具體生產(chǎn)環(huán)境中也不建議總是采用這種做法，索引列的增加也會增大插入更新數(shù)據(jù)時的索引更新成本，具體的優(yōu)化要視具體情況決策。t_book上的二級索引test_index的索引結(jié)構(gòu)由下圖表示。
通過以上結(jié)構(gòu)，我們可以推斷出二級索引的以下關(guān)鍵特點：
索引覆蓋：
最左前綴匹配：
二級索引可以說是我們在Mysql中最常用的索引，通過理解二級索引的索引結(jié)構(gòu)可以更容易理解二級索引的特性和使用。
最后聊點輕松的索引結(jié)構(gòu)，哈希索引就是通過哈希表實現(xiàn)的索引，即通過被索引的列計算出哈希值，并指向被索引的記錄。
哈希索引有如下特性：
Mysql索引原理及慢查詢優(yōu)化
高性能Mysql 第三版
1.添加PRIMARY KEY（主鍵索引）
mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysqlALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
? ?通常大家都會根據(jù)查詢的WHERE條件來創(chuàng)建合適的索引，不過這只是索引優(yōu)化的一個方面。設(shè)計優(yōu)秀的索引應(yīng)該考慮到整個查詢，而不單單是WHERE條件部分。索引確實是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要再回到表中查詢呢？ 如果一個索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。
覆蓋索引是非常有用的工具，能夠極大地提高性能：
? ?在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。
? ?不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引都不存儲索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲引擎實現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。
? ?當發(fā)起一個唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。
如： explain select col1 from layout_test where col2=99
? ?索引覆蓋查詢還有很多陷阱可能會導致無法實現(xiàn)優(yōu)化。MySQL查詢優(yōu)化器會在執(zhí)行查詢前判斷是否有一個索引能進行覆蓋。假設(shè)索引覆蓋了wehre條件中的字段，但不是整個查詢涉及的字段。mysql5.5和更早的版本也總是會回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會被過濾掉。
如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'
這里索引無法覆蓋該查詢，有兩個原因：
這條語句只檢索1行，而之前的 like '%Kim%'要檢索3行。
也有辦法解決上面所說的兩個問題，需要重寫查詢并巧妙設(shè)計索引。
? ?這種方式叫做延遲關(guān)聯(lián)，因為延遲了對列的訪問。在查詢第一個階段MySQL可以使用覆蓋索引，因為索引包含了主鍵id的值，不需要做二次查找。
? ?在FROM子句的子查詢中找到匹配的id，然后根據(jù)這些id值在外層查詢匹配獲取需要的所有列值。雖然無法使用索引覆蓋整個查詢，但總算比完全無法利用索引覆蓋的好吧。
數(shù)據(jù)量大了怎么辦？
? ?這樣優(yōu)化的效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個people表有100萬行，我們看一下上面兩個查詢在三個不同的數(shù)據(jù)集上的表現(xiàn)，每個數(shù)據(jù)集都包含100萬行。
實例1中 ，查詢返回了一個很大的結(jié)果集，因此看不到優(yōu)化的效果。大部分時間都花在讀取和發(fā)送數(shù)據(jù)上了。
實例2中 ，經(jīng)過索引過濾，尤其是第二個條件過濾后只返回了很少的結(jié)果集，優(yōu)化的效果非常明顯：在這個數(shù)據(jù)及上性能提高了很多，優(yōu)化后的查詢效率主要得益于只需讀取40行完整數(shù)據(jù)行，而不是原查詢中需要的30000行。
實例3中 ，子查詢效率反而下降。因為索引過濾時符合第一個條件的結(jié)果集已經(jīng)很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。
? ?在大多數(shù)存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進一步優(yōu)化InnoDB。回想一下，InnoDB的二級索引的葉子節(jié)點都包含了主鍵的值，這意味著InnoDB的二級索引可以有效地利用這些額外的主鍵列來覆蓋查詢。
? ?例如，people表中l(wèi)ast_name字段有一個二級索引，雖然該索引的列不包括主鍵id，但也能夠用于對id做覆蓋查詢：
select id,last_name from people where last_name='hua'
重慶分公司
重慶分公司
