MySQL索引失效与优化

索引失效：

1、索引字段上使用（ != 、 <>、is null、is not null ）判断时，索引会失效，而转向全表扫描。

2、索引字段上使用通配符（%）开头，索引会失效，而转向全表扫描。

3、索引字段是字符串，但查询时不加单引号，索引会失效，而转向全表扫描。

4、索引字段使用or时，索引会失效，而转向全表扫描。

查询优化：

1、复合索引尽量全匹配。

2、最佳左前缀法则（带头索引不能死，中间索引不能断）。

3、不在索引上做任何操作。

4、不能继续使用索引中范围条件右边的列。

5、尽量使用覆盖索引。

6、innodb主键推荐使用自增列（主键建立聚簇索引，主键不应该被修改。字符串不应该做索引）（理解innodb的索引保存结构就知道了）；不用外键（由程序保证约束）

7、不用select * （消耗cpu，io，内存，带宽而且不具有扩展性）

8、or改写成in（在字段没有索引的情况下性能差别较大）

9、or改成union（索引无效变有效）

10、使用union all替代union（union有去重开销，例如分表操作）

11、少用连接join（超过3个join，一般移到业务代码里执行）

12、分页limit优化（偏移量越大，执行越慢）

mysql优化：

1、针对百万数量级，放弃在mysql中的join操作，推荐分别根据索引单表取数据，然后在程序里面做join，merge操作。

2、尽量使用nosql，例如redis，memcached等来缓存热点数据，从而缓解mysql压力。

总体优化：

1、不在数据库做运算，运算务必移至业务层。

2、库命名简洁明确（长度不能超过30个字符）

3、控制列数量（字段少而精，字段数简易在20以内）

4、平衡范式与冗余

5、拒绝3B(拒绝大sql：big sql、拒绝大事务：big transaction、拒绝大批量：big batch)

字段类优化

1、用好数值类型（用和是的字段类型节约空间）

2、字符转化为数字

3、避免使用null字段（null字段很难查询优化、null字段的索引需要额外空间、null字段的复合索引无效）

4、少用text类型（尽量使用varchar代替text字段）