mysql sql语句优化建议

1.select * from table_name where;

建议将*改为需要的列。影响网络传输和内存。

2.like语句

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

3.不要在列上进行运算，无法运用索引

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

4.不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

5.limit huge_num,offset

在分页的时候，当huge_num比较大的时候，这样分页有一个很大的性能问题。我用‘limit 1000,20;’去1000-1020行的记录，会遍历1020行，然后前1000条记录都会被抛弃。

SELECT * FROM payment ORDER BY rental_id LIMIT 100,10;

思路一：使用子查询,按照索引分页后回表方式改写sql

SELECT * FROM payment a INNER JOIN (SELECT payment_id FROM payment ORDER BY rental_id LIMIT 100,10)b ON a.payment_id = b.payment_id;

思路二：使用between … and…

#不建议，用处不大SELECT * FROM payment WHERE rental_id BETWEEN 100 AND 110 ORDER BY rental_id;

思路三：和开发人员协商，翻页过程通过增加一个参数last_page_record，来记录上一页最后一行排序编号，然后通过该参数范围查找下一页的记录。

6、 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

7、访问频率较低的大字段拆分出数据表。

有些大字段占用空间多，访问频率较其他字段明显要少很多，这种情况进行拆分，频繁的查询中就不需要读取大字段，造成IO资源的浪费。

8、数据库字段类型定义

1、经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段，如用TIMESTAMP(4个字节，最小值1970-01-01 00:00:00)代替Datetime（8个字节，最小值1001-01-01 00:00:00）,
2、字符型变长字段使用varchar，不要使用char
3、对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中。

9、索引字段上进行运算会使索引失效

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

SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2

9、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

10、把列设置为NOT NULL

允许NULL的列不仅占用更多磁盘空间，而且会影响查询分析器对SQL语句的优化，在业务场景允许的情况下应优先设置列为NOT NULL，并赋予默认值如空白字符串、-1等

11、优先把索引设置为唯一

如果已知一个列的内容不会出现重复的行且有必要建立索引，则应建立唯一索引，除了防止误操作，还可以明确的告知查询分析器，某些查询只要找到一条符合条件的记录就可以结束扫描了。

12、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

13、使用哈希索引

如果在一个很长的字符串列上做精确查找，直接建立索引可能不是最好的办法，这会导致占用更多磁盘空间和索引效率的降低，例如这个查询

select url from myurls where url='http://blog.csdn.net/autfish/article/details/51660864'; 

可以考虑从应用层面上优化，对myurls表增加一个int列hashurl，在插入记录时通过一定哈希算法计算url的哈希值，记入hashurl列，并对该列建立索引

查询语句修改为：

select url from myurls where hashurl=3346369 and url='http://blog.csdn.net/autfish/article/details/51660864'; 

通过hashurl的索引会过滤掉大部分不符合条件的行数，后面的精确匹配解决了哈希冲突问题。

14、避免类型转换
　　这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换：
　　人为在column_name 上通过转换函数进行转换
　　直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引，如果非要转换，应该在传入的参数上进行转换
　　由数据库自己进行转换
　　如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。

15、尽量用 union all 代替 union
　　union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

16、尽量少 or
　　当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上 MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

17、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID    WHERE salesinfo.CustomerID IS NULL

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