MySQL索引和优化

MySQL索引和优化
1 索引
1.1 索引算法
1.1.1 顺序查找
1.1.2 二分查找
1.1.3 二叉查找树
1.1.4 平衡二叉树
1.1.5 B树
1.1.6 B+树
1.2 索引类型
1.2.1 B+树索引
1.2.2 哈希索引
1.3 索引策略
1.3.1 聚簇索引
1.3.2 辅助索引
1.3.3 唯一索引
1.3.4 单列索引
1.3.5 多列索引
1.3.6 覆盖索引
1.4 索引操作
1.4.1 查看索引
1.4.2 创建索引
1.4.3 删除索引
1.5 索引和锁
2 优化
2.1 参数调优
2.1.1 参数加载顺序
2.1.2 常用参数调优
2.1.2.1 innodb_buffer_pool_size
2.1.2.2 innodb_buffer_pool_instances
2.1.2.3 character-set-server
2.1.2.4 connect_timeout
2.1.2.5 interactive_timeout
2.1.2.6 wait_timeout
2.1.2.7 net_read_timeout
2.1.2.8 net_write_timeout
2.1.2.9 lock_wait_timeout
2.1.2.10 innodb_lock_wait_timeout
2.1.2.11 lower_case_table_names
2.1.2.12 max_connections
2.1.2.13 transaction_isolation
2.1.2.14 tmp_table_size
2.1.2.15 read_rnd_buffer_size
2.1.2.16 sort_buffer_size
2.1.2.17 slow_query_log
2.1.2.18 long_query_time
2.1.2.19 log_queries_not_using_index
2.1.2.20 expire_log_days
2.1.2.21 binlog_expire_logs_seconds
2.1.2.22 binlog_format
2.1.2.23 innodb_buffer_pool_dump_at_shutdown
2.1.2.24 innodb_buffer_pool_load_at_startup
2.1.2.25 max-allowed-packet
2.1.2.26 innodb_flush_neighbors
2.1.2.27 innodb_log_file_size
2.1.2.28 innodb_thread_concurrency
2.1.2.29 innodb_print_all_deadlocks
2.1.2.30 innodb_strict_mode
2.1.2.31 innodb_buffer_pool_dump_pct
2.1.2.32 log_timestamps
2.1.2.33 sync_binlog
2.1.2.34 innodb_flush_log_at_trx_commit
2.1.2.35 innodb_flush_method
2.1.2.36 sql_mode
2.2 SQL优化
2.2.1 分页查询优化
2.2.2 not in优化
2.2.3 order by优化
2.2.4 group by 优化
2.2.5 索引hint优化
3 执行计划
3.1 执行计划说明
3.1.1 查看执行计划
3.1.2 执行计划作用
3.2 执行计划解析
3.2.1 id
3.2.2 select_type
3.2.3 table
3.2.4 partitions
3.2.5 type
3.2.6 possible_keys
3.2.7 key
3.2.8 key _len
3.2.9 ref
3.2.10 rows
3.2.11 filtered
3.2.12 extra
1 索引 1.1 索引算法 1.1.1 顺序查找
如果要在一组数据中找到对应的记录，通常是一个一个地扫描，直到找到对应的记录。

1.1.2 二分查找
二分查找是将记录顺序排列，查找时先将序列的中间元素作为比较对象 。如果要找的元素的值小于该中间元素的值 ，那么只需要在前一半元素中继续查找；如果要找的元素的值等于该中间元素的值，则匹配成功，查询完成；如果要找的元素的值大于该中间元素的值，那么只需要在后一半元素中继续查找。

1.1.3 二叉查找树
二叉查找树是将一组无序的数据构造成一查有序的树，其设计思想与二分查找的设计思想类似 。二叉查找树有如下几个重要的特性：

每个节点最多有两个子节点。
每个节点都大于自已的左子节点。
每个节点都小于自己的右子节点。
1.1.4 平衡二叉树
平衡二叉树是二叉查找树的改进版本，除了要满足二叉查找树的定义，还必须满足任意节点的平衡因子（两棵子树的高度差）的绝对值最大为1。

