逍遥阁

mysql索引的知识

关键词: mysql

mysql索引的知识

索引是存储引擎用于快速找到记录的一种数据结构

mysql只能高效地使用索引的最左前缀列

当表的数据非常大(TB级别)，不适合再使用索引了，应该用`块级别元数据技术`替代索引

索引列不能是表达式的一部分，也不能是函数的参数

# 索引的类型

### B-Tree索引

按照顺序存储数据

每一个叶子节点都包含指向下一个叶子节点的指针

假设有一个索引有3个字段`key(a,b,c)`，则

可以将a、b、c用于`orderr by`或者`group by`

```
select * from test where a='值' and b='值' and c='值'(abc都用)
select * from test where a='值'(只使用a)
select * from test where a like '值%'(以指定字符串开头，只使用a)
select * from test where a in ('值1','值2')(只使用a)
select * from test where a ='值' and b like '值%'(只使用ab)
select * from test where a='值' and c='值'(只使用a，不能跳过索引中的列，如果不得不跳过某个列，可以用in列出所有值)
select * from test where a='值' and b like '值%' and c='值'(只使用ab，如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询)
```

如果范围查询的列值的数量有限，那么可以通过使用多个等于条件来代替范围条件，使用in也是可以的

下面是不会用到索引的写法

```
select * from test where b='值'(没有用到索引，如果不是按照索引的最左列开始查找，则无法使用索引，相当于不能跳过索引中的列)
select * from test where a like '%值'(以指定字符串结尾，没有用到索引)
select * from test where a+1=5(索引列不能是表达式的一部分，没有用到索引)
```

### 哈希索引

只有精确匹配索引所有列的查询才有效

无法用于排序

不支持部分索引列匹配查找(因为哈希值是根据所有索引列计算的)

只支持等值比较查询

不要使用sha1和md5作为哈希函数，因为计算的值是字符串，可以使用crc32

当使用哈希索引进行查询的时候，必须在where子句中包含常量值(处理哈希冲突)，即

```
select * from test where the_crc32=crc32('https://www.zhouchun.net') and url='https://www.zhouchun.net'
```

### 空间数据索引

### 全文索引

不适用于where条件操作

### 聚簇索引

数据行和相邻的键值紧凑地存储在一起

一个表只能有一个聚簇索引

### 覆盖索引

### 复合索引

### 唯一索引

### 前缀索引

就是把列的指定长度拿来做索引

```
alter table test add key(a(5))
```

对于blob、text、或者很长的varchar类型的列，必须使用前缀索引

无法用于`orderr by`或者`group by`

# 范围查询

```
>
>=
<
<=
<>
like
between and
```

尽可能将需要做范围查询的列放到索引的后面

# 等值查询

```
=
in
```

# 索引选择性

索引选择性=不重复的索引值/总的记录数

范围为[1/总的记录数，1]

索引的选择性越高查询效率越高

唯一索引的选择性是1

计算一个列的选择性

```
select count(distinct a)/count(*) from test
```

应该将选择性最高的列放到索引最前列(这个只是一般的经验法则)

admin
2018-01-15 21:43:49
mysql

文章分类

文章标签

文章归档

友情链接

mysql索引的知识