2019年/11月/05日

首页回退

一塌糊涂的SQL

SQL是一个声明式语言，足够抽象，理论上，学习声明式语言收益是巨大的，再加上SQL底层有着干净数学模型，可是现实中存在着各种丑陋肮脏的抽象泄露

所谓的泄露就是我们每写一个SQL都如履薄冰,看看下面我做的测试

explain基础:

select_type

type

从最好到最差的排列 const、eq_reg、ref、range、index 和 ALL

possible_keys: 可能命中的索引

key: 实际使用的索引

Extra

效率最低的是 Using temporary 和 Using filesort

测试表：

DROP TABLE IF EXISTS test_table;
CREATE TABLE `test_table` (
   id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `apply` varchar(100) NOT NULL DEFAULT '',
  `apply2` varchar(100) NULL,
  `age` int NOT NULL,
  `age2` int NULL,
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '修改时间',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE INDEX index_apply ON test_table (apply);
CREATE INDEX index_apply2 ON test_table (apply2);
CREATE INDEX index_age ON test_table (age);
CREATE INDEX index_age2 ON test_table (age2);


DROP TABLE IF EXISTS test;
create table test(
a int,
b int,
c int,
d int,
KEY index_abc(a,b,c)
);

插入数据

import groovy.sql.Sql
import org.apache.commons.lang3.RandomUtils

def url = ''
def user = ''
def password = ''
def driver = 'org.gjt.mm.mysql.Driver'
def sql = Sql.newInstance(url, user, password, driver)

//Insert Null
1000.times {
    def insertSql = 'insert into test_table(apply,age,created_at,updated_at) values (?,?,?,?)'
    def params = [System.currentTimeMillis(), RandomUtils.nextInt(), new Date(), new Date()]
    sql.executeInsert insertSql, params
}

//Inert Not Null
1000.times {
    def insertSql = 'insert into test_table(apply,apply2,age,age2,created_at,updated_at) values (?,?,?,?,?,?)'
    def params = [System.currentTimeMillis(), System.currentTimeMillis(), RandomUtils.nextInt(), RandomUtils.nextInt(), new Date(), new Date()]
    sql.executeInsert insertSql, params
}

// 不留空
1000.times {
    def insertSql = 'insert into test(a,b,c,d) values (?,?,?,?)'
    def params = [RandomUtils.nextInt(), RandomUtils.nextInt(),RandomUtils.nextInt(),RandomUtils.nextInt()]
    sql.executeInsert insertSql, params
}

sql.close()

注意必须要插入数据来测试,空表没有意义

单索引

explain列的定义:

select_type table type possible_keys key key_len ref rows Extra

等和不等

explain select * from test_table where id = 1
SIMPLE	test_table	const	PRIMARY	PRIMARY	4	const	1	NULL

explain select * from test_table where apply = 'hello'
SIMPLE	test_table	ref	index_apply	index_apply	302	const	1	Using index condition


explain select * from test_table where apply != 'hello'
SIMPLE	test_table	ALL	index_apply	NULL	NULL	NULL	1000	Using where


explain select * from test_table where apply is null
SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Like和Not Like

explain select * from test_table where apply like 'h%'
SIMPLE	test_table	range	index_apply	index_apply	302	NULL	1	Using index condition

explain select * from test_table where apply like '%h'
SIMPLE	test_table	ALL	NULL	NULL	NULL	NULL	1000	Using where

explain select * from test_table where apply not like 'h%'
SIMPLE	test_table	ALL	NULL	NULL	NULL	NULL	1000	Using where

类型不匹配

explain select * from test_table where apply > 1
SIMPLE	test_table	ALL	index_apply	NULL	NULL	NULL	1000	Using where

NULL

explain select * from test_table where apply2 is null
SIMPLE	test_table	ref	index_apply2	index_apply2	303	const	1999	Using index condition

explain select * from test_table where apply2 is not null
SIMPLE	test_table	ALL	index_apply2	NULL	NULL	NULL	3000	Using where

大于 小于

explain select * from test_table where age = 2
SIMPLE	test_table	ref	index_age	index_age	4	const	1	NULL

explain select * from test_table where age > 2
explain select * from test_table where age < 2

SIMPLE	test_table	ALL	index_age	NULL	NULL	NULL	6116	Using where //区分度不高，选择了权标扫描
SIMPLE	test_table	range	index_age	index_age	4	NULL	1	Using index condition //区分度高，所以命中索引

不等和运算

explain select * from test_table where age != 2
SIMPLE	test_table	ALL	index_age	NULL	NULL	NULL	3000	Using where

explain select * from test_table where age <> 2
SIMPLE	test_table	ALL	index_age	NULL	NULL	NULL	3000	Using where


explain select * from test_table where age/2 = 100
SIMPLE	test_table	ALL	NULL	NULL	NULL	NULL	3000	Using where

in between or

explain select * from test_table where age in (1,2,3)
SIMPLE	test_table	range	index_age	index_age	4	NULL	3	Using index condition

explain select * from test_table where age = 1 or age = 2 or age = 3
SIMPLE	test_table	range	index_age	index_age	4	NULL	3	Using index condition

explain select * from test_table where age not in (1,2,3)
SIMPLE	test_table	ALL	index_age	NULL	NULL	NULL	3000	Using where

explain select * from test_table where age between 1 and 1000
SIMPLE	test_table	range	index_age	index_age	4	NULL	1	Using index condition

NULL

explain select * from test_table where age2 is null
SIMPLE	test_table	ref	index_age2	index_age2	5	const	2999	Using index condition

explain select * from test_table where age2 is not null
SIMPLE	test_table	ALL	index_age2	NULL	NULL	NULL	2000	Using where

复合索引

必须按顺序

explain select * from test where a = 10
SIMPLE	test	ref	index_abc	index_abc	5	const	1	Using index

explain select * from test where b = 10
SIMPLE	test	ALL	NULL	NULL	NULL	NULL	2000	Using where

explain select * from test where a = 10 and b = 10
SIMPLE	test	ref	index_abc	index_abc	10	const,const	1	Using index

explain select * from test where a = 10 and d = 10
SIMPLE	test	ref	index_abc	index_abc	5	const	1	Using where

or里如果存在没有索引

explain select * from test where a = 10 or d = 10
SIMPLE	test	ALL	index_abc	NULL	NULL	NULL	2000	Using where

不按顺序又排序，还分组

explain select * from test where b = 10 and c = 10
SIMPLE	test	ALL	NULL	NULL	NULL	NULL	2000	Using where

explain select * from test where a = 10 order by d desc
SIMPLE	test	ref	index_abc	index_abc	5	const	1	Using where; Using filesort

explain select max(d) from test
SIMPLE	test	ALL	NULL	NULL	NULL	NULL	2000	NULL

explain select * from test group by d
SIMPLE	test	ALL	NULL	NULL	NULL	NULL	2000	Using temporary; Using filesort

套路 (部分参考了网络资料,请持有批判精神)

结论

所以简单的写个sql会发现一堆的套路，而且同一个数据库的不同版本可能还有差异，更别提不同的数据库了，还有更严重的