索引与sql的优化

一、索引

1.索引的定义：数据库维护着满足特定查找的数据结构，这些数据结构以某种方式引用指向数据，这样可以在这些数据结构上实现高效查找，这些数据结构就是索引；

2.分类：

（1）主键索引：设定为主键后，数据库会自动建立索引，innodb为聚族索引，主键索引其引列值不能为空；

（2）唯一索引：索引的列值为一，可为空，但只能存在一个；

（3）复合索引：一个索引包括多个列

（4）单值索引：一个索引只包含单个列，一个表中可以有多个单值索引；

3.索引的优势与劣势

（1）优势：

①提高数据检索效率，降低磁盘io成本；

②通过对数据的排序，减低排序成本；

（2）劣势：

①索引虽然提高了查找效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系；

②系统成本提高，需要成本维护索引，一个性能良好的索引需要不断地更新优化；

4.索引的适用场景

（1）主键自动建立唯一索引

（2）频繁作为查询条件的字段（where后面的字段）

（3）查询中与其他表关联的字段（各种join on后面的字段）

（4）单值/复合索引选择？（高并发下倾向选择复合索引）

（5）查询中排序的字段

（6）查询中统计或分组的字段

（7）当表中数据较少，数据频繁更新，where后面用不到的字段时不适合建立索引

5.索引失效的情况

（1）like 以通配符('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：如果你的索引有多个字段,索引字段有 kq_time, card_no, kq_type这三个字段 (最左前缀就是按这三个字段的前后顺序进行排序的)

（2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

（3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

（4）尽量使用覆盖索引，不要select *

（5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

（6）IS NULL、IS NOT NULL无法使用索引，理由同上

（7）字符串不加单引号索引失效

隐式转换-->函数操作、

（8）用or连接时会导致索引失效

二、sql的优化

1.大量插入数据优化

（1）对一个普通字段添加索引：create index  索引名 on 表名（字段名）；创建成功后通过该字段进行查询会提高sql的查询效率

（2）主键顺序插入：因为innodb类型的表是按照主键的顺序存储的，所以导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果innoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用主键顺序插入以提高导入数据的效率；load data local infile ‘/root/sql.long’ into table ‘ tb_user’ fields terminated by ',' lines terminated by '\n';

 

（3）关闭唯一性效验：再导入数据前执行：SET UNIQUE_CHECKS=0,关闭唯一性效验，再导入结束后执行：SET UNIQUE_CHECKS=1，恢复唯一性效验，可以提高导入效率；

 

（4）手动提交事务：如果应用使用自动提交是的方式，建议再导入前执行：SET AUTOCOMMIT=0,关闭自动提交，导入结束后再执行：SET AUTOCOMMIT=1，打开自动提交，也可以提高导入效率

 2.insert语句优化

（1）insert into 表名 values（值1），（值2），...；将多个数据用逗号隔开一次性插入多个数据；

（2）将事务提交改为手动提交；

（3）将数据按主键排序顺序插入；

3.order by 语句的优化

        尽量减少额外排序，通过索引直接返回数据，where条件和order by 使用相同的索引，并且order by的顺序和索引的顺序相同，且order by的字段要么都是升序，要么都是降序。否则需要额外的操作，就会出现filedort；

（1）filesort的优化：

        通过创建合适的索引，减少filesort的出现：①两次扫描：MySQL4.1之前使用该方式排序。首先根据条件取出字段和行指针信息，然后也在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，改操作可能会导致大量随机I/O操作；    ②一次扫描算法：一次性取出满足条件的所有字段，然后再排序区sort  buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效果较高。

        可以适当提高MySQL系统变量sort_buffer_size和max_length_for_data，来增大排序区总大小，提高排序效率；

 4.group by语句的优化

（1）如果查询包含group by但是用户想要避免排序结果的消耗，则可以执行order by bull禁止排序；select 字段名,count(*) from 表名 group by 字段名 order by null;

5.优化嵌套查询 

（1）使用多表联查，替换子查询

6.优化or条件

（1）对包含or的查询子句，如果要利用索引，则or之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引；

show index from emp;  查看表emp中的所有索引

 （2）用 union 来替换or也会提高查询效率（union左右两边都是查询语句）

7.优化分页limit查询

        优化limit查询：这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。

（1）在索引上完成排序分页操作，最后根据主键关联回原表查询需要的其他内容

select * from 表名 别名t，（select 字段名 from 表名 order by 字段名 linmit 20000000，10）别名 a where t.id=a.id

（2）如果主键的自增表，可以把limit查询转换成某个位置的查询；添加一个where条件

8.使用sql提示

（1）sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化的目的；

①USE INDEX：在查询语句中表明后面，添加use index来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

②IGNORE INDEX：如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用ignore index作为hint。

③FPRCE INDEX：为强制MySQL使用一个特定的索引，可在查询中使用force index作为hint 。

9.其他 

（1）尽量避免select *，改使用select 列名，避免返回多余的列。

select * from 表名 where 筛选条件；

（3）尽量避免同时修改或删除过多数据，因为会造成cpu利用率过高，从而影响别人对数据库的访问，建议分批操作

（4）前缀索引：如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率