简书 https://www.jianshu.com/p/7e3e2f814d22
聚簇索引
- 主键索引
辅助索引
- 单列索引
- 联合索引
- 前缀索引:截取字段的前几个字符去创建索引。
索引的管理
什么时候该创建索引
并不是将所有列都建立为索引,不是索引越多越好。
按照业务语句的需求创建合适的索引。
将索引语句建立在经常where group by order by join on 的条件
为什么不能乱建索引
- 如果冗余索引过多,表的数据变化的时候,很有可能导致索引频繁更新。
会阻塞很多正常的业务更新的需求。
- 索引过多,会导致优化器选择出现偏差。
数据更新、删除操作需要维护索引。
管理命令
-
查询表的索引情况
desc t1;
key:
-
PRI 聚簇索引
-
MUL 辅助索引
-
UNI 唯一索引
-
-
查看具体索引信息情况
- show index from t1;
-
创建索引
单列索引:alter table 表名 add index 索引名(列名);
联合索引:alter table 表名 add index 索引名(列名1,列名2,…);
前缀索引:alter table 表名 add index 索引名(列名(长度));
索引名一般以idx_开头。
注意
最左原则
-
删除索引
alter table 表名 drop index 索引名;
压力测试
source 导入100w数据
mysqlslap 压测
怎么减少回表
1.将查询尽可能用ID主键查询
2.设计合理的联合索引,完全覆盖
3.更精确的查询条件+联合索引
执行计划
查看执行计划
explain SQL语句
或
desc SQL语句
mysql> explain select * from product;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | product | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
table 此次执行计划涉及到的表
type 查询类型,全表,索引扫
possible_keys 可能会用到的索引
key 最后选择的索引
key_len 索引的覆盖程度
rows 此次查询扫描的行数
Extra 额外的信息
输出信息介绍
table 此次查询涉及到的表,针对一个查询中的多个表时,精确到问题表。
type 查询类型
-
全表扫描:不用任何索引,类型是ALL 需要优化。
desc select * from product; desc select * from product where 1 = 1 like '%%'、not in () 、!= 不走索引 特殊情况: 查询条件为主键时 not in 、!= 会走range索引。
-
索引扫描:index、 range 、ref、 eq_ref 、 const(system) 性能越好
-
index:全索引扫描,扫描索引树;
-
range:范围查询 >、 >=、 < 、<= 、 like ‘xx%’、 in、 or、 between and。
in 可以改为union all 可能会提升性能。
-
ref 辅助索引等值查询
desc select * from product where product_name = '111';
-
eq_ref 多表连接中,非驱动表连接条件是主键或唯一键。
-
const(system):聚簇索引等值查询
desc desc select * from product where product_id = 1;
-
possible_keys 可能会走的索引,所有和此次查询有关的索引;
key 此次查询选择的索引,一般我们不会去强制sql走某个索引。
key_len:联合索引的覆盖程度,
key_len的计算
对于索引 idx(a, b, c),key_len = a的长度 + b的长度 + c的长度
长度指的是列的最大存储值的字节长度。
长度受到数据类型、字符集的影响。
数字:
数字不受字符集的影响。
not null 没有not null
tinyint 1字节 1+1
int 4字节 4+1
bigint 8字节 8+1
没有 not null 需要1个字节用来存储是否非空。
比如:
a int not null -- 4
a int -- 5(4+1)
字符:
utf8 一个字符最多占3个字节(utf8mb4 一个字符最多占4个字节)
not null 没有not null
char(10) 3*10 3*10+1
varchar(10) 3*10+2 3*10+2+1
对于varchar另外需要有存储长度的字节,最多2个字节。
例子:
b char(10) not null 30
b char(10) 31
c varchar(10) not null 32
c varchar(10) 33
extra:
using file sort 表示此次查询使用了文件排序,说明查询中有排序操作order by 、group by 、distinct 等。
需要将where条件和排序条件创建联合索引。
索引规范
-
一定要有主键,一般是数字类型的无关列。
-
最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引,联合索引将重复值少的列放在最左侧。
-
经常做where 、order by、group by、join on 等条件操作的字段建立索引
排序操作会浪费很多时间。
where A B C – A,B,C
where A group by B order by C – A,B,C
-
尽量使用前缀来索引,如果索引字段的值很长,最好使用值的前缀来索引。
-
限制索引的数目
索引的数目不是越多越好,每个索引都需要占用磁盘空间。
修改表时,对索引的重构和更新很麻烦,会使更新表变得浪费时间。
优化器的负担会很重,有可能会影响到优化器的选择。
-
删除不再使用或者很少使用的索引(percona toolkit)
-
删除冗余索引(SQLyog工具可以查询冗余索引)
-
大表加索引以及索引维护时,要避开业务繁忙期。
-
尽量减少在经常更新的列上建索引。
不走索引的情况分析
-
没有查询条件或者查询条件没有建立索引
在业务数据库中,特别是数据量比较大的表,是不允许有全表扫描着这种情况的。
对用户查看非常慢,对服务器来讲也是毁灭性的。
select * from t1; 改成如下语句,需要在price上建索引。 select * from t1 order by price limit 10;
-
查询结果是原表中的大部分数据,差不多15%-30%,优化器觉得没必要走索引了。
如果业务允许,可以使用limit控制。如果没有更好的方式,可以将数据存放在redis等NoSQL数据库中。
-
查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+ - * / ! 等)。
-
隐式转换导致索引失效,这点在开发中经常会犯的错误。
-
< > not in 不走索引(辅助索引)。
-
like ‘%_’ 百分号在前面不走索引,’%hello%'这类的搜索需求,可以使用elasticsearch solr mongodb 等专门做搜索服务的数据库产品。
优化分页查询
select id, device_id from req_log where id >=
(select id from req_log order by create_at, id limit 100000, 1)
limit 10;
select id, device_id from req_log order by create_at, id limit 100000, 10;