Mysql索引

最新推荐文章于 2024-04-02 08:00:01 发布

NightCharm

最新推荐文章于 2024-04-02 08:00:01 发布

阅读量354

点赞数 1

分类专栏： mysql 文章标签： mysql索引

本文链接：https://blog.csdn.net/nightcharm/article/details/79439057

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Mysq索引

什么是索引

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

简单的理解成书上的目录，提高查询速度(索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。)

小结

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查地图等等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据

索引结构

主要有BTree索引、Hash索引、full-text全文索引、R-Tree索引。

备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE，

BTree

Innodb默认索引

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。
　　在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

这里写图片描述

【初始化介绍】

　　一颗b+树，浅蓝色的块称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，如磁盘块1包含数据项17和35，包含指针P1, P2, P3, P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
　　真实的数据存在于叶子节点即3，5，9，19…
　　非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17，35并不真实存在于数据表中。

【查找过程】

　　如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短(相比于磁盘的IO)可以忽略不计。
　　通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO, 29在26和30之前，锁定磁盘块3的P2指针。
　　通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

BTree性质

索引字段要尽量的小
索引的最左匹配特性

HASH

hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value,为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据. hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率.

HASH与BTREE比较:

索引层次区别：

hash类型的索引：查找单条快，查询范围慢
bree类型的索引：范围查询和随机查询快（innodb默认索引类型）

索引引擎区别：

InnoDB 支持事务，支持行级别锁定，支持 Btree、Hash 等索引，不支持Full-text 索引；
MyISAM 不支持事务，支持表级别锁定，支持 Btree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 Btree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 Btree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 Btree、Hash、Full-text 等索引；

聚集索引与辅助索引

在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引

# InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

# 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

# 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

# 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

优点

：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

注意

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

区别

不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

MySQL索引管理

功能

#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

MySQL常用的索引

语法规则

查看索引

# 语法
show index from 表名;

查看索引列介绍

Table 表的名称。
Non_unique 如果索引为唯一索引,则为0,如果可以则为1。
Key_name 索引的名称
Seq_in_index 索引中的列序列号，从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment 多种评注

索引语法规则

# 方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


# 方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


# 方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

# 删除索引：DROP INDEX 索引名 ON 表名字;

普通索引

index 加速查找

创建普通索引

create table t1
    id int not null primary key auto_increment;
    index name varchar(50);

添加普通索引

create index idx_name on t1(name);

删除普通索引

drop index idx_name on t1;

唯一索引

主键索引 | PRIMARY KEY 加速查找 + 约束（不能为空、不能重复）
唯一索引 | UNIQUE 加速查找 + 约束（不能重复）

联合索引

PRIMARY KEY (联合主键索引)
UNIQUE(联合唯一索引)
INDEX(联合普通索引)

使用索引的时机

需要创建索引

主键自动建立唯一索引
频繁作为查询查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
where条件里用不到的字段不创建索引
单键/组合索引的选择问题(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不需要创建索引

表记录太少
经常增删改的表(因为不仅要保存数据，还要保存一下索引文件)
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

注意:

mysql先去索引表里根据b+树的搜索原理很快搜索到id为4567890的数据,IO大大降低，因而速度明显提升
我们可以去mysql的data目录下找到该表，可以看到添加索引后该表占用的硬盘空间多了　
如果使用没有添加索引的字段进行条件查询,速度依旧会很慢

正确使用索引命

索引未命中

最佳左前缀法则：如果索引了多列，要尊守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
存储引擎不能使用索引中范围条件右边的列。
- 如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。
.尽量使用覆盖索引(只访问索引的查询(索引列和查询列致))，如select age from user减少select *
mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
is null, is not null 也无法使用索引。
like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。
- 所以最好用右边like 'abc%'。如果两边都要用，可以用select age from user where username like '%abc%',其中age是索引列
- 假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用
字符串不加单引号索引失效
少用or，用它来连接时会索引失效
尽量避免子查询，而用join

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

left/right join注意

on与 where的执行顺序
1. ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
2. 所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行.
注意ON 子句和 WHERE 子句的不同

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

　　从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

使用索引的注意事项

1. 避免使用``select *

2. 其他数据库中使用``count``(1)或``count``(列) 代替 ``count``(*),而mysql数据库中``count``(*)经过优化后,效率与前两种基本一样.

3. 创建表时尽量时 ``char 代替 ``varchar

4. 表的字段顺序固定长度的字段优先

5. 组合索引代替多个单列索引（经常使用多个条件查询时）

6. 使用连接（``JOIN``）来代替子查询(Sub-Queries)

7. 不要有超过4个以上的表连接（``JOIN``）

8. 优先执行那些能够大量减少结果的连接。

9. 连表时注意条件类型需一致

10.索引散列值不适合建索引，例：性别不适合

查询截取分析

查询优化

1.永远小表驱动大表

　　在java程序里，两个for循环，循环次数不管谁先谁后都是两者次数相乘。
　　但在mysql的语句中，一定要小表驱动大表，因为小表跟Mysql连接和释放数量少
如in与exists

select * from A where id in (select id form B)
等价于
for select id from B
for select * from A where A.id=B.id
结论：当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id=A.id) // 这里的1用任何常量都行
等价于
for select * from A
for select * from B where B.id=A.id
结论：当A表的数据集必须小于B表的数据集时，用in优于exists。

