数据库优化

数据库优化很容易说一些大而无用的空话，每个人都会说。所以应该要具体，才能让人信服。

1. 表字段的优化

1.1 项目中的数据表全部为单表，没有外键。早就是这样的了。
1.2 因设备名称会经常模糊查询，所以再建立一个search_name列来存储设备名称的unicode值，提高查询效率。
1.3 定长与变长字段分离，常用和不常用字段分离。定长的字段放在一起，常用的字段放在一起。
如id的int，char ，注意还有timestamp都是定长的，数据在查询时定长会很快。
1.4 添加冗余字段。如当时我们将组织名称和某些情况下的设备名称会做一个冗余字段，这样就减少了关联查询。
1.5 字段类型的优先选择：int > time（不包含时区的） ,date > char > varchar > text ,json(postgres特有的）

2. 索引相关

       索引可以帮助我们更快的查找和获取指定的行数据，是增强数据库性能的常用手段，但是如果数据库索引使用不合理反而会降低数据库性能，所以我们需要了解索引的相关原理和使用规范。

创建索引语句为：CREATE INDEX test1_id_index ON test1 (id);
删除索引语句为：DROP INDEX test1_id_index;

       索引被创建以后，当向表中插入更新数据时，会自动被触发更新索引，所以在表上创建非必须的索引会导致更多的开销，意味着插入数据会变慢。
       更新、删除和join索引列也会优化查询，使查询更快。
       给已有大量数据的表添加索引会需要一段长的时间。但是在创建索引的时间内，查询还是可以执行的（多线程），但是插入、更新和删除会被阻塞。

设计原则
①、索引并不是越多越好，因为他会占用大量的磁盘，还会影响语句的性能。

②、不要在经常更新的字段添加索引，要在经常查询、频繁排序或分组的列上建立索引。

③、建立索引的列数据量不能太小；区分度要高；长度较短，不然更新速度很慢，占用内存多。

索引失效
①、在索引上做任何操作（计算、函数、显式或隐式类型转换）会导致索引失效

②、mysql在使用 != <>无法使用索引，is null，is not null也无法使用索引

2.1 联合索引的左前缀原则。
下面是联合索引的底层存储结构

我们创建(a,b,c)联合索引时，参照上图结构相当于先按a进行排序，然后按b，最后按c进行排序。想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

• 如果是a>10002 and b= ‘Staff’这种用到了a和b
• where a=1 and b=4 order by c这种分组排序也可以利用到索引
  因为a和b下面的c都是排好序的。但是where a=10001 order by c是只用到了a索引，c没有发挥排序效果。参照图也很好理解，找到了所有的a=10001,但它是先按b排序的，c其实乱序的，所以c排序无法利用索引。
• 'where b = ‘staff’ order by a;'b没有用到索引，a排序也没有利用索引。
  这里首先要先去找b，根据左前缀原则肯定无法利用索引。为啥a排序用不到索引呢？

如何解决like '%关键字%'时索引不被使用的办法？
用覆盖索引来解决。下面这些语句都不会执行全表扫描，因为要查询的字段都在索引里面。

select id from user where a like "%aa%";
select id, b ,c from user where a like "%aa%";

2.2 聚簇索引和非聚簇索引

• 聚簇索引
  聚簇索引指叶子结点即存储索引值又存储实际表数据，因此不必再根据地址去磁盘查。对于次级索引会存着主键的id，指向对主键的引用，主键的索引那里存着实际的数据。如innodb 。所以一张表只能有唯一的聚簇索引，但是可以有多个次级索引。
• 非聚簇索引
  myisam中，主索引和次索引都指向数据磁盘所在位置。

2.3 覆盖索引
       如果查询的列通过索引可直接返回，就称该索引为查询sql的覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。
       通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果次级索引的叶子节点包含了要查询的数据，那么就不用去查询主键聚集索引了。覆盖索引讲解

• 对于存储http：//www.baidu.com这样地址的字段，前面的都是http：//开头，可以采用倒序的方式建立索引。也可以采用伪哈希的方式，将内容哈希后建立索引，查询时将要查询的值先哈希再去查。
• 重复索引没有任何好处。但是冗余索引：比如相同两列建立联合索引，但是顺序不同。
• 磁盘容易导致内存碎片化，可以运行一些数据库的命令修复。
• 索引的type: all < index < range < ref < const