mysql 优化

sql语句优化

优化insert语句

1、如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。

2、如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入。

3、将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；

4、 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；

5、当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

优化group by 语句

默认情况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。这与在查询中指定ORDER BY col1，col2…类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL

优化order by 语句

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序

下列时会使用索引的情况

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

下列是不使用索引的情况

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
//order by 的字段混合 ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
//用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2；
//对不同的关键字使用 ORDER BY：

优化嵌套查询

在上面的查询中只有department 使用了索引,employee 并没有使用索引

改为联合查询，两个表都使用了索引

mysql 优化 or 条件

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

使用sql 提示

SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的.

use index

在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

--提示参考的索引为index_num 不考虑其他的索引
select * from employee use index(index_num) where num = 1;

ignore index

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT

--提示忽略index_name 索引
select * from employee ignore index(index_name) where num=1;

force index

为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引。

--强制使用index_num索引
select * from employee force index(index_num) where num > 0;

应用优化

1、链接池

应用上一般访问数据库，都是先和MySQL Server创建连接，然后发送SQL语句，Server处理完成后，再把结果通过网络返回给应用，然后关闭和MySQL Server的连接，因此短时间大量的数据库访问，消耗的TCP三次握手和四次挥手所花费的时间就很大了，稍微大一点的项目，我们都会在应用访问数据库的那一层上，添加连接池模块，相当于应用和MySQL Server事先创建一组连接，当应用需要请求MySQL Server时，不需要再进行TCP连接和释放连接了，一般连接池都会维护以下资源：

1.连接池里面保持固定数量的活跃TCP连接，供应用使用。

2.如果应用瞬间访问MySQL的量比较大，那么连接池会实时创建更多的连接给应用使用。

3.当连接池里面的TCP连接一段时间内没有被用到，连接池会释放多余的连接资源，保留它设置的最大空闲连接量就可以了。

2、增加Cache缓存层

比较有名的，提高数据库访问效率的缓存层当然是redis和memcache了，一般用缓存把经常访问的数据缓存起来，相当于就是提高了数据库的访问效率。
但是，添加Cache缓存层有一个非常大的问题，就是需要考虑“缓存数据的一致性”问题。
当多个事务并发读取的时候，那么先从缓存命中，如果不能命中，则去数据库中读取，再写缓存，并发读没有问题！

当多个事务进行读写的时候，事务1先写数据库，还没等更新缓存，事务2把缓存的旧值读走了，数据不一致；或者事务1写之前先把缓存删掉，再写数据库，最后写缓存，但是当事务1写数据库还没写完呢，事务2把旧值又读走了，又造成缓存数据不一致的问题！