mysql in 优化 mysql常用优化方案

mysql where in(几千个ID)如何优化

// 等效为：

你是指这种吗？update表1aseta.字段='test'whereexists(select1from表2bwherea.字段1=b①磁盘寻道能力（磁盘I/O），我们现在上的都是SAS15000转的硬盘。MySQL每秒钟都在进行大量、复杂的查询作，对磁盘的读写量可想而知。.字段1)将查询的条件都放在exists里面

mysql in 优化 mysql常用优化方案

mysql in 优化 mysql常用优化方案

MySQL 的子查询和left join的比较，什么时候用子查询效率高，什么时候用left join效率高？

对于以下表达式，优化器使用个 = ，也会使用第二个 >= ,但是忽略其他索引部分，并且不将第三部分用作间隔构造。

子查询优化策略

对于 BTREE 索引，一个间隔可以是使用 AND 组成的多个范围条件的，每个条件都将索引的一部分和一个常量使用 =,<=>,IS NULL,>,<,>=,<=,!=,<>,BETWEENT,LIKE 'pattern'(pattern不以通配符开始) 进行比较。只要可以确定与条件匹配的一个索引元组，就可以使用一个间隔（ !=,<> 使用两个间隔）

对于不同类型的子查询，优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：

semijoin

Materialization

exists

2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：

Materialization

exists

3. 对于 derived 派生表，优化器有如下策略选择：

derived_merge，将派生表合并到外部查询中（5.7 引入 ）；

将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL作。

子查询虽然很灵活，但是执行效率并不高。

可以使用JOIN、LEFT JOIN等方式代替子查询，这些方式不需要建立临时表，因此速度比子查询快。

mysql表用count字段排序，出现using temporary怎么优化

select from T where number = 1 lock in share mode;

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。前者使用起来比较简便。使用。

–with-charset=latin1 –with-extra-charset=utf8,gbk –with-innodb

1. check table 和 repair table

登陆mysql 终端：

mysql -uxxxxx -p dbname

check table tabTest;

如果出现的结果说Status是OK，则不用修复，如果有Error，可以用：

repair table tabTest;

进行修复，修复之后可以在用check table命令来进行检查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。

2. myisamchk, isamchk

其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：

myisamchk tablename.MYI

进行检测，如果需要修复的话，可以使用：

myisamchk -of tablename.MYI

关于myisamchk的详细参数说明，可以参见它的使用帮助。需要注意的时在进行修改时必须确保MySQL没有访问这个数据表，保险的情况下是在进行检测时把MySQLShutdown掉。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

另外可以把下面的命令放在你的rc.local里面启动MySQL前：

[ -x /tmp/mysql.sock ] && /pathtochk/myisamchk -of /DATA_DIR//.MYI

其中的/tmp/mysql.sock是MySQL的Sock文件位置，对于使用RPM安装的用户应该是/var/lib/mysql/mysql.sock，对于使用源码安装则是/tmp/mysql.sock可以根据自己的实际情况进行变更，而pathtochk则是myisamchk所在的位置，DATA_DIR是你的MySQL数据库存放的位置。

需要注意的时，如果你打算把这条命令放在你的rc.local里面，必须确认在执行这条指令时MySQL必须没有启动！检测修复所有数据库(表)

mysql查询语句in和exists二者的区别和性能影响

MySQL IN 语法

如果查询的两个表大小相当，那么用port = 3306in和exists别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）

1：

select from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；

select from A where exists(select cc from B where cc=A) 效率高，用到了B表上cc列的索引。

相反的

2：

select from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；

select from B where exists(select cc from A where cc=B) 效率低，用到了A表上cc列的索引。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

in 与 =的区别

select name from student where name in ('zhang','wang','li','zhao');

与select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

的结果是相同的。

Mysql使用limit深度分页优化

范围访问方法使用一个索引来检索包含一个或多个索引间隔中的表行的子集。它可以使用索引中的一列或者多列，以下各节描述了优化器使用范围访问的条件

mysql使用select limit offset, rows分页在深度分页的情况下。性能急剧下降。

limit用于数据的分页查询，当然也会用于数据的截取，下面是limit的用法：

1. 模仿百度、谷歌方案（前端业务控制）

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了

2. 记录每次取出的id， 然后where id > id

select from table_name Where id > id limit 10000, 10;

这种方法适用于：除了主键ID等离散型字段外，也适用连续型字段datetime等

id由前端分页pageNum和pageIndex计算出来。

3. IN获取id

4. join方式 + 覆盖索引（）

如果对于有where 条件，又想走索引用limit的，必须设计引用一个 github 上面的评论 地址：一个索引，将where 放位，limit用到的主键放第2位，而且只能select 主键！

