索引语法SQL性能分析索引的使用

目录

准备:

一.索引语法

1). 创建索引

2). 查看索引

3). 删除索引

4).使用索引查询和普通查询的区别

二.SQL性能分析

1.SQL执行频率

2.慢查询日志

3.profile详情

4.explain

三.索引的使用

1.最左前缀法则

2.范围查询

3.索引失效情况

索引列运算

字符串不加引号

字符串不加引号

模糊查询

or连接条件

---

准备:

创建tb-sku表并插入两百万条数据

CREATE TABLE `tb_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `sn` varchar(100) NOT NULL COMMENT '商品条码',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL COMMENT '价格（分）',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `sale_num` int(11) DEFAULT '0' COMMENT '销量',
  `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
  `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

查询数据是否有两百万条

一.索引语法

1). 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

create index 索引名 on 表名(列名1,列名2,...)

2). 查看索引

show index from 表名;

3). 删除索引

drop index 索引名 on 表名;

4).使用索引查询和普通查询的区别

name没有建立索引,速度慢

select * from tb_sku where name='华为Meta1';

给name创建索引,再查询

create index sku_naem on tb_sku(name);

select * from tb_sku where name='华为Meta1';

可以看到速度已经非常快了

主键索引

select * from tb_sku where id=18;

id是主键列,查询是自动创建主键索引,速度也很快

二.SQL性能分析

1.SQL执行频率

查询SQL使用次数(频率)

show global status like 'Com_%';

Com_delete: 删除次数

Com_insert: 插入次数

Com_select: 查询次数

Com_update: 更新次数

2.慢查询日志

1.查看慢查询是否开启

show variables like 'slow_query_log';

2.开启慢查询

需要修改配置文件

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

3.重启MySQL就可以看慢日志

在MySQL安装目录下的data目录下

3.profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

1.测试当前MySQL是否支持profile操作

select @@have_profiling;

2.查看profile操作是开启还是关闭的

show variables like '%profiling%';

3.临时开启profile

set profiling = 1;

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了

4.explain

获取 MySQL 过程中表如何连接和连接的顺序。

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下； id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接 或者子查询）、 PRIMARY （主查询，即外层的查询）、 UNION （UNION 中的第二个或者后面的查询语句）、 SUBQUERY （SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、 system、 const、 eq_ref、 ref、 range、 index、 all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

查询价格比华为Mate1高的数据

explain 
select * from tb_sku where price>
(select price from tb_sku where name='华为Meta1')

三.索引的使用

1.最左前缀法则

最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

1.创建sn和name字段索引

create index sn_name on tb_sku(sn,name);

2.使用索引查询

explain select * from tb_sku
where sn='1000000031450042' and name='华为Meta42';

把sn去掉索引就失效了

2.范围查询

联合索引中，出现范围查询 (>,<)，范围查询右侧的列索引失效。

explain select * from tb_sku
where sn>='1000000031450042' and name='华为Meta42' ;

3.索引失效情况

索引列运算

不要在索引列上进行运算操作， 索引将失效。

在tb_user表中，除了前面介绍的联合索引之外，还有一个索引，是phone字段的单列索引。

A. 当根据phone字段进行等值匹配查询时, 索引生效。

explain select * from tb_user where phone = '17799990015';

B. 当根据phone字段进行函数运算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

字符串不加引号

加引号

explain select * from tb_user where name='jack';

不加引号

模糊查询

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。
 

explain select * from tb_user where name like '1%';

explain select * from tb_user where name='%1%';

or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引，所以即使id、 phone有索引，索引也会失效。所以需要针对于age也要建立索引。

然后，我们可以对age字段建立索引。

create index idx_user_age on tb_user(age);

建立了索引之后，我们再次执行上述的SQL语句，看看前后执行计划的变化。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

最终，我们发现，当or连接的条件，左右两侧字段都有索引时，索引才会生效。