索引
InnoDB采用了一个B+数来存储索引,使得在千万级数据量的一个情况下,树的高度可以控制在3层以内,而层高代表磁盘IO的一个次数,因此基于索引查找可以减少磁盘IO的次数
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种
二叉树缺点: 顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。
BTree(多路平衡查找树)
每个节点可以包含多个子节点。相比于二叉搜索树,B+树的节点可以存储更多的关键字,减少了树的高度,提高了检索效率。
以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key,5个指针):
知识小贴士: 树的度数指的是一个节点的子节点个数。
B+Tree
相对于B-Tree区别:
-
所有的数据都会出现在叶子节点
-
叶子节点形成一个单向链表
-
B+树非叶子节点不存诸数据,所以每一层能够存诸的索引数量会增加,意味着B+树在层高相同的情况下存诸的数据量要比B树要多,使得磁盘IO次数更低。
-
Mysql中,范围查询是一个比较常用的操作,而B+树的所有存诸在叶子节点的数据使用了双向链表来关联,所以在查询的时候只需查两个节点进行遍历就行,而B树需要获取所有节点,所以B+村在范围查询上效率更高
-
在数据检索方面,由于所有的数据都存诸在叶子节点,所以B+树的IO 次数会更加稳定一些
-
因为叶子节点存诸所有数据,所以B+树的全局扫描能力更强一些,因为它只需要扫描叶子节点。但是B树需要遍历整个树
MySQL的B+Tree
MVSQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
索引分类
也叫聚簇索引和非聚簇索引
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则innoDB会自动生成一个rowid作为隐藏的聚集索引
面试官:什么是聚簇索引什么是非聚簇索引 ?
候选人:
聚簇索引主要是指数据与索引放到一块,B+树的叶子节点保存了整行数据,聚簇索引有且只有一个,一般情况下主键在作为聚簇索引的
.
非聚簇索引值的是数据与索引分开存储,B+树的叶子节点保存对应的主键,可以有多个,一般我们自己定义的索引都是非聚簇索引
回表查询
面试官:知道什么是回表查询嘛 ?
候选人:嗯,其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的,回表的意思就是通过二级索引找到对应的主键值,然后再通过主键值找到聚集索引中所对应的整行数据,这个过程就是回表
面试官:知道什么叫覆盖索引嘛 ?
候选人:嗯~,清楚的
~
覆盖索引是指select查询语句使用了索引,在返回的列,必须在索引中全部能够找到,而无需回表查询所需的列,如果我们使用id查询,它会直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。
~
如果按照二级索引查询数据的时候,返回的列中没有创建索引,有可能会触发回表查询,尽量避免使用select *,尽量在返回的列中都包含添加索引的字段
举例来说,假设有一个表 orders,其中包含 order_id、customer_id 和 order_date 等列。如果你经常执行类似的查询:
SELECT order_id FROM orders WHERE customer_id = 123;
你可以创建一个覆盖索引,该索引包含 (customer_id, order_id) 列,这样查询就可以直接从索引中获取所需的 order_id 列,而不需要访问表的数据页。
面试官:MYSQL超大分页怎么处理 ?
候选人:嗯,超大分页一般都是在数据量比较大时,我们使用了limit分页查询,并且需要对数据进行排序,这个时候效率就很低,我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段,确定了id之后,再用子查询来过滤,只查询这个id列表中的数据就可以了
因为查询id的时候,走的覆盖索引,所以效率可以提升很多
索引语法
创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 ON 表名 (列名,... ) ;
查看索引
SHOW INDEX FROM 表名 ;
删除索引
DROP INDEX index name ON 表名 ;
哪些情况适合建索引
- 数据量较大,且查询比较频繁的表(300w以上建)
- 常作为查询条件、排序、分组的字段,排序字段若通过索引去访问将大大提高排序速度
- 字段内容区分度高
- 内容较长,使用前缀索引
- 在高并发下倾向创建组合索引
- 要控制索引的数量
- 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它
哪些情况不适合建索引
- Where条件里用不到的字段不创建索引
- 表记录太少(300w以上建)
- 经常增删改的表(提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件)
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。(比如:国籍、性别)
- 假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率天约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高
explain执行计划
EXPLAIN
或者 DESC
命令获取 MySQL
如何执行 SELECT
语句的信息,包括在 SELECT
语句执行过程中表如何连接和连接的顺序。
语法:
直接在select
语句之前加上关键字 explain
/ desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
字段解释
id
:表的读取顺序
select
查询的序列号,包含一组数字,表示查询中执行select
子句或操作表的顺序
id
相同,执行顺序从表格由上至下id
不同,如果是子查询,id
的序号会递增,id
值越大优先级越高,越先被执行
select_type
: 数据读取操作的操作类型
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
SIMPLE
:简单的select
查询,查询中不包含子查询或者UNION
PRIMARY
:查询中若包含任何复杂的子部分,最外层查询则被标记为(最后加载的那个)SUBQUERY
:在SELECT
或WHERE
列表中包含了子查询DERIVED
:在FROM
列表中包含的子查询被标记为DERIVED
(衍生)MySQL
会递归执行这些子查询,把结果放在临时表里UNION
:若第二个SELECT
出现在UNION
之后,则被标记为UNION
;若UNION
包含在FROM
子句的子查询中外层SELECT
将被标记为:DERIVED
UNION RESULT
:从UNION
表获取结果的SELECT
(两个select
语句用UNION
合并)
table
:显示执行的表名
显示这一行的数据是关于哪张表的
type
: sql的连接的类型
这条sql
的连接的类型,性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
system
:查询系统中的表const
:根据主键索引查询eq_ref
:主键索引查询或唯一索引查询,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。ref
:索引查询,非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体range
:范围查询,只检索给定范围的行,使用一个索引来选择行。key
列显示使用了哪个索引一般就是在你的where
语句中出现了between、<、>、in
等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引index
:索引树扫描,index
与ALL
区别为index
类型只遍历索引列。这通常比ALL快,因为索引文件通常比数据文件小(也就是说虽然all和Index都是读全表,但index
是从索引中读取的,而all
是从硬盘中读的)all
:全盘扫描
possible_key
: 当前sql可能会使用到的索引
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段火若存在索引,则该索引将被列出,但不一定被查询实际使用(系统认为理论上会使用某些索引)
key
当前sql实际命中的索引
实际使用的索引。如果为NULL
,则没有使用索引(要么没建,要么建了失效)
查询中若使用了覆盖索引,则该索引仅出现在key列表中
key_len
索引占用的大小
可通过该列计算查询中使用的索引的长度。
含义是:The length of the chosen key,所选键的长度。其单位是字节。
根据这个值,就可以判断索引使用情况。比如当key_len
列显示为NULL
时,key
列也就会显示为NULL
, 说明语句没有用到索引。比如在使用组合索引的时候,判断是否所有的索引字段是否都被用到。
如何根据key_len
的值判断是否所有的索引字段都被用到,就要知道key_len
的计算规则。
key_len
的计算规则:
-
可以为
NULL
的列的key
长度比非NULL
列的key
长度大1。CREATE TABLE `a_test` ( `id` int(4) unsigned NOT NULL AUTO_INCREMENT, `server_id` int(4) NOT NULL DEFAULT <span style="color:#98c379">'0'</span>, `user_id` int(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_server_id` (`server_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
如上所示,当使用
idx_user_id
索引时,key_len
的值是5(int
类型长度4+1),而使用idx_server_id
索引时,key_len
的值是4(仅为int
类型长度4)。 -
如果索引列是字符型(
char
)字段,则索引列数据类型本身占用空间跟字符集有关。不同的字符集下,同一个字符存储到表中的时候,它所占用的空间大小是不同的。一个字符存储在表中,到底占用多少个字节
byte
,需要根据不同的字符集来分别计算。常用的几种字符集下,字符
character
和字节byte
的换算关系如下:字符集 1个字符占用字节数(Maxlen) GBK 2 UTF8 3 UTF8mb4 4 latin1 1 -
如果索引列是变长的(比如varchar),则在索引列数据类型本身占用空间的基础上再加2。
我们把上面的char类型替换成varchar。
ref
:表之间的引用
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
Extra
额外的优化建议
如果一条sql执行很慢的话,我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况,比如在这里面可以通过key和key_len检查是否命中了索引,如果本身已经添加了索引,也可以判断索引是否有失效的情况
第二个,可以通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描
第三个可以通过extra建议来判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复
面试官:了解过索引吗?(什么是索引)
候选人:嗯,索引在项目中还是比较常见的,
- 它是帮助MySQL高效获取数据的数据结构,
- 主要是用来提高数据检索的效率,降低数据库的IO成本,
- 同时通过索引列对数据进行排序,降低数据排序的成本,也能降低了CPU的消耗
面试官:索引的底层数据结构了解过嘛 ?
候选人:MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引,选择B+树的主要的原因是:
- 第一阶数更多,路径更短
- 第二个磁盘读写代价B+树更低,非叶子节点只存储指针,叶子阶段存储数据
- 第三是B+树便于扫库和区间查询,叶子节点是一个双向链表
索引优化
1.索引单表优化案例
sql语句是这样的
SELECT
id, author_id FROM article
WHERE
category_id = 1
AND comments > 1
ORDER BY views DESC LIMIT 1;
如果我们给这三个字段创建索引:
create index idx_article_ccv on article(category_id,comments,views);
因comments > 1
条件是一个范围值查询,MySQL无法利用索引再对后面的views
部分进行检索,即range
类型查询字段后面的索引无效,还产生了Using filesort
索引用处不大,删除:
DROP INDEX idx_article_ccv ON article;
上次创建索引相比,这次不为comments
字段创建索引:
结论:type
变为了ref
,ref
中是 const
,Extra
中的 Using filesort
也消失了,结果非常理想
2.索引多表优化案例
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
Explain
分析:type
都是all
,需要优化(总有一个表来添加索引驱动)
EXPLAIN SELECT *FROM class LEFT JOIN book ON class.card = book.card;
type
都是all
,这是肯定要优化的
不同的连接方式,索引加的位置也不一样,我们这里是左连接,我们就已左连接为例:
-
我们为左表加索引
ALTER TABLE class ADD INDEX card_idx(card);
这个时候
Type
变成Index
,但rows
都还是4条 -
我们为左表加索引
--删除原来的索引: drop index card_idx on class; ALTER TABLE book ADD INDEX card_idx(card);
可以看到右表的type
变为ref
,并且rows
只有1行,优化非常明显
结论:
这是由左连接特性决定的。LEFT JOIN
条件用于确定如何从右表搜索行,保留所有左表的行,所以左表一定会全部遍历,所以右边是我们的关键点,一定需要在右表建立索引(并且尽量用小表驱动大表),同理:右连接,左表加索引
3.索引三表优化案例
同样使用上一节两个表,删除他们的索引:
DROP INDEX card_idx on book
再新建一个表
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
三表查询语句应为:
SELECT * FROM class
LEFT JOIN book ON class.card = book.card
LEFT JOIN phone ON book.card = phone.card;
按两表优化的案例,应该为第一个LFET JOIN
的右表 book
建索引
alter table `book` add index card_idx (`card`);
再为第二个LFET JOIN
的右表 phone
建索引
alter table `phone` add index card_idx (`card`);
Explain
分析:
后2行的 type
都是ref
且总 rows
优化很好,效果不错。因此索引最好设置在需要经常查询的字段中
索引失效
1.最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。
最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)
以下是一个最左前缀法则的例子:
假设有一个复合索引包含了 last_name 和 first_name 列,即 (last_name, first_name)。那么,在查询中,以下条件将满足最左前缀法则:
查询:SELECT * FROM employees WHERE last_name = 'Smith'
查询:SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John'
查询:SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Smith'
而以下条件不满足最左前缀法则:
查询:SELECT * FROM employees WHERE first_name = 'John'
对于联合索引(col1,col2,col3),查询语句SELECT * FROM test WHERE col2=2;
是否能够触发索引?
答:可以,但用的是index类型的索引。
index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引
2.范围查询
联合索引中,出现范围查询(>,<,between、like)),范围查询右侧的列索引失效
explain select * from tb user where profession = 软件工程' and age > 30 and status = '0':
#创建一个联合索引, 注意字段的顺序
create index idx_age_classid_name on student(age,classid,name);
#执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc' ;
第一个没用全,只用到了联合索引“idx_age_classid_name” 的age和classid。
#再创建一个联合索引,与上面的索引对比字段顺序变了
create index idx_age_name_classid on student(age,name,classid);
#再执行一模一样的执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;
看到两个执行计划虽然都用到了索引,但是:
第二个把联合索引“idx_age_name_classid”的age,name和classid都用上了。
3.索引列运算
不要在索引列上进行运算操作,索引将失效
explain select * from tb user where substring(phone,10,2) = '15';
4.字符串不加引号
字符串类型字段使用时,不加引号,索引将失效
explain select * from tb user where profession = 软件工程 and age = 31 and status = 0;
explain select * from tb user where phone = 17799990015;
5.模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
explain select * from tb user where profession like '软件%'; #不失效
explain select * from tb user where profession like '%工程'; #失效
explain select * from tb user where profession like '%工%'; #失效
6.or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
explain select * from tb user where id = 10 or age = 23;
explain select * from tb user where phone = !17799990017' or age = 23;
由于age没有索引,所以即使id、phone有索引,索引也会失效。所以需要针对于age也要建立索引。
7.数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
多个单列索引在多条件查询时优化器会选择最优索引策略,可能只用一个索引,也可能将多个索引全用上! 但多个单列索引底层会建立多个B+索引树,比较占用空间,也会浪费一定搜索效率,故如果只有多条件联合查询时最好建联合索引!
数据分布不均匀: 如果索引列的数据分布不均匀,例如某个值出现频率过高,可能会导致优化器不选择使用索引。
索引选择性低: 如果索引的选择性很低,即索引列中的不同值很少,优化器可能会选择不使用索引。