mysql-索引的使用及sql的一些优化

于 2020-06-09 19:30:21 发布
阅读量538 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/chidieguai8187/article/details/106648821
版权
本文详细介绍了MySQL的优化方法，包括查看SQL执行效率、定位低效SQL、使用EXPLAIN分析执行计划、SHOW PROFILE分析SQL性能以及索引的合理使用。强调了最左前缀法则、避免全表扫描、优化OR查询、分页查询的优化策略等关键点。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

MySQL优化
一，查看SQL的执行效率

MySQL 客户端连接成功后，通过一下语句查看服务器相关状态

show [session|global] status 命令可以提供服务器状态信息

show status like 'Com_______';

在这里插入图片描述

show status like 'Innodb_rows_%';

在这里插入图片描述

参数含义
Com_select执行 select 操作的次数，一次查询只累加 1。
Com_insert执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update执行 UPDATE 操作的次数。
Com_delete执行 DELETE 操作的次数。
Innodb_rows_readselect 查询返回的行数。
Innodb_rows_inserted执行 INSERT 操作插入的行数。
Innodb_rows_updated执行 UPDATE 操作更新的行数。
Innodb_rows_deleted执行 DELETE 操作删除的行数。
Connections试图连接 MySQL 服务器的次数。
Uptime服务器工作时间。
Slow_queries慢查询的次数。
二，如何定位执行效率低的SQL语句
  • show processlist：show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

在这里插入图片描述

列含义
id用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
user显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db显示这个进程目前连接的是哪个数据库
command显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time显示这个状态持续的时间，单位是秒
state显示使用当前连接的sql语句的状态
info显示sql语句

  • 慢查询日志
    慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志

    慢日志的设置

    # 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10
三，explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

在这里插入图片描述

字段含义
idselect查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table输出结果集的表
type表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
rows扫描行的数量
filtered实际显示行数占扫描rows的比例
extra执行情况的说明和描述

  • id

    id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序

    • id相同，表示加载表的顺序是从上到下
    EXPLAIN select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

在这里插入图片描述

  • id不同，id的值越大，优先级越高，越先被执行
  EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

在这里插入图片描述

  • id有相同，有不同，同时存在的时候，id相同的认为是一组，从上到下顺序执行，在所有的组中，id值越大优先级越高越先执行
 EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

在这里插入图片描述

  • select_type

    select_type表示select的类型

    select_type含义
    SIMPLE简单的select查询，查询中不包含子查询或者UNION
    PRIMARY查询中若包含任何复杂的子查询，最外层查询标记为该标识
    SUBQUERY在SELECT 或 WHERE 列表中包含了子查询
    DERIVED在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
    UNION若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
    UNION RESULT从UNION表获取结果的SELECT

  • table

    table表示展示的数据属于哪个表

  • type
    type表示连接类型

    性能由好到差：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    常见的type如下：

    system > const > eq_ref > ref > range > index > ALL
    type含义
    NULLMySQL不访问任何表，索引，直接返回结果
    system表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
    const表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
    eq_ref类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
    ref非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
    range只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
    indexindex 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
    all将遍历全表以找到匹配的行

  • possible_keys

    显示可能应用在这张表的索引，一个或多个

  • key
    实际使用的索引， 如果为NULL， 则没有使用索引

  • key_len
    索引字段的长度

  • rows
    扫描行的数量

  • extra
    用于显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息

内容含义
Using filesortMySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
Using temporary用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not existsMYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。
Using index说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。
Using index condition这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
Using where使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接
impossible wherewhere子句的值总是false，不能用来获取任何元组
select tables optimized away在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
四，show profile分析SQL

show profiles 用来展示SQL执行过程中消耗的时间

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述指令后，使用 show profiles 指令

在这里插入图片描述

通过show profile for query [query_id] 可以查看到指定queryid 的sql 执行过程中每个线程的状态和消耗的时间

在这里插入图片描述

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

也可以选择all、cpu、block io 、context switch、page faults等查看相应的参数

show profile all query [query_id]

show profile cpu query [query_id]

show profile block io query [query_id]

show profile context switch query [query_id]

show profile page faults query [query_id]

五，索引的使用

数据准备：

