MySQL------想好怎么写SQL了吗?
一、怎样设计数据库?
数据表字段类型的选择原则:更简单或者占用空间更小。Example:
1、如果长度允许,整型尽量使用tinyint、smallint、mediumint而非int;
bigint 一位为 8 个字节;
int 一位为 4 个字节,int 的 SQL-92 同义词为 integer;
mediumint 一位为 3 个字节;
smallint 一位为 2 个字节;
tinyint 一位为 1 个字节;
整型类型 | SIGNED | RANGE | UNSIGNED | RANGE |
---|---|---|---|---|
tinyint | 有符号的范围是 | -128 – 127 | 无符号的范围是 | 0 – 255 |
smallint | 有符号的范围是 | -2^15 (-32,768) 到 2^15 - 1 (32,767) | 无符号的范围是 | 0到65,535 |
mediumint | 有符号的范围是 | -8,388,608到8,388,607 | 无符号的范围是 | 0到16,777,215 |
int | 有符号的范围是 | -2^31(-2,147,483,648)到 2^31-1(2,147,483,647) | 无符号的范围是 | 0到4,294,967,295 |
bigint | 有符号的范围是 | -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807) | 无符号的范围是 | 0到18,446,744,073,709,551,615 |
2、字段类型为字符串且长度确定,用char类型;
3、如果varchar可以满足,不用text类型;
4、精度要求较高的用decimal类型,也可以用bigint,比如精确两位小数就乘以100后保存;
5、尽量用timestamp而非datetime.
相比datetime,timestamp占用更少的空间,以UTC的格式储存自动转换时区
慎用空值
MySQL中字段为NULL时依然占用空间,会使索引、索引统计更加复杂。从NULL更新到非NULL无法做到原地更新,容易发生索引分裂影响性能。尽可能将NULL用有意义的值代替,也能避免SQL语句里面包含is not null的判断。
text类型优化
由于text字段储存大量数据,表容量会达到极限,影响其他字段的查询性能。建议抽取出来放在子表里,用业务主键关联。
二、索引优化,方向对了吗
索引分类
1 普通索引:最基本的索引
创建方法:三种方法 关键字:index
(1) 建表时添加索引
create table if not exists `tablename`(
...
index index_name(name)
)
(2)建表后添加索引
create index index_name on tablename(name);
(3)修改表的方式添加索引
alter table tablename add index index_name(name);
2 组合索引:多个字段上建立的索引,能够加速复合查询条件的检索。
创建方法:三种方法 关键字:key 与 index
(1) 建表时添加索引
create table if not exists `tablename`(
no int not null primary key,
name varchar(20) null,
...
key index_no_name(no,name)
)
(2)建表后添加索引
create index index_name_no on tablename(name,no);
(3)修改表的方式添加索引
alter table tablename add index index_name_no(name,no);
3 唯一索引:与普通索引类似,但索引列的值必须唯一,允许有空值
创建方法:三种方法 关键字:unique index
(1) 建表时添加索引
create table if not exists `tablename`(
no int not null primary key,
email varchar(50) null,
...
unique index index_email(email )
)
(2)建表后添加索引
create unique index index_email on tablename(email);
(3)修改表的方式添加索引
alter table tablename add unique index index_email (email);
4 组合唯一索引:列值的组合必须唯一
5 主键索引:特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用primary key约束。
创建方法:两种方法 关键字:primary key
(1) 建表时添加索引
create table if not exists `tablename`(
id int(20) comment'主键',
...
primary key(id)
)
(2)修改表的方式添加索引
alter table tablename add primary key(id);
6 全文索引:用于海量文本的查询,MySQL5.6之后的InnoDB和MyISAM均支持全文索引。
创建方法:三种方法 关键字:fulltext index
(1) 建表时添加索引
create table if not exists `tablename`(
no int not null primary key,
...
info varchar(20) null,
fulltext index index_info(info)
)
(2)建表后添加索引
create fulltext index index_info on tablename(info);
(3)修改表的方式添加索引
alter table tablename add fulltext index index_info(info);
由于查询精度以及扩展性不佳,更多的企业选择ElasticSearch。
索引优化
1 分页查询很重要,如果查询数据量超过30%,MYSQL不会使用索引。
2 单表索引数不超过5个、单个索引字段数不超过5个。
3 字符串可使用前缀索引,前缀长度控制在5-8个字符。
4 字段唯一性不够,增加索引没有意义,如:是否删除(is_delete)、性别(gender)。
合理使用覆盖索引,如下所示:
select username, nickname from tablename where username= ?
三,sql语句优化方向
业务描述:更新所有用户已过期的优惠券为不可用状态。
update status = 0 FROM `mall_coupon` WHERE expire_time <= #{currenttime} and status=1;
如果大量优惠券需要更新为不可用状态,执行这条SQL可能会堵死其他SQL,分批处理伪代码如下:
int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
if (CollectionUtils.isEmpty(batchIdList)) {
return;
}
update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
pageNo ++;
}
范围操作符’<>’,’!='等的优化
通常操作符无法使用索引,比如,查询金额不为100元的订单:
select order_id from `mall_orders` where order_amount != 100;
如果金额不为100的订单极少,这种数据分布不均衡的情况下,有可能使用索引。鉴于这种不确定性,采用 union all 聚合查询,如下:
(select order_id from `mall_orders`where order_amount > 100)
union all
(select order_id from `mall_orders`where order_amount < 100 and order_amount > 0)
关键字OR的优化
在Innodb引擎下or无法使用组合索引,比如:
select order_id ,product_name from `mall_orders`where phone = '13888889999' or user_id = 666;
or无法命中phone + user_id的组合索引,可采用union,如下所示:
(select order_id ,product_name from `mall_orders` where phone = '13888889999')
union
(select order_id ,product_name from `mall_orders` where user_id = 666);
此时order_id 和product_name字段如果都有索引,查询才最高效。
关键字IN的优化
IN适合主表大子表小,EXIST适合主表小子表大。
由于查询优化器的不断升级,很多场景这两者性能差不多一样了。
尝试改为join查询,举例如下:
select order_id from `mall_orders` where user_id in (select user_id from `user` where level = 'VIP');
采用JOIN如下所示:
select mo.order_id from `mall_orders` mo left join `user` u on mo.user_id = u.user_id where u.level = 'VIP';
列运算不划算
通常在查询条件列运算会导致索引失效,如下所示:
查询当天订单的明细
select order_id from `mall_orders` where date_format(create_time,'%Y-%m-%d') = '2018-08-08';
date_format函数会导致这个查询无法使用索引,调整为:
select order_id from `mall_orders` where create_time between '2018-08-08 00:00:00'
and '2018-08-08 23:59:59';
避免Select All
如果不查询表中所有的列,就不用select *,它会进行全表扫描,不能有效利用索引。
Like优化
like用于模糊查询,举个例子(column2 已建立索引):
SELECT column1,column2 FROM tablename WHERE column2 like '%keyword%';
这个查询未命中索引,换成下面的写法:
SELECT column1,column2 FROM tablename WHERE column2 like 'keyword%';
去除了前面的通配符%查询将会命中索引,但是实际业务场景一定要前后模糊匹配 全文索引fulltext可以尝试一下,但ElasticSearch才是真香。
Join优化
join的实现是采用Nested Loop Join算法,就是通过驱动表的结果集作为基础数据,通过该结数据作为过滤条件到下一个表中循环查询数据,然后合并结果。如果有多个join,则将前面的结果集作为循环数据,再次到后一个表中查询数据。
驱动表和被驱动表尽可能增加查询条件,满足ON的条件而少用Where,用小结果集驱动大结果集。
被驱动表的join字段上加上索引,无法建立索引的时候,设置足够的Join Buffer Size。
禁止join连接三个以上的表,尝试增加冗余字段。
Limit优化
limit用于分页查询时越往后翻性能越差,解决的原则:缩小扫描范围,如下所示:
耗时时间仅供参考,具体跟每个人的集成环境相关
select * from `mall_orders` order by order_id limit 100000,10
耗时0.4秒
select * from `mall_orders` order by order_id limit 1000000,10
耗时5.2秒
先筛选出order_id 缩小查询范围,写法如下:
select * from `mall_orders` where order_id > (select order_id from `mall_orders` order by order_id limit 1000000, 1) order by order_id limit 10
耗时0.5秒
如果查询条件仅有主键order_id ,写法如下:
select order_id from `mall_orders` where order_id between 1000000 and 1000010 order by order_id
耗时0.3秒