SQL语句_索引

　　为什么数据库表要加索引，唯一的目的就是提高查询的效率。一个索引可以涵盖表中的一个参数或多个参数。即 CREATE INDEX "索引名" ON "表名"（表中参数名）

如创建一个user_Info表

CREATE TABLE user_Info(id int(10), firstname char(50), lastname char(50), address char(50), city char(50), country char(50), age int(3))

我们可以在user_Info表中的id上创建一个索引   即：

CREATE INDEX IDX_USERINFO_ID ON user_Info(id)  

也可以在city、country上创建一个联合索引  即：

CREATE INDEX IDX_USERINFO_CITY_COUNTRY ON user_Info(city, country)

　　索引可以创建，当然也是可以删除的，但是索引删除时，需要关注几点：

1. 删除索引会增加表的读取开销，索引是暂用内存空间的，因此需要谨慎操作；
2. 如果删除了某个索引，可能会导致相关查询的性能下降，因此需要在删除之前慎重考虑；
3. 删除索引需要谨慎操作，因为它可能会影响表的完整性和一致性

如果以上的问题都不会影响，且要删除的索引已经没有作用了，那么就可以删除了  即： DROP INDEX IDX_USERINFO_CITY_COUNTRY ON user_Info

 

　　我们在所用索引的时候，往往会出现索引失效的情况，那我们怎么解决索引失效的问题。这里总结了几个索引失效的情况以及相对应的解决办法。

情况一： SELECT * FROM user_Info WHERE lastname LIKE '%Smith'   lastname添加了索引。 这种情况SQL语句在执行时不会走索引的。原因是模糊匹配时使用了%。

解决办法： 使用索引时，尽量避免使用模糊查询，用较准确的条件查询。

情况二：SELECT * FROM user_Info WHERE city = '江苏' OR country = '南京'  city添加了索引，country没有索引。这种情况SQL语句在执行时也不会走索引。原因时在使用OR时，OR的两边的字段都要有索引，才会走索引；一边有索引，一边没有索引，那么有索引的也会失效。

解决办法：可以使用我们之前说的UNION联合查询解决，即

SELECT * FROM user_Info WHERE city = '江苏'

UNION

SELECT * FROM user_Info WHERE country = '南京'

情况三：SELECT * FROM user_Info WHERE lastname = 123123   lastname添加了索引。这种情况SQL语句在执行时不会走索引。原因是lastname是字符型，其值需要加引号，查询时才会走索引。

解决办法：SELECT * FROM user_Info WHERE lastname = "123123"

情况四：SELECT * FROM user_Info WHERE country = '南京'    city,country是组合索引。这种情况SQL语句在执行时不会走索引。原因是没有组合索引中第一个索引city字段。

解决办法：SELECT * FROM user_Info WHERE city = "江苏" AND country = "南京"

情况五：SELECT * FROM user_Info WHERE id - 1 = 30  id添加了索引。 这种情况SQL语句在执行时不会走索引。原因是索引参数不能有运算。

解决办法：SELECT * FROM user_Info WHERE id = 31  提前先算好需要查询的id值

情况六: SELECT * FROM user_Info WHERE lower(lastname) = "smith"   lastname添加了索引。 这种情况SQL语句在执行时不会走索引。原因是索引参数使用了函数。

解决办法：SELECT * FROM user_Info WHERE lastname = "smith" 

情况七：SELECT * FROM user_Info 没有用到索引条件查询，就不会走索引。

情况八：WHERE 条件中使用范围查询 BETWEEN 、IN、大于 或 小于时，会根据过滤后的得到的数据量的大小来判定是否会走索引。数据量较小时会走索引；数据量较大，查找索引再回表，开销比直接查表还大，类似于重复性高的字段也是索引失效的原因