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-treeIts 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 indexstores 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 indexThe only field in the business is suitable for establishing a unique index. There can be multiple unique indexes in a table

• Primary key indexand 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

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: 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 >, <, in, between and other queries

• #index: full table scan, but traverses the entire index tree

• all: Full table scan, no index is used

possible_keys: Query If there is an index on the field, it will be displayed.

key: The specific index used. If a covering index is used, possible_keys is null and will only be displayed in the key

key_len: The number of bytes used in the index, the maximum possible length, not the actual length. key_len is calculated based on the table definition, not retrieved from the table

ref: Indicates which field is being indexed

rows: Roughly estimates the number of rows that need to be read

filtered: Shows the percentage estimate of the number of rows filtered by the condition.

Extra:

• Using filesort: The sorting that mysql cannot complete using the index is called file sorting

• Using temporary: Use temporary tables to store intermediate results. Mysql uses temporary tables when sorting query results. It is common in order by and 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教程(视频)

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!