注意：A表与B表的ID字段应建立索引　　exists通用语法`select ... from table where exists (subquery)`

　　该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(true或false)来决定主查询的数据结果是否得以保留。
提示：
　　1).exists(subquery)只返回true或false，因此子查询中select *也可以是select 1或select 'X', 官方说法是实际执行会忽略select 清单，因此没有区别。
　　2).exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验。
　　3).exists 子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

2.order by 关键字优化

1).order by子句，尽量使用index方式排序，避免使用FileSort方式排序

　　MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。(用explain可以在extra字段里看到Using index/filesort)
　　Order By满足两种情况，会使用Index方式排序
　　　a. Order by语句使用索引最左前列
　　　b. 使用where子句与Order by子句条件组合满足索引最左前列。

2).尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

3).如果不在索引列上，fileSort有两种算法：mysql就要启动双路排序和单路排序
　　双路排序：MySQL4.1之前是使用双路排序，字面意思就是 两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行了两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1后，出现了改进算法，就是单路排序
　　单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
　　结论及引申出的问题：由于单路是后出的，总体而言好过双路，但是单路也有问题。

单路的问题
　　在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并),排完再取sort_buffer容量大小，再排…..从而多次IO.
　　本来想省一次IO操作，反而导致了大量的I/O操作，反而得不偿失。

4).参数调优
　　增大sort_buffer_size参数的设置
　　增大 max_length_for_sort_data 参数的设置

参数设置，提高order by 的速度
　　1.order by 时select * 是一个大忌，最好只Query需要，这点非常重要。在这里影响的是：
　　　　a).当Query 的字段大小总和小于 max_length_for_sort_data ，而且排序字段不是text|blob类型时，会用改进后的算法 – 单路排序；否则用老算法–多路排序
　　　　b).两种算法算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size
　　2.尝试提高 sort_buffer_size
　　　　不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
　　3.尝试提高 max_length_for_sort_data
　　　　提高这个参数，会增加用改进算法的概率。但如果设的太高，数据总容量超出 sort_buffer_size 的概率增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

5).总结
　　MySql两种排序方式：文件排序(filesort)或扫描有序索引排序(index)
　　MySql能为排序与查询使用相同的索引

KEY a_b_c(a, b, c)

order by 能使用索引最左前缀
- order by a
- order by a, b
- order by a desc, c desc

如果where使用索引的最左前缀定义为常量，则order by 能使用索引
- where a = const order by b, c
- where a = const and b = const order by c
- where a = const order by b,c
- where a = const and b > const order by b, c

不能使用索引进行排序
- order by a asc, b desc, c desc  /* 排序不一致 */
- where g = const order by b, c   /* 丢失a索引  */
- where a = const order by c      /* 丢失b索引  */
- where a = const order by a, d   /* d不是索引的一部分  */
- where a in (..) order by b, c   /* 对于排序来说，多个相等条件也是范围查询(in 也是范围查询)！！  */

3.group by 关键字优化

基本与 order by 优化一致
　　1).group by 实质是先排序后分组，遵照索引建的最佳左前缀
　　2).当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大sort_buffer_size参数的设置
　　3).where高于having，能写在where限定的条件就不要去having限定了。

NightCharm

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引

Mysq索引什么是索引索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。简单的理解成书上的目录，提高查询速度(索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百...
复制链接

扫一扫