1.1.5 B树
B树可以理解为平衡二叉树的拓展，也是一棵平衡树，但是是多叉的。也可以把B树看成1个节点可以拥有多于2个子节点的多叉查找树。B树有如下几个特点：

B树的每个节点存储的都是数据。
B树的查询效率与键在B树的位置有关，最大时间复杂度与B+树的相同（数据在叶子节点上），最小的时间复杂度为1（数据在非叶子节点上）
1.1.6 B+树
B+树是B树的变体，其定义与B树的定义基本一致，与B树相比，B+树的具有以下的不同点

B+树的键都出现在叶子节点上，可能在非叶子节点上重复出现。
B+树的非叶子节点存储的都是键值，叶子节点才存储键值对应的数据。
B+树的非叶子节点不存储数据，所以B+树比B树占的空间更多，但是B+树的非叶子节点具有索引的作用，所以B+树的查询效率比B树的查询效率更高。
1.2 索引类型 1.2.1 B+树索引
InnoDB引擎默认使用B+树索引，B+树索引是基于B+树发展起来的，通常在InnoDB上对某个字段添加索引，就是对这个字段构建一查B+树。

B+树索引，所有的数据都保存在叶子节点上，且叶子叶节是有序的，B+树索引使用指针把相临的叶子节点连接起来，最终所有的叶子节点形成了一个环形的双向链表，从而支持范围查找。

B+树索引，所有的数据都保存在叶子节点上，所以B+树索引的查询效率很稳定，所有数据的查询效率都是一样的。

B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找 。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义 了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

B+树索引支持以下类型的查找：

全值匹配：全值匹配指的是和索引中的所有列进行匹配。
匹配最左前缀 ：
匹配列前缀：也可以只匹配某一列的值的开头部分。
匹配范围值 ：
精准匹配某一列并范围匹配另外一列：
B+树索引也有如下的限制：

如果不是按照索引的最左列开始查找，则无法使用索引 。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则期右边所有列都无法使用索引优化查找。
1.2.2 哈希索引
Memory引擎默认使用哈希索引，哈希索引是基于哈希表实现的，只有精确匹配索引所有的列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键 值 的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用列的全部内容来计算哈希值。
哈希索引只支持等值比较查询，包括=、IN()、<>，也不支持任何范围查询。
访问哈希索引的数据非常快，除非有很多哈希冲突 。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针， 逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代码也会很高。
1.3 索引策略 1.3.1 聚簇索引
聚簇索引一般是指主键索引，也称为一级索引，聚簇索引的叶子节点存储的是完整的数据行。

InnoDB通过主键聚簇数据，如果没有定义主键，那么InnoDB会选择第一个非空的唯一索引代替，如果没有非空的唯一索引，那么InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引占用的空间最大，因为它保存了全部数据。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

1.3.2 辅助索引
辅助索引一般是指非主键索引，也称为二级索引，辅助索引的叶子节点存储的是索引字段的值和主键ID。

在使用二级索引时，因为它只存储了索引字段的值和主键ID，所以需要查询其它列的数据时，就需要先通过二级索引中的值找到对应的主键，再通过主键找到聚簇索引中的其它列的数据，这个过程称为回表。

为了减少回表次数，可以将语句中经常使用到的所有列以合适的顺序建议一个二级联合索引，这样所有需要的列都被这个二级联合索引覆盖，就不需要回表。

1.3.3 唯一索引
唯一索引是一个不包含重复值的二级索引，一般是指基于唯一键创建的索引。

1.3.4 单列索引
单列索引是指基于单列创建的索引

1.3.5 多列索引
多列索引是指基于多列创建的索引，又称为联合索引

1.3.6 覆盖索引
如果一个索引包含所有需要查询的字段的值 ，我们就称这个索引为覆盖索引。

通过覆盖索引使得查询只需要扫描索引无须回表，能够极大地提高性能。覆盖索引的好处如下：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，