前言: 关系型数据库中索引和sql优化是一定要懂的知识点，此文章仅代表鄙人的总结和理解，如有错漏，欢迎指正...

一、索引类型结构和索引种类

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

索引类型结构

1、FULLTEXT（全文索引）：
目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2、HASH索引（键值对，适用于随机访问的场合,查找每条数据的时间几乎相同）：
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。
但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3、B-TREE(mysql默认的，适合用于查找某范围内的数据,可以很快的从当前数据找到下条数据)：
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4、R-TREE：
常用于查询比较接近的数据.

索引种类：

1、普通索引：仅加速查询  
2、唯一索引：加速查询 + 列值唯一（可以有null）  
3、主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个  
4、组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并  
5、全文索引：对文本的内容进行分词，进行搜索  

spatial空间索引：
Oracle Spatial可对空间数据进行R-tree索引， 每个空间图层（Spatial Layer）的空间索引元信息都可以在USER_SDO_INDEX_METADATA视图中找到。 具体的索引数据保存在MDRT字段开头的表中,每个空间图层都会对应一个索引表（表的格式是MDRT_[...]$），空间索引表中的主要数据是MBR

通过空间索引元数据视图（USER_SDO_INDEX_METADATA）可以查到每个空间图层的空间索引名、空间索引数据表名、R-tree索引的根节点ROWID，R-tree的分支因子（又叫扇出fanout，即R-tree节点的最大子节点数）及其它相关信息。

二、索引失效、不推荐使用索引的情况

索引失效的情况：

1.条件中有or,即使其中有部分条件带索引，索引也会失效
2.like查询是以%开头会索引失效，如果是%结尾，索引有效
3.mysql的表中数据量很小，使用全表扫描要比使用索引快,则不使用索引

不推荐使用索引的情况：

1) 数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
2) 频繁更新的字段不要使用索引
3) 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引
4）where 子句里对索引列使用不等于（<>），使用索引效果一般

三、sql的优化方式

核心的是怎么 合理创建索引、怎么使用索引、索引失效、合理创建表字段这4个方面。

1：在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多或用 join 代替，小表关联大表（索引失效）
2：对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。（怎么创建索引）
3：最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
注意是尽量不要null，请根据实际业务选择（合理创建表字段）
4：应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。（索引失效）
5：应尽量避免在 where 子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，（索引失效）
6：索引最左前缀匹配原则，SQL代码需要注意这个原则  （索引失效）
7：尽量避免向客户端返回大数据量，若数据量过大,建议分页或数量限制（设计上，这个和SQL没关系罗）
8：其他，非常多注意mysql 、oracle区别，版本问题 不同版本特性也不一样

总结：
平时注意SQL的一些关键字对索引的作用 如like 、in、not in、<> 代码写的很爽后期业务数据量上来了就要优化了，还不如开始写代码时候考虑清楚 考虑业务数据量未来增长情况，索引的缺点也要考虑，查询快了更新就慢了，需要根据实际业务场景去平衡

四、count(1)、count(※)、count(列名)的区别

1、count(*)和count(1)查询速度

使用count函数，当要统计的数量比较大时，发现count(*)花费的时间比较多，相对来说count(1)花费的时间比较少。

1、如果你的数据表没有主键，那么count(1)比count(*)快；如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快  。

2、如果你的表只有一个字段的话那count(*)就是最快的。  

3、如果count(1)是聚索引,id,那肯定是count(1)快,但是差的很小的。因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*),sql会帮你完成优化。此时count(1)和count(*)基本没有区别! 

2、count(*) 和count(列名) 两者查询差异的原因分析。

1、在数据记录都不为空的时候查询出来结果上没有差别的。

2、count(*)（是针对全表）将返回表格中所有存在的行的总数包括值为null的行；

3、count(列名)（是针对某一列）将返回表格中某一列除去null以外的所有行的总数。

3、引申

distinct 列名,得到的结果将是除去值为null和重复数据后的结果

结语：以往都是看别人的博客进行学习技术，其中不乏有精华博客也有吊儿郎当的CV大法文章，所以决定将自己所学所用所整理的知识分享给大家，主要还是想为了后浪们少走些弯路，多些正能量的博客，如有错漏，欢迎指正，仅希望大家能在我的博客中学到知识，解决到问题，那么就足够了。谢谢大家！（转载请注明原文出处）