sql索引优化知识

1.什么是索引

索引指数据库的目录，比如：字典上面的字母目录 (适用于大数据量)

2.索引介绍

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

3.建立索引的优缺点

优点: 查询速度快
缺点：增删改慢，因为数据库要同步去维护索引文件，所以速度慢

4.索引有哪些

• 普通索引：是最基本的索引，它没有任何限制；
• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
• 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
• 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
• 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，mysql中MyISAM支持全文索引而InnoDB不支持；

5. 索引检索为什么快

索引结构：B+Tree

6.一般你们会在什么情况下加索引

（1）主键自动建立唯一索引
（2）频繁作为查询条件的字段应该创建索引
（3）查询中与其他表关联的字段，外键关系建立索引
（4）单键/组合索引的选择问题，组合索引的性价比更高
（5）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
（6）查询中统计或者分组字段。
（7）过滤条件好的字段选择一段选择加索引

7.怎么知道索引用没用上

通过explain查询sql执行计划，主要看key使用的是哪个索引

8.用过组合索引吗，是有序的吗

用过， 有序

9.什么情况下会使索引失效？

（1）like
（2）like “%123%，前面不能+%
（3）使用 关键字 in ，or ，null，!=

10. sql优化您们是怎么做的？

一.首先开启数据库慢查询日志，定位到查询效率比较低的sql , 找出对应的sql语句并进行分析

• 1.表设计是否规范，是否符合三范式的标准
  （1）第一范式：保证原子性（不可拆分）
  （2）第二范式：每张表都有主键
  （3）第三范式（每一列都有主键相关)
• 2.查看数据表中是否存在大量的冗余字段，字段数据类型是否合理
• 3.尽可能的使用varchar代替char 建表数据类型，能用数值的绝对不用字符存储
• 4.尽量避免null值，使用默认值替代空值，数值型可以使用0，字符型可以使用空字符串

二.查看sql语句是否规范
（1）避免使用关键字：or ，in，not in ，!=，<>，避免使用select *
（2）尽量避免子查询，大部分子查询都可以连接查询
（3）用到or的地方可以使用union去代替实现
（4）用到in的地方可以使用exists去代替

三.分析sql的索引是否可以用上
（1) explain查询sql的执行计划,重点关注的几个列就是，type是不是全表扫描
（2）看一下索引是否能够用的上,主要看key使用的是哪个索引
（3）看一下rows扫描行数是不是很大

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

11.1:聚集索引

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

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

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

11.2非聚集索引

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

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

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

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

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

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

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

12:索引的存储机制

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

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

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

13:建立索引的原则

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

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

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

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

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

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

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

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

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

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

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