mysql怎么sql优化 mysql sql优化技巧

mysql 优化sql方法有哪些

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

创新互联主营邕宁网站建设的网络公司,主营网站建设方案,App定制开发,邕宁h5小程序设计搭建,邕宁网站营销推广欢迎邕宁等地区企业咨询

2.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate='2005-11-30' and createdate'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

sql优化的N种方法

1.SQL语句中IN包含的值不应过多:

例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了； 实测速度差距不是很大.

2.SELECT语句务必指明字段名称:

禁止用 * 来查询 ,禁止用 * 来查询 ,禁止用 * 来查询 , 查找哪个字段,就写具体的字段.

select * from user_test WHERE address=15988;

select address from user_test WHERE address=15988;

3.只查询一条数据的时候，使用limit 1

【这个很有用】

4.避免在where子句中对字段进行null值判断:

【实测：null值的判断依然走了索引】

explain select uid from user_test WHERE phone is null;

5.避免在where子句中对字段进行表达式操作:

6.对于联合索引来说，要遵守最左前缀法则:

例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败

注意: id, name,sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.

前三条sql都能命中索引,中间两条由于不符合最左匹配原则,索引失效.

最后一条sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

7.尽量使用inner join，避免left join：

如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

【实测：不是很准确，具体用explain测试】

8.注意范围查询语句:

对于联合索引来说，如果存在范围查询，比如between、、等条件时，会造成后面的索引字段失效。

解决办法: 业务允许的情况下,使用 = 或者= 这样不影响索引的使用.

explain select * from user_test where uid=10 and name='张三' and phone='13527748096';

explain select * from user_test where uid between( 1 and 10) and name ='张三' and phone='13527748096';

9.不建议使用%前缀模糊查询:

例如 : LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

explain select * from user_test where uid=10 and uid like "%1" ;

explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 来连接条件，如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描

解决办法: 将or连接的双方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='张三';

11.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。(此处存在疑点,我本人测试的时候,发现索引还是能使用到)

12.字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效

mysql sql优化之 优化GROUP BY 和 DISTINCT

创建表tb_point 表

准备空的tb_box表

函数

编写存储过程，给tb_box表添加100万条数据

修改关联数据

好于

优于

在执行以下语句时会报错：

前面在 文章中有提到这个问题，是直接修改sql_mode将 ONLY_FULL_GROUP_BY直接干掉。但是在《高性能mysql》中有一段话是这样的：

那么既然指出不要直接修改 sql_mode，那么我们应该如何让冲突的GRUOPBY语句正确执行呢？

文中有提到，可以使用max()和min()函数来实现；但是这种方式使用max和min函数较真的人可能会说这样写的分组查询有问题，确实如此。但是如果更加在乎查询效率，这样做也无可厚非。

如果，实在无法接受使用上面那种方式的话，可以这样使用子查询的方式来进行查询：

书上对于这种方式有描述如下：

这样写更满足关系理论，但是成本有点高，因为子查询需要填充临时表，而子查询中创建的临时表是没有任何索引的。

作者认为这样写对性能有影响。

但是从我测得结果来看，子查询的耗时反而更少。性能反而更佳。这个子查询耗时0.4秒。而使用max方式耗时0.8秒。几乎一倍。我的mysql版本是 5.7.22-log

为了解其中的原因，我们查看它的执行计划：

可见，因为子查询而产生了一层 DERIVED 临时表，但是这个临时表的Extra字段有显示 Using index、key里面显示自建索引。说明用到了索引。这是查询性能可观的一个重要原因吧；

另外我分别使用 SHOW PROFILE命令查看各部分耗时，对比之下。没看到有哪部分耗时差别特别大，使用JOIN、MAX 耗时比上子查询耗时都差不多是1倍

有些时候对一没有建立索引的字段，进行GRUOP BY时。会产生Using filesort 文件内排序。因为GRUOP BY是在排序的基础上进行分组的。

如下面sql：

如果业务上不对排序有要求。那么就可以禁止GRUOP BY的排序：

这样就把Using filesort给干掉了！ 执行时间 1.237

当然，多数情况是多排序有要求的。此时也可以在GRUOP BY后面使用DESC和ASC关键字，使分组的结果集按需要的方向排序。如下：

分组查询的一个变种就是要求mysql对分组结果再进行一次超级聚合。可以使用GROUP BY WITH ROLLUP 来实现这种逻辑，但可能性能不佳。因为通过查询计划分析出它是使用 Using temporary; Using filesort 来实现的。

使用WITH ROLLUP，查询时间2.531秒。不使用0.774 秒。

1、所以，很多时候。我们在应用程序中做超级聚合是最好的！

2、当然也可使用UNION ALL 来实现：

3、还可以通过FROM子句嵌套使用子查询：

MySQL——SQL优化

优化的目的就是让sql语句使用索引，避免进行全文检索，以此加速检索的速度。在使用mysql的过程中，我们接触最多的就是sql语句了。这是最容易优化也是最常优化的地方。

SQL优化有以下几点：

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

explain的作用

mysql的sql语句优化5种方式

只有5种吗？我知道十种以上的说。

索引（没我得全表查询了）

改变数据储引擎（MyISAM没事务再也不用担心锁表了）

增加冗余数来减少连表查询数（消耗硬盘空间减少CPU使用）

调整查询顺序减少查询量优先（数量少了连表的笛卡儿积也少了）

全文索引（文字长度有限制，而且IO使用量会大增，但是妥妥的快）

查询尽量不要用函数（函数可是不走索引的哦亲）

查询变量类型要提前对好减少系统负担（我提前改变了系统你就不用检测了）

升级服务器硬件（没什么是氪金解决不了的）

配置好临时表空间，合理理由临时表减少主表查询抢资源（唯我独查）

合理理由函数减少系统的判断（明明都能确认内容不同你用UNION 系统还是傻傻的查一遍是否重复 UNION  ALL则跳过这个步骤同理 inner join 和 left join 也一样 ）

强制走索引（复合索引的情况有时候手动走比系统判断要好哦）

脏读、幻读等（你堵车我绕路）

数据归档，迁移（没用的数据要进仓哦，别占着主表的资源）

表的碎片整理（迁移后碎片整理更健康哦亲）

索引重构（数据都走了索引也应该重构一下才能保证速度哦）

善用存储过程（串N个表（N大于10）的查询千万别一个SQL到底，分布式查询在吧结果集合并吧骚年）

预处理数据（mysql也有job哦，对于经常要子查询的数据可以先弄个明细表根据主表在后台进行补完，查询的时候就更方便了）

懒得说了。。。。。。。。。。。。。。。。。。

mysql数据库怎么优化sql语句

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

1，slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2，long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3，slow_query_log_file

记录日志的文件名。

4，log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

二、检测mysql中sql语句的效率的方法

1、通过查询日志

（1）、Windows下开启MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上

代码如下

log-slow-queries = F:/MySQL/log/mysqlslowquery。log

long_query_time = 2

（2）、Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

代码如下

log-slow-queries=/data/mysqldata/slowquery。log

long_query_time=2