MySQL使用小坑总结（一）

1、数据库中的字段类型不要使用long型，否则会造成查询缓慢，可以选择BigInt类型来代替。

2、避免select ＊。

3、如果表结构中字段定义的类型与应用传入的类型不一致，这时候可能会发生隐式转换；两个表join的时候，如果字符集定义不一致，也会导致隐式转换，mysql中定义的隐式转换规则如下所示：

a、两个参数至少有一个是NULL时，比较的结果也是NULL，例外是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换；

b、两个参数都是字符串，会按照字符串来比较，不做类型转换；

c、两个参数都是整数，按照整数来比较，不做类型转换；

d、十六进制的值和非数字做比较死活，会被当作二进制串；

e、有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp；

f、有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较；

g、所有其它情况下，两个参数都会被转换为浮点数再进行比较；

隐式转换会使索引失效，导致查询无法命中索引。

4、limit m,n

mysql翻页查询使用下面的语法：select * from table limit m,n；

其中m是指记录开始的index，数据库中的第一条记录标记为0，n是指从第m+1条记录开始，取n条数据。

比如select * from table limit 2,4，该语句取出第3条至第6条数据，共4条记录。

需要注意的是普通的limit M,N的翻页写法，在越往后翻页的过程中速度越慢，原因是由于mysql会读取表中的前M+N条数据，M越大，则性能越差。可以选择的优化方式是使用关联子查询进行优化，如下面的实验，我们先后用上下两条语句查询数据库的100条数据：

优化前：select * from table limit 10000,100;优化后：select * from hregiondb a,(select id from hregiondb limit 10000,100) b where a.id=b.id

两条查询语句的耗时如下图，可见优化后查询的速度快了不少。

5、删除表中数据

如果要清空表中的所有记录，可以使用如下两种方式：

方式一：delete from table方式二：truncate table

其中delete的方式可以返回被删除的记录数，但执行速度不快，相反，truncate不能返回被删除的记录条数，但是执行速度快。

推荐使用truncate方式，这是因为binlog有个row格式，delete时会把所有被操作数据的整行都记录在binlog里，如果表中数据量很大，会造成binlog的急剧膨胀。

此外采用先truncate后drop的方式也可以减少系统表被锁定的时间。

7、索引要仔细设计，保证索引有很好的过滤性，这是因为索引本身也占消耗。什么叫过滤性好的索引，举个例子，比如一张职工表，那么性别字段就无需建立索引。

8、optimize table

mysql中删除了数据时，被删除数据占用的磁盘空间并没有马上被释放，而是被写入了一个标记为，只有执行了optimize之后，这部分空间包括索引位才会被释放出来。

9、可以使用explain命令查看sql语句的执行计划

我们使用上面那条语句来看看explain命令的结果。

explain select * from hregiondb a,(select id from hregiondb limit 10000,100) b where a.id=b.id

结果截图如下：

各个列的解释简列如下：

1、select_type，select类型，simple表示最简单的select，primary出现在有子查询的语句中，最外面的select查询就是primary；

2、table，显示这一行的数据是关于哪张表的；

3、type，显示连接使用了何种类型，从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL；

4、possible_keys，显示可能应用在该表中的索引；

5、key，实际使用的索引；

6、key_len，使用的索引的长度，该值越小越好；

7、ref，显示索引的哪一列被使用了；

8、rows，mysql认为必须检查的用来返回请求数据的行数；

9、Extra，额外信息，如果是Using temporary或者Using filesort，意味着mysql不会使用索引，这样的检索会很慢；