1. jdbcpagingReader使用方式

2. db索引分区器使用方式

入参1： 表名 如test_table

入参2： 排序索引字段 可以是主键，也可以是其他索引。需要保证是索引即可。如：id

入参3： 主键可手动传入，也可以根据表名计算出来：现在只支持单列主键的。 如：id

入参4: 具体表 要分多少块。如：4

MySQL RANGE优化

#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不

对于一个单列索引，索引值间隔可以方便地由 WHERE 条件中的相应条件表示，表示为范围条件而不是 intervals 。

SELECT FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0)

上述的 常量 指以下情况之一：

以下是在 WHERE 子句中具有范围条件的查询示例

MySQL对于每个可能使用的索引，尝试从 WHERE 子句中提取范围条件。在提取过程中，不能用于构建条件范围的条件被删除，产生重复范围的条件被合并，产生空范围的条件被删除。

假设有以下语句， key1 是一个被索引的列，而 nonkey 没有索引

提取 key1 索引的过程如下：

通常，范围扫描使用的条件比 WHERE 子句中的限制要少()。MySQL执行额外的检查来过滤满足范围条件但是不完全满足 WHERE 子句的行。

范围条件提取算法可以处理任意深度嵌套的 AND/OR 构造，并且它的输出不取决于条件在 WHERE 子句中出现的顺序

MySQL不支持为空间索引的 range 访问合并多个范围。要解决此限制，可以在相同的 SELECT 语句中使用 UNION 语句，将每个空间谓词放在不同的 SELECT 中。

多列索引的范围条件是单列索引的扩展，多列索引的范围条件将索引行限制在一个或多个索引元组的间隔中。索引元组间隔是一个按照索引顺序的，索引元组的。

假设有一个多列索引 key1(key_part1,key_part2,key_part3) ,按照索引顺序，具有以下键值元组列表

key_part1 = 1 定义了一个间隔： (1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf) ，这个间隔包括上面的第4、5、6个元组并且可以被用来进行范围访问。

但是， key_part3 = 'abc' 没有定义间隔并且不能被范围访问方法使用。

就是索引的最左前缀原则，B树索引是有序的，多列索引是首先按照列进行排序，然后在列排序的基础上，再对第二列数据进行排序，所以后面的列的顺序来看不是有序的，就不能单独用后面的列来进行排序或者范围访问的作。

对于 HASH 索引，只能使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔：

这里， const1,const2... 是常量， cmp 是比较表达式： =,<=>,IS NULL ,并且条件覆盖所有的索引部分（就是说，如果有 N 个条件，那么每个条件都需要是一个 N列 索引的一部分）。例如：以下是一个三列 HASH 索引的一个范围条件

当比较运算符是 =,<=>,IS NULL 时，优化器尝试使用索引的其他部分来确定间隔。如果比较运算符是 >, <, >=, <=, !=, <>, BETWEEN, LIKE ,优化器使用索引，但不考虑索引中的其他列。

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单个间隔为：

创建的间隔中可能包括比原始条件更多的行，比如，前面这个间隔可能会包括 ('foo',11,0) 这个值， 0<10 ,这个值不满足原始条件

如果覆盖间隔中的行的条件使用 OR 进行组合，则他们会形成间隔的并集。

如果条件使用 AND 进行组合，他们形成一个包括间隔交集的行。

示例：

这个在两列索引上的条件：

