索引、视图、sql优化

数据库索引

• 索引是为了提高数据的查询速度,相当于给数据进行编号,在查找数据的时候可以通过编号快速找到对应的数据,索引是帮助mysql高效获取数据的数据结构
• 索引的分类
  1. 主键索引(PRIMARY KEY)
     唯一的标识,主键不可重复,只能有一个列作为主键
  2. 唯一索引(UNIQUE KEY)
     避免重复的列出现,可以重复,多个列都可以标识为唯一索引
  3. 常规索引(KEY/INDEX)
     默认,index/key关键字
  4. 全文索引(FULLTEXT)
     特定的数据库引擎下才有,MYISAM,快速定位数据
• 索引的使用
  1. 在创建表的时候给字段增加索引
  2. 创建完毕后,增加索引
  3. show index from 表名 查看所有索引信息
  4. alter table 表名 add fulltext index 列名 ('字段名') 增加全文索引
     explain 查询语句 分析sql执行状况
• 使用原则
  1. 对经常更新的表避免对其进行过多的索引,对经常用于查询的字段应该创建索引
  2. 数据量小的表最好不要使用索引,因为由于数据较少,可能不会产生优化效果
  3. 在不同值少的列上不要使用索引,例如性别,在不同值多的列可以简历索引
  • 优点
    • 大大提高了查询速度
  • 缺点
  1. 创建索引和维护索引要耗费时间,这种事件随数据量的增加而增加
  2. 索引需要占物理空间,除了数据表占数据空间之外,每个索引还要占据一定的物理空间
  3. 当对表中的数据进行增删改的时候,索引也要动态的维护,降低了数据库的维护速度
• 索引内部用的是B+树数据结构
  • 数据存储的内部结构类似于链表的形式,通过指针关联不同的数据,这种数据量很大的时候查询很慢,
  • mysql中有page的概念,相当于给数据进行分页,把一部分数据存入一个page中,每个page存储16kb的数据,这就相当于给数据库建立了上层目录,先找大目录,再找具体数据.
  • mysql也给page提供了快速查询的page目录,把每个page中的第一条数据的id+指针存入page目录中,查询数据的时候,先找到它的page,一个page目录中也存储16kb的数据,如果是海量数据,page目录也会有很多层
  • 为了提高查询效率,mysql给page目录再加一层目录,这种结构就是B+树

数据库视图

• 创建视图
  create view 视图名称 as sql语句
• 含义
  从数据库一个或多个表中导出的虚拟表
• 作用
  1. 方便用户操作
  2. 增加数据安全性 :通过视图,用户只能查看或修改指定的数据
  3. 提高表的独立逻辑性:原有数据表结构的变化,不会影响视图,如果修改原有列,则只需修改视图即可

sql优化

1. 查询SQL尽量不要使用select *，而是具体字段
2. 避免在where子句中使用or来连接条件
3. 使用varchar代替char
4. 尽量使用数值替代字符串类型
5. 查询尽量避免返回大量数据
6. 是否使用了索引及其扫描类型
7. 优化like语句,做模糊查询时，尽量确定开始的元素 where name like ‘a%’
8. 索引不宜太多，一般5个以内
9. 索引不适合建在有大量重复数据的字段上
10. 避免在where中对字段进行表达式操作
11. 避免在where子句中使用!=或<>操作符
12. 去重distinct过滤字段要少
13. where中使用默认值代替null
14. 不要有超过5个以上的表连接
15. inner join 、left join、right join，优先使用inner join
16. 尽量使用union all替代union
17. 观察SQL的执行性能，使用关键字explain，目的就是观察SQL有没有使用索引
18. …
    参考帖子：sql优化.