MySQL索引优化与sql优化

1. sql语句优化

1.1 减少请求的数据量

• 只返回必要的列，用具体的字段列表代替 select * 语句
• 只返回必要的行，使用 Limit 语句来限制返回的数据。如果不使用 Limit 的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助 Limit 可以实现当找到指定行数时，直接返回查询结果，提高效率

1.2 尽量避免大事务操作，提高系统并发能力

1.3 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

（1）减少锁竞争；
（2）让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
（3）分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
（4）在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
（5）查询本身效率也可能会有所提升。比如使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查 询，这可能比随机的连接要更高效。

1.4 避免使用select的内联子查询

在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

1.5 尽量使用Join代替子查询

由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

select 
    b.member_id,b.member_type, a.create_time,a.device_model 
from 
    member_operation_log a 
inner join 
    (select member_id,member_type from member_base_info where `status` = 1) as b 
on 
    a.member_id = b.member_id;

1.6 避免在使用or来连接查询条件

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

1.7 写出统一的SQL语句

对于以下两句SQL语句，很多人都认为是相同的。不过数据库查询优化器则认为是不同的，虽然只是大小写不同，但必须进行两次解析，生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

select * from dual
select * From dual

1.8 避免频繁创建和删除临时表，以减少系统表资源的消耗

2. 索引优化

2.1 like语句的前导模糊查询不使用索引

select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

2.2 范围条件右边的列不能使用索引（范围列可以用到索引）

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (emp_no 、title、from_date )，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010’ and title='Senior
Engineer’and from_date between ‘1986-01-01’ and ‘1986-12-31’

2.3 在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描

select * from doc where YEAR(create_time) <= '2016';         -- 不能使用索引
select * from doc where create_time<= '2016-01-01';          -- 可以使用索引
select * from order where date < = CURDATE()；               -- 不能使用索引
select * from order where date < = '2018-01-2412:00:00';     -- 可以使用索引
select id from t where substring(name,1,3)=’abc’             -- 不能使用索引
select id from t where name like ‘abc%’                      -- 可以使用索引
select id from t where num/2=100                             -- 不能使用索引
select id from t where num=100*2                             -- 可以使用索引

2.4 where 子句中索引列使用参数，也会导致索引失效

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num                      -- 不能使用索引

select id from t with(index(索引名)) where num=@num   --可以改为强制查询使用索引：

2.5 强制类型转换会导致全表扫描

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引，因为内部发生的类型转换。

select * from user where phone=13800001234;      -- 不能使用索引

select * from user where phone='13800001234';    -- 可以使用索引

2.6 更新十分频繁、数据区分度不高的列不宜建立索引

数据更新会变更 B+ 树，在更新频繁的字段建立索引会大大降低数据库性能。类似于“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

2.7 is null, is not null 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

select id from t where num is null;  -- mysql低版本不能使用索引

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

2.8 负向条件查询不能使用索引：

负向条件有：!=、<>、not in、not exists、not like 等

例如下面SQL语句：（假设status的取值为0、1、2、3、4）

select * from doc where status != 1 and status != 2;     --不能使用索引

select * from doc where status in (0,3,4);                      --优化为 in 查询，可以使用索引