《Linux与数据库》课件5. 索引原理基础.pptx

1、索引解决的问题索引解决的问题 提高查询速度 对DML语句附加真正行锁，以减少等待创建大表脚本创建大表脚本1delimiter/CREATE PROCEDURE big_table(cnt int)BEGIN DECLARE i INT DEFAULT 1;SET autocommit=0;DROP TABLE IF EXISTS big_table;CREATE TABLE big_table(id1 INT PRIMARY KEY,id2 INT,data VARCHAR(30);WHILE(i=cnt)DO INSERT INTO big_table VALUES(i,i,CONCAT(r

2、ecord,i);SET i=i+1;END WHILE;COMMIT;SET autocommit=1;END;/delimiter;创建大表脚本创建大表脚本2create procedure big_table(cnt int)begin declare i int default 1;set autocommit=0;drop table if exists big_table;create table big_table(id1 bigint primary key,id2 bigint,data char(100);while(i select*from big_table wher

3、e id2=100;+-+-+-+|id1|id2|data|+-+-+-+|100|100|rec 100|+-+-+-+1 row in set(38.34 sec)影响数据查询速度的主要因素影响数据查询速度的主要因素耗费时间的操作 CPU处理 内存处理 读取磁盘次数读取磁盘次数读取磁盘的单位 数据页 读取磁盘的次数=读取的数据页数读取磁盘时间的决定因素 磁盘读取时间=读取的数据页数 每个数据页平均读取时间innodb_page_size设置数据页大小，可以为4kB，8kB，16kB，默认为16kB mysql show variables like innodb_page_size;+-

4、+-+|Variable_name|Value|+-+-+|innodb_page_size|16384|+-+-+给表分配空间的单位给表分配空间的单位-extent For 4KB,8KB,and 16KB pages sizes,the extent size is 1MB For a 32KB page size,the extent size is 2MB For a 64KB page size,the extent size is 4MB创建主键约束后，表内行的存储方式创建主键约束后，表内行的存储方式 叶节点内容 把行按照主键值从小到大排序 存入分到的数据页 分支节点内容 当叶节点

5、数据页超过1个时，会分配1个新页作为分支节点，在其中存储其下层每个数据在其中存储其下层每个数据页页的第一个的第一个主键主键值及此值所在的数据值及此值所在的数据页页页页号。号。若由于数据量增大，导致分支节点数据页又超过一个，则又会在上层产生一个分支节点，这个数据块也是存储下层每个数据这个数据块也是存储下层每个数据页页的第的第1个值及此列值所在的数据块块号。个值及此列值所在的数据块块号。最上层分支节点总保持一个数据块 这个结构称为这个结构称为B树结构树结构 若未创建主键，也没有not null的唯一约束，则MySQL会自动增加一个6字节的自增整型主键列，其值全局共享，即实例内所有表共享。索引分类索

6、引分类(information_schema.innodb_indexes)0=nonunique Secondary Index 1=automatically generated clustered index(GEN_CLUST_INDEX)2=Unique nonclustered Index 3=Primary Index 32=full-text index 64=spatial index 128=secondary index on a virtual generated column.mysql select name,type from innodb_indexes -wh

7、ere table_id in(select table_id from innodb_tables where name like law/emp);+-+-+|name|type|+-+-+|PRIMARY|3|fk_deptno|0|+-+-+2 rows in set(0.01 sec)普通索引叶节点存储内容普通索引叶节点存储内容 排序后的索引列值及其所在记录的主键值 create index idx on dept(dname)普通索引分支节点普通索引分支节点 当叶节点数据块超过一个时，每个叶节点数据块也会保存其前后数据块的块号，而B树索引会在叶节点上层产生一个新的分支节点，在其中存

8、储其下层每个数据块在其中存储其下层每个数据块的第一个索引列值及此列值所在的数据的第一个索引列值及此列值所在的数据页页页页号。号。若由于索引数据量增大，导致分支节点数据块又超过一个，则又会在上层产生一个分支节点，这个数据这个数据页页也是存储下层每个数据也是存储下层每个数据页页的第一个索引列值及此列值所的第一个索引列值及此列值所在的数据在的数据页页页页号。号。B树索引的最上层分支节点总保持一个数据页B树索引树索引 1971年，波音公司，Rudolf Bayer与Ed McCreight B的含义尚无定论，一般认为表示Boeing、Bayer或Balanced创建和删除索引创建和删除索引 标准语法m

9、ysql create index idx_ename on emp(ename);mysql create index idx_com on emp(empno,ename,sal);mysql drop index idx_ename on emp;MySQL扩展语法mysql alter table t add index idx_b(b desc);mysql alter table t drop index idx_b;mysql alter table emp alter index idx_emp_ename invisible;说明：MySQL不支持alter index语句，

10、desc索引及invisible属性为8.0加入约束和索引约束和索引 自动创建索引的约束类型 主键 唯一 外键 索引的命名 主键约束产生的索引名称总为PRIAMRY 唯一和外键约束创建的索引与约束名称相同mysql use information_schemamysql select i.name,t.name -from innodb_indexes i,innodb_tables t -where i.table_id=t.table_id and t.name=law/t;查询索引信息查询索引信息mysql show index from law.c;设置索引的可见性设置索引的可见性my

11、sql alter table t alter index idx_b invisible;mysql alter table t alter index idx_b visible;Query OK,0 rows affected(0.06 sec)说明：主键索引不能设置为不可见。索引可见性由8.0版本引入使用索引的情形使用索引的情形 单表查询单表查询 where index_col=val(=,between)表连接的连接字段表连接的连接字段 其他其他 min(),max()order by distinct group by 查询只涉及复合索引中的列查询只涉及复合索引中的列explain

12、-查看执行计划查看执行计划 EXPLAIN works with SELECT,DELETE,INSERT,REPLACE,and UPDATE statements.EXPLAIN returns a row of information for each table used in the SELECT statement.It lists the tables in the output in the order that MySQL would read them while processing the statement.只查看预估执行计划 若from子句有子查询，可能会实际执行一

13、部分任务 MySQL resolves all joins using a nested-loop join method.简单的执行计划简单的执行计划mysql explain select ename from emp where empno=7369;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|const|PRIMARY|

14、PRIMARY|2|const|1|100.00|NULL|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)mysql explain select ename from emp where empno 7800;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|ran

15、ge|PRIMARY|PRIMARY|2|NULL|5|100.00|Using where|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)简单的执行计划简单的执行计划mysql alter table emp drop primary key;Query OK,12 rows affected(0.23 sec)Records:12 Duplicates:0 Warnings:0mysql explain select ename from emp where empno=7369;+-+-+-+-+-+-+-+-+-+-+

16、-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|ALL|NULL|NULL|NULL|NULL|12|10.00|Using where|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)mysql explain select ename from emp where empno 7800;+-+-+-+-+

17、-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|ALL|NULL|NULL|NULL|NULL|12|33.33|Using where|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)执行计划中的执行计划中的select_type SIMPLE：未使用子查询，并等 PRIMARY：使

