索引 sql优化

索引失效情况

1.索引列位运算

当根据phone字段进行等值匹配查询时, 索引生效。

explain select * from tb_user where phone = '17799990015';

explain select * from tb_user where substring(phone,10,2) = '15'; 

当根据phone字段进行函数运算操作之后，索引失效。

2.字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。
接下来，我们通过两组示例，来看看对于字符串类型的字段，加单引号与不加单引号的区别：

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;

如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

3.模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。
而如果在关键字前面加了%，索引将会失效。

4.or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会
被用到。当or连接的条件，左右两侧字段都有索引时，索引才会生效。

5.数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。
select * from tb_user where phone >= ‘17799990005’;
select * from tb_user where phone >= ‘17799990015’;
经过测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样
就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索
引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，
避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增
删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

1.插入数据insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化
1.批量插入数据
2.手动控制事务
3.主键顺序插入，性能要高于乱序插入。
大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使
用MySQL数据库提供的load指令进行插入

2.主键优化

索引设计原则
满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改

3.order by优化

MySQL的排序，有两种方式：
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort
buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要
额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序
操作时，尽量要优化为 Using index。
order by优化原则:
A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小
sort_buffer_size(默认256k)。

4.group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：
A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的。

5.limit优化

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化。

6.count优化

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽
量使用 count( * )。

7.update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁
升级为表锁 。