sql

mysql-锁引

2021-04-07  本文已影响0人  IT雪山

1 锁引介绍

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目
录，能加快数据库的查询速度。
官方文档：
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

2 锁引分类

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。

create table student (

id INT UNSIGNED AUTO_INCREMENT,

name VARCHAR(255),

PRIMARY KEY(id),

KEY(name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。

也就是说查询name='Arle'的记录时，首先通过name索引表查找到Arle的主键id（可能有多个主键id，因为有重名的同学），再根据主键id的聚集索引找到相应的行记录；

聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

每张表只有一个聚集索引，因为聚集索引在精确查找和范围查找方面良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增id充当聚集索引）。

从宏观上分析下聚集索引和普通索引的性能差异，还是针对上述student表：

（1）select * from student where id >5000 and id <20000;

（2）select * from student where name > 'Alie' and name < 'John';

第一条SQL语句根据id进行范围查询，因为(5000, 20000)范围内的记录在磁盘上按顺序存储，顺序读取磁盘很快就能读到这批数据。

第二条SQL语句查询（'Alie', 'John'）范围内的记录，主键id分布可能是离散的1，100，20001，5000.....；增加了随机读取数据页几率；所以普通索引的范围查询效率被聚集索引甩开几条街都不止；非聚集索引的精确查询效率还是可以的，比聚集索引查询只增加了一次IO开销。

创建锁引

单列锁引-普通锁引
CREATE INDEX index_name ON table(column(length)) ; ALTER TABLE table_name ADD INDEX index_name (column(length)) ;
单列锁引-唯一锁引
CREATE UNIQUE INDEX index_name ON table(column(length)) ; alter table table_name add unique index index_name(column);
单列锁引-全文锁引
CREATE FULLTEXT INDEX index_name ON table(column(length)) ; alter table table_name add fulltext index_name(column)
组合索引
ALTER TABLE article ADD INDEX index_titme_time (name1(50),name2(10)) ;

删除锁引

DROP INDEX index_name ON table

查看索引

SHOW INDEX FROM table_name

3 锁引原理

锁引是在存储引擎中实现的，不同的存储引擎会使用不同的锁引，MyISAM和InnoDB存储引擎只支持B+TREE锁引
B树和B+树
数据结构示例网站：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B树是为了磁盘或其他存储设备而设计的一种多叉平衡查找树

B和B+的区别
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
B树是非叶子节点和叶子节点都会存储数据。
B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。


B -Tree B+Tress

4 锁引使用场景

需要创建锁引：

组合锁引


在一颗索引树上由多个字段

执行计划

explain出来的信息有10列，分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
举例：

--用户表 create table tuser( id int primary key, loginname varchar(100), name varchar(100), age int, sex varchar(100), dep int, address varchar(100) );
--部门表 create table tdep( id int primary key, name varchar(100) );
--地址表 create table taddr( id int primary key, addr varchar(100) );

--创建普通索引 mysql> alter table tuser add index idx_dep(dep);
--创建普通索引 mysql> alter table tdep add index idx_name(name);
--创建唯一索引 mysql> alter table tuser add unique index idx_loginname(loginname); 
--创建组合索引 mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--创建全文索引 mysql> alter table taddr add fulltext ft_addr(addr);


INSERT into tuser VALUES(1, 'zhangshan', '张三', 10, '', 2, '辽宁');
INSERT into tuser VALUES(2, 'zhangshan2', '张三2', 12, '', 2, '辽宁1');
INSERT into tuser VALUES(3, 'zhangshan3', '张三3', 13, '', 3, '辽宁3');
INSERT into tuser VALUES(4, 'zhangshan4', '张三4', 14, '', 4, '辽宁4');
INSERT into tuser VALUES(5, 'zhangshan5', '张三5', 15, '', 5, '辽宁5');

id

1,每个 SELECT语句都会自动分配的一个唯一标识符.
2,表示查询中操作表的顺序，有三种情况：
　　id相同：执行顺序由上到下
　　id不同：如果是子查询，id号会自增，id越大，优先级越高。
　　id相同的不同的同时存在
3, id列为null的就表示这是一个结果集，不需要使用它来进行查询。

select_type*

查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。

table

type*

依次从好到差：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
优化器会选用最优索引 一个
最少要索引使用到range级别。

explain select * from tuser where id=1;



explain select * from tuser where loginname = 'zhangshan';


explain select a.id from tuser a left join tdep b on a.dep=b.id;


等值非主键连接
explain select a.id from tuser a left join tdep b on a.name=b.name;


最左前缀
explain select * from tuser where name = '张三';



ref :const
key_len 403
使用name锁引

explain select * from tuser where name = '张三' and age=10;



ref :const,const
key_len 408
使用name, age锁引

explain select * from tuser where name = '张三' and age=10 and sex='11';


ref :const,const,const
key_len 811
使用name, age, sex锁引

explain select * from tuser where age=10 and sex='1';



全表扫描

explain select * from tuser where name = '张三' and sex='1';



只用到了name锁引

结论： 最佳左前缀法则：带头大哥不能死、中间兄弟不能断

explain select * from tuser where name like 'a%';


explain select age from tuser;



组合索引

explain select name,age,sex from tuser;



覆盖锁引

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

ref

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原
因是InnoDB里面使用了MVCC并发机制）

extra*

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有

索引失效：
explain select * from tuser where age=1;


截屏2021-04-08 16.46.19.png

备注：
查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
explain select * from tuser where name='张三' and sex='10'

根据官方解释：
EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using indexbecause that does not apply when full table rows must be read.

锁引失效分析*

a :explain select * from tuser where name='aa' and sex='1' and age=23;


b: explain select * from tuser where name='aa' and sex=1 and age=23;



比较a,b
sex类型不一致，导致b只用了name 和age锁引

explain select name,age,sex from tuser ;


explain select * from tuser ;


explain select address from tuser ;


explain select loginname from tuser ;


explain select * from tuser where loginname='zhangshan';


explain select * from tuser where loginname!='zhangshan';


截屏2021-04-08 17.45.05.png

explain select * from tuser where dep!=7;


explain select * from tuser where dep=7;


问题：解决like ‘%字符串%’时，索引失效问题的方法？ 使用覆盖索引可以解决
explain select name ,age,sex from tuser where name like '%a%';


口诀：
全值匹配我最爱， 最左前缀要遵守
带头大哥不能死，中间兄弟不能断
锁引列上少计算，范围之后全失效
LIKE百分写最后，覆盖锁引不写星
不等空值还有or， 锁引失效要少用

上一篇 下一篇

猜你喜欢

热点阅读