-
数据表中建立索引的目的:
-
B+Tree索引:就是为表建立"目录",索引的目的就是为了防止全表扫描,索引的存储形式是由存储引擎决定。
-
数据表中索引分类:
- 从存储结构上来划分:BTree索引(B-Tree或B+Tree索引),Hash索引、full-index全文索引、R-Tree索引。
- 从应用分层类划分:普通索引、唯一索引、复合索引
- 从数据的物理顺序与键值的逻辑(索引)顺序关系:聚集索引(实际数据的顺序与索引中的数据是一致的)、非聚集索引(实际数据的顺序与索引中的数据不一致)
-
MySQL常用索引:
- B+Tree:适用于范围查找(id从2到10)
- Hash索引:适用于精确匹配(name为XX的数据)
-
B+Tree索引:InnoDB与MyISAM采用的是B+Tree索引。
-
B+Tree采用的是树形链表结构建立数据"目录"。
-
索引本身也存储在磁盘中,索引中存储的不是表数据,而是数据的目录。
-
B+Tree:在底层对数据进行了聚集排列, 很容易进行范围查找。
-
InnoDB中使用自增长索引的话,在新增数据的时候,只需要在底层的额队列中新增一个数据,再调整上面的部分节点,计算量会很少。
-
InnoDB中B+Tree索引的聚集索引,MyISAM中的索引为非聚集索引。
-
InnoDB中B+Tree索引是以数据的主键来组织数据的,MyISAM中B+Tree索引是以数据实际存储的物理地址来组织数据的。
-
B-Tree与B+Tree的区别:
都是以树的形式组织数据,B+Tree的最底层采用单向链表的形式组织数据,B-Tree的最底层没有单向链表;B+Tree中所有的数据都能在最底层中查找到,B-Tree中的某些数据保存在树的上层节点,在查找的时候,需要遍历树中的所有节点。所以B+Tree比B-Tree更适合作为索引。
-
查看SQL执行计划:
EXPLAIN select * from orders_126 where id = 5;
在查询的额结果中,key的值表示查询中使用的索引,type为All时表示全表扫描。
-
在表中对普通索引的操作:
create index index_orderId on orders_126(orderId); --添加索引 drop index index_orderId on orders_126; --删除索引
-
索引的使用技巧:
- 精准匹配,允许使用BTree索引;
- 范围匹配允许使用索引;
- 查询优化器会自动进行类型转化,但仍然建议使用与定义相符的类型;
- 字符串字段btree索引允许进行前缀查询,虽然支持,但是效率依然不高;
- 后缀查询与模糊匹配btree均不支持;
- 符合索引查询条件必须包含左侧列;
- 如果直接使用右侧列进行查询的话,索引将无法使用;
- 查询条件中使用<>和not in将导致索引将无法使用;
-
Hash索引:基于哈希表实现,Hash索引会为每一条数据生成一个HashCode
-
只有对数据进行精准匹配索引列的时候才有效。
-
在建表的时候使用Hash索引(MySQL中目前只有MEMORY显式支持Hash索引):
CREATE TABLE test_hash ( fname VARCHAR (50) NOT NULL, lname VARCHAR (50) NOT NULL, KEY USING HASH (fname) ) ENGINE = MEMORY;
-
在插入数据的时候,Hash索引会为该条数据的索引字段生成一个hashcode,等下次精确查询的时候,Hash索引会先根据查询的条件生成一个hashcode,再根据hashcode去查询对应的数据。
-
Hash索引的特点:
- Hash索引只包含hash值和行指针;
- 只支持精准匹配,不支持范围查询、模糊查询以及排序;
- Hash索引是非聚集索引;
- Hash索引取值的速度非常的快,但索引选择性很低时不建议使用(男、女);
- MySQL中目前只有MEMORY显式支持Hash索引(再创建表的时候,使用USING HASH)
-
InnoDB中的Hash索引:
- InnoDB存储引擎只支持显式创建BTree索引;
- 数据精准匹配时,MySQL会自动生成一个HashCode,存入缓存(内存)。
-
索引的优点:
- 索引大幅度提高了数据的检索效率;
- 索引把随机IO,变成了顺序IO。
-
索引不是越多越好:
- 降低了写入数据的效率;
- 太多的索引增加了查询优化器的选择时间;
- 不合理的使用索引,会大幅占用磁盘空间。
-
什么情况下不会用到索引:
- 索引选择性太差(查询范围太大或者索引值重复太多)(通过索引查询的数据超过全表数量的25%的时候,极有可能全表扫描)
- <> 和 not in 无法使用索引
- is null 会使用索引,is not null 不会使用索引;
- where 子句中跳过左侧的索引列,直接查询右侧索引列;
- 多索引列进行计算或者使用函数
-
在查看排序SQL的执行计划时,如果Extra字段中出现Using filesort的时候,表示该条排序时读取文件重新排列,这样排序的效率会很低。
-
索引的优化策略:
-
排序的优化(当order by字段与索引字段顺序/排序方向相同时索引可优化排序顺序):
- 排序字段为单字段时,当排序出现了索引左侧列,则允许使用索引排序;左侧字段单字段排序时,索引支持升降序。
- 排序字段为多字段时,左侧字段必须是升序,且排序字段的顺序必须与创建索引是字段的顺序一致;
-
pt-duplicate-key-checher是percona-tookiet工具包中的实用组件,可以帮助我们在语法层面检测表中重复的索引或者主键。
-
实用索引SQL语句,查看索引使用的统计情况:
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, --为null时,表示全表扫描时总共提取的数据,对于null,统计数据越少越好 COUNT_READ, --索引在计算过程中读取了多少行 COUNT_FETCH, --查询结果得到了多少行 COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM PERFORMANCE_SCHEMA .table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC; --SUM_TIMER_WAIT表示索引使用的时长
当统计数据都为0时,表示该索引没有被使用到,可以放心的删除。
-
对表中的索引重新统计,减少索引碎片
ANALYZE TABLE orders_126; --orders_126为表名
-
优化表空间,释放表空间,减少表碎片
testdb.orders_126; --优化表空间,释放表空间(在执行的过程中会锁表,会造成IO阻塞)
-
关联查询的执行过程
SELECT tbl1.col1, tbl2.col2, from tbl1, tbl2 where tbl1.col3 = tbl2.col3 and tbl1.col3 = 1;
MySQL在执行上面的语句的时候,会先查询 tbl1 中所有 col3 = 1 的数据,然后将查询到的结果再根据关联条件去查询 tbl2 中的数据,最后返回结果。
tbl1被称为驱动表,驱动表中查询到的数据越少越好
-
例如查询sql
SELECT gc.*, g.title FROM t_goods g, t_goods_cover gc WHERE g.goods_id = gc.goods_id AND g.category_id = 44; --查看执行计划的时候,发现t_goods为驱动表,且查询的时候为全表扫描,此时需要对该关联查询进行优化
-
查看执行计划
-
EXPLAIN 默认第一行出现的表就是驱动表,由查询优化器自动选择
-
EXPLAIN type值的含义:
- ALL – 全表扫描
- eq_ref 联表查询的情况,按联表的主键或唯一键联合查询
-
关联查询优化要点
-
外键上加索引
CREATE INDEX index_goods_id on t_goods_cover(goods_id);
-
查询条件上加索引
CREATE INDEX index_category_id on t_goods(category_id);
-
-
关闭查询缓存
SET GLOBAL query_cache_size = 0; SET GLOBAL query_cache_type = 0;
-
慢SQL:执行慢的SQL
-
查询是否开启慢SQL日志
SHOW VARIABLES like '%query%';
-
开启慢SQL日志(SET GLOBAL设置的参数在重启后会失效):
SET GLOBAL slow_query_log = on; --开启慢查询日志 SET GLOBAL long_query_time = 0.001; --查询时间超过1毫秒(日常应用应不超过300毫秒) SET GLOBAL slow_query_log_file = "slow-sql.log"; --指定慢sql文件名,慢SQL日志保存在mysql/data目录下 SET GLOBAL log_queries_not_using_indexes = on; --开启记录没有使用SQL语句
-
慢查询中各个参数的说明
名称 代表的意思 Time 执行查询的时间 Query_time 查询时间 Lock_time 资源锁定时间 Rows_sent 查询结果总行数 Rows_examaned 扫描的行数(越大表示查询越慢) -
EXPLAIN执行计划:SQL在执行过程中依次要执行的步骤
-
EXPLAIN执行计划各字段 的含义:
-
id:select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的,id=1是驱动表,没有id则表示为查询的中间结果
-
select_type:查询类型
simple 简单查询 primary 意味着这是一个嵌套查询 derived 派生查询,包含在from子句中的子查询,MySQL会将结果放在一个临时表中,也称为派生表 union union关键字之后的select查询 union result union会将去重后的结果,从union临时表检索的结果(会产生一个临时表,没有索引,查询效率会很慢) subquery 包含在select子句中的子查询 -
table:explain的一行正在访问哪个表
表示对id为3的查询结果的汇总 <union3,4> 表示对id为3和4两个查询的结果的汇总 -
partitions:分区表
-
type:关联类型或者访问类型,即MySQL决定如何查找表中的行(以下为执行效率从高到低的排序):
system const 常量引用,用于primary key或unique key,最多有一个匹配行,地区一次,速度比较快 eq_ref 关联查询,primary key或unique key索引的所有部分被连接使用,最多只返回一条符合条件的记录,这可能是const之外最好的链接类型了,简单的select不会出现这种情况 ref 非主键或非唯一性索引的检索,不使用唯一索引,而是普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行 fulltext 全文检索 ref_or_null 包含null的索引检索,类似ref,但是可以搜索值为null的行 index_merge unique_subquery index_subquery range 范围检索,通常出现在in,between,>,<,>=等操作中。使用一个索引来检索给定范围的行,如果范围很大的话,执行效率就会很差。 index 通过索引做的全表扫描,和ALL一样,不同的是MySQL只需扫描索引树,通常比ALL快一些 ALL 全表扫描,以为着MySQL从头到尾查询所需要的行,通常情况下这需要通过增加索引来进行优化了 -
possible_key:展示查询可能使用哪些索引来查询
-
key:展示MySQL实际采用哪个索引来优化对该表的访问
-
key_len:key的长度,展示了MySQL在索引里使用的字节数,通过这个值可以计算出具体使用了索引中的哪些列。
-
ref:引用的信息,展示了再key列记录的索引中,表查找值所用到的列或常量,常见的有const,func,NULL,字段名
-
rows:表示MySQL估计要读取并检索的行数,注意:这个不是结果集里面的行数
-
filtered:是一个百分比的值,代表(rows*filtered)/100,这个结果将与前表产生交互,预期最终数据产生的比例
-
Extra:展示的是额外额度信息
distinct 一旦MySQL找到了与行相联合匹配的行就不再检索了 Using index 这发生在对表的请求都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的行记录,是性能高的表现select id from table order by id,也叫做索引覆盖 Using where MySQL服务器将在存储引擎检索后再进行过滤,就是先读取整行数据,再按where条件进行检索,符合就留下,不符合就丢弃,往往代表访问效率不高 Using temporary 使用临时表,在查询的过程中需要创建一张临时表进行中间操作,效率非常低 Using filesort 采用文件扫描的方式对结果进行计算排序,执行效率很差;对于排序只有select字段与order by字段都被索引覆盖,才允许使用Using index
-
-
查看更详细的执行计划(在服务器上面执行的时候,会打印出更多的警告信息)
EXPLAIN EXTENDED SELECT gc.*, g.title FROM t_goods g, t_goods_cover gc WHERE g.goods_id = gc.goods_id AND g.category_id = 44;
MySQL的索引的使用与执行计划分析
最新推荐文章于 2024-07-17 09:37:23 发布