Marco's Java 之【SQL索引和SQL优化】

SQL索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,类似于字典的目录。当我们创建索引之后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。变相的提高了查询的效率。

索引的分类和创建

普通索引

>> 创建索引
CREATE INDEX index_Name on TableName (columnName(10));//行创建
ALTER TABLE TableName ADD INDEX index_Name(columnName(10));//修改表创建
CREATE TABLE TableName (......,PRIMARY KEY(columnName),INDEX index_Name(columnName(10)));//新建表创建

>> 查看索引
SHOW index_Name FROM TableName;

>> 删除索引
DROP INDEX index_Name ON TableName;

主键索引
创建方式和创建主键时一致，需要注意的是当我们创建主键约束的时候，系统会默认给定一个主键索引，默认名称为 PRIMARY,因此，当我们查询一条数据并且条件是使用到主键时，查询的效率最高

ALTER TABLE TableName ADD PRIMARY KEY (columnName(10));

唯一索引
唯一索引创建的方式也大同小异，当我们创建唯一约束的时候，这个字段默认也会有一个索引，名字就是该字段名称

CREATE UNIQUE INDEX index_Name ON TableName (columnName(10))

其实普通索引和主键索引以及唯一索引的区别不大，主要的区别在于，主键索引不仅是普通索引，还有唯一且非空的特性，而唯一索引则是唯一可为空

---

当然事物都是具有双面性的，索引也不例外。

索引的优点

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性(默认在主
键上创建唯一索引，用主键查询就会用到索引)。
2.可以大大加快数据的检索速度
3.可以加速表和表之间的连接，列入将两个表之间相关联的那一列的字段设置为索引，那么我们在两表关联查询完整的信息的时候，效率会更佳
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排
序的时间。
5.通过使用索引，可以在查询的过程中，使用查询优化器(查到的数据会缓存
起来，并且将查询到的信息按照Map集合的关系映射，MySQL的缓存会将我们的查询语句转为为Hashcode并以key保存起来，而查询到的结果则以value值保存起来，那么当我们再次调用相同的查询语句的时候，我们就不会全表查询或者索引查询，而是会先查询缓存区域)，提高系统的性能。

索引的缺点

当然索引除了那么多优点，相反也会有他的缺点
1.随着数据量的增加而增加，创建索引和维护索引要耗费时间更长
2.索引需要占物理空间(正如上面提到的，索引保存在硬盘的MYI文件中)，除了数据表占数据空
间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更
大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样
就降低了数据的维护速度。

因此当我们在建索引的时候，应当考虑到以下因素：

1.是否是需要搜索的列(该列作为查询的条件)
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3.该列是否是经常作为连接列或者作为外键来作为表与表之间连接使用的
4.该列是否经常需要根据范围进行搜索
5.在经常需要排序的列上创建索引(索引默认是会排序的，因此，如果符合该条件并使用索引也可以减少排序所花费的时间)
6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
注意： 建立索引，一般按照select的where条件来建立，比如： select的条件是where f1 and
f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立
索引才有用。

反之，有些数据我们不应当或者应该慎重考虑建索引的问题：

1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些
列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反
而降低了系统的维护速度和增大了空间需求。
2.对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值
很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比
例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这
些列的数据量要么相当大，要么取值很少(例如商城系统商品 表的商品描述字段类型为
text)。
4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索
性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引
时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建
索引。

---

拓展：MySQL中索引种类

B-TREE索引
B-Tree索引，顾名思义，就是所有的索引节点都按照balance tree的数据结构来存储。
B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。
B-tree中，每个节点包含：
1、本节点所含关键字的个数
2、指向父节点的指针
3、关键字
4、指向子节点的指针
对于一棵m阶(阶是指子节点个数的最大值)的B-tree，每个节点至多可以拥有
m个子节点。各节点的关键字和可以拥有的子节点数都有限制，规定m阶B-tree中，根节点
至少有2个子节点，除非根节点为叶子节点，相应的，根节点中关键字的个数为1~m-1；非
根节点至少有ceil(m/2)个子节点，相应的，关键字个数为ceil(m/2)-1~m-1。
B树的查询流程： 如上图我要从上图中找到E字母，查找流程如下
（1）获取根节点的关键字进行比较，当前根节点关键字为M，E要小于M（26个字母顺
序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子
节点、右边放大于当前节点值的子节点）；
（2）拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；
（3）拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要
查找的节点则返回null）

全文索引
Full-text索引就是我们常说的全文索引，它的存储结构也是b-tree。
主要是为了解决在我们需要用like查询的低效问题。只能解决’xxx%’的like查
询。如：字段数据为ABCDE，索引建立为- A、AB、ABC、ABCD、ABCDE五个。

Hash索引
基于哈希表实现，只有精确匹配到索引列的查询，才会起到效果。对于每一行数
据，存储引擎都会对所有的索引列计算出一个哈希码（hash code）,哈希码是一个较小的
整数值，并且不同键值的行计算出来的哈希码也不一样。
注意：只有Memory存储引擎支持Hash索引

---

SQL语句优化

避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及
的列上建立索引。

避免判断null值
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索
引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

注意：严格来说，只有当判断null值的字段上没有建立索引时，才会走全表扫描，
设置了索引后，是可以使用到索引的
避免不等值判断
应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行
全表扫描。

避免使用or逻辑
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引
而进行全表扫描，如：

select id from t where num1=10 or num2=20

可以这样查询：

select id from t where num1=10
union all
select id from t where num2=20

慎用in和not in逻辑
in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t1 where id in(select id from t2 where id > 10)

此时外层查询会全表扫描，不使用索引。可以修改为：

select id from t1,(select id from t2 where id > 10) t2 where t1.id = t2.id

此时索引被使用，可以明显提升查询效率。

注意模糊查询
下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr等）。
避免查询条件中计算字段
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引
而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

避免查询条件中对字段进行函数操作
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进
行全表扫描。如：

select id from t where Year(create_time)<'2017'
>> 应改为:
select id from t where create<'2017-01-01'

where子句 “=” 左边注意点
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则
系统将可能无法正确使用索引。

组合索引使用
在使用索引字段作为条件时，如果该索引是组合索引，那么必须使用到该索引中的
第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能
的让字段顺序与索引顺序相一致。

使用Exists
很多时候用 exists 代替 in 是一个好的选择,exists子句返回的结果并不是从数据库
中取出的结果集，而是一个布尔值，如果子句查询到数据，那么返回true，反之返回
false。

create table a(
	a_id int,
	a_name varchar(20)
)
create table b(
	b_id int,
	b_name varchar(20)
)
select * from a where a_name in (select b_name from b)
select * from a where exists (select b_id from b where b.b_name=a.a_name)

索引也可能失效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列
有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几
乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

表格字段类型选择
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查
询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串
中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省
存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

查询语法中字段
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用
不到的任何字段。

开启查询缓存
query_cache_type=0时表示关闭，1时表示打开，2表示只要select 中明确指定
SQL_CACHE才缓存。

（作者注：以上内容有些多为概念性的知识点，因此多数为网上搜集，部分结合了自己的一些看法，如有雷同，还请谅解~）