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

mysql教程栏目介绍相关索引。

一、  索引数据结构

面试的时候肯定会问这一个问题，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:  范围查询，用到了>，b64ec64ea3055b2f3039087d18c21b30,3730409ab0102a19484a1261b4c3727d, like "%xxx" 索引会失效

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

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

Atas ialah kandungan terperinci mysql可以靠索引，而我只能靠打工..... Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!