SQL优化

以下用法回导致索引失效

• 计算，如：+、-、*、！=、<>、is null， is not null or
• 函数 sum、round
• 手动/自动类型转换，如id=“1”，本来是数字，给写成了字符串了。

利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往表记录真实数据的唯一路径。

对数据表中的若干字段进行排序，类似于对一本书做目录，有了目录就可以快速定位数据的具体位置；

唯一性索引，保证数据表中每一行数据的唯一性、加快数据的索引速度。

主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用。

选择较短的数据类型，有效的减少索引的磁盘空间，提高索引的缓存效率；

非聚集索引查询、非聚集索引的非叶子节点记录所在的索引页(index page)

B+树索引分类

1.1 聚集索引

按主键构成B+树，叶子节点中存放整张表的行记录数据，聚集索引的叶子节点成为数据节点，每个节点之间双向链表来进行连接。存放的每行的所有记录，，主键的值和指向数据页的偏移量。聚集索引

1. 以在叶子节点直接找到数据
2. 对于主键的排序查找和范围查找速度非常快，索引根据键值排好顺序，在叶子节点是连续的，可以很快的找到随后一个数据页，然后取出之后的10条数据；

Innodb中的每张表都会有一个聚集索引，而聚集索引又是以物理磁盘顺序来存储的，自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。导致数据的移动；

如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂，表碎片横生。

聚集索引选取(id->非空唯一索引->innodb产生隐藏的行id索引)

1.当你定义一个主键时，InnnodDB存储引擎则把它当做聚集索引

2.如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均飞空的，则将其当做聚集索引。

3如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引，

补充：由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能有一个聚集索引，聚集索引对于主键的排序和范围查找非常有利，

非聚集索引

通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，

为什么效率低？ sql语句本身没有使用索引或索引失效，服务器本身没有使用缓存，搜索引擎去进行优化。

如何优化查询语句？

筛选条件和排序字段涉及到的字段建立索引；

不能对建立索引的字段上进行隐式的类型转换、表达式操作、函数操作；

确认没有空值； 避免使用逻辑或（逻辑运算符）指定多个条件、in和not in、不等于（比较运算符）；

只包含数字信息的字段，字段使用数值类型存储字段值；(不使用字符串类型存储字段值；连接和查询时，字符串类型需要逐个比较每个字符，数值类型只需比较一次；)

具体的字段列表，不要使用* 加入。

优化器判断是否使用索引，索引降低了插入和更新的效率；

范围查询右边失效原理

举例：
select * form testtable where a>1 and b=2

首先a字段再B+树上是有序的，所以可以利用二分查找法定位到1，然后将大于1的数据取出来，a可以用到索引。

b有序的前提是a是确定的值，a值过滤的数据量不确定；

a的值大于1的数据（也就是那部分B+树） b字段是无序的，b不可用索引；