MySQL调优(二) 索引

最新推荐文章于 2024-09-15 20:24:46 发布

冯居竹

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量162

点赞数

分类专栏： MySQL 文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_41098689/article/details/106537847

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引简介

1.索引是什么

MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构。

索引是数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
在这里插入图片描述
从左到右分别为: 物理地址，主键，数据、二叉查找树。
每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
**弊端:**二叉树可能会发生两边不平衡的情况

而B-TREE是多路搜索树，会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定(Myisam引擎使用的方式)
在这里插入图片描述

真实的数据存在于叶子节点
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，

B+TREE：Innodb所使用的索引

在这里插入图片描述

B+TREE 第二级的数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于 B-TREE
B-TREE 第二级可以直接取出来，树形结构比较重，在内存无限大的时候有优势。接下来会介绍两种树的区别及使用场景。

**B+Tree与B-Tree 的区别：**结论在内存有限的情况下，B+TREE 永远比 B-TREE好。无限内存则后者方便

1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。(一次查询可能进行两次i/o操作)
　 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
　
**思考：**为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的磁盘读写代价更低
　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
B+树的查询效率更加稳定
　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B树，B+树：它们特点是一样的，是多路查找树，一般用于数据库系统中，为什么，因为它们分支多层数少呗，都知道磁盘IO是非常耗时的，而像大量数据存储在磁盘中所以我们要有效的减少磁盘IO次数避免磁盘频繁的查找。B+树是B树的变种树，有n棵子树的节点中含有n个关键字，每个关键字不保存数据，只用来索引，数据都保存在叶子节点。是为文件系统而生的。

由于Myisam和Innodb分别使用的是B-树与B+树，因此需要看看两者的区别，两者的区别跳转MyISAM与InnoDB 的区别（9个不同点）

因此做一个总结:
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。我们平常所说的索引，如果没有特别指明，都是指B树结构组织的索引，其中聚集索引
，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然，除了B+树这种类型的索引之外，还有哈希索引等

使用索引的目的?
1.降低数据库的磁盘IO成本
2.通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗

MySQL的索引结构

除了以上说明的两种索引结构之外，mysql的索引结构还有其它的。分别是聚簇索引与非聚簇索引、full-text全文索引、Hash索引、R-Tree索引。
聚簇索引与非聚簇索引

数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
聚簇索引的限制：
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。（参考聚簇索引的好处。）

full-text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

例如:
CREATE TABLE article (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(200) DEFAULT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT KEY title (title,content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询：
SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;
全文索引用match+against方式查询：
SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

明显的提高查询效率。

限制：
mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。
5.7以后官方支持中文分词。

随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr,elasticSearch等专门的搜索引擎所替代。

Hash索引
Hash索引只有Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。
NoSql采用此中索引结构。
R-Tree索引
R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

相对于b-tree，r-tree的优势在于范围查找。

MySQL索引分类

主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引
随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

unsigned (无符号的)
使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。

CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);

单独建主键索引：

ALTER TABLE customer
add PRIMARY KEY customer(customer_no);

删除建主键索引：

ALTER TABLE customer
drop PRIMARY KEY ;

修改建主键索引：

必须先删除掉(drop)原索引，再新建(add)索引

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

索引建立成哪种索引类型？根据数据引擎类型自动选择的索引类型除开 innodb 引擎主键默认为聚簇索引外。 innodb 的索引都采用的
B+TREE myisam 则都采用的 B-TREE索引

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);
 随表一起建立的索引 索引名同 列名(customer_name)
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name ;

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

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。  
 
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

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

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引) 当表的行数远大于索引列的数目时可以使用复合索引

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  **KEY (customer_no,customer_name)**
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

基本语法
创建 : ALTER mytable ADD [UNIQUE] INDEX【indexName】 ON (columnname(length))
删除：DROP INDEX【indexName】 ON mytable;
查看:SHOW INDEX FROM table_name\G
在这里插入图片描述
使用ALTER命令
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)