Mysql 优化技术

Mysql数据库优化

1、查询SQL尽量不要使用select ※，而是使用具体的字段 select id

例如：

select id from TEST_TABLE;

说明：select *查询时，可能不会使用到覆盖索引，导致回表查询。

2、查询结果只有一条记录或者只要最大或最小的记录，建议使用 limit 1
例如：

select id，name from TEST_TABLE where name='shun' limit 1;

说明：limit主要是防止全表扫描，从而提高性能。

3、尽量避免在where字句中使用or来作连接条件
错误例子：

select * from TEST_TABLE WHERE id=1 or age=2;

正确例子：使用union all 或者 分开写sql

select * from TEST_TABLE where id=1 union all select * from TEST_TABLE WHERE age=2;

说明：使用or查询可能会导致索引失效，从而全表扫描。

4、limit分页优化
需求：作分页时，一般会使用limit实现，当偏移量特别大时，查询效率变低。
错误例子：

select id，name，age from TEST_TABLE limit 10000，10;

正确例子：
①返回上次查询的最大偏移量，这样可以跳过偏移量。

select id，name from TEST_TABLE where id>10000 limit 10;

②order by +索引查询

select id，name from TEST_TABLE order by id limit 10000，10;

5、优化like语句
把%放前面，不走索引
把%放关键字后面，走索引
例如：

select id，name from TEST_TABLE where name like '蔡%';

6、不要再where子句中对字段进行表达式操作
错误例子：

select * from TEST_TABLE where age-1 = 10;

说明：这样会导致索引失效，从而全表扫描。

7、Inner join 、left join、right join，优先使用Inner join，如果是left
join，左边表数据比右边表要小

连接类型	说明
Inner join 内连接	只保留两张表中完全匹配的结果集
left join 左连接	会返回左表所有的行，即使在右表中没有匹配的记录
right join右连接	会返回右表所有的行，即使在左表中没有匹配的记录

错误例子：

select * from TEST_TABLE1 t1 left join TEST_TABLE2 t2 on t1.age = t2.age where t1.id>2;

正确例子：

select * from (select * from TEST_TABLE1 where id>2) t1 left join TEST_TABLE2 t2 on t1.age = t2.age;

说明：
使用左连接，条件语句放在左边处理，意味着左边返回的行数会变小，从而提高性能。
8、避免在where子句中使用!=或<>操作符
错误例子：

select age,name from TEST_TABLE where age <>18;

正确例子：分开两条sql来写

select age,name from TEST_TABLE where age <18;
select age,name from TEST_TABLE where age >18;

说明：使用!=和<>可能会让索引失效。

9、使用explain分析SQL的计划

explain select * from TEST_TABLE where id=2 or age =18;

说明：可以查看是否有索引。

10、永远为每张表设置一个ID

每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用
UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

11、使用ENUM而不是VARCHAR

ENUM 类型是非常快和紧凑的。
比如“性别”，“国家”，“民族”，“状态”，“部门”等，这些字段的取值是有限而且固定的，应该使用ENUM 而不是VARCHAR。