MySQL 基础优化

索引及执行计划

1. MySQL索引介绍
1.1 索引是什么？

相当于一本书中的目录。帮助我们快速找到需要内容的页码。
索引可以帮我们快速找到所需要行的数据页码。起到优化查询的功能。

1.2 MySQL索引类型

Btree 索引 *****
Rtree 索引
HASH  索引
Fulltext 全文索引
GIS      地理位置索引

2. B+Tree 结构
2.1 介绍

遍历 -----> 二叉树 -----> 平衡二叉树 -----> Balance Tree

2.2 Btree查找算法引入

2.3 Btree种类

B-Tree -----> B+Tree -----> B*Tree

3. MySQL B+Tree索引构建过程（InnoDB独有）
3.1 聚簇索引BTREE结构
3.1.1 簇

区 extent ======> 簇 ======> 64个pages ====> 1M

3.1.2 作用：

有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照ID值的顺序，有序在磁盘存储数据。
MySQL InnoDB 表 聚簇索引组织存储数据表。

3.1.3 构建前提：

1. 建表时，指定了主键列，MySQL InnoDB 会将主键作为聚簇索引索引列，比如ID not null primary key
2. 没有指定主键，自动选择唯一键（unique）的列，作为聚簇索引。
3. 以上都没有，生成隐藏聚簇索引。
作用：
有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照ID值的顺序，有序在磁盘存储数据。

3.1.4 画图说明，聚簇索引 Btree构建过程

3.2 辅助索引BTREE结构
3.2.1 说明

使用普通列作为条件构建的索引。需要人为创建。

3.2.2 作用：

优化非聚簇索引之外的查询条件的优化。

3.2.3 画图说明，辅助索引 Btree构建过程

3.2.4 辅助索引的细节

（1）单列索引
（2）联合索引
说明：使用多列组合一个索引。
联合索引，注意最左原则。
1. 查询条件中，必须要包含最左列，上面例子就是a列
2. 建立联合索引时，一定要选择重复值少的列，作为最左列。
例如： idx(a,b,c) ----> a ab abc
全部覆盖：
select * from t1 where a= and b= and c=
select * from t1 where a in and b in and c in
select * from t1 where b= and c= and a=
select * from t1 where a and b order by c
部分覆盖：
select * from t1 where a= and b=
select * from t1 where a=
select * from t1 where a= and c=
select * from t1 where a= and b> < >= like and c=
select xxx from t1 where a order by c

不覆盖：
bc
b
c

（3）前缀索引
前缀索引是针对于，我们所选择的索引列值长度，会导致索引树高度增高。
所以可以选择大字段的前面部分字符作为索引生成条件。
会导致索引应用时，需要读取更多的索引数据页
MySQL中建议索引树高度3-4层。

4. B+Tree索引树高度影响因素

1. 索引字段较长 ： 前缀索引
2. 数据行过多 ： 分区表， 归档表（pt-archive）,分布式架构（大企业）
3. 数据类型 ： 选择合适的数据类型。

5. 索引的管理命令
5.1 什么时候创建索引？

按照业务语句的需求创建合适的索引。
并不是将所有列都建立索引。不是索引越多越好。
将索引建立在，经常 where group by order by join on ......的条件
为什么不能乱建索引，
1. 如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新。会阻塞很多正常的业务更新的请求。
2. 索引过多，会导致优化器选择出现偏差。

5.2 管理命令

1. 查询表的索引情况
mysql> desc city;
key: 　　PRI聚簇索引 
　　　　　MUL辅助索引
　　　　　UNI唯一索引
mysql> show index from city;

2. 建立索引：
分析业务语句：
mysql> select * from city where name='wuhan';
mysql> alter table city add index idx_na(name);　　　　　　　　　　: 单列索引
mysql> alter table city add index idx_n_c(name,countrycode);　　: 联合索引
mysql> alter table city add index idx_d(district(5));　　　　　　 : 前缀索引
mysql> show index from city;
mysql> desc city;

3. 删除索引
mysql> alter table city drop index idx_na;
mysql> alter table city drop index idx_n_c;
mysql> alter table city drop index idx_d;
mysql> desc city;

5.3 压力测试

1. 导入100w的测试表
source t100w.sql

2. 压测命令:未建立索引
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780p'" engine=innodb \
--number-of-queries=200 -uroot -pmysql -verbose

[root@db01 ~]$ mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='780p'" engine=innodb \
> --number-of-queries=200 -uroot -pmysql -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 118.578 seconds
Minimum number of seconds to run all queries: 118.578 seconds
Maximum number of seconds to run all queries: 118.578 seconds
Number of clients running queries: 100
Average number of queries per client: 2

3. 建立合适索引
mysql> use test
mysql> alter table t100w add index idx_k2(k2);

4. 再次压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780p'" engine=innodb \
--number-of-queries=200 -uroot -pmysql -verbose

[root@db01 ~]$ mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='780p'" engine=innodb \
> --number-of-queries=200 -uroot -pmysql -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 2.035 seconds
Minimum number of seconds to run all queries: 2.035 seconds
Maximum number of seconds to run all queries: 2.035 seconds
Number of clients running queries: 100
Average number of queries per client: 2

6. 执行计划分析
7. 索引应用规范
8.

面试题：

更新数据时，会对索引有影响吗，数据的变化会使索引实时更新吗

比如insert，update，delete 一行数据。
对于聚簇索引，会立即更新。
对于辅助索引，不是实时更新的。

 

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change
change buffer 功能是临时缓存辅助索引需要的数据更新。
当我们需要查询新insert的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

双11的时候，并发度太高，提前1-2周将热点商品数据，提前灌入到Tair（redis，memcached）集群中
============================================================================================================