关于java:咱们500万条数据测试一下如何合理使用索引加速

tips：

500 万条建表 sql 参照网盘 sql 脚本

[root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql

索引是数据库优化最罕用也是最重要的伎俩之一, 通过索引通常能够帮忙用户解决大多数的 MySQL 的性能优化问题。

在咱们筹备的表构造 product_list 中，一共存储了 500 多万记录；

mysql> select count(1) from product_list;
+----------+
| count(1) |
+----------+
|  5072825 |
+----------+
1 row in set (1.71 sec)

mysql>

1）依据 ID 查问

SELECT * FROM product_list WHERE id = 121926;

查问速度很快，靠近 0s，次要的起因是因为 id 为主键，有索引；

2). 依据 store_name 进行准确查问

执行用时 4 分钟

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

查看 SQL 语句的执行打算：

explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

解决计划，针对 store_name 字段，创立索引：

create index product_list_stname on product_list(store_name);

索引创立实现之后，再次进行查问：

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

通过 explain，查看执行打算，执行 SQL 时应用了方才创立的索引

-- 查看 SQL 语句的执行打算
explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

create table `tb_seller` (`sellerid` varchar (100),
    `name`  varchar (100) not null,
    `nickname` varchar (50),
    `password` varchar (60),
    `status`  varchar (1) not null,
    `address`  varchar (100) not null,
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO 科技有限公司','OPPO 官网旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

组合索引 (name,status,address)

1) 全值匹配

对索引中所有列都指定具体值。

-- 全值匹配
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
ken_len = 3 * N + 2;
-- name varchar(100)      ==302
-- status varchar(1)      ==5
-- address varchar(100) ==302

2) 最左前缀法令

如果索引了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法令，走索引：

explain select * from tb_seller  where name='小米科技';

违反最左前缀法令，索引生效：

explain select * from tb_seller  where status='1';
explain select * from tb_seller  where status='1'  and  address='北京市';

如果合乎最左法令，然而呈现跳跃某一列，只有最左列索引失效：

explain select * from tb_seller  where name='小米科技'  and  address='北京市';

3) 范畴查问左边的列

-- 应用范畴查问的状况，左边的列生效 
explain select * from tb_seller  where name='小米科技' and status='1'  and  address='北京市';
explain select * from tb_seller  where name='小米科技' and status>'1'  and  address='北京市';

依据后面的两个字段 name，status 查问是走索引的，然而最初一个条件 address 没有用到索引。

4) 禁止列运算

-- 不要在索引列上进行运算操作，索引将生效。explain select * from tb_seller  where substring(name,3,2) ='科技';

5) 字符串不加单引号

造成索引生效。

-- 字符串不加单引号, 造成索引生效。explain select * from tb_seller  where name='科技' and status='0';
explain select * from tb_seller  where name='科技' and status=0;

因为，在查问时，没有对字符串加单引号，MySQL 的查问优化器，会主动的进行类型转换，造成索引生效。

6) 尽量应用笼罩索引

防止 select *

尽量应用笼罩索引（只拜访索引的查问（索引列齐全蕴含查问列）），缩小 select *。

-- 尽量应用笼罩索引
explain select * from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status  from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status,address  from tb_seller  where name='科技' and status='0'  and  address='西安市';

如果查问列，超出索引列，也会升高性能。

explain select status,address ,password  from tb_seller  where name='科技' and status='0'  and  address='西安市';

TIP : 
    
    using index：应用笼罩索引的时候就会呈现

    using where：在查找应用索引的状况下，须要回表去查问所需的数据

    using index condition：查找应用了索引，然而须要回表查问数据

    using index ; using where：查找应用了索引，然而须要的数据都在索引列中能找到，所以不须要回表查问数据

7) 正当应用 or 条件

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

示例，name 字段是索引列，而 createtime 不是索引列，两头是 or 进行连贯是不走索引的：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';

8) 正当应用 like 查问

以 % 结尾的 Like 含糊查问，索引生效。

-- 如果仅仅是尾部含糊匹配，索引不会生效。如果是头部含糊匹配，索引生效。explain select * from tb_seller  where name like '黑马程序员 %';
explain select * from tb_seller  where name like '% 黑马程序';
explain select * from tb_seller  where name like '% 黑马程序员 %';

