MySQL高级2——索引优化分析

性能下降SQL慢，执行时间长，等待时间长

• 查询语句写的烂
• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各种参数设置（缓冲、线程数等）

常见通用的Join查询

SQL执行顺序

1. 手写：
   

2. 机读
   

3. 总结
   

7种join

MySQL全连接可以通过union实现

索引简介

是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构。
索引的目的在于提高查询效率，可以类比字典
你可以简单理解为**“排好序的快速查找数据结构”**。
结论：数据本身之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优势

• 类似大学图书馆书目索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

• 实际上索引也是一张表，该表保存了主键于索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询速度，同时却降低更新表的速度，如对表进行insert、update、delete。因为更新表是，MySQL不仅要保存数据，还要保存一下索引文件每次更新台南佳了索引列的字段。都会因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：即一个索引包含多个列。

索引的基本语法

创建：

1. CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
2. ALTER TABLE mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

删除：
DROP INDEX [indexname] ON table;

查看：
SHOW INDEX FROM table_name;

使用ALTER命令添加数据表的索引：

1. ALTER TABLE tbl_name ADD PRIMARY KEY(column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null。
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能回出现多次）。
3. ALTER TABLE tbl_name ADD INDEX index_name(column_list):添加普通索引，索引值可出现多次。
4. ALTER TABLE tbl_name ADD FULL TEXT index_name(column_list)：该语句指定了索引为FULL TEXT，用于全文索引。

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中于其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，
5. Where条件里用不到的字段不创建索引，加重了IO负担。
6. 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
7. 查询中排序的字段，排序字段若通过索引取访问讲大大提高排序速度。
8. 查询中统计或分组字段。

哪些请开给你不要创建索引

1. 表记录太少
2. 经常增删改的表。WHY：提高了查询速度，同时却回降低更新表的速度，如对表进行Insert、Update、Delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
3. 数据重复且分布平均的表字段，因此应该只为了最经常查询和最经常排序的数据列建立索引。注意，如果莫格数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析

MySQL Query Optimizer

MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free, iostat,和vmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或表结构的性能瓶颈。

能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

怎么玩

Explain+SQL语句
如：EXPLAIN SELECT * FROM accounts;
执行计划包含的信息

各字段解释

id

select查询的序列号，包含一组数字，表示擦汗寻中执行select子句或操作表的顺序。
有三种情况：

• id相同，执行顺序由上至下
  
• id不同，如果是子查询，id的序列会递增，id值越大优先级越高，越先被执行。
  
• id相同不同，同时存在
  

select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂查询

1. simple 简单的select查询，查询中不包含子查询或UNION
2. primary 查询中若半酣任何复杂的子部分，最外层查询则被标记为primary
3. subquery 在select或where列表中包含了子查询。
4. derived 在FROM列表中半酣的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。
5. Union 若第二个select出现在Union之后，则被标记为UNION；若UNION包含在from子句的子查询中，外层SELECT将被标记为：DERIVED
6. UNION RESULT 从uion表获取结果的SELECT。

table

显示这一行的数据是关于哪张表的

type

显示查询使用了哪种类型

访问类型排序，从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref.

possible_keys

显示可能应用在这个表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引，如果为null，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估计出找出所需的记录所需要读取的行数。

Extra

包含不适合在其他列中显示但十分重要的额外信息学

覆盖索引

索引优化

索引分析

索引单表优化案例

article表

查询category_id为1且comments大于1的情况下，views最多的id。
explain select id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

结论：很显然，type是ALL,即最坏的情况。extra里还出现了using filesort，也是最坏的情况，优化是必须的。
开始优化：
优化1：
create index idx_article_ccv on article(category_id, comments,views);

explain select id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

虽然解决的全表扫描的问题，但还存在using filesort问题

优化2：
create index idx_article_ccv on article(category_id,views);

结论：可以看到type变成了ref，extra中的Using filesort也小事了，结果非常理想。

索引两表优化案例

book表

class表

下面开始explain分析
explain select * from class left join book on class.card=book.card;

结论：type为null。

优化1：为book表的card建立索引
create index ind_book_card on book(card);

优化2：为class表的card建立索引
create index ind_class_card on class(card);

索引三表优化案例

phone表

下面开始explain分析
explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

优化：
create index idx_b_card on table book(card);
create index idx_p_card on table phone(card);

后两行的type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

结论：
join语句的优化

索引失效（应该避免）

staffs表

案例（索引失效）

1. 全值匹配我最爱
   

2. 最佳左前缀法则（带头大哥不能死，中间兄弟不能断）
   
   用到了部分索引
   

3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
   

4. 存储引擎不能使用索引中范围条件右边的列。（范围之后全失效）
   

5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少使用select *。
   

7.

8.

问题：解决like’ %字符串%'时，索引不被使用的问题？
建立覆盖索引。这里可以通过建立name和age的复合索引解决。

9. 字符串不加单引号会导致索引失效。
   
10. 少用or，用它来连接时会索引失效

口诀：
带头大哥不能死，
中间兄弟不能断，
索引列上不计算，
like%加右边，
范围之后全失效，
字符串引号不能丢。

面试题讲解

建立索引：create index idx_c1234 on test(c1,c2,c3,c4);，问下列使用索引情况。

这里mysql优化器会自动优化顺序！！！


这里c3的作用在排序不是查找，用到了但没有统计。

这里越过了c3，可以看到mysql对c4进行了排序，出现了filesort。

只用了c1一个字段索引，但是c2、c3用于排序，无filesort

出现了filesort，我们建的索引是1234，他没有按顺序来，3，2 颠倒了


这里这里c2排序字段已经是一个常量了，所以，排不排序都一样。

分组之前必排序，如果没有用到index，会有临时表的产生。

小练习：
select* from test