Mysql常用优化技巧

导致SQL执行慢的原因：

• 硬件问题：如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
• 没有索引或者索引失效 数据过多
• 服务器调优及各个参数设置（调整my.cnf）
• 由于多表关联造成了大量的复杂的SQL语句

使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的，是否有全表扫描等；

1 选取最适用的字段属性

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

• 使用enum而不是varchar， enum保存的是tinyint类型，但其显示为字符串。enum用来作一些选项列表很合适，比如“性别”、“状态”或“所属部门”等，你知道这些字段的值是固定且有限的，那么可以考虑使用enum。
• 对于性别这个字段，一般分为两种，有可能还有保密这种情况，那么我们可以使用数字1、2、3来分别表示这种情况，而对于这些数字含义的区分则是业务层的事情了。 我们需要将一些繁琐的需要计算的步骤全部放到业务层（服务层），因为系统的瓶颈在数据库，我们不能将过多的计算过程压到数据库上面去。数据库存储的数据应该尽量简单，但是，我们会在业务层结合具体的业务，对这些简单的数据进行分析。
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
• 尽可能的使用not null，除非你有一个很特别的原因要去使用null值，你应该总让你的字段保持为not null。可以在字段上设置默认值0，确保表中字段列中没有null值。

2 为搜索的字段建立索引

• 查询的条件尽量使用索引字段，如某一个表有多个条件，就尽量使用复合索引查询。复合索引使用要注意字段的先后顺序，且必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引(最左原则)，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。
• 在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，索引带来的性能提高比较明显。

注意：

• 对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

• 尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。

• 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

• 应考虑在 where 及 order by 涉及的列上建立索引。

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

• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致搜索引擎放弃使用索引而进行全表扫描。尽量将or 转换为 union all。

• in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了。用exists代替in，not exists代替not in，效率会更好；。

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

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

select id from t where num/2 = 100
应改为:
select id from t where num = 100*2

• 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量。
  sql select id from t where num=@num
• like查询（前置百分号）也将导致全表扫描，前模糊和全模糊不会走索引。
  select id from t where name like ‘%abc%’

3 其他注意事项

1）当只需要一条数据时，使用LIMIT 1

作为开发者，我们能够知道查询结果的条数，若已经知道结果中只有一条数据的时候，一定要使用limit 1 ，这样一来，MySQL在查询到一条数据之后，会立即停止搜索，这会带来性能上的提升。

2）避免全表查询

• 尽量使用limit进行分页批量查询，不要一次全部获取。
• 绝对避免select *的使用，尽量select具体需要的字段，减少不必要字段的查询；

3）为每张表设置一个id作为主键

这个id最好是一个int类型的，推荐使用unsigned，并将其设置为自动增加auto_increment。如果将varchar的字段作为主键， 那么在数据量较大的时候，数据库性能会出现下降，所以尽量不要使用varchar来当主键。

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

• 多表关联尽量用join，减少子查询的使用。表的关联字段如果能用主键就用主键，也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
• MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个查询工作。

5）避免使用HAVING子句

HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

5）千万不要 ORDER BY RAND()

7）尽量避免大事务操作，提高系统并发能力。

4 选择正确的存储引擎

myisam适合一些需要大量查询的应用。但其对于大量写操作并不是很好，因为它使用到的是表级锁，所以在你更新的时候，整张表都会被锁起来，试想一下，当你在更新某一行数据的时候，导致其他的行都无法被访问，这会不会很难受呢。但是，myisam对于select count(*)这类操作的计算时很快的。而至于innodb而言，对于一些小的应用，它会比myisam还慢。它支持的是行级锁，于是写操作较多的时候，它会更加优秀。它还支持一些更高级的应用，比如说：事务。

参考博客：
[1] https://blog.csdn.net/daaikuaichuan/article/details/84928272
[2] mysql sql 语句 优化 常用的30种方法