首页 > 数据库 >【MySQL高级】索引优化

【MySQL高级】索引优化

时间：2023-02-08 14:04:15浏览次数：43  
标签：insert name -- 优化 seller 索引 MySQL tb


目录

​​1.使用索引优化​​

​​1.1数据准备​​

​​1.2避免索引失效应用-全值匹配​​

​​1.3避免索引失效应用-最左前缀法则​​

​​1.4避免索引失效应用-其他匹配原则​​

​​2.SQL优化​​

​​2.1大批量插入数据​​

​​2.2优化insert语句​​

​​2.3优化order by语句​​

​​2.4优化group by​​

​​2.5优化子查询​​

​​2.6优化limit查询​​

1.使用索引优化

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL

的性能优化问题。

1.1数据准备

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
);

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL

的性能优化问题。

数据准备

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);

1.2避免索引失效应用-全值匹配

该情况下，索引生效，执行效率高。

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

1.3避免索引失效应用-最左前缀法则

该情况下，索引生效，执行效率高。

-- 最左前缀法则
 -- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技'; -- 403
 
explain select * from tb_seller where name='小米科技' and status='1'; -- 410
explain select * from tb_seller where  status='1' and name='小米科技'; -- 410
-- 违法最左前缀法则 ， 索引失效：
explain select * from tb_seller where status='1'; -- nulll
 
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技'  and address='北京市'; -- 403

1.4避免索引失效应用-其他匹配原则

该情况下，索引生效，执行效率高。

-- 范围查询右边的列，不能使用索引 。 
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市’; 

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

-- 字符串不加单引号，造成索引失效。 
explain select * from tb_seller where name='小米科技' and status = 1 ;
-- 1、范围查询右边的列，不能使用索引 。
-- 根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市';
 
-- 2、不要在索引列上进行运算操作， 索引将失效。
explain select * from tb_seller where substring(name,3,2)='科技'
 
-- 3、字符串不加单引号，造成索引失效。 
explain select * from tb_seller where name='小米科技' and status = 1 ;
 
-- 4、尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
 
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
-- 尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
 
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

-- 用or分割开的条件， 那么涉及的索引都不会被用到。
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; 
explain select * from tb_seller where name='黑马程序员' or address = '西安市';  
explain select * from tb_seller where name='黑马程序员' or status = '1';   
 
-- 以%开头的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 name from tb_seller where name like '%科技%';
--  1、如果MySQL评估使用索引比全表更慢，则不使用索引。
  -- 这种情况是由数据本身的特点来决定的
create index index_address on tb_seller(address);
 
explain select * from tb_seller where address = '北京市'; -- 没有使用索引
explain select * from tb_seller where address = '西安市'; -- 没有使用索引
 
 
--  2、is  NULL ， is NOT NULL  有时有效，有时索引失效。
create index index_address on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL;  -- 索引有效
explain select * from tb_seller where nickname is not NULL; -- 无效

2.SQL优化

2.1大批量插入数据

create table `tb_user` (
  `id` int(11) not null auto_increment,
  `username` varchar(45) not null,
  `password` varchar(96) not null,
  `name` varchar(45) not null,
  `birthday` datetime default null,
  `sex` char(1) default null,
  `email` varchar(45) default null,
  `phone` varchar(45) default null,
  `qq` varchar(32) default null,
  `status` varchar(32) not null comment '用户状态',
  `create_time` datetime not null,
  `update_time` datetime default null,
  primary key (`id`),
  unique key `unique_user_username` (`username`)
);

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。对于 InnoDB 类型的表，有以

下几种方式可以提高导入的效率：

1） 主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有

效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主

键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

【MySQL高级】索引优化_java

-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
 
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
 
-- 3、加载数据 
/*
脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

  2 )、关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET

UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
 
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
 
SET UNIQUE_CHECKS=1;

2.2优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案:

-- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
 
 
-- 优化后的方案为 ： 
 
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
-- 在事务中进行数据插入。
begin;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;
-- 数据有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
 
 
-- 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

2.3优化order by语句

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
 
create index idx_emp_age_salary on emp(age,salary);

2、两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排

序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，

操作效率高。

3、Filesort 的优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消

失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法： 1） 两次扫描算

法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序

区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排

序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。 2）一次扫描算法：

一次性取出满足条件的所有字段，然后在排序区 sort  buffer 中排序后直接输出结果集。排序时内

存开销较大，但是排序效率比两次扫描算法要高。 MySQL 通过比较系统变量

max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如

果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。 可以适

当提高 sort_buffer_size  和 max_length_for_sort_data  系统变量，来增大排序区的大小，提高排

序的效率。

2.4优化group by

于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多

了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些

聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。

如下 ：

drop index idx_emp_age_salary on emp; 

explain select age,count(*) from emp group by age;

explain select age,count(*) from emp group by age order by null;

create index idx_emp_age_salary on emp(age,salary)；

2.5优化子查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务

或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）

替代。

explain select * from user where uid in (select uid from user_role );

【MySQL高级】索引优化_java_02

explain select * from user u , user_role ur where u.uid = ur.uid;

【MySQL高级】索引优化_数据库_03

 system>const>eq_ref>ref>range>index>ALL

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上

需要两个步骤的查询工作。

2.6优化limit查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit

900000,10  ，此时需要MySQL排序前900010 记录，仅仅返回900000 - 900010 的记录，其他记录

丢弃，查询排序的代价非常大 。

1、优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

2、优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

标签：insert,name,--,优化,seller,索引,MySQL,tb
From： https://blog.51cto.com/u_15680317/6044111

相关文章

  • navicat for mysql
    1、先下载并安装navicat_premium_trial_64（注意是64bit版本） 链接:https://pan.baidu.com/s/11nRYrmddDLj1R0rrIfbKcg提取码:5cmv2、下载PatchNavicat并将其复制到Na......
  • MySQL 如何实现数据更新
    一般在更新时会遇到以下场景：1.所有字段全部更新；2.根据条件更新字段中的某部分内容；3.根据不同的条件更新不同的值，以下是几种场景中常用的update方法。一、方法分类二、具......
  • 解决com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too
    1.今天遇到一个蛮奇怪的问题，项目一天都没啥问题，然后等我下班测试就跟我说报错报错如下：    2.然后我立马去控制台打印实时日志查看报如下错;    3.经......
  • pycharm爬虫报错：pymysql.err.DataError: (1406, "Data too long for column 'content'
     在学习爬虫的时候，获取数据存入mysql时出现了问题：pymysql.err.DataError:(1406,"Datatoolongforcolumn'content'atrow1")因为mysql也是本地搭建的，所以一时间......
  • MySql多表查询
    多表关系一对多实现：在多的一方建立外键，用来指向一的一方的主键多对多实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键一对一实现：在任意一方加入......
  • Centos 7在线安装JDK1.8+Tomcat+MySQL8.0+Nginx
    一、安装JDK注：以下命令环境在Xshell中进行。温馨提示：在线安装方式需要有网速的前提，有的软件下载需要大量时间。1、查询出系统自带的OpenJDK及版本rpm-qa|grepjdk......
  • sqlserver重建索引
    `DECLARE@objectidINT;DECLARE@indexidINT;DECLARE@partitioncountBIGINT;DECLARE@schemanamesysname;DECLARE@objectnamesysname;DECLARE@indexnamesysn......
  • k8s部署canal-1.1.6版本实现MySQL数据库数据同步
    1、版本说明软件&镜像版本&镜像信息说明Kubernetesv1.23.7k8s服务器Kuboardv3.5.2.0k8s连接管理工具Canalv1.1.6数据同步......
  • 数据库索引
    索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储......
  • MySQL 8.0.32如期而至
    GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。作者：叶金荣文章来源：GreatSQL社区原创MyS......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99