MySql性能优化——基础篇

2016-12-31

记录工作中累计的sql正确的使用方式，从sql语句上优化mysql性能

1.分页

mysql的limit语法跟sqlserver的top比起来真的是好用太多，特别是在分页上基本是想要什么就取什么，那么当单标数据量达到百万以上时应该怎样正确的使用分页呢？

1	SELECT * FROM table ORDER BY id LIMIT 1000000, 10;

这是大部分分页的方式，然而当数据量超过百万甚至千万时耗时大概十几秒甚至几十秒！

1	SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;

这样的话可以优化只0.x秒内

1	SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

比上面那句，还要再快5至10倍

2.尽量不在sql中使用函数

尽量不要在sql中使用函数，因为这样缓存是无效的

1	SELECT username FROM user WHERE signup_date >= CURDATE()

3.为你的搜索字段建立索引

为你的搜索字段以及连接查询的关联字段创建索引可以大大提高搜索效率，不过如果你的搜索字段是varchar并且使用like模糊查询就没有必要使用索引了。

4.当判断是否存在时使用LIMIT 1

如果在表中只为了判断是否存在请使用limit 1,例如用户注册判断是否存在同名的用户等情况

1	SELECT 1 FROM user WHERE country = 'China' LIMIT 1

5.千万不要ORDER BY RAND()

6. 请不要使用SELECT *

使用SELECT *的弊端有两点：

1.需要什么字段就查询什么字段，这样可以不需要遍历所有列
2.可以减少网络传输中不必要的数据

7.建议给每张表建一个自增长的主键ID

最好给每张表建一个为ID的主键列，查询，排序时会十分方便，如果知道数据量不大，ID的类型可以为SMALLINT或者更小的无符号类型

8.尽量使用NOT NULL

我们开发过程中最讨厌的就是空指针异常了，因为他真的防不胜防，所以请尽量给每一个字段加上NOT NULL，有需要的请加上默认值

9.把IP地址存成 UNSIGNED INT

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

10.设计表的时候请垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，不仅可以加快处理速度，也可减少数据冗余，从而达到优化的目的。

11.请拆分大的连接语句

之前见过同事写的连接语句，把编译器整整暂满了10行，看起来简直蛋疼。请拆分你的连接语句，不仅可以提高执行效率而且分段查询可以减少因行锁而产生的死锁问题

12.选择正确的存储引擎

MySql的存储引擎有两种：MyISAM、InnoDB

1.MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

2.InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

13.尽量少使用连接查询

避免少使用连接查询，虽然有的同学说多次查询会增加连接次数，但是也有一个很有利的优点就是单个查询语句可以利用缓存，所以合理的拆分你的连接语句可以大大提高执行效率