mysql优化探索（6）简单的mysql优化方法（sql优化）

一、两种常见的清理表碎片，和优化表空间的方法：

1.optimize table 表名命令可以清理和合并因删除表中大部分数据，或者更改含有VARCHAR、BLOB、TEXT等不定长数据类型列时造成的空间碎片。

2.再innodb引擎的表中，可以设置innodb_file_per_table参数，设置innodb为独立表空间模式用于存储数据和索引，这样可以一定程度的减轻innodb的空间回收问题。innodb可以通过alter table 但不修改引擎的方式回收不用的空间，如下图：

需要注意的是optimize和alter table命令都会对表锁定，所以执行时最好是在数据库不频繁的时候执行。

二、一些非查询语句的优化

1.插入语句，用批量插入要比单条循环插入的性能好，因为省去了大量的连接数据库操作。

insert into test values(1,2),(2,3)...

2.order by 语句的优化

首先我们要知道，mysql的两种排序，一种是通过索引直接顺序扫描返回有序数据，对应的Extra：Using index；第二种排序排序是通过返回的数据进行排序，对应的Extra:Using filesort。由于第一种排序方式是直接用索引返回所以比第二种排序方式快的多。

根据上面文字，我们就能知道怎么优化order by 语句了：要让查询尽量使用Using index，也就是直接通过索引返回排序数据，所以我们要根据实际数据库情况建立索引。

下面用到索引的典型order by 语句

select * from tablename where key_part1=1 order by key_part2;

如果我们实在是避免不了要使用Using filesort.那我们还需要对Using filesort进行优化。Filesort有两种扫描算法，一种是先根据条件取出排序字段和行指针，然后再sort buffer中排序，如果sort buffer不够，就会建立临时表排序，排序完成之后再根据字段和行指针回表查记录。第二种方法是一次取出满足所有行的字段再sort buffer 中排序然后直接返回结果集。第一种性能慢，但是内存开销小，第二种是查询快，但是内存开销大。mysql会根据情况选择。

mysql通过比较系统变量max_length_for_sort_data的大小和query语句的取出的字段的总大小来判断使用那种方法。如果max_length_for_sort_data更大那就用第二种算法，否则用第一种。

适当加大max_length_for_sort_data的值能让mysql选择更优化的Filesort排序算法。但是max_length_for_sort_data设置过大会造成cpu利用率过低和磁盘I\O过高，所以我们要平衡改变该值大小。

适当加大配置文件中sort_buffer_size 的大小可以再排序时防止建立临时表，加快排序速度。但是过度加大也会造成swap严重，因为这个排序区的尺寸是单个线程设置的。

3.子查询的优化

子查询改成用join连接查询速度会更快如下图：

通过查询计划显示的类型可以看出ref要比index_subquery快一些。

4.or语句的优化，当使用or关键字时or关键字的前后字段都有索引才会用到索引：

 

 

以上两个图展示，当store_id不是索引的时候不会用到索引，当给store_id添加上索引之后查询就用到了索引。

5.limit翻页语句的优化方式,首先先说明一下，比如像limit 1000，10这样的分页方式，其实是mysql排序出前1010条记录后仅仅需要返回第1001到1010条记录前1000条都会被丢弃，导致查询代价非常高。

所以我们总结出两种优化方式：

第一种，先查询出带索引的一个字段，然后拿这个字段去连接查出其他的字段，这样要比直接查询要快：

 

第一张图直接查显示没有用到索引，第二张图先查出索引字段然后用索引字段去链表查询这样用到了索引。

第二种方式是，和开发人员配合，让开发人员每次执行分页语句时都记住最后一条记录的主键id，然后就可以直接用范围查找查询了，效率也会提高：

第一张图时全表扫描，第二张图，开发人员记住了上一次的位置是15640 用范围查找可以用到索引。

 6.sql提示关键字的使用，sql提示关键字能认为的让sql在执行时使用索引。

use index 关键字，提示sql使用哪个索引如图：

 ignore index 忽略使用某个索引：

 force index 强制使用某个索引，有时mysql 不会自动使用索引，或者use index 不起作用，那我们就用这个强制使用索引操作：