解决方案：通过笼罩索引来解决

explain select sellerid from tb_seller  where name like '% 科技 %';
explain select sellerid,name from tb_seller  where name like '% 科技 %';
explain select sellerid,name,status,address  from tb_seller  where name like '% 科技 %';

9) 正当评估索引执行

如果 MySQL 评估应用索引比全表更慢，则不应用索引。

-- 如果 MySQL 评估应用索引比全表更慢，则不应用索引。create index idx_seller_addr on tb_seller(address);
explain select * from tb_seller  where address='北京市';
explain select * from tb_seller  where address='西安市';

10) is NULL 和 is NOT NULL

<font color=’red’> 有时 </font> 索引生效。

-- is  NULL 和 is NOT NULL 
explain select * from tb_seller  where name  is null;
explain select * from tb_seller  where name  is not null;

解决方案：把 null 值设置一个默认值

11) in 和 not in

in 走索引，not in 索引生效。

-- in 走索引，not in 索引生效。explain select * from tb_seller  where sellerid in('oppo','xiaomi','sina');
explain select * from tb_seller  where sellerid not in  ('oppo','xiaomi','sina');

12) 单列索引和复合索引

尽量应用复合索引，而少应用单列索引。

创立复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创立了三个索引：name
    name + status
    name + status + address

创立单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会抉择一个最优的索引（辨识度最高索引）来应用，并不会应用全副索引。

show status like 'Handler_read%';    
show global status like 'Handler_read%';

mysql> show status like 'Handler_read%';    
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 18      |
| Handler_read_key      | 19      |
| Handler_read_last     | 0       |
| Handler_read_next     | 5072825 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 269     |
+-----------------------+---------+
7 rows in set (0.02 sec)

mysql>

Handler_read_first：索引中第一条被读的次数。如果较高，示意服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，示意索引失去的性能改善不高，因为索引不常常应用（这个值越高越好）。Handler_read_next：依照键程序读下一行的申请数。如果你用范畴束缚或如果执行索引扫描来查问索引列，该值减少。Handler_read_prev：依照键程序读前一行的申请数。该读办法次要用于优化 ORDER BY ... DESC。Handler_read_rnd：依据固定地位读一行的申请数。如果你正执行大量查问并须要对后果进行排序该值较高。你可能应用了大量须要 MySQL 扫描整个表的查问或你的连贯没有正确应用键。这个值较高，意味着运行效率低，应该建设索引来补救。Handler_read_rnd_next：在数据文件中读下一行的申请数。如果你正进行大量的表扫描，该值较高。通常阐明你的表索引不正确或写入的查问没有利用索引。

本文由传智教育博学谷 – 狂野架构师教研团队公布，转载请注明出处！

如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源

关于java:咱们500万条数据测试一下如何合理使用索引加速

5 如何正当应用索引减速

5.1 验证索引晋升查问效率

5.2 索引的应用

5.2.1 筹备环境

5.2.2 防止索引生效

1) 全值匹配

2) 最左前缀法令

3) 范畴查问左边的列

4) 禁止列运算

5) 字符串不加单引号

6) 尽量应用笼罩索引

7) 正当应用 or 条件

8) 正当应用 like 查问

9) 正当评估索引执行

10) is NULL 和 is NOT NULL

11) in 和 not in

12) 单列索引和复合索引

5.3 查看索引应用状况

Just My Socks（注册教程内含优惠码）

关于java:咱们500万条数据测试一下如何合理使用索引加速

5 如何正当应用索引减速

5.1 验证索引晋升查问效率

5.2 索引的应用

5.2.1 筹备环境

5.2.2 防止索引生效

1) 全值匹配

2) 最左前缀法令

3) 范畴查问左边的列

4) 禁止列运算

5) 字符串不加单引号

6) 尽量应用笼罩索引

7) 正当应用 or 条件

8) 正当应用 like 查问

9) 正当评估索引执行

10) is NULL 和 is NOT NULL

11) in 和 not in

12) 单列索引和复合索引

5.3 查看索引应用状况

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）