数据库优化学习之【索引】

一、什么是索引？

是对数据库表中一列或多列的值进行排序的一种结构。

二、索引对单表查询的影响

例如：select * from userinfo where age > 20;

对一个用户信息表进行查询，查询条件是年龄大于20

如果userinfo表中没有添加索引，那么就会对userinfo全表进行检索，以找出符合条件（age>20）的全部行

如果userinfo表中添加了索引（此例子中对age列添加了索引），那么就会对索引进行检索。当搜索到age这列的值大于20时，对索引停止搜索（因为索引是排序的，所以到检索到age=21或者其他大于20的值时，没必要对后面的记录进行检索）。

三、索引对多表查询的影响

例如：select A.* from A,B,C where A.VALUE=B.VALUE AND A.VALUE=C.VALUE

如果A，B，C表没有添加索引，那么对A，B，C表都进行全表的检索，以找出所有满足查询条件的记录；

如果A，B，C表添加了索引（此例子中对3个表的VALUE列添加了索引），那么：

第一次：先从A表中选第一行，查看此行中VALUE的值。然后检索B表的索引，找到与A.VALUE值相同的行。再到C表的索引，找到与A.VALUE相同的行；

第二次：从A表中选出第二行，查看此行中VALUE的值。然后检索B表的索引，找到与A.VALUE值相同的行。再到C表的索引，找到与A.VALUE相同的行；

。。。

以此类推，虽然还是会对A表进行全表搜索，但是对B，C表来说，并没有进行全表搜索。大大加快了搜索的速度。

四、多列索引

可以对一个表中的多个列添加索引：alter table tableName add index(column1,column2,column3)

多列索引有一个【最左前缀】，例如：column1,column2,column3是表tableName的索引，那么：

(column1,column2,column3)相当于(column1)、(column1,column2)，(column1,column2,column3)这3个索引

(column1,column3)、(column2,column3)、(column2)、(column3)都不能用作索引。因此

select * from tableName where column1 = 5; 【使用了索引】

select * from tableName where column1 = 5 and column3 = 7; 【未使用索引】

select * from tableName where column1 = 5 and column2 = 8;【使用了索引】

五、索引被用到的条件

=、>、>=、 >、 >=、 <、 <=、 BETWEEN 和一个有一个非通配符前缀的LIKE的列

六、索引的作用：优化查询

七、索引的弊端：

1、索引存储在索引文件中，而索引文件存储在磁盘空间中。因此如果有太多索引文件，会占磁盘空间；

2、表的写操作性能下降：一旦更新表的内容，那么索引也会修改。因此索引越多，性能影响越大。

八、确定数据表索引的准则：

1、尽量选择出现在子句中的列（where子句，连接子句）；

2、有惟一值的列；

3、短索引（如果将要添加索引的列为VARCHAR(200),如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引）

4、利用最左前缀