原文：https://blog.youkuaiyun.com/weixin_41133386/article/details/89856139
作者：Camus_Code
系统优化分析
-
性能下降sql慢/执行时间长/等待时间长
- 查询语句写的烂
- 索引失效
- 单值
- 复合
- 关联查询太多join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置
-
常见通用的join查询
-
sql执行顺序
- 手写
- 机读
- 总结
- 手写
-
join图
-
建sql表
-
7种join
-
后两种在mysql中不支持，可以替换为
-
-
索引简介
-
是什么
- MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
- 可以得到索引的本质：索引是数据结构。
- 可以简单理解为排好序的快速查找数据结构。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
- 我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，
复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
-
优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
-
劣势
-
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
-
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
-
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句
-
-
-
mysql索引结构
-
mysql索引分类
- 主键索引
- 设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引
- 索引列的值必须唯一，但允许有空值
- 复合索引
- 即一个索引包含多个列
- 基本语法
- 创建 — ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length)) - 删除 — DROP INDEX [indexName] ON mytable;
- 查看 — SHOW INDEX FROM table_name\G
- 使用alter命令，四种方式来添加数据表的索引：
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
-
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
-
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
-
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
-
- 创建 — ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
- 主键索引
-
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引(where 后面的语句)
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
-
哪些情况不需要创建索引
- 表记录太少
- 经常增删改的表
- Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
- Where条件里用不到的字段不创建索引
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
-
性能分析
- mysql query optimizer
- mysql 常见瓶颈
- CPU ： CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO ： 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
- explain
-
是什么
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
-
能干嘛
- 表的读取顺序
- 哪些索引可以使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
-
怎么玩
- explain + sql 语句
- 执行计划包含的信息
-
各字段解释：
-
id： select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
（1）id相同，执行顺序由上至下
此例中 先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。 而 t2.id 的结果建立在 t2.id=t3.id 的基础之上。
（2）id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
（3）id相同不同，同时存在
id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）
select_type： 查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂查询。
类型有 如下几种：
各自的含义：
- simple： 简单的 select 查询,查询中不包含子查询或者UNION
- primary 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
- derived 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
- subquery 在SELECT或WHERE列表中包含了子查询dependent subquery 在SELECT或WHERE列表中包含了子查询,子查询基于外层
- uncacheable subquery 无法被缓存的子查询
- union 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
- union result 从union表获取结果的select
table： 这一行的数据是关于那张表的
type： 显示查询使用了哪种类型。
- 访问类型排序
-
显示查询使用了何种类型，从最好到最差依次是 system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。 -
system —
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计 -
const —
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快
如将主键置于where列表中，MySQL就能将该查询转换为一个常量 -
eq_ref —
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 -
ref —
非唯一性索引扫描，返回匹配某个单独值的所有行.
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 -
range —
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。 -
index —
Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的） -
all —
Full Table Scan，将遍历全表以找到匹配的行 -
index_merge
在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中 -
ref_or_null
对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。 -
index_subquery
利用索引来关联子查询，不再全表扫描。 -
unique_subquery
该联接类型类似于index_subquery。 子查询中的唯一索引
-
possible_keys: 显示可能应用在这张表中的索引，一个或多个
- 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key: 实际使用的索引。如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引，则该索引和查询的select字段重叠
**key_len:**表示索引中使用的字节数.
- 可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引
ref： 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows： rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
extra：
- Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序” - Using temporary
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 - Using index
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
- Using where
表明使用了where过滤 - Using join buffer
使用了连接缓存： - impossible where
where子句的值总是false，不能用来获取任何元组 - select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
热身case
索引优化
单表优化
很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
第一次优化：建立索引 create index idx_article_ccv on article(category_id,comments,views);
type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
但是已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理,
先排序 category_id,
如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
当 comments 字段在联合索引里处于中间位置时,
因comments > 1 条件是一个范围值(所谓 range),
MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
第二次建立索引
create index idx_article_cv on article(category_id,views);
两表优化
建立左表索引之后
建立右表索引之后
相反建（左连接索引建在右表上，右连接索引建在左表上）
三表优化 还是反向建
结论：join语句的优化
1、尽可能的减少join语句中的nestedLoop的循环总次数，“永远用小结果集驱动大的结果集” 有限优化nestedLoop 的内层循环；
2、保证join语句中被驱动表上join条件字段已经被索引；
3、当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝JoinBuffer的设置。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
