sql优化和索引注意事项

1.什么是索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引指数据库的目录，比如：字典上面的字母目录 (适用于大数据量)

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

3.索引有哪些
普通 主键 唯一 组合

4. 索引检索为什么快
   索引结构：B+Tree

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

6.怎么知道索引用没用上
通过explain查询sql执行计划，主要看key使用的是哪个索引

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

8.什么情况下会使索引失效？
（1）like
（2）like “%123%，前面不能+%
（3）使用 关键字 in ，or ，null，!=

9. 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扫描行数是不是很大

下面通过一些例子，告诉大家如何正确使用索引
使用如下的表tb_test作为示例进行说明：

create table tb_test
(
id int not null,
age int not null, 
name varchar(30) not null,
addr varchar(50) not null
);
create unique index idx1_tb_test on tb_test(id);
create index idx2_tb_test on tb_test(name);
create index idx3_tb_test on tb_test(addr);

索引优化建议
1.对索引列进行计算

例如，我们想要将表tb_test中id大于100的数据记录中的age和name查找出来。
正确的SQL语句是：
select age,name from tb_test where id > 1*100;
不建议采用的SQL语句是：
select age,name from tb_test where id/100 > 1;

2.对索引列进行拼接
例如，我们想要将表tb_test中name为“zhou”、addr为“CQ”的记录中的id和age查找出来。
正确的SQL语句是：
select id,age from tb_test where name=‘zhou’ and addr=‘CQ’;
不建议采用的SQL语句是：
select id,age from tb_test where concat(name,’ ‘,addr) = ‘zhou CQ’;

3.在索引列上is null或is not null的使用
例如，我们想要将表tb_test中id大于等于“0”的记录中的age查找出来。
正确的SQL语句是：
select age from tb_test where id >= 0;
不建议采用的SQL语句是：
select age from tb_test where id is not null;

4.在索引列上or的使用
例如，我们想要将表tb_test中id等于101或102的记录中的age和name查找出来。
正确的SQL语句(使用union)是：
select age,name from tb_test where id = 101 union select age,name from tb_test where id = 102;
不建议采用的SQL语句(使用or)是：
select age,name from tb_test where id = 101 or id = 102;

5.尽可能避免索引列在like的首字符使用通配符
例如，我们想要将表tb_test中name匹配“zho”的记录中的id和age查找出来。
正确的SQL语句是：
select id,age from tb_test where name like ‘zho%’;
不建议采用的SQL语句是：
select id,age from tb_test where name like ‘%ho%’;

6.复合索引的使用
如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
例如，我们在表tb_test上新建了如下索引：
create index idx4_tb_test on tb_test(id,name,addr);
以上索引idx4_tb_test相当于建立了index(id)、index(id,name)、index(id,name,addr) 这3个索引。在SQL语句的where条件中单独使用name或addr时不会使用到该索引，必须使用id时才会使用到该索引。