like '%%'如何命中索引
相信各位老鐵在開發(fā)的過程中肯定都遇到過like全匹配的需求货徙。在數(shù)據(jù)量很小的時候惠桃，select * from user where name like '%hello%'扣墩。這種查詢問題也不大族跛，但是久而久之弛作，會發(fā)現(xiàn)輸入框再輸入這種簡單文字的時候頁面就會loading很久愕提。當(dāng)數(shù)據(jù)量大起來馒稍，like雙百分號匹配不走索引的時候就很憂傷，但是需求不會因為這個而改變浅侨。那么纽谒，mysql是如何解決這個問題的呢？或許可以試試explain select name from user where name like '%hello%'如输。你會意外的發(fā)現(xiàn)key字段竟然不是null鼓黔。W-H-Y？
覆蓋索引
這里引入一個概念不见。索引包含所有滿足查詢需要的數(shù)據(jù)的索引澳化，稱為覆蓋索引(Covering Index)。百度百科來的定義稳吮、是不是抽象的一臉懵逼缎谷？可以簡單的理解為select的數(shù)據(jù)列只用從索引中就能夠取得，不需要查數(shù)據(jù)就能拿到結(jié)果盖高。拿這個栗子來說慎陵，name字段自身是建立了索引的（innodb引擎下索引自身是保存了數(shù)據(jù)的）眼虱，select查詢name字段直接就可以獲取到值喻奥，就不需要再根據(jù)索引去數(shù)據(jù)表里面查對應(yīng)的值。
B+Tree數(shù)據(jù)結(jié)構(gòu)簡介
這里有必要介紹一下mysql -- innodb引擎下B+Tree索引的數(shù)據(jù)結(jié)構(gòu)捏悬。
- 淺藍(lán)色的塊是磁盤塊撞蚕，對應(yīng)硬件上的磁盤空間。其中深藍(lán)色的塊表示數(shù)據(jù)項（17过牙，35）甥厦，黃色的表是指針（P1,P2,P3）
- 真實的數(shù)據(jù)存在于葉子節(jié)點(diǎn)中，非葉子節(jié)點(diǎn)只儲存數(shù)據(jù)項和指引方向的指針寇钉，這里17刀疙，和35并不實際存在于數(shù)據(jù)表中。
- 如果我們要在上圖中搜索數(shù)據(jù)75扫倡，磁盤僅需要3次IO即可谦秧。第一次，與17和35比較，發(fā)現(xiàn)數(shù)據(jù)大于35疚鲤，根據(jù)指針P3指示繼續(xù)往下走锥累。第二次，發(fā)現(xiàn)數(shù)據(jù)介于65和87中間集歇，指針P2指向數(shù)據(jù)塊10桶略，那么第三次就能直接查找到數(shù)據(jù)。
- 顯然如果沒有建立索引诲宇，直接挨個查找75的IO次數(shù)遠(yuǎn)不止3次际歼。
關(guān)于like的索引問題
索引失效的幾種情況大家可以先預(yù)習(xí)一下，這是百度上搜到的http://blog.csdn.net/zmx729618/article/details/52701370姑蓝，如果覺得沒必要蹬挺，可以直接跳過。下面我們來模擬數(shù)據(jù)它掂。
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(125) NOT NULL DEFAULT '' COMMENT '名稱',
`age` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '年齡',
`address` varchar(50) NOT NULL DEFAULT '' COMMENT '住址',
`deleted` tinyint(4) unsigned DEFAULT NULL,
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_age_address` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='用戶表測試';
INSERT INTO `user` (`id`, `name`, `age`, `address`, `deleted`, `created`)
VALUES
(1, '5', 1000, '花果山', 0, '2017-12-10 21:48:03'),
(2, '8', 800, '高老莊', 0, '2017-12-10 21:48:16'),
(3, '3', 25, '長安', 0, '2017-12-10 21:48:49'),
(4, '白龍馬', 700, '龍宮', 0, '2017-12-10 21:49:16');
執(zhí)行上面的建表語句巴帮，初始化數(shù)據(jù)之后，我們來看一下下面的兩個sql
select * from user
where name = '3' and age = 20 and address like '長安%'
select * from user
where name like 3 and age > 20 and address like '長安%'
然后分析一波下面的問題
- 第二個查詢語句有沒有命中索引虐秋？
- 如果沒有命中索引榕茧，如何讓它命中索引
- 它能命中索引的哪些字段（name,age,address）
.
.
.
.
.
.
好了，思考一個省略號的時間之后我們來回答這個問題客给。
- 第二個sql命中了索引用押。
- 這里name字段是varchar類型，字符串未加單引號索引會失效靶剑，但是like特殊蜻拨，不受限制，這里加不加單引號并沒有影響桩引。
- 命中了索引的字段是缎讼，name、age坑匠。 address并未命中血崭。這里name字段like查詢?yōu)榉秶樵儯凑章?lián)合索引返回查詢之后全失效的規(guī)則厘灼，這里age本不應(yīng)該命中索引夹纫，但是like例外。
下面是分析過程
我們分析的依據(jù)是第一個sql的執(zhí)行結(jié)果设凹，用explain來分析查看舰讹，可以看到3個字段都命中索引之后 key_len值為708。
再看第二個sql闪朱，發(fā)現(xiàn)key_len為506月匣⌒僬觯可以證明真正命中了索引的字段是name和age。
今天就分析到這里桶错，希望這些例子能幫助大家少踩一些坑：剿簟！院刁！
