数据库优化

索引优化与查询优化

1.概述

1.1优化方向

索引失效，没有充分利用到索引 -------索引建立
关联查询太多JOIN（设计缺陷或需求必须的要求）-------SQL优化
服务器调优及各个参数设置（缓冲，线程数等）-------调整my.cnf
数据过多------分库分表

1.2分类

物理查询优化：
1）索引
2）表连接
逻辑查询优化：
等价变换

2.导致索引效率低案例

2.1匹配最合适的索引

三个索引中，在执行查询语句时选择最合适的索引效率最高。此时，选择的是联合索引idx_age_classid_name，效率优于其他两个索引，另外两个索引冗余。

2.2最佳左前缀法则

对于联合索引（多列索引），查询语句的过滤条件使用索引时，需要按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法使用

2.3主键插入顺序

对于使用innodb存储引擎的表来说，在没有显示的创建索引时，表中的数据实际存储在聚簇索引的叶子节点（当没有显示的创建主键时，会自动指定一个主键，会自动为主键创建索引）。这些数据以数据页为单位存放在硬盘中，数据页中的记录以主键按从小到大的顺序排列，如果插入记录按递增的顺序插入，那么每插满一个数据页就换到下个数据页继续插入。而当插入记录没有按照递增顺序插入的话，需要在插入位置将后续记录向后移，此时造成了性能的浪费。

2.4计算，函数，类型转换（自动或主动）导致索引失效

查询name字段以abc开头的记录时，第一条语句使用模糊查询，使用到了索引。第二条语句，先计算所有记录中最左边的三个字符，查询了所有记录，没有使用索引，效率低下。

2.5范围条件右边的列索引失效

联合索引创建顺寻：age,classId,NAME
查询语句中，先age等值匹配，然后classId范围匹配后，最后一个字段name没有使用到索引

2.6不等于（！=或者<>）索引失效

使用==作为过滤条件时，b+树结构可以保证数据页加载到内存的数量变少，而过滤条件是！=时，需要加载所有的数据页

2.7 is null可以使用索引，is not null无法使用索引

同理，在查询中使用not like也无法使用索引，导致全表扫描
因此，最好在设计数据表的时候，就将字段设置为not null约束，比如将int类型字段默认值设为0，将字符型的默认值设为空字符串’’

2.8like以通配符%开头索引失效

要求：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

2.9or前后存在非索引的列，索引失效

or前后都有索引时，会使用合并索引

数据库和表的字符集不统一，索引失效

应统一使用utf8mb4,统一字符集可以避免由字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3.子查询优化

子查询结果集较大时，无法为结果集（表）创建索引，需要考虑将子查询改造为多表查询

4.排序优化

4.1排序方式

1）index排序，索引可以保证数据的有序性，不需要进行排序，效率更高
2）FileSot排序，在内存中排序，占用cpu较多。如果排序结果较大，会产生临时文件i/o到磁盘进行排序，效率较低

4.2优化建议

1）SQL中，可以在where和order by子句中使用索引，目的是where子句避免全表扫描，在order by子句避免FileSort排序。
2) 尽量使用index完成order by排序，如果where和order by后面是相同的列就是用单列索引，如果不同就是用联合索引
3）无法使用index时，需要对FileSort方式进行调优