mysql索引综述

最新推荐文章于 2022-12-11 21:07:23 发布

无畏D尘埃

最新推荐文章于 2022-12-11 21:07:23 发布

阅读量331

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_37735385/article/details/89501959

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、索引概述

简介

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引是什么:可以简单理解为：排好序的快速查找数据结构（索引+排序）

定义：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

图释：左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。.
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上.
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

基本语法

说明：索引名的命名规范为【idx_表名_列名1_列名2…】

创建索引，如果加unique则会创建唯一索引

ALTER [表名] ADD 【UNIQUE】INDEX [索引名] ON ([字段1],[字段2]……)
索引也可以在创建表的同时指定，如下所示。

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

删除索引
DROP INDEX [索引名] ON [表名];
查询索引
SHOW INDEX FROM [表名]\G
使用ALTER命令

有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

二、索引结构

BTree索引

索引原理

【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3。
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
【真实的情况是】 3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+Tree索引

聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。数据行在磁盘的排列和索引排序保持一致。
术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。
聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
聚簇索引的限制：
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。（参考聚簇索引的好处。）
这里说明了主键索引为何采用自增的方式：1、业务需求，有序。2、能使用到聚簇索引

full-text全文索引

Hash索引

R-Tree索引

三、索引分类

主键索引

设定为主键后数据库会自动建立索引，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+TREEmyisam 则都采用的 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 ;

复合索引

基本语法

四、索引的运用

需要创建索引的情况

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
A 表关联 B 表：A join B 。on 后面的连接条件既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率。因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（索引顺序和用索引顺序相同）
group by 和 order by 后面的字段有索引大大提高效率
查询中统计或者分组字段

不要创建索引的情况

表记录太少
经常增删改的表
Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
Where等其他条件里用不到的字段不创建索引（索引建多了影响增删改的效率）
数据重复且分布平均的表字段不要创建索引，因此应该只为最经常查询和最经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

五、性能分析

SQL常见瓶颈

CPU

SQL中对大量数据进行比较、关联、排序、分组

实例内存满足不了缓存数据或排序等需要，导致产生大量物理 IO。

查询执行效率低，扫描过多数据行

锁

不适宜的锁的设置，导致线程阻塞，性能下降。

死锁，线程之间交叉调用资源，导致死锁，程序卡住。

服务器硬件的性能瓶颈

top,free, iostat和vmstat来查看系统的性能状态

六、优化案例

单表优化案例

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

SELECT * FROM article;

#查询 category_id 为1 且  comments 大于 1 的情况下,views 最多的 article_id。 
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

在这里插入图片描述
结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

#开始优化：
# 1.1 新建索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article
\# 1.2 第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;

#结论：
#type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
#但是我们已经建立了索引,为啥没用呢?
#这是因为按照 BTree 索引的工作原理,
# 先排序 category_id,
# 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
#当 comments 字段在联合索引里处于中间位置时,
#因comments > 1 条件是一个范围值(所谓 range),
#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

# 1.3 删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;

# 1.4 第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);

# 1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
DROP INDEX idx_article_cv ON article;

两表优化案例
三表优化案例
其他案例
假设我们在c1、c2、c3、c4创建了符合索引

c3是个范围，所以之后的c4不能利用索引，c3只是用了索引的排序。
都是常量，mysql底层会有优化，实际的顺序是c1-c2-c3-c4，前三个肯定用到索引，最后一个是范围范围后的失效就失效，反正是最后一个，但是c4用到了排序，没有出现filesort。
c1和c2用到索引，c3用到了索引的排序。c4什么都没用到，到c4就断了
基本同上
可以看到没有c3，在这已经断了，所以c1和c2用到了索引，c4不能用到索引，出现文件内排序。
c1用到索引c2和c3用索引进行了排序。

7.看c3和c2的顺序，不是按照索引的顺序，出现了文件内排序。
虽然有两个c2，但是也是按照索引的顺序，无文件内排序。
就只多了c5，他就是一个常量，迷惑项，c2和c3还是用到了索引的排序功能。
虽然是c3.c2，仍然没有文件内排序，是因为c2在前面已经定义为一个常量，c2在order by中一点用也没有，它就是一个迷惑项，对比项请看下面。*
作为上一个的对比去看
group by中c2和c3用到索引，记住，分组之前必排序，只不过这不是范围查询否则就是range类型了。
出现文件内排序，而且，c3和c2没用到索引，导致临时表的产生！

七、索引优化

以下都以此表为基础

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;

全值匹配

索引  idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的

【加入复合索引】
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

1、会用到索引name
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
2、会用到索引name、age
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
3、会用到索引name、age、pos
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
4、会用到索引name，但是pos索引不会用到，因为中间的age断了（了解下B-Tree工作原理）
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev';
5、不会用到索引，带头大哥name如果没有了，则不能用到索引
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';

最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
经过试验结论  建立了 idx_nameAge 索引  id 为主键
    1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
    2.除开上述条件 才满足最左前缀法则。
 
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

不会用到索引，因为使用了函数，或计算在索引字段上。
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

在这里插入图片描述

存储引擎不能使用索引中范围条件右边的列
范围若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)

因为age这个字段的索引对应的是个范围，范围后面的字段的索引都会失效！比如in，between等，是范围后面的失效。
这个范围的这个字段用到了索引的排序功能，
因为索引有两个功能：排序+索引。
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age >25 AND pos = 'dev';

在这里插入图片描述

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描（迫不得已就这样写）
is not null 也无法使用索引,但是is null是可以使用索引的
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作（重点）
问题：解决like '%字符串%'时索引不被使用的方法？用覆盖索引！请看以下sql！

CREATE TABLE `tbl_user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR(20) DEFAULT NULL,
	`age` INT(11) DEFAULT NULL,
	`email` VARCHAR(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');

创建索引
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';

解释：
1、创建索引前以上所有类似like '%字符串%'都不会利用到索引，创建覆盖索引后以上都可以用到索引。
2、创建覆盖索引，只要查询的字段包含在索引内类似like '%字符串%'都能利用到索引。
3、类似like '%字符串%'，但凡查询的字段有一个不在覆盖索引内，就不会利用索引。如以下sql
EXPLAIN SELECT *     FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age,email  FROM tbl_user WHERE NAME LIKE '%aa%';

如果在name和age创建了索引，如以下情况，where条件最近的不是name也没关系，mysql优化器会自动进行优化，name和age索引都会利用到。
有个前提，条件必须是常量。如：800 33必须是确定的数。
EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

字符串不加单引号索引失效，必须极其重视！
不管是字符串隐式转换为数值型，还是数值型隐式转换为字符串，都会导致索引失效。但是都能查出数据是因为优化器自动做的隐式转换。
少用or,用它来连接时会索引失效
以下sql已经事先在name和name建立了索引，但是用or连接，会导致索引失效。但是用or的情况挺多，迫不得已也就用。失效也就失效吧。
小总结

无畏D尘埃

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql索引综述

索引综述一、索引概述简介优势劣势语法二、索引结构BTree索引B+Tree索引聚簇索引与非聚簇索引full-text全文索引Hash索引R-Tree索引三、索引分类主键索引单值索引唯一索引复合索引基本语法四、索引的运用需要创建索引的情况不要创建索引的情况五、性能分析六、性能优化一、索引概述简介优势劣势语法二、索引结构BTree索引B+Tree索引聚簇索引与非聚簇索引full-t...
复制链接

扫一扫