nysql高性能索引设计

《为什么数据库中要使用B+tree索引，而不用hash索引？MySQL中的B+tree索引介绍》 

只有看完以上这篇文章，才会明白为什么下面要这么设计。

 

1、能设计成数字类型的，就不要使用varchar，因为比如varchar(20)，mysql就一定会开辟20个字节的内存出来，在计算的时候。

2、需要数字绝对精确的，可以设计Decimal类型，可是该类型在计算的时候对于性能有一定牺牲。decimal存储的是65个数字，以字符形式存储。

3、数字类型字段，一般情况下bigint(11)  float  double类型就够了。

4、一个字段标记为unsigned无符号设计和不标记，没有性能上差别，计算起来都是一样的。

5、如果有blob或者text这种大量字符串存储的字段，可以把该字段独立出来一张新表，用id关联即可。

6、用enum枚举来替代字符串，但是enum类型order by效率低，需要额外操作。一般建议定义为tinyint类型。

7、定义主键ID !!! 重要的事情说三遍！因为即使不定义主键，mysql也会默认生成一个隐式主键，还不如有个显式主键，利用主键索引，提高查询效率。

 8、日期类型的设计选择datetime, dateime的时间区间是 1001年 - 9999年，而timestamp的时间区间是 1970年 - 2038年，前者用8个字节存储，后者用4个字节存储。另外datetime跟时区没关联，而timestamp跟时区是有关联的。

     需要注意的是，这两者日期类型都只精确到秒。那怎么存储更精确的时间呢？用 bigint 类型存储时间戳即可。

9、尽量避免字段存储null，因为mysql在计算null时需要额外开销，所以友好的设计是：not null default

10、索引命名也很重要，主键索引命名pk_前缀，唯一索引uk_前缀，普通索引 idx_前缀。

11、是或否的字段，用 is_前缀。

 

高性能索引使用策略：

1.不在索引列上做任何操作。比如upper(索引列)

2.尽量全值匹配。比如 index_column = 100

3.最佳左前缀法则。

4.范围条件放最后

5.覆盖索引尽量用

6.不等于要慎用。用的不好就会全表扫描

7.Null/Not有影响。请参考《mysql5.7字段设计需要为非null，尤其是索引列，原理讲述》

8.Like查询要当心。比如不能 like "%abc" 这样使用

9.字符类型加引号。比如不要这样使用： where username = 6   mysql会尝试加隐式函数，比如where cast(username) = 6 这样会使索引失效

10.使用or关键字时要注意

11.使用索引扫描来做排序

12.ASC，DESC别混用。就是别混合使用，比如 order by age asc,username desc

13.尽可能按主键顺序插入行

14.优化Count查询

15.优化limit分页。select * from t_user limit 10000,10;  mysql会取出10010条数据后，把前面一万条扔了，返回给你最后10条。可以改成：select * from t_user where id > 10000 limit 10;(推荐)    或者：   select * from (select id from t_user limit 10000,10) b, t_user a where a.id = b.id;

 

end.