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 :  範圍查詢，用到了>，40ff39ec250df60e725043355966d10b,3730409ab0102a19484a1261b4c3727d, like "%xxx" 索引会失效

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

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

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