第2章 索引优化分析

１，慢SQL

性能下降、 SQL 慢、执行时间长、等待时间长的原因分析:

1. 查询语句写的烂

2. 索引失效：

   单值索引：在user表中给name属性建个索引，create index idx_user_name on user(name)
   复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email)

3. 关联查询太多join(设计缺陷或不得已的需求)

4. 服务器调优及各个参数设置(缓冲、线程数等)

2,join查询

2.1、SQL 执行顺序

我们手写的 SQL 顺序:

MySQL 实际执行 SQL 顺序

mysql 执行的顺序：随着 Mysql 版本的更新换代， 其优化器也在不断的升级， 优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
下面是经常出现的查询顺序：

• 总结：mysql 从 FROM 开始执行

2.2、JOIN 连接查询

join查询图

2.3 七种join语句

３ 索引

3.1 索引是什么

• MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
• 你可以简单理解为"排好序的快速查找数据结构"，即索引 = 排序 + 查找
• 一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。
• 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
• 聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。
• 索引的目的在于提高查询效率，可以类比字典。
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
• update 更新慢，不仅要改数据，还要改索引，所以经常更新的数据不适合建索引。

3.2 索引的优劣势

索引的优势

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

索引的劣势

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

3.3 索引分类

• 单值索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
• 唯一索引：与前面的单值索引类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
• 复合索引：一个索引包含多个列

3.4、MySQL 索引语法

建立索引的 SQL 语句

创建索引：

• 如果是CHAR和VARCHAR类型，length可以小于字段实际长度；

• 如果是BLOB和TEXT类型，必须指定length

  CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
  ’ or ’
  ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));

删除索引
DROP INDEX [indexName] ON mytable;

查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）
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，用于全文索引。

3.５、MySQL 索引结构

3.5.1、Btree 索引

Btree 索引搜索过程

【初始化介绍】<