SQL优化、索引

谈谈你对索引的理解？

索引说白了其实就是一种用于快速查找数据的数据结构。像我在之前的工作里MySQL数据库用的比较多一些，我了解的MySQL底层索引使用的数据结构是B+Tree。你像B+Tree它其实就是在B-Tree的基础上优化而来的，B+Tree和B-Tree最大的区别就是B-Tree不管是叶子节点还是非叶子节点除了保存索引值之外还会保存具体的数据，而B+Tree只有叶子节点才会保存具体的数据，这样做的最大好处就是可以让非叶子节点保存更多的索引值，同等高度的B+Tree可以维护更多的数据。树的高度降低了，这也就意味着我们执行查找的次数变少了，效率也就提高了。当然这个索引它也不是完美的，它也有优缺点。
优点呢就是，
第一它可以提高我们查找数据的效率，减少对磁盘的IO次数。
第二呢就是因为索引已经对数据进行过排序，所以我们在使用索引进行排序时就可以降低排序的成本，降低CPU的消耗。
缺点的话主要就两个吧，
第一索引它会降低更新表的速度，因为在对表进行INSERT、UPDATE和DELETE操作的时候，数据库不仅要更新数据，还要更新索引信息。
第二就是索引会占用额外的空间，但是我觉着有些情况下拿空间换时间还是非常值当的。所以说我们应该选择合适的时机去建索引，不是说无论什么情况下都去创建索引。那啥情况下去建索引呢？
s我觉着主要分三种情况吧：
第一就是频繁作为查询条件的字段应该建索引
第二就是跟其它表关联的字段应该建索引
第三就是查询中排序、统计或者分组字段应该建索引
当然还有一些情况不应该去建索引，比如说：
表记录太少啊，
或者说你这个表或者字段经常增删改，
再者说你Where条件里压根用不到的字段也不要建索引。
还有就是你这个字段重复的值太多了比如说超过了50%也不太适合建索引。
当然了有时候不是说索引创建好了之后就一定会被用到，如果你写的SQL语句不好的话可能就会导致索引失效，所以我们应该在写SQL的时候注意一下以下几点：
第一个就是要遵守最佳左前缀原则：比如说你创建了一个复合索引index(a,b,c)，那么在写SQL语句的时候一定要从索引的第一列开始写并且还得注意不能跳过索引中的列。
第二个就是不要索引列上做任何操作（比如说计算啊、使用函数啊、（自动or手动）类型转换），这都会导致索引失效而转向全表扫描。
第三个就是范围条件右边的索引会失效(比如说你范围条件右边与范围条件使用的同一个复合索引，右边的才会失效。如果是两个字段走的是不同索引则不会失效)。
第四个就是咱们在写SQL的时候尽量使用覆盖索引，覆盖索引说白了就是只访问索引就能够查询到你想要的数据，想要使用覆盖索引具体的做法就是让你查询的列包含在索引列里面，所以说我们平时写SQL的时候尽量减少select 这种写法。
第五个就是平时写SQL的时候使用不等于（!= 或者 <>）的时候也会导致索引失效导致扫描全表。
第六个呢就是like以通配符开头(’%abc…’)会导致索引失效变成全表扫描，如果一定要以通配符开头可以使用咱们的覆盖索引来解决。
第七个就是还有is not null也会导致索引失效。
大概我现在就记住了这几个避免索引失效的方式。
你像平时我们要想知道一条SQL语句在执行的时候有没有走索引，用的最多的就是explain命令了，它可以帮我们查看SQL语句的执行计划。这个命令返回的有几个关键的列，比如说possible_keys这一列[跑shei博K死]代表的是你这次查询可能会用到的索引，key这一列就代表你这次查询实际用到的索引，还有一个type列，这一列就比较重要了，它里面显示的值是衡量你这个查询性能高低的重要指标。你像常见的值我记得有三个，分别是range、index还有all，range代表的意思就是只扫描了部分索引就查到你想要的数据，index代表扫描了全部索引才查到了你想要的数据，all是最差的它代表压根没走索引而是进行的全表扫描。所以说一旦我们某个查询它的type是all就需要我们注意了。你向除了type这一列还有一列也比较重要，就是extra(埃克斯chua)这一列，它里面主要显示的就是一些额外的比较重要的信息了。常见的有什么Using[有人] filesort[菲欧臊特]，就说明你这次查询使用了文件排序。文件排序说白了就是没利用索引去完成排序。想解决也很简单，我们可以给排序的字段上创建索引。有时候还会出现Using[有人] temporary(太目炮rai瑞)，说明你这次查询使用了临时表保存中间结果，比较常见的就是分组查询 group by。如果出现了这个也不好，这个时候咱们可以给你分组的字段上建个索引。还有些情况下会出现Using index，说明你这次查询中使用了覆盖索引(Covering Index)，避免了回表查询，效率很好！
2)索引的类型
从索引的特性上来说可以分为唯一索引和普通索引。
唯一索引所在字段中的值必须是唯一的。
主键字段会自动创建唯一索引。
从字段的个数来说单值(单列、单一)索引和复合(组合)索引。
如果对多个字段同时加索引就称之为复合索引。
3)聚集(聚合、聚簇)索引和非聚集(聚合、聚簇)索引
聚集索引的的叶子节点直接保存的数据就是主键值对应的数据，可以直接获取到主键值对应的全部列的值。主键就属于聚集索引，而且在一张表上聚集索引只能有一个。
而非聚集索引的叶子节点保存的数据是只是主键值，如果在索引没有覆盖到对应的列的时候需要对聚集索引进行二次查询。在一张表上非聚集索引可以有多个。
4)SQL优化的具体做法？
这里要强调的重点是SQL优化的关键在于使用索引和避免索引失效。
对于大数据量的分页不要用常规的limit开始位置，每页条数；这样越靠后查询时间越长，效率越低，可以使用id>=的方法来替代，比如
select 字段名from 表名
where id >=(select id from 表名limit 开始位置，1）
limit 每页条数
SELECT语句中避免使用，只查询需要返回的字段，这样可以减少解析SQL语句的时间，以及减少带宽，CPU,内存，IO等各方面的消耗。
外键必须加索引，这样可以提升多表联查的性能。
通过冗余字段避免多表连查，从而提高性能。比如在商品表中可以不仅有分类的id，还可以加上分类的名字，这样就不用为了显示分类名字而去进行多表联查了。又比如在会员表中不仅有地区的id，还可以有地区的名字这样也可以避免为了显示地区名而进行多表联查。
避免在where子句中对有索引的字段进行运算这会导致索引失效，从而进行全表扫描，比如select 字段名from表名where库存量+1>=10就需要改为select 字段名 from表名where 库存量>=9
在where,order by 以及GROPU BY 涉及的列上建立索引，为了避免全表扫描。
用in替代exists
主要是就是驱动顺序的改变，这是性能变化的关键，
(1)如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，以内层表为驱动表。
(2)所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
避免在where字句中对字段进行null值判断，这样会导致即便字段上有索引也会进行全表扫描，好的处理方式就是给字段设置默认值。
通常要用小表去驱动大表，这样可以提高性能，在left join中左边的表为驱动表，所以应该让左边的表尽可能的为小表，在right join 中右边的表为驱动表，所以应该让右边的表尽可能的小，在inner join 中 mysql会自动选择较小的表为驱动表。
5)索引底层使用的数据结构？
比如说普通二叉树、红黑树、Hash表、B树(B-Tree)、B+树(B+Tree)等这些数据结构。我平时用MySQL数据库比较多一些，像我了解到的MySQL数据库它用的就是B+Tree这种数据结构。B+Tree是在B-Tree的基础上做了一些优化。B+树只在叶子节点存储数据，非叶子节点只存储索引，而且相邻叶子节点之间有一个双向指针。这样在节点大小相同的情况下，B+树能存储更多的索引。假设一个索引大小16B，一个节点大小16K，则一个节点可存储1024个索引。而如果非叶子节点带有存储内容data且假设data大小为1K，则一个节点只能存储16个索引，变相增加了树的高度。
6)MySQL底层采用B+树的原因
相比于普通二叉树和红黑树，B+树的一个节点可以存储更多的数据，因此B+树的高度更短，因此查找速度更快。
相比于hash表，虽然hash表对单个数据查找更快，却因为hash表的无序性导致范围查找上B+树更胜一筹。（当然也有用hash表作为索引的数据表，但大多数仍然使用B+树作为索引）
相对于B树，B+树只在叶子节点存储数据，非叶子节点只存储索引，而且相邻叶子节点之间有一个双向指针。这样在节点大小相同的情况下，B+树能存储更多的索引。假设一个索引大小16B，一个节点大小16K，则一个节点可存储1024个索引。而如果非叶子节点带有存储内容data且假设data大小为1K，则一个节点只能存储16个索引，变相增加了树的高度。
7)那为什么推荐使用整型自增主键而不是选择UUID？
UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
8)索引的语法？
操作 命令
创建 create [unique ] index 索引名 on 表名(字段名,…);
删除 drop index 索引名 on 表名;
查看 show index from 表名\g;
使用alter命令 alter table 表名 add primary key (column_list) : 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 null。
alter table tbl_name add primary key (column_list)
alter table tbl_name add index index_name (column_list): 添加普通索引，索引值可出现多次。
alter table tbl_name add fulltext index_name (column_list):该语句指定了索引为 fulltext ，用于全文索引。