介绍
https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
作用
优化查询
算法
索引的算法包括
BTreeHashRTreeFullTextGIS
B+Tree结构
BTree查找算法图
B+Tree查找算法图（在叶子节点上加上双向指针）
B*Treee查找算法图（在枝节点上也使用双向指针）
官方图
分类
构建过程
聚簇索引构建过程
作用：
有了聚簇索引，将来插入的数据行，在同一个区内，都会按照ID值得顺序，有序在磁盘存储数据。
MySQL InnoDB 表 通过聚簇索引组织存储数据表。
前提：
-
1.建表时指定了主键列，MySQL InnoDB会将主键自动作为聚簇索引列，比如
Id not null primary key -
2.没有指定主键，自动选择唯一键的列作为聚簇索引列
-
3.以上都没有，生成隐藏聚簇索引
辅助索引构建过程
作用：
优化非聚簇索引列
回表
介绍
MySQL用来存储数据行的逻辑结构，表的数据行最终存储到了很多个Page上
InnoDB存储引擎，会按照聚簇索引，有序的组织存储表数据到各个区的连续的页中上
这些连续的数据页，成为了聚簇索引的叶子节点，可以认为聚簇所以就是原表数据
所以回表就是回聚簇索引
辅助索引：将辅助索引列值+ID主键值，构建辅助索引BTree结构
用户使用辅助索引作为条件查询时，首先扫描辅助索引的B树
-
1.如果辅助索引能够完全覆盖到查询结构时，就不需要回表
-
2.如果不能完全覆盖到，只能通过得到的ID主键值，回到聚簇索引（回表）扫描，最终得到想要的结果
影响
-
1.IO量级变大 -
2.IOPS会增大 -
3.随机IO会增大
减少回表
-
1.将查询尽可能用ID主键查询 -
2.设计合理的联合索引 -
3.更精确的查询条件+联合索引 -
4.基于优化器对索引的算法（MRR）
基本操作
建议将索引建立在，经常where ,group by ,order by ,join on ...的条件
查询索引
desc test;
key :
PRI 聚簇索引(主键索引)
MUL 辅助索引
UNI 唯一索引
(root@localhost) [test]> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | MUL | NULL | |
| sex | varchar(2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| phone | varchar(11) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
查询索引详细信息
show index from test;
(root@localhost) [test]> show index from test;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| test | 0 | ix_unique_phone | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | ix_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | ix_prifix_phone | 1 | phone | A | 0 | 3 | NULL | YES | BTREE | | |
| test | 1 | ix_name_sex | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | ix_name_sex | 2 | sex | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
创建索引
创建表时创建索引
create table test(
id int primary key auto_increment , # 主键索引
name varchar(20) not null,
sex varchar(2) ,
age int ,
phone varchar(11),
unique index ix_unique_phone(phone asc), # 唯一索引
index ix_name(name), # 单列索引
index ix_prefix_phone(phone(3)), # 前缀索引
index ix_name_sex(name,sex) # 联合索引
);
修改表时创建索引
alter table test add primary key(id); # 添加主键索引
alter table test modify id integer auto_increment; # 自增
alter table test add unique ix_unique_phone(phone asc); # 唯一索引 #
alter table test add index ix_name(name); # 单列索引
alter table test add index ix_prifix_phone(phone(3)); # 前缀索引
alter table test add index ix_name_sex(name,sex); # 联合索引
删除索引
alter table test modify id int; # 取消自增
alter table test drop primary key; # 删除主键索引
# 删除其它索引
alter table test drop index ix_unique_phone;
alter table test drop index ix_name;
alter table test drop index ix_prefix_phone;
alter table test drop index ix_name_sex;
执行计划
查看
explain select * from test;
desc select * from test;
(root@localhost) [test]> insert into test(name,sex,age,phone) values('张三','男',18,'13744551221');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> insert into test(name,sex,age,phone) values('李四','男',18,'13744531221');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> insert into test(name,sex,age,phone) values('王五','女',19,'13744531421');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> explain select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
(root@localhost) [test]> desc select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
分析
| 列名 | 说明 |
|---|---|
| id | 标识符，表示执行顺序 |
| select_type | 查询类型 1. SIMPLE：简单select，不使用union和子查询 2. PRIMARY：查询中包含任何复杂的子部分，最外层的select被标记为PRIMARY 3. UNION：union中第二个后面的select语句 4. EPENDENT UNION：一般是子查询中的第二个select语句（取决于外查询，mysql内部也有些优化） 5. UNION RESULT：union的结果 6. SUBQUERY：子查询中的第一个select 7. DEPENDENT SUBQUERY：子查询中第一个select，取决于外查询（在mysql中会有些优化，有些dependent会直接优化成simple） 8. DERIVED：派生表的select（from子句的子查询） |
| table | 显示数据来自于哪个表，有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表id为多少的查询。 |
| partitions | 使用的哪个分区，需要结合表分区才可以看到 |
| type | 类型：全表扫描(all)，索引扫描(index,range,ref,eq_ref,const(system)) 1. system：表只有一行记录，这个是const的特例，一般不会出现，可以忽略 2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快 3. eq_ref：唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引 4. ref：非唯一行索引扫描，返回匹配某个单独值的所有行 5. range：检索给定范围的行，一般条件查询中出现了>、<、in、between等查询 6. index：遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的 7. all：遍历全表以找到匹配的行 |
| possible_keys | 可能会用到的索引 |
| key | 实际使用的索引 |
| key_len | 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。 |
| ref | 表示哪一列被使用了，常数表示这一列等于某个常数 |
| rows | 大致找到所需记录需要读取的行数 |
| filtered | 表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80% |
| Extra | 额外的信息 1. Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化） 2. Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化） 3. Using index：表示select语句中使用了覆盖索引，直接冲索引中取值，而不需要回行（从磁盘中取数据） 4. Using where：使用了where过滤 5. Using index condition：5.6之后新增的，表示查询的列有非索引的列，先判断索引的条件，以减少磁盘的IO 6. Using join buffer：使用了连接缓存 7. impossible where：where子句的值总是false |
应用规范
必须建表一定要有主键，一般是个无关列
选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
限制索引的数目
Percona-toolkit中有个工具:专门分析索引是否有用
尽量少在经常更新值得列建索引
Innodb_index-stats
Innodb_table-stats
Optimize table city;
函数，隐式转换不走索引
<,>,in等也可能不走索引，和结果集有关 超过15%~30%就不走索引
优化器针对索引的算法
思考
更新数据时，会对索引有影响吗？索引是否实时更新？
比如insert，delete,update一行数据
对于聚簇索引会立即更新
对于辅助索引，不是实时更新的
在InnoDB内存结构中，加入了insert buffer(会话)，现在版本叫change buffer
change buffer功能是临时缓冲辅助索引需要的数据更新
当我们需要查询新的insert数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的
B+Tree索引树高度影响因素?
1.索引字段较长： 前缀索引
2.数据行过多：分区表，归档表（pt-archive），分布式架构
3.数据类型：选择合适的数据类型*
为什么不能乱建索引?
如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新。会阻塞很多业务更新的请求
索引过多，会导致优化器选择出现偏差
1331
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