18、用子查询时，最外层的主查询 DERIVED：from子句中的子查询 select*from(select deptno,sum(sal)from emp group by deptno)a SUBQUERY：非相关子查询，如where、select子句 select ename,sal from emp where sal=(select max(sal)from emp)select(select max(sal)from emp)sal_t,ename from emp DEPENDENT SUBQUERY：相关子查询 type(access_type,json格式格式)ALL 全表扫描，

19、效率最低，所以用大写 index perform a full index scan,this may be chosen in combination of using a covering index system used with tables that have exactly one row.This means thetable can be treated as a constant select*from(select 1)a const At most one row is matched for the table,for example,when there is a

20、filter on a single value of the primary key or a unique index select*from emp where empno=7369 type(continue)eq_ref The table is the right-hand table in a join where the condition on the table is on a primary key or not null unique index The eq_ref access type is a specialized case of the ref access

21、 type where only one row can be returned per lookup select*from emp e straight_join dept d on d.deptno=e.deptno ref The table is filtered by a nonunique secondary index select*from emp where deptno=10 index_merge select*from emp where deptno=10 or empno=7369 range The range access type is used when

22、an index is used to look up several values either in sequence or in groups.It is used both for explicit ranges like ID BETWEEN 1 AND 10,for IN clauses,or where several conditions on the same column are separated by ORExtra Using index a covering index is used Using index condition select*from emp wh

23、ere deptno in(10,20)Using where When a WHERE clause is applied to the table without using an index.This may be an indication that the indexes on the table are not optimal.select*from emp where ename=SMITH Using index for group-by Using join buffer(Block Nested Loop)This means that a join is made whe

24、re no index can be used,so the join buffer is used instead.Tables with this message are candidates to have an index added.Using join buffer(Batched Key Access)Using MRR Using filesort Using temporary An internal temporary table is used to store the result of a subquery,for sorting,or for grouping.Fo

