Mysql can rely on indexing, but I can only rely on part-time work....

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

一、  索引数据结构

面试的时候肯定会问这一个问题，mysql为什么会选择b 树作为索引呢？而不选择其他索引，例如b树？hash？

下面说的磁盘IO是指数据从硬盘加载到内存中的操作

• hash索引的话，不支持范围查询，因为hash就是一个键对应一个值的，没办法范围查询

• In the case of a binary tree, its characteristic is that the left subtree is smaller than the root node and smaller than the right subtree. If there is a problem with the value of the root node, it may It will degenerate into a linked list, that is, the tree will not bifurcate, and the tree will always go to the left or right, so that it cannot search in half to reduce the number of IOs. Range queries are not supported. If range queries are used, they must be traversed from the root every time. The tree is also too tall. The taller the tree, the more frequent IO operations are, which wastes resources.

• If you balance a binary tree, there will be no binary tree. This has the disadvantage of degenerating into a linked list, because the difference between its left and right child nodes is at most 1 level, but it does not support range search, which is the same as the problem of binary trees

• B tree, compared with the binary tree, the tree is very short and fat, and the IO operations are reduced. It is a multi-fork tree. Each node stores the corresponding row of data, but if the data of this row As the columns continue to increase, the number of nodes stored on this page will decrease, because the space occupied will continue to increase, the tree will become higher and higher, and the number of IO operations will increase. At the same time, range searches are not supported. It would be better if the same size of space can store a lot of node data, so there is the following b-tree

• b-tree Its non-leaf nodes only store index data, not the entire row of data, but the leaf nodes are redundant, redundant non-leaf nodes, and the leaf nodes are also linked with doubly linked lists, which facilitates sequential search, b Compared with the b-tree, the tree is chunkier and has fewer disk IO times

2. Index type in mysql

• Clustered index and non-clustered index

We can simply understood as Clustered index is the primary key index, and non-clustered index is ordinary index

The essential difference is

Clustered indexThe leaf node stores the entire row of data

innodb implements clustered indexes through primary keys. If there is no primary key, then it will choose a unique non-empty index to implement. If there is no primary key, it will be generated implicitly. A primary key to implement a clustered index

##Non-clustered index stores the index value and primary key value

• Ordinary indexThere can be multiple ordinary indexes in a table, and an index can be established on any field. Most of the indexes we usually create are ordinary indexes

• Joint indexAn index created by combining several fields

• Unique index The only field in the business is suitable for establishing a unique index. There can be multiple unique indexes in a table

• Primary key index and unique The same as the index, the primary key index is also unique. The difference is that a table can only have one primary key index

3. About indexes sql

Create a primary key index

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

Create a unique index

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

Create a normal index

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

Create a joint index

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

Modify the index name: delete first and then add

Delete the index (two methods)

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复制代码

View the index in the table

SHOW INDEX FROM test复制代码

Analysis Index

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: There is only one row of data in the table

• const: Constant query is usually used to compare primary key equals A constant that can be found in one query using the index

• eq_ref: Unique index, each index corresponds to a piece of data, such as the primary key index

• ref: Non-unique index, each index may correspond to multiple rows of data, such as ordinary index

• range : Range query, using >, 60b17f53d2822093f64e74cdfafb2115,3730409ab0102a19484a1261b4c3727d, like "%xxx" 索引会失效

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

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

The above is the detailed content of Mysql can rely on indexing, but I can only rely on part-time work..... For more information, please follow other related articles on the PHP Chinese website!