create table `t_user` (
	`userid` varchar (100),
	`username` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`userid`)
)engine=innodb default charset=utf8mb4; 

insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('aaaa','USER001','撒旦','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('bbbb','USER002','徐盛','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('cccc','USER003','华生','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('dddd','USER004','张智','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('eeee','USER005','马程','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('ffff','USER006','罗小','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('gggg','USER007','方店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('hhhh','USER008','小白','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('jjjj','USER009','千度','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('kkkk','USER010','浪方','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('llll','USER011','米鱼','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `t_user` (`userid`, `username`, `nickname`, `password`, `status`, `address`, `createtime`) values('mmmm','USER012','周家','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_user_username_sta_addr on t_user(username,status,address);


COMMIT;

1，全值匹配，对索引中所有列都指定具体值

这种情况，索引生效，执行率高

explain select * from t_user where username='华生' and status='1' and address='北京市';

在这里插入图片描述

2，最左前缀法则

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

explain select * from t_user where username='华生';
explain select * from t_user where username='华生' and status='1';
explain select * from t_user where username='华生' and status='1' and address='北京市';

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

违反最左前缀法则，索引会失效

explain select * from t_user where status='1';

在这里插入图片描述

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

在这里插入图片描述

符合最左前缀法则但是只有最左列生效，出现跳跃某一列

explain select * from t_user where username='华生'  and address='北京市';

在这里插入图片描述

3，范围查询右边的列，不能使用索引

explain select * from t_user where username='华生' and status='1' and address='北京市';
explain select * from t_user where username='华生' and status>'1' and address='北京市';

在这里插入图片描述
在这里插入图片描述
username和status是走了索引的，但是address是没有走索引的，范围查询的右边的列都是不走索引的

4，不要再索引列上进行运算操作，索引将会失效

explain select * from t_user where SUBSTRING(username,0,1) = '生';

在这里插入图片描述

5，字符串不加单引号，造成索引失效（MySQL的查询优化器，会自动的进行类型转换，造成索引失效）

explain select * from t_user where username='华生' and status='1' ;
explain select * from t_user where username='华生' and status=1 ;

在这里插入图片描述

在这里插入图片描述
6，尽量使用覆盖索引，避免使用select *
尽量使用覆盖索引（只访问索引的查询，索引列完全包含查询列），减少select *

1，explain select * from t_user where username='华生' and status='1' and address='北京市';
2，explain select username from t_user where username='华生' and status='1' and address='北京市';
3，explain select username,status from t_user where username='华生' and status='1' and address='北京市';
4，explain select username,status,address from t_user where username='华生' and status='1' and address='北京市';
#查询列超出索引列，也会降低性能
5，explain select username,status,address,password,createtime from t_user where username='华生' and status='1' and address='北京市';

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

查询列超出索引列

在这里插入图片描述

extra：
 	using index ：使用覆盖索引的时候就会出现
    using where：在查找使用索引的情况下，需要回表去查询所需的数据
    using index condition：查找使用了索引，但是需要回表查询数据
    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

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

explain select * from t_user where username = '华生' and createtime = '2088-01-01 12:00:00';
explain select * from t_user where username = '华生' or createtime = '2088-01-01 12:00:00';

在这里插入图片描述

在这里插入图片描述

8，以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from t_user where username like '%华' ;
explain select * from t_user where username like '华%' ;

在这里插入图片描述

在这里插入图片描述

解决：通过覆盖所引来解决

explain select username from t_user where username like '%华' ;

在这里插入图片描述

explain select username,status from t_user where username like '%华' ;

在这里插入图片描述

explain select username,status,address from t_user where username like '%华' ;

在这里插入图片描述

9，如果mysql评估使用索引比全表更慢，则不使用索引

当表中的某类数据特别多的时候，查询此类数据，使用索引比全表还慢，则使不用索引

create index idx_address on t_user(address);
show index from t_user;
select * from t_user;

在这里插入图片描述

explain select * from t_user where address='北京市';

在这里插入图片描述

explain select * from t_user where address='西安市';

在这里插入图片描述

10， is NULL ， is NOT NULL 有时索引失效。

原理是mysql评估使用索引比全表更慢，则不使用索引，如果走索引快，则走索引

例子：当数据库中某个字段为null的数据少，is null走索引，is not null不走索引，反之亦然

11，in 走索引，not in索引失效

explain select * from t_user where username in ('华生','小白');
explain select * from t_user where username not in ('华生','小白');

在这里插入图片描述
在这里插入图片描述

12，单列索引和复合索引

尽量使用符合索引，而少使用单列索引

创建复合索引：	
	create index idx_user_username_sta_addr on t_user(username,status,address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address
	
创建单列索引：
	create index idx_user_username on t_user(username);
	create index idx_user_sta on t_user(status);
	create index idx_user_addr on t_user(address);
	
数据库会选择一个最优的索引来使用，并不会使用全部索引

12 查看索引的使用情况

show status like 'Handler_read%';	

show global status like 'Handler_read%';

在这里插入图片描述

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

13，索引下推

#现有联合索引（username，age）
select * from t_user where username like '华%' and age > 23

语句执行有两种可能：

​ 1，根据联合索引查询所有满足名称以“华”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄大于23的用户数据
​ 2，根据联合索引查询所有满足名称以“华”开头的索引，然后直接再筛选出年龄大于23的索引，之后再回表查询全行数据

第二种方式回表查询的全行数据少，这就是索引下推，mysql默认启用索引下推，可以修改参数控制

SET optimizer_switch = 'index_condition_pushdown=off';

六，SQL的一些优化

数据准备

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`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','zhang','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','li','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','yang','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','zhou','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','zhu','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','chen','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','wang','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','yao','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','feng','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','lv','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','cui','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','xu','37','4500');

create index idx_emp_age_salary on emp(age,salary);

1，order by

（1）通过对返回数据进行排序，即using filesort，也就是filesort排序，所有不是通过索引直接返回排序结果的排序都是filesort排序

explain select * from emp order by age desc;

在这里插入图片描述

（2）通过有索引顺序扫描直接返回有序数据，即using index，不需要额外排序，效率高

explain select id,age,salary from emp order by age desc;

在这里插入图片描述

（3）多字段的情况

# 均为索引字段，或者一块都有排序且排序顺序相同
explain select id,age,salary from emp order by age ,salary ;
explain select id,age,salary from emp order by age desc,salary desc;

在这里插入图片描述

#排序顺序和索引顺序不同
explain select id,age,salary from emp order by salary  desc,age desc;
# 部分字段使用排序
explain select id,age,salary from emp order by age desc,salary ;
explain select id,age,salary from emp order by age ,salary desc;
# 排序顺序不同
explain select id,age,salary from emp order by age asc,salary desc;

在这里插入图片描述

总结：尽量减少额外的排序，通过索引直接返回有序数据，where条件和oeder by使用相同的索引，并且order by的顺序和索引顺序相同，并且排序的顺序也要相同，否则会有额外操作，出现filesort

那么出现using filesort时候怎么办？

filesort的出现降低效率，通过合适的索引，可以减少filesort。但是有些查询条件的限制不能让filesort消失，那就需要加快filesort的排序操作，基于filesort，mysql有两种算法

1，两次扫描算法：首先根据条件去除排序字段和行指针信息，然后在排序区sort buff中排序，如果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，group by

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

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

3，嵌套查询

SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

 explain select * from t_user where id in (select user_id from user_role );

优化：

explain select * from t_user u , user_role ur where u.id = ur.user_id;

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

4，or

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

5，分页查询

当查询一个 limit 1000000,10 的数据时，仅仅返回1000000 - 1000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

explain select * from t_user u,(select id from t_user order by id limit 1000000,10) t where u.id = t.id

2，把limit 查询转化为某个位置的查询（使用自增主键）

explain select * from t_user where id>1000000  limit 10

接（JOIN）替代。

 explain select * from t_user where id in (select user_id from user_role );

优化：

explain select * from t_user u , user_role ur where u.id = ur.user_id;

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

4，or

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

5，分页查询

当查询一个 limit 1000000,10 的数据时，仅仅返回1000000 - 1000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

explain select * from t_user u,(select id from t_user order by id limit 1000000,10) t where u.id = t.id

2，把limit 查询转化为某个位置的查询（使用自增主键）

explain select * from t_user where id>1000000  limit 10
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值