(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

间隔是：

可以查看 EXPLAIN 输出中的 key_len 部分查看使用的索引前缀的长度。

在某些情况下， key_len 包括已使用的索引列，但是这个列可能不是你期望的，假设 key_part1 和 key_part2 可以为 NULL ,然后， key_len 显示以下条件的两个索引部分长度：

但是实际上，这个条件被转换为：

key_part1 >= 1 AND key_part2 IS NOT NULL

假设以下表达式, col_name 是一个索引的列

只要 col_name 等同于这些值中的任意一个，这个表达式结果就是 true 。这种比较是等值范围比较（其中的“范围”是一个单独的值）。

优化器按照以下方法，估算读取相等的值来进行等值范围比较的成本：

当使用 index dive 时，优化器在每个范围的末端进行 dive 并且使用该范围中的行数作为估算值。例如： col_name IN (10, 20, 30) 具有三个等值范围，优化器对每个范围进行两次 dive 以生成估算值。每次 dive 都会得出具有给定值的行数的估算值。

使用 index dive 提供了准确的行数估算值，但是随着表达式中要比较的值的数量增加，优化器需要使用更长的时间来生成行数的估算值。而使用索引统计信息的准确性不如直接使用索引，但是可以对大表进行更快的估算。

eq_range_index_dive_limit 选项可以控制优化器选择评估策略的值。要对 N 个等值范围使用 index dive ，将 eq_range_index_dive_limit 设置为 N+1 ,要禁用统计信息，总是使用 index dive ，将 eq_range_index_dive_limit 设置为0。

在MySQL8.0以前，除了使用 eq_range_index_dive_limit ，没有其他方法可以跳过 index dive 。在MySQL8.0中，当满足以下条件时，跳过 index dive :

对于 EXPLAIN FOR CONNECTION ,如果跳过了 index dive ，输出结果有所变更：

不包括 FOR CONNECTION 的 EXPLAIN 输出没有变化

在执行跳过 index dive 的查询后， INFORMATION_SCHEMA.OPTIMIZER_TRACE 表包含一个值为 skipped_due_to_force_index 的 index_dives_for_range_access 行

优化器可以对这种形式的查询进行范围扫描：

要使用范围扫描，查询必须满足以下条件：

要控制有多少内存可以用来进行范围优化，使用 range_optimizer_max_mem_size 变量

使用以下原则估算范围扫描使用的内存：

IN() 中的每个值被当做使用 OR 结合的一个谓词。如果有两个 IN() 列表，每个列表中都是列表中的值的数量个谓词通过 OR 结合。在这种情况下，视作 M × N 个 谓词通过OR 结合。

求教，mysql千万级数据多表查询做分页该如何优化

#默认为64K

查询指定的记录通过Id进行in查询来获得真实的数据.其实不是而是必须，也就是你应该先查询出复合的ID列表,通过in查询来获得数据

我们来做一个测试ipdatas表：

CREATE TABLE `ipdatas` (

`uid` INT(8) NOT NULL DEFAULT '0',

`ipaddress` VARCHAR(50) NOT NULL,

`source` VARCHAR(255) DEFAULT NULL,

`track` VARCHAR(255) DEFAULT NULL,

`entrance` VARCHAR(255) DEFAULT NULL,

`createdtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

`skip-name-resolvecreateddate` DATE NOT NULL DEFAULT '0000-00-00',

PRIMARY KEY (`id`),

KEY `uid` (`uid`)

) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;

这是我们做的广告联盟的推广ip数据记录表，由于我也不是mysql的DBA所以这里咱们仅仅是测试

因为原来里面有大概70152条数据

这里我们通过jdbc的batch插入6000万条数据到此表当中“JDBC插入6000W条数据用时:9999297ms”；

大概用了两个多小时,这里面我用的是batch大小大概在1w多每次提交,还有一点是每次提交的数据都很小，而且这里用的myisam数据表，因为我需要知道mysql数据库的大小以及索引数据的大小结果是

ipdatas.MYD 3.99 GB (4,288,979,008 字节)

ipdatas.MYI 1.28 GB (1,377,600,512 字节)

这里面我要说的是如果真的是大数据如果时间需要索引还是改成数字字段,索引的大小和查询速度都比时间字段可观。

步入正题:

SELECT COUNT(id) FROM ipdatas;

SELECT COUNT(uid) FROM ipdatas;

SELECT COUNT() FROM ipdatas;

首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的条数

查询索引条件

SELECT COUNT() FROM ipdatas WHERE uid=1; 返回结果时间:2分31秒594

SELECT COUNT(id) FROM ipdatas WHERE uid=1; 返回结果时间:1分29秒609

SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回结果时间:2分41秒813

条开始查询

SELECT FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒

mysql 中 创建索引很慢，怎么解决

一些非常量可能会在优化器传播阶段转换为常量

你可以先把表里的数据备份导出成sql脚本

创建索引很慢一般是由于表里的数据太多造成的，

用delete from table；删除表里所有的数据

给 表创建索引，因为表里没有数据，速度 会很快

执行备份的sql脚本导入数据到表里

mysql 里 delete in 语句暴慢无比 优化

十万级别查询量 志强e5 cpu 单核 超3分钟才能跑完。

优化后10秒内可以跑完。

思路 通过临时表创建索引用 空间换时间避免频繁SELECT FROM ipdatas LIMIT 1,10 ; 15ms读取原表信息

mysql删除新版本的mysql配置起来不象以前的那个傻瓜化了，这个问题折腾了我一上午的时间，晚上回来总算是解决了，嘿嘿，又学到一些东西。原则

not exist 比not in执行效率高 （线上项目保持正确性，没有尝试网上有人使用 not exist 由于改动大没有尝试）

truncate 比 delete执行效率高