使用IntelliJ IDEA开发groovy，创建一个项目进入到这个界面: 如果Groovy. library是no. library select 那就点击右侧的create按钮,选择你安装的groovy目录就可以, 如果IntelliJ IDEA 左侧没有Groovy选项的话那就安装groovy插件, 新建的groovy项目是这样的: 会发现并不是像我们Android 在studio下创建项目一样有一个默认的MainActivity,groovy是没有的，那么从事编程开发的入门都是从hello world开始的，那么现在创建一个hello.groovy文件
|
1 2 3 4 5 6 7 8 9 |
class Hello { public static void main(String[] args){ print("hello world groovy") } } |
这是我们创建的第一个类Hello.groovy 从上面的代码就可以提现出groovy是完全支持Java的 而且更方便的是我们在控制台输入一句话 直接使用println()方法就行,相当于Java中的静态导包功能. 还有更神奇的写法: 是不是groovy很强大 这个写法和上面的Java中的写法结果是一样的.如果想弄懂gradle 构建是有必须去学习下. from:https://blog.csdn.net/coderinchina/article/details/89600861
View Details前几天统计一个sql，是一个人提交了多少工单，顺便做了相关sql优化。数据大概2000多w。
|
1 2 3 |
select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; |
为了实验最少受其他因素干扰，将生产库的200多w数据导出来，用测试服务器进行测试。 导出来的数据是一个堆表，没有主键，没有索引。
|
1 2 3 4 5 |
mysql> show index from WorkOrder; 查询index方法1 Empty set (0.00 sec) mysql> show keys from WorkOrder; 查询index方法2 Empty set (0.00 sec) |
1.堆表的情况 这时候就在这时候，用执行计划分析下语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | NULL | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ 1 row in set |
select_type的值为SIMPLE，表示简单的select查询，不使用union或子查询。 type的值为ALL，表示要对表进行表扫描。 possible_keys 表示能使用哪个索引找到行记录。 key 表示Mysql决定使用的索引（键）。 key_len 表示Mysql决定使用索引的长度。 ref 表示使用哪个列和key一起从表中选择行。 rows 表示Mysql认为它执行查询时必须检查的行数。 extra 表示查询的详情信息，用到where，临时表，排序。 执行下该语句三次，发现执行了16.30 sec、16.34 sec、16.24 sec。 2.有索引的情况 建了四个索引，分别以custname，CreateDate建两个单列索引，另外两个是联合索引，只是最左边列不一样。
|
1 2 3 4 |
alter table WorkOrder add index ix_name(custname) alter table WorkOrder add index ix_date(CreateDate) alter table WorkOrder add index ix_namedate(custname,CreateDate) alter table WorkOrder add index ix_datename(CreateDate,custname) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show keys from WorkOrder; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | WorkOrder | 1 | ix_name | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_date | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_namedate | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_namedate | 2 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 2 | CustName | A | 2528727 | NULL | NULL | YES | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) |
之后，用执行计划分析下sql查询语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ 1 row in set (0.01 sec) |
从执行计划可以看出，Mysql从四个索引中选取了ix_datename这个索引，type为range表示索引范围扫描。rows的数量值是没堆表的1/3。 执行语句三次，时间是 8.64 sec、8.61sec、8.55 sec。 我建了三个索引，那么我想用下另外三个索引怎么办？ 这里可以用force index()，这个指令可以指定本次查询强制使用哪个索引，因为Mysql优化器的选择并不是最优的索引。
|
1 2 3 4 5 6 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_namedate) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_namedate | 307 | NULL | 2528727 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ |
选用另一个联合索引 ix_namedate，这次type变为index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 7.84 sec、7.92 sec、7.84 sec。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_name) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_name | 303 | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ 1 row in set |
选用另一个联合索引 ix_name，这次type是index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 1 min 28.17 sec、1 min 27.64 sec、1 min 27.58 sec。
|
1 2 3 4 5 6 7 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_date | 4 | NULL | 921062 | Using index condition; Using MRR; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ |
选用另一个联合索引 ix_date，这次type是range，表示索引范围扫描，rows的值是堆表的1/3多些 。 执行语句三次，时间是 9.55 sec、9.52 sec、9.39 sec。 假如我不想用索引了怎么办？ 可以使用ignore index()，这个指令可以强制Mysql在查询时，不使用某索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date,ix_name,ix_namedate,ix_datename) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | ix_name,ix_namedate,ix_datename | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ |
上面第一个强制不使用ix_date索引，那么就Mysql就从剩下的三个索引中，选取他认为是最优的索引。第二个时将四个索引都不使用，那么Mysql就进行全表扫描了。 总结： 1.Mysql的语句优化，没有绝对的正确，explain也只是给出个大致的方向，例如 key_len值小的，rows小的按理说，时间应该最短，效率最高。但是，实验中时间最少的却不是那个值最小的。 2. 优化还需根据实际数据情况，例如，假如我where选取的时间范围变化，或者说CustName的分布有些变化，可能跟刚才的实验，又会产生一定偏差。 3. 同样我还实验了，当给表加上主键时，整体的查询时间会缩短些。 […]
View Details