25、r sorting and grouping,the use of an internal temporary table can sometimes be avoided by adding an index or rewriting the query.只访问索引的情况只访问索引的情况 查询的列都包含在索引中 执行计划的Extra为using index（主键索引除外）mysql alter table emp add index(sal,ename);Query OK,0 rows affected(0.12 sec)Records:0 Duplicates:0 Warnings:0my

26、sql explain select ename,sal from emp;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|index|NULL|sal|48|NULL|12|100.00|Using index|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)顺序访

27、问索引不再排序顺序访问索引不再排序 索引列出现在order bymysql explain select ename,sal from emp order by sal;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|index|NULL|sal|48|NULL|12|100.00|Using index|+-+-+-+-+-+-+

28、-+-+-+-+-+-+1 row in set,1 warning(0.00 sec)group by使用索引使用索引mysql alter table emp add index(sal);Query OK,0 rows affected(0.04 sec)Records:0 Duplicates:0 Warnings:0mysql show index from emp;+-+-+-+-+-+-+-+-+-+-+-+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Pack

29、ed|Null|Index_type|+-+-+-+-+-+-+-+-+-+-+-+|emp|1|fk_deptno|1|deptno|A|3|NULL|NULL|YES|BTREE|emp|1|sal|1|sal|A|11|NULL|NULL|YES|BTREE|emp|1|sal|2|ename|A|12|NULL|NULL|YES|BTREE|emp|1|sal_2|1|sal|A|11|NULL|NULL|YES|BTREE|+-+-+-+-+-+-+-+-+-+-+-+4 rows in set(0.10 sec)mysql explain select sal,count(*)fr

30、om emp group by sal;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|emp|NULL|index|sal,sal_2|sal_2|5|NULL|12|100.00|Using index|+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set,1 warning(0.01 sec)distinct的处理方式与的

