MySQL常用优化

SQL优化

1.字段加索引。这个是大家都能想到的,但不得不说,加索引也是有技巧的,对于一些区分不是很大的情况来说,例如一个字段表示删除与否的状态只有0,1两种值的情况下,这个字段就不要加索引了。意义不大。同时,如果某张表里有排序字段的话,而且出现的频率比较高的,例如通过符合索引来加字段。例如(field_name,create_time),经常需要这样查询where field = field_name order create_time。当然,复合索引也遵循前缀的原则,当我们只是需要where field = field_name的时候,查询也会走索引。

2.使用IN查询时里面的值不应该过多。MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 。

3.在查询的过程中如果不需要查询出全部字段,尽量不要使用select *查询。MySQL在查询数据的时候,需要先把数据先拿出来然后再按照条件来筛选的。如果用*查询的话,就需要把整条记录都拿出,增加了很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性。

4.当需要拿出一条数据时,在sql结尾处最好加上limit 1。

5.模糊查询的时候进行用右模糊的方式。例如select field_name from table_name like field_name like 'xxx%'。这样做的好处就是在查询的时候会走索引,减少查询时间。

6.连表查询的时候尽量使用inner join,避免left join;被驱动表的索引字段作为on的限制字段以此合理利用索引。参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

7.查询的判断条件中尽量的不要使用!=,not in或者是判断null等情况。这些情况会导致查询的效率变慢。

8.合理的利用分页方式来提高分页效率。例如select id,name from product limit 800000, 20。这条sql语句在随着表的增大的过程中查询会变得越来越慢。这种情况我们可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。例如select id,name from product where id > 800000 limit 20。

9.如果限制条件中其他字段没有索引,尽量少用or。or条件中如果有其他字段没有索引的话,容易使得mysql在查询的时候不走索引。大多时候都是用union all来代替or。

10.where字句中避免对字段进行表达式操作。例如select user_name from user where age*2=30。对字段进行算数操作会使其不走索引,所以这种情况应该改成select user_name from user where age=15。

11.必要是强制使用force index让查询走索引。有时候MySQL的优化器会走它认为的合理的索引,但那时候可能不是我们想要的,所以有时候要使用强制所以的命令。

12.连表时尽量使用inner join。参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表。

SQL检验

1.使用Explain来查看自己的sql语句使用索引的情况。如果是在命令行模式下的话,则是explain select.........的形式来查看。如果是用的Navicat这样的sql工具的话,也可以在查询的时候直接按解释,也会出现sql索引使用的情况。形如下面:

MySQL常用优化

Explain

其主要关键的几个字段的意思如下:

  1. type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别

  2. key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式

  3. key_len列,索引长度

  4. rows列,扫描行数。该值是个预估值

  5. extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary

当然的,mysql还有其他方面的优化。如果你真的想要深入对mysql的理解的话,也可以买一些书籍来看,深入mysql才能真正知道如何优化最好。

2.通过慢查询日志来定位查询时间久的sql语句。执行以下命令时,可以查询当前的慢查询的设置时间,默认是10s。

show variables like 'long_query_time'

修改慢查询时间,以下命令是设置慢查询时间为2s。

set long_query_time=2

然后我们允许sql语句,如果sql语句时间超过2s的话,就会被记录。相应的,记录的位置我们可以参考MySQL的my.ini文件里的datadir这个参数的目录,进去相应的目录之后,我们可以看到一个slow.log结尾的文件,里面记录的就是我们运行的超过2s的mysql的查询记录。


头像
QQ登录: