索引与排序

排序

排序是一个很耗时的过程，最好取出来的数据本身就是排序好的，利用索引排序，比如 good商品表联合索引（cat_id,shop_price) where cat_id = n order by shop_price
select good_id,cat_id,shop_price from goods order by shop_price;
用到了using where
select good_id,cat_id,shop_price from goods order by click_count;
用到了filesort文件排序 using where

重复索引

add index index1(name)

add index index2(name)
#index1 与 index2 为重复索引

冗余索引

add index index1(name,age)
add index  index2(name)
#index1 与 index2 为冗余索引
add index index3(x,y)
add index index4(y,x)
#index3与 index4 也为冗余索引

索引碎片与维护

在长期的数据更改过程中，索引文件和数据文件都将产生空洞形成碎片，我们可以通过一个nop操作（不产生对数据实质影响的操作）,来修改表
比如:表的引擎为innodb,可以 alter table xxx engine innodb;
或者 optimize table 表名 也可以修复
注意：修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，如果表的行非常多的时候，是一个非常消耗时间的操作，所以不能频繁修复，最好在夜间非高峰期操作

QQ浏览器截图20200208090549.png

索引的建立原则

1、表的主键、外键必须有索引;

2、数据量超过300的表应该有索引;

3、经常与其他表进行连接的表，在连接字段上应该建立索引;

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引;

5、索引应该建在选择性高的字段上;

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引;

7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替：

     A、正确选择复合索引中的主列字段，一般是选择性较好的字段;

     B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是，则可以建立复合索引;否则考虑单字段索引;

     C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引;

     E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引;

8、频繁进行数据操作的表，不要建立太多的索引;
9、删除无用的索引，避免对执行计划造成负面影响;

   以上是一些普遍的建立索引时的判断依据，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的;相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
   总的来说，小型表肯定不建索引，或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。其实这个问题更感觉偏向于做软件项目的一种经验。