Mysql索引优化及面试题

Mysql索引优化

一:索引介绍

索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引.

MySql索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引.

1.1:聚集索引

聚集(clustered)索引,也叫做聚簇索引.

定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引.

注意:聚集索引做查询可以直接获取对应的全部列的数据.所以聚集查询较快.

1.2非聚集索引

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引.

除了聚集索引以外的索引都是非聚集索引,分成普通索引,唯一索引和全文索引.

注意:非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询,索引非聚集查询较慢.

1.2.1如何解决非聚集索引的二次查询问题

复合索引(覆盖索引)

建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1,col2),执行下面的语句

select col1,col2 from 表名 where col1=‘xxx’;

要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用.

二:索引的存储机制

无索引的表,查询时,是按照顺序存序的方法扫描每个记录来查询符合条件的记录,这样效率很低.

聚集索引和非聚集索引的根本区别在于表记录的排列顺序和索引的排列顺序是否一致.

聚集索引就是在数据库被开辟一个物理空间放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个物理空间,而非聚集索引其实可以看做是一个含有聚集索引的表,它只仅包含原表中非聚集索引的列和指向实际物理表的指针,它只记录一个指针,其实就有点和堆栈差不多的感觉.

三:建立索引的原则

1. 定义主键的数据列一定要建立索引。

2. 定义有外键的数据列一定要建立索引。

3. 对于经常查询的数据列最好建立索引。

4. 对于需要在指定范围内的快速或频繁查询的数据列;

5. 经常用在WHERE子句中的数据列。

6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8. 对于定义为text、image和bit的数据类型的列不要建立索引。

9. 对于经常存取的列避免建立索引

10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

11. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

四:索引优化面试题

select * from student s where s.stuName in(“张三”,“李四”,“王五”)and s.age>18 and s.sex=‘男’;

思路:

1.肯定要建立二级联合索引:index(stuName,age,sex)

2.这里要优化一下sql语句中的in,改用union all

优化后的sql:

select * from student s where s.stuName=“张三” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“李四” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“王五” and s.age>18 and s.sex=‘男’ ;