-
数据表中建立索引的目的：
-
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;
07-17
520
520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
