Mysql索引优化

最新推荐文章于 2022-10-31 11:13:40 发布

dzysunshine

最新推荐文章于 2022-10-31 11:13:40 发布

阅读量247

点赞数

分类专栏： Mysql 文章标签： Mysql

本文链接：https://blog.csdn.net/dzysunshine/article/details/95378807

版权

Mysql 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

1. 索引

1.1 什么是索引

索引是帮助MySQL高效获取数据的数据结构。
更通俗的说，索引就相当于目录。当你在用新华字典时，帮你把目录撕掉了，你查询某个字开头的成语只能从第一页翻到第一千页。累！把目录还给你，则能快速定位！

1.2 索引的优缺点

优点：

可以大大加快数据的检索速度，降低检索过程中必须要读取的数据量，降低数据库IO成本，提高系统的性能。

降低数据库的排序成本。因为索引就是对字段数据进行排序后存储的，如果待排序的字段与索引键字段一致，就在取出数据后不用再次排序了，因为通过索引取得的数据已满足排序要求。另外，分组操作是先排序后分组，所以索引同样可以省略分组的排序操作，降低内存与CPU资源的消耗。

缺点：

索引会增加增、删、改操作所带来的IO量与调整索引的计算量。
索引要占用空间，随着数据量的不断增大，索引还会带来存储空间的消耗。
索引需要额外的维护成本；因为索引文件是单独存在的文件,对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。

判断是否应该建索引的条件

1、较频繁的作为查询条件的字段应该创建索引

2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

3、增、删、改操作较多的数据库字段不适合建索引

1.3 索引的作用

提高数据查询的效率。

索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序。

1.4 创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1．ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2．CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

1.5 创建索引的原则

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。

1.6 索引类型

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-test全文索引，R-Tree索引；

从应用层次来分：普通索引，唯一索引，复合索引；

根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

就像手机分类：安卓手机，IOS手机与华为手机，苹果手机，OPPO手机一样。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

1.7 删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

1.8 查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

1.9 索引优化建议

1.对索引列进行计算

例如，我们想要将表tb_test中id大于100的数据记录中的age和name查找出来。

正确的SQL语句是：

select age,name from tb_test where id > 1*100;

不建议采用的SQL语句是：

select age,name from tb_test where id/100 > 1;

2.对索引列进行拼接

例如，我们想要将表tb_test中name为“zhou”、addr为“CQ”的记录中的id和age查找出来。

正确的SQL语句是：

select id,age from tb_test where name='zhou' and addr='CQ';

不建议采用的SQL语句是：

select id,age from tb_test where concat(name,' ‘,addr) = ‘zhou CQ';

3.在索引列上is null或is not null的使用

例如，我们想要将表tb_test中id大于等于“0”的记录中的age查找出来。

正确的SQL语句是：

select age from tb_test where id >= 0;

不建议采用的SQL语句是：

select age from tb_test where id is not null;

4.在索引列上or的使用

例如，我们想要将表tb_test中id等于101或102的记录中的age和name查找出来。

正确的SQL语句(使用union)是：

select age,name from tb_test where id = 101 union select age,name from tb_test where id = 102;

不建议采用的SQL语句(使用or)是：

select age,name from tb_test where id = 101 or id = 102;

5.尽可能避免索引列在like的首字符使用通配符

例如，我们想要将表tb_test中name匹配“zho”的记录中的id和age查找出来。

正确的SQL语句是：

select id,age from tb_test where name like ‘zho%';

不建议采用的SQL语句是：

select id,age from tb_test where name like ‘%zho%';

6.复合索引的使用

如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。

例如，我们在表tb_test上新建了如下索引：

create index idx4_tb_test on tb_test(id,name,addr);

以上索引idx4_tb_test相当于建立了index(id)、index(id,name)、index(id,name,addr) 这3个索引。在SQL语句的where条件中单独使用name或addr时不会使用到该索引，必须使用id时才会使用到该索引。

在我们编写的SQL语句中，不正确地使用索引列可能会导致索引不被使用，而进行全表扫描，极大地降低了数据库的性能。因此，学习正确的索引的使用方法实在是很有必要的。

2. 索引优化

2.1 B-Tree索引

2.1.1 概述

Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持B-Tree索引。正是其优异的检索表现，才使其有这样的地位。

2.1.2 存储结构

正如其名，这类索引的物理文件大多就是以B-Tree结构来存储的，但会有不同的存储引擎在使用B-Tree索引时，对存储结构稍作修改，比如MyISAM存储引擎，使用B+Tree的数据结构，它相对与BTree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。
在这里插入图片描述

2.1.3 B-树的特性

1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制。

2.2 B+Tree索引

2.2.1 B+Tree

是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。
在这里插入图片描述

2.2.2 B+的特性

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统。

2.2.3 InnoDB 与 MyISAm 的区别

对于innoDB存储引擎，虽然同样使用B+Tree作为索引的存储结构，但具体实现却与MyISAM截然不同，这也是作为MyISAM与InnoDB存储引擎的一个重要区别反复被面试官问到。

（1）MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为“非聚簇索引”。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

如下图所示为非聚簇索引的主键索引：
在这里插入图片描述

其检索算法：先按照B+Tree的检索算法检索，找到指定关键字，则取出对应数据域的值，作为地址取出数据记录。

（2）InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

如下图所示为聚簇索引的主键索引：
在这里插入图片描述

面试常问

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

问：为什么官方建议使用自增长主键作为索引。

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

2.3 HASH索引

2.3.1 什么是HASH索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。
主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。
在这里插入图片描述

2.3.2 Hash索引的弊端

一般来说，索引的检索效率非常高，可以一次定位，不像B-Tree索引需要进行从根节点到叶节点的多次IO操作。有利必有弊，Hash算法在索引的应用也有很多弊端。

a、Hash索引仅仅能满足等值的查询，范围查询不保证结果正确。因为数据在经过Hash算法后，其大小关系就可能发生变化。

b、Hash索引不能被排序。同样是因为数据经过Hash算法后，大小关系就可能发生变化，排序是没有意义的。

c、Hash索引不能避免表数据的扫描。因为发生Hash碰撞时，仅仅比较Hash值是不够的，需要比较实际的值以判定是否符合要求。

d、Hash索引在发生大量Hash值相同的情况时性能不一定比B-Tree索引高。因为碰撞情况会导致多次的表数据的扫描，造成整体性能的低下，可以通过采用合适的Hash算法一定程度解决这个问题。

e、Hash索引不能使用部分索引键查询。因为当使用组合索引情况时，是把多个数据库列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。

2.4 Full-Text索引

2.4.1 概述

全文索引，目前MySQL中只有MyISAM存储引擎支持，并且只有CHAR、VARCHAR、TEXT类型支持。它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

2.4.2 存储结构

同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

参考

Mysql创建索引
 90%程序员面试会遇到的索引优化问题
 面试题:MySQL性能调优——索引详解与索引的优化
 图解MySQL索引–B-Tree（B+Tree）

dzysunshine

关注

0
点赞
踩
0

收藏

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

文章目录1. 索引1.1 什么是索引1.2 索引的优缺点1.3 索引的作用1.4 创建索引1.5 索引类型1.6 删除索引1.7 查看索引参考1. 索引1.1 什么是索引索引是帮助MySQL高效获取数据的数据结构。更通俗的说，索引就相当于目录。当你在用新华字典时，帮你把目录撕掉了，你查询某个字开头的成语只能从第一页翻到第一千页。累！把目录还给你，则能快速定位！1.2 索引的优缺点优点：...
复制链接

扫一扫