mysql语句优化

1、选取最适用的字段属性
例：能用char则不用varchar，长度也要尽可能小

2、使用连接（JOIN）来代替子查询(Sub-Queries)
例：SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL

3、使用联合(UNION)来代替手动创建的临时表
例：SELECT Name,Phone FROM client UNION
SELECT Name,BirthDate FROMauthor UNION
SELECT Name,Supplier FROM product

4、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
例：SELECT * FROM customerinfo 应改为：select id，name，sex from customerinfo

5、在 where 子句中使用参数，也会导致全表扫描。
例：select id from t where num =1 应改为 select id from t with(index(索引名)) where num = 1

6、当多个用户同时使用相同的数据源时，事务会提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
使用"start transaction"开启MySQL数据库的事务
conn.commit();
conn.rollback(sp);
7、当数据量太大时，加索引可以使查询速度变快
例：加主键索引：ALTER TABLE table_name ADD PRIMARY KEY ( column )

8、关于like语句，一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。
例：like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。大数据量情况下索引失效会使查询速度变慢

9、sql语句避免在行上进行运算，以免导致索引失效
例：select * from users where YEAR(adddate)<2020;应改为select id，name，sex from users where adddate<‘2020-01-01’;

10、NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
例：NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

11、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
例：设置全文索引：ALTER TABLE table_name ADD FULLTEXT ( column)

12、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
例：select id from t where num is null 应改为： 在num上设置默认值0，然后查询：select id from t where num = 0

13、避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
例：用between 代替：select id from t where num between 1 and 3

14、避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
例：select id from t where num=10 or Name = ‘admin’ 应改为：select id from t where num = 10 union all select id from t where Name = ‘admin’

15、in 和 not in 也要慎用，某些连续数值情况会导致全表扫描，可用exists代替in
例：select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
应改为：
select num from a where exists(select 1 from b where num=a.num)

16、如果在 where 子句中使用参数，也会导致全表扫描。
例：select id from t where name like ‘%abc%’

17、避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
例：
select id from t where substring(name,1,3) = ’abc’应改为 select id from t where name like ‘abc%’

18、避免没有意义的查询，如需要生成一个空表结构：
例：
select col1,col2 into #t from t where 1=0不会返回任何结果集，但是会消耗系统资源，应改成这样：
create table #t(…)

19、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
UPDATE employees SET lastname = ‘Hill’,email = ‘mary.hill@yiibai.com’ 应改为 UPDATE employees SET email = ‘123456@yiibai.com’

20、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。