31、处理方式与group by相似相似 自行练习何时创建索引何时创建索引 连接查询时，连接条件列 单表查询时，频繁用作查询条件的列，且重复率不高创建的索引何时不会使用创建的索引何时不会使用 使用了字符串模糊查询，且通配符在第一个位置 对索引列使用了函数，此时可使用基于函数的索引 列值重复率高(MySQL总会使用索引，Oracle支持)基于函数的索引基于函数的索引 使用生成列(5.7)create table emp(empno decimal(2),ename varchar(12),upper_ename varchar(12)as(upper(ename)stored);alter table

32、 emp add upper_ename varchar(12)as(upper(ename)stored;create index idx_upper_ename on emp(upper_ename);使用函数索引(8.0.13)create index idx_up_ename on emp(upper(ename);说明：以上两种方式，效果相同，第二种方式更直观简洁，注意函数外是两对括号。应用实例where upper(ename)=SMITHwhere upper_ename=SMITH生成列生成列-模拟基于函数的索引模拟基于函数的索引(旧版本旧版本)mysql alter tabl

33、e emp add salplus numeric(7,2)as(sal+1)stored;mysql create index idx_salplus on emp(salplus);mysql explain select ename,sal from emp where salplus=801;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-+|1|SIMPLE|e

34、mp|NULL|ref|idx_salplus|idx_salplus|5|const|1|100.00|NULL|+-+-+-+-+-+-+-+-+-+-+-+-+mysql explain select ename,sal from emp where sal+1=801;mysql explain select ename,sal from emp where 1+sal=801;使用索引使用索引-恰当设计查询条件恰当设计查询条件 WHERE mycol*2 WHERE mycol 4/2 WHERE YEAR(date_col)WHERE date_col analyze table

35、t update histogram on a,b with 5 buckets;mysql analyze table t drop histogram on a,b;查询直方图 information_schema.column_statisticsbucket类型类型 singleton(bucket个数不小于distinct列值个数)Value 1:The value for the bucket.Value 2:A double representing the cumulative frequency for the value.equi-height(bucket个数小于dist

36、inct列值个数)Values 1,2:The lower and upper inclusive values for the bucket.The type depends on the column data type.Value 3:A double representing the cumulative frequency for the value.Value 4:The number of distinct values in the range from the bucket lower value to its upper value.直方图实例直方图实例(使用使用sakil

37、a示例数据库的示例数据库的film表表)mysql analyze table film update histogram on length with 5 buckets;mysql select*from information_schema.column_statistics where table_name=filmG*1.row*SCHEMA_NAME:sakila TABLE_NAME:filmCOLUMN_NAME:length HISTOGRAM:buckets:46,73,0.196,28,74,101,0.397,28,102,128,0.601,27,129,156,0.

38、8,28,157,185,1.0,29,data-type:int,null-values:0.0,collation-id:8,last-updated:2018-09-10 07:11:38.593435,sampling-rate:1.0,histogram-type:equi-height,number-of-buckets-specified:51 row in set(0.00 sec)index statistics相关概念相关概念 index statistics are a measure of the selectivity of the index.There are t

39、wo main statistics the number of unique values-cardinality the number of values in some range-histogram two ways to store the statistics persistent-MySQL 5.5引入，以替换不稳定的transient方式 transient -表打开时计算统计信息，并存入内存 When collecting index statistics,the choice has been made to use read uncommitted.persistent

40、statistics相关参数相关参数 控制参数 系统级 innodb_stats_persistent-控制是否使用persistent统计信息，默认为1 innodb_stats_persistent_sample_pages innodb_stats_auto_recalc-改变的行数达到10%时，是否自动更新统计信息 innodb_stats_include_delete_marked-只有系统级 表级 STATS_PERSISTENT-控制表级是否使用persistent统计信息 STATS_AUTO_RECALC STATS_SAMPLE_PAGES-设置表的属性 mysql alt

41、er table t STATS_SAMPLE_PAGES 30;-STATS_SAMPLE_PAGES=30说明：for multicolumn indexes,the number of pages is per column.transient statistics相关参数相关参数 innodb_stats_transient_sample_pages-default 8 innodb_stats_on_metadata-一般不宜开启 When that is enabled,querying the TABLES and STATISTICS tables in the Informa

42、tion Schema or using their equivalent SHOW statements triggers an update of the index statistics.说明：没有专门的表用于存储临时统计信息null的处理的处理 设置innodb_stats_method nulls_equal-default nulls_unequal nulls_ignored 计算统计信息时使用的事务隔离级别计算统计信息时使用的事务隔离级别 read uncommitted 设置默认统计信息的存储方式设置默认统计信息的存储方式 innodb_stats_persistent-系统

43、级 STATS_PERSISTENT-表级 ALTER TABLE world.city STATS_PERSISTENT=1;persistent统计信息更新方式统计信息更新方式 analyze命令mysql analyze table t1,t2;innodb_stats_auto_recalc 10%的行被改变后，是否重新计算统计信息 系统变量information_schema_stats_expiry 用于设置自动更新统计信息的间隔 默认值为86400秒(24小时)若设置为0，则查询时总得到最新统计信息(statistics和tables视图)persistent统计结果保存位置统计

44、结果保存位置 mysql.innodb_index_stats mysql.innodb_table_stats说明：这两个表可以手工修改，修改后需执行：FLUSH TABLE tbl_name，才能加载新的statistics transient统计信息更新方式统计信息更新方式 A table is first opened Running SHOW TABLE STATUS,SHOW INDEX,or querying the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables with the innodb_

45、stats_on_metadata option enabled.Starting a mysql client with the-auto-rehash option enabled,which is the default.The autorehash option causes all InnoDB tables to be opened,and the open table operations cause statistics to be recalculated 6.25%(1/16)of the rows have changed with a requirementthat a

46、t least 16 updates have occurred 查看统计信息查看统计信息 mysql.innodb_index_stats mysql.innodb_table_stats information_schema.innodb_tablestats 包含persistent及transient统计信息 information_schema.statistics information_schema.tables show index from.-以上两个方式的结果相同 show extended index from.包含索引中的所有列，如主键索引包含整行，普通索引包含主键列

47、DB_TRX_ID-6B B_ROLL_PTR-7B，指向undo log，实现MVCC执行计划使用直方图执行计划使用直方图(以以film.length为例为例)mysql explain select*from film where length explain select*from film where length=76;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+

48、-+-+|1|SIMPLE|film|NULL|ALL|NULL|NULL|NULL|NULL|1000|0.72|Using where|+-+-+-+-+-+-+-+-+-+-+-+-+在在length列建立索引后列建立索引后mysql explain select*from film where length 160;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+-+-+-+-+-

49、+|1|SIMPLE|film|NULL|range|idx_film_length|idx_film_length|3|NULL|176|100.00|Using index condition|+-+-+-+-+-+-+-+-+-+-+-+-+mysql explain select*from film where length 100;+-+-+-+-+-+-+-+-+-+-+-+-+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+-+-+-+-+-+-+-+

50、-+-+-+-+-+|1|SIMPLE|film|NULL|ALL|idx_film_length|NULL|NULL|NULL|1000|61.00|Using where|+-+-+-+-+-+-+-+-+-+-+-+-+查看全表扫描的表查看全表扫描的表 sys.schema_tables_with_full_table_scans 基于performance_schema.table_io_waits_summary_by_index_usage sys.statements_with_full_table_scans 基于performance_schema.events_statem