MySQL-------想好怎么写SQL了吗?

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秒