mysql可以靠索引，而我只能靠打工....

mysql教學##欄位介紹相關索引。

.markdown-body{word-break:break-word;行高:1.75;字體粗細:400;字體大小:15px ;overflow-x :hidden;color:#333}.markdown-body h1,.markdown-body h2,.markdown-body h3,.markdown-body h4,.markdown-body h5,.markdown-body h6{line-高度：1.5; margin-top：35px;margin-bottom：10px;padding-bottom：5px}.markdown-body h1{font-size：30px;margin-bottom：5px}.markdown-body h2{padding-bottom： 12px;字體大小：24px;邊框底部：1px實心#ececec}.markdown-body h3{font-size:18px;padding-bottom:0}.markdown-body h4{font-size:16px}.markdown- body h5{font-size :15px}.markdown-body h6{margin-top:5px}.markdown-body p{line-height:inherit;margin-top:22px;margin-bottom:22px}.markdown-body img {max-width:100 %}.markdown-body hr{border:none;border-top:1pxsolid #ddd;margin-top:32px;margin-bottom:32px}.markdown-body程式碼{word-break:break -word;border-radius: 2px;overflow-x:auto;background-color:#fff5f5;color:#ff502c;font-size:.87em;padding:.065em .4em}.markdown-body程式碼,.markdown- body pre{font-family: Menlo,Monaco,Consolas,Courier New,monospace}.markdown-body pre{overflow:auto;position:relative;line-height:1.75}.markdown-body pre>code{字體大小: 12px;padding:15px 12px;margincode{字體大小: 12px;padding:15px 12px;margincode{字體大小: 12px;padding:15px 12px;margincode{字體大小: :0;word-break:normal;display:block;overflow-x:auto;background:#f8f8f8}.markdown-body a{text-decoration:none;color:#0269c8;border -bottom:1pxsolid #d1e9ff}. markdown-body a:active,.markdown-body a:hover{color:#275b8c}.markdown-body table{display:inline-block!important;font-size:12px;width :auto;最大寬度:100%;溢出:auto;邊框:1px 實心#f6f6f6}.markdown-body thead{背景:#f6f6f6;顏色:#000;text-align:left}.markdown-body tr:nth -child(2n){背景顏色:# fcfcfc}.markdown-body td,.markdown-body th{padding:12px 7px;line-height:24px}.markdown-body td{min-width:120px}.markdown -body blockquote{顏色：#666;padding: 1px 23px;margin:22px 0;border-left:4pxsolid #cbcbcb;background-color:#f8f8f8}.markdown-body blockquote:after{display:block;content:" "}.markdown-body blockquote>p{margin:display:block;content:" "}.markdown-body blockquote>p{margin:) 10px 0}.markdown-body ol,.markdown-body ul{padding-left:28px}.markdown-body ol li,.markdown-body ul li{margin-bottom :0;list-style:inherit}.markdown- body ol li .task-list-item,.markdown-body ul li .task-list-item{list-style:none}.markdown-body ol li .task-列表項ol,.markdown-body ol li .任務清單項目ul,.markdown-body ul li .任務清單項目ol,.markdown-body ul li .任務清單項目ul{margin-top :0}.markdown-body ol ol,.markdown-body ol ul,.markdown -body ul ol,.markdown-body ul ul{margin-top:3px}.markdown-body ol li{padding-left:6px} @media (最大寬度:720px){.markdown-body h1{字體大小:24px }.markdown-body h2{字體大小:20px}.markdown-body h3{字體大小:18px}}

##一、索引資料結構##面試的時候一定會問這個問題，mysql為什麼會選擇b樹作為索引呢？而不選擇其他索引，例如b樹？hash？

#下面所說的磁碟IO是指資料從硬碟載入到記憶體中的操作

• hash索引的話
  ，不支援範圍查詢，因為hash就是一個鍵對應一個值的，不行範圍查詢

• 二元樹的話，它的特徵是左子樹小於根節點小於右子樹，如果根節點取值有問題的話，有可能會退化成鍊錶，就是樹不分叉了，樹一直往左或一直往右，這樣就不能折半查找從而減少IO次數了，不支持範圍查詢，要是範圍查詢的話，每次都要從根部遍歷，樹也太高了，樹越高，IO操作越頻繁，浪費資源

• #平衡二叉樹的話，它就沒有了二叉樹的這種退化成鍊錶的缺點，因為他左右子節點最多相差1層，可是他也不支持範圍查找這一點和二叉樹的問題一樣

• b樹的話，和二​​元樹比起來樹是很矮胖，IO操作減少了，是個多叉樹，它每個節點都存了對應的行數據，可是如果這一行的數據的列不斷的增加，那麼這一頁儲存的節點就會變少，因為所佔的空間不斷的變大，樹也會越來越高，增加IO操作次數，同時是也不支援範圍查找。要是相同大小的空間可以存很多的節點資料的話就更好了，所以就有了下面的b 樹

• b 樹 它非葉子節點只存索引的數據，不存整行數據，但是葉子節點是冗餘的，冗餘了非葉子節點，葉子節點還都用雙向鍊錶鏈接起來，這樣有助於順序查找，b樹和b樹比起來，更矮胖，磁碟IO次數更少

#二、mysql中索引類型

• 叢集索引與非叢集索引

我們可以簡單的理解為 叢集索引就是主鍵索引，非叢集索引就是普通索引

本質的差異是

##叢集索引的葉子節點儲存的是整行資料

innodb是透過主鍵來實現叢集索引的，如果沒有主鍵的話，那麼他就會選擇一個唯一非空的索引來實現，如果再沒有的話，他就會隱式生成一個主鍵來實作叢集索引

#非叢集索引儲存的是索引值和主鍵值

• 普通索引一張表中可以有多個普通索引，隨便一個欄位都可以建立的索引，我們平常建立的索引大部分都是普通索引

• #聯合索引好幾個欄位聯合起來建立的索引

• 唯一索引業務中唯一的欄位適合建立唯一索引，一個表中可以有多個唯一索引

• #主鍵索引和唯一索引一樣，主鍵索引也是唯一的，不同的就是，一個表只能有一個主鍵索引

三、關於索引的sql

建立主鍵索引

ALTER TABLE test add  PRIMARY  KEY (id)复制代码

建立唯一索引

ALTER TABLE test add UNIQUE idx_id_card(id_card)复制代码

建立普通索引

ALTER TABLE test add INDEX idx_name(name)复制代码

建立聯合索引

ALTER TABLE test add INDEX idx_age_name(age,name)复制代码

修改索引名稱：先刪除再新增

刪除索引（兩種方式）

ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test --删除主键索引DROP PRIMARY key on test  ALTER TABLE test DROP  PRIMARY key复制代码

檢視表中索引

SHOW INDEX FROM test复制代码

分析索引

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

我们先给name字段添加一个索引,索引名字叫做idx_name

ALTER TABLE test add INDEX idx_name(name)复制代码

查看test表中的索引

SHOW INDEX FROM test复制代码

其中的属性

• table: 表名

• Non_unique: 能重复的话为1，不能重复的话为0，我们主键的那里是0，而name那里是1，因为name可以重复，而主键不能重复

• Key_name: 索引名称

• Seq_in_index:索引中列的顺序

• Column_name:列名称

• Collation:列以什么方式存储的，A升序，null无序

• Cardinality:数目越大，则使用该索引的可能性越大

• Sub_part:如果列只是部分的编入索引，则被编入索引的字符数目，如果整列被编入索引，则为null

• Packed：关键字是否被压缩，null表示没有被压缩

• Null:如果该列含有null,则为yes,如果没有null,则为no

• Index_type:索引数据结构

• Comment:多种评注

四、回表查询

select * from test where  name = "xhJaver"复制代码

假如说我们name字段建立了索引，然后当我们运行这一句sql语句的时候，因为建立的是普通索引，所以我们的b+树的叶子节点存储的数据是id，我们会找到name是xhJaver的这条记录的id，再根据这个id,去主键索引的那棵b+树去查询，查询到叶子节点时即查询出这条记录，可见这个过程中，我们从一棵树跑到了另一棵树继续查，这样就叫做“回表查询”，那有没有办法只查一棵树就可以查询出结果呢？

五、覆盖索引

办法当然是有的啦，那就是覆盖索引，我们注意到，刚才这个sql语句时查询出来了所有元素，假如说我们这样写的话

select address from test where  name = "xhJaver"复制代码

假如说我们建立的索引是（name,address）那么这个时候（name,address）这棵b+树的叶子节点存储的数据就包括address了，此时就不需要再根据name = "xhJaver"的id去第二棵树查了，这样就避免了回表查询

六、最左匹配原则

假如说现在我们写一个这样的sql语句

select *  from test where  name = "xhJaver" and age =23  and address="京东"复制代码

并且我们建立的索引是（name,address,age）这样是会用到（name,address,age）索引的，可是如果要这样写的话

select *  from test where  name = "xhJaver" and age >23  and address="京东"复制代码

这样只会用到（name，age）这两个索引，从左边开始匹配，如果要是遇到范围查询的话，则不继续往右匹配索引

七、explain分析索引语句

我们用explain语句解析一下下面这条sql语句

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

它的属性有

id: 执行的顺序

• id相同时，顺序从上到下执行
• id不同时，id大的先执行

select_type: 查询的类型

• primary: 最外层的查询被标记为primary
• simple: 简单查询，没有关联其他表，就一张表
• subquery: 在where或者select中的子查询
• derived: 衍生虚拟表 例如from(子查询) t,这个子查询的结果就被放在虚拟表t中

table: 关于哪张表的

partitions: 分区相关（还没搞懂呜呜呜）

type:访问类型

性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，好的sql查询至少达到range级别，最好能达到ref

• system:表中只有一行資料

• #const:常數查詢通常用來比較主鍵等於一個常數，用索引查詢一次就查到了

• eq_ref:唯一性索引，每個索引對應一條數據，例如主鍵索引

• #ref:非唯一索引，每個索引有可能對應多行數據，例如普通索引

• range :  範圍查詢，用到了>，<，in,between等查詢

• #index：全表掃描，但是是遍歷整棵索引樹

• all:全表掃描，沒有用到索引

#possible_keys：查詢的欄位上有索引的話，就會顯示出來，

key : 具體用到的索引，若用到了覆蓋索引，則possible_keys為null,只會顯示在key中

key_len:索引中使用的位元組數，最大可能長度，並非實際長度，key_len是根據表定義計算而得的，不是透過表內檢索出的

ref: 表示使用索引的是哪一個欄位

rows:大致估算出所需要讀取的行數

# filtered:顯示了透過條件過濾出的行數的百分比估計值。

Extra:

• Using filesort ：  mysql無法利用索引完成的排序稱為檔案排序

• #Using temporary：  使用暫存表儲存了下中間結果，mysql對查詢結果排序時是使用了暫存表，常見於order by 和group by

• Using index：使用了覆盖索引，查询内容在索引内

  1. 如果出现了Using where，表示对查询出来的数据进行了过滤
  2. 如果没有出现Using where，表示对查询出来的数据没有进行过滤

• 只有Using where 查询内容不在索引内，且对查出来的数据进行了过滤

1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver") 
复制代码

我们写几个sql语句实际分析下 1.SELECT后面2.where后面

我们就拿后面这个图来实战分析一下,挑几个重要的属性说一下

select_type：

• 我们最外层的查询是 from teacher 所以table为teacher的那个表的select_type就是primary

• select/where后面的括号中的查询语句中的表是student，所以table为student的那个表的select_type就是subquery

table: 这条sql查询用到的表

type: 访问类型

• 第一行const : teacher.id =巴拉巴拉巴拉（这个是常数）主键和常数比较时，这个表最多有一个匹配数据，只读取一次

• 第二行ref：代表用到了普通索引，就是这个索引name和xhJaver匹配,可能匹配到很多相同的值

possible_key: 代表可能用到的索引，但是不一定会用到

key: 代表用到的索引， 用到了idx_name，PRIMARY索引

ref: 这一列显示了在key列记录的索引中，表查找值所用到的列或常量， 常见的有：const，字段名

extra：

• using index: 一般是使用了覆盖索引，看我们这个sql语句，

select student.id from student WHERE student.`name`="xhJaver"复制代码

name字段有索引，查询的是id，b+树叶子节点存的数据就是id，所以不需要回表查询了，用到了覆盖索引

八、索引失效原因

1. 遇到范围查询（>,<,like,beetwon），右边的索引列会失效

2. 索引字段不能有函数操作或者不能是表达式的一部分

3. 索引字段隐式类型转换 索引字段类型是string，我们传进来个int

4. 使用时or,is null ,is not null , !=, <>, like "%xxx" 索引会失效

但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段，或者select索引字段和主键，也会走索引的。

更多相关免费学习推荐：mysql教程(视频)

以上是mysql可以靠索引，而我只能靠打工....的詳細內容。更多資訊請關注PHP中文網其他相關文章！