MySQL优化

字段

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。

实数类型

尽量使用数字型字段，若只含数字信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

VARCHAR的长度只分配真正需要的空间。

VARCHAR只占用必要的存储空间。VARCHAR(20)，20个字符，不是20个字节。

CHAR(20)，20个字符。

使用枚举或整数代替字符串类型。

尽量使用TIMESTAMP，而非DATETIME。

数据类型尽量使用最小的。

单表不要有太多字段，建议在20以内。

避免定义null字段。

 

MySQL Scheme优化

对表建立索引，有大量重复值、且经常有范围查询（>，<，>=，<=）和group by、order by发生的列，考虑建立索引。

根据explain查看是否用了索引还是全表扫描。

值分布很稀少的字段不适合建索引，例如性别字段。

避免使用!=、<>、IS NULL、IS NOT NULL、IN、NOT IN这样的操作符，这样引擎将放弃使用索引而进行全表扫描。

字符串字段只建前缀索引。

字符串字段最好不要做主键。

不用外键，由程序保证约束。

尽量不用unique，由程序保证约束。

不用函数和触发器，在应用程序实现。

使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引。

有时，增加列时，先删除索引，之后再加上索引会更快。

先truncate table，然后drop table，这样可以避免表的较长时间锁定。

为不同的需求选择不同的存储引擎。

可通过开启慢查询日志来找出较慢的sql。

 

查询优化

1.避免对整个表使用count(*)，它可能会将整个表锁住。

2.尽量避免在where子句中使用!=或<>，否则引擎将放弃使用索引而进行全表扫描。

3.尽量避免全表扫描，考虑在WHERE、GROUP BY和ORDER BY的列上加上索引。

4.尽量避免在where子句中对字段进行null值判断，否则引擎将放弃使用索引而进行全表扫描。is null   is not null

 

select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询

 

select id from t where num=0

5.尽量避免在where子句中使用or来连接条件，否则引擎将放弃使用索引而进行全表扫描。

 

 

select id from t where num=10 or num=20

可以这样查询

 

 

select id from t where num=10
union
select id from t where num=20

6.下面的查询也将导致全表扫描（不能前置百分号）。索引不起作用。

 

 

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

7.使用like和通配符时，考虑优化。

 

select id from t where name like 'MYSQL%'

可以这样查询

 

 

select id from t where name>='MYSQL' and name<'MYSQM'

 

8.in和not in也要慎用，否则会导致全表扫描。

 

select id from t where num in(1,2,3)

对于连续的数值，能用between就不要用in了。

 

 

select id from t where num between 1 and 3

9.尽量避免在where子句中对字段进行表达式操作，否则引擎将放弃使用索引而进行全表扫描。

 

 

select id from t where num/2=100

应该为

 

 

select id from t where num=100*2

10.尽量避免在where子句中对字段进行函数操作，否则引擎将放弃使用索引而进行全表扫描。

 

 

select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为

 

 

select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

11.不要在where子句中的"="左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。尽量将操作移至"="号右边。

 

如果合适，用GROUP BY代替DISTINCT。

LIMIT M，N 在特定场景下会降低查询效率，有节制使用。

使用DROP TABLE，然后再CREATE TABLE，而不是DELETE FROM，以删除表中所有数据。

最小化需要查询的数据，通常来说不要使用*。

union all而不是union，若无需对结果进行去重，则用union all，union要去重，有开销。

同类型比较，数字比数字，字符比字符。

 

限制结果集的行数

使用top

 

<span style="color:#000088">select top 300 cols1,cols2,cols3 from t</span>

增加limit 1会让查询更加高效

 

引擎发现只有1后停止扫描，而不会进行全表扫描。

 

高效分页

 

<span style="color:#000088">select * from A limit 100000,10</span>

 

应改为

 

<span style="color:#000088">select id,name,age from A where id >=(select id from A limit 100000,1) limit 10</span>