数据库性能优化

一、表结构优化

1、数字类型：存储空间浪费，空间大小不一样，搜索速度也不一样
(1).tinyint 8位 -128~127 unsigned tinyint(0~255)
int,bigint,能确定不会使用负数的字段，建议添加unsigned定义
(2).固定精度的小数，不建议使用decimal表示，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附件维护成本，比如钱都是精确到分，不存3.12元，存312分

2、字符类型：
(1).char类型：定长类型
(2).不定长类型使用varchar，设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定
(3).非万不得已，不要使用text数据类型，其处理方式决定了它的性能低于char或varchar类型的处理
(4).建议使用整形来存储某些特殊的定长字符数据，比如IP地址，这样可以大大的节约存储空间

select inet_aton('192.168.100.200') --> 3232261320
select inet_ntoa(3232261320) --> 192.168.100.200

采用上面的方式，使用一个int就可以存放IP地址了

3、时间类型：
(1).尽量使用timestamp类型，因为其存储空间只需要datetime类型的一半
(2).对于只需要精确到某一天的数据类型，建议使用date类型，因为他的存储空间只需要3个字节，比timestamp还少
(3).不建议通过int类型存储一个UNIX TIMESTAMP的值，因为这太不直观，会给运维带来不必要的麻烦，同时还不会带来任何好处

4、适当拆分：
当表中存储类似于TEXT或者是很大的VARCHAR类型的大字段的时候，如果大部分访问这张表的时候都不需要这个字段，就该把这个字段拆分到另外的独立表中，以减少常用数据块所占用的存储空间;
这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理IO次数，也能大大提高内存中的缓存命中率。

5、适度冗余：
被频繁引用且只能通关Join 2张（或更多）大表的方式才能得到的独立小字段，但是，需要特别注意的是，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

6、尽量使用not null
尽量确保字段的default值不是null，因为null类型比较特殊，使用null类型会导致SQL语句难以优化

 

二、索引优化

1、选择索引列：考虑在where字句中出现列或join字句中出现的列上建索引

select 
    age  -- 不使用索引
from
    t_user
where
    name = 'xiaoming'  -- 考虑使用索引
    and psw = '123456' -- 考虑使用索引

2、最左前缀原则：在创建联合索引的时候，尽量将经常参与查询的字段放在联合索引的最左边。

-- 已创建联合索引(name,age,phone)
select * from t_student where
(1)name='zhangsan' and age=18 and phone='12345678910' -- 会使用到索引
(2)age=18 and phone='12345678910'   -- 不会使用到索引
(3)name='zhangsan' and phone='12345678910'  -- 会使用到部分索引

3、like的使用：一般情况下不建议使用like操作，如果非要使用不可的话，需要注意：like '%abc%'不会使用索引，而like 'abc%'可以使用索引。这也是前面的最左前缀原则的一个使用场景。

4、不能使用索引的说明：
mysql会按照联合索引从左到右进行匹配，知道遇到范围查询，如：>,<,between,like等就停止匹配

a=1 and b=2 and c>3 and d=4

如果建立(a,b,c,d)顺序的索引，d是不会使用索引的。但如果建立联合索引是(a,b,d,c)的话，则a,b,c,d都可以使用到索引，只是最终c是一个范围值。

5、order by：在很多情况下，我们希望order by也可以使用到索引

-- 如果ID是单列索引，则order by会使用索引
select score from t_user where id=3 order by id

-- 如果id是单列索引，name不是索引或name也是单列索引，则order by不会使用索引
select score from t_user where id=3 order by name

 

三、查询优化

1、应尽量避免在where子句中使用!=或<>操作符

2、应尽量避免在where子句中对字段进行null值判断

select id from t_user where num is null
-- 建议在num上设置默认值0,确保表中num列没有null值
select id from t_user where num=0

3、应尽量避免在where子句中使用or来连接条件

select id from t_user where num=10 or num=20
-- 可以这样查询：
select id from t_user where num=10
union all
select id from t_user where num=20

4、应尽量避免使用in和not in

select id from t_user where num in(1,2,3)
-- 对于连续的值，使用between就不用in了
select id from t_user where num between 1 and 3

5、应尽量避免在where子句中对字段进行表达式操作

select id from t_user where num/2=100
-- 应改为：
select id from t_user where num = 2*100

6、应尽量避免在where子句中对字段进行函数操作

select id from t_user where substring(name,1,3) = 'abc'
select id from t_user where datediff(day, create_date, '2018-11-01')
-- 应改为：
select id from t_user where name like 'abc%'
select id from t_user where create_date >= '2018-11-01' and create_date < '2018-11-02'

7、很多时候用exists代替in是一个好的选择

select id from t_user where num in(select num from t_xx)
-- 用下面的语句替换
select id from t_user where exists (select 1 from t_xx where num = t_user.num)

8、任何地方都不要使用select * from t,用具体的字段列表代替*，不要返回用不到的任何字段

select * from t_user
-- 应改为：
select id,name,age from t_user