mysql实现动态行转列

需求背景：在任务管理系统中，有任务详情表，每个任务下又分子任务节点，每个任务节点都有具体的跟进日期，包括开始时间结束时间，每天的任务完成进度。 有这样一个需求：在任务管理系统中根据任务节点id，展示每天的进度情况。 因为每个任务的时间范围不一样，所以需要用到动态的行转列，将时间周期作为动态的列，进度做为列的值。 任务节点表： quest_node_id：任务子节点id，follow_date:跟进日期 ，grade:进度百分比。 在mysql中创建存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
BEGIN
 
 
 
SET @EE='';
 
SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(follow_date=\'',follow_date,'\'',',grade,0)) AS ',follow_date,',') col
 
FROM (SELECT DISTINCT follow_date FROM quest_follow_copy) A) b ORDER BY b.col desc limit 0,1 ;
 
SET @QQ=CONCAT('SELECT quest_follow_copy.quest_node_id AS columnA,',LEFT(@EE,LENGTH(@EE)-1),'
 
FROM quest_follow_copy GROUP BY quest_node_id');
 
PREPARE stmt2 FROM @QQ;
 
EXECUTE stmt2;
 
 
 
END

  注意：我发现follow_date的日期格式（如2018-08-10）会报错，存储过程执行不了，并且数字开头也不可以，所以我把日期改成了varchar类型，并在值里加了前缀。 执行结果如下： 某个节点在某天没有值则补零。 参考文档：mysql 动态行转列   from:https://blog.csdn.net/xiaoxiangzi520/article/details/81480477

龙生   22 Dec 2020
View Details

mysql行列转换

创建数据库、表

1
2
3
4
5
6
7
8
create database tests;
use tests;
create table t_score(
id int primary key auto_increment,
name varchar(20) not null,  #名字
Subject varchar(10) not null, #科目
Fraction double default 0  #分数
);

  添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海', '语文', 86),
        ('王海', '数学', 83),
        ('王海', '英语', 93),
        ('陶俊', '语文', 88),
        ('陶俊', '数学', 84),
        ('陶俊', '英语', 94),
        ('刘可', '语文', 80),
        ('刘可', '数学', 86),
        ('刘可', '英语', 88),
        ('李春', '语文', 89),
        ('李春', '数学', 80),
        ('李春', '英语', 87);

  方式一:使用if

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select name as 名字 ,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
round(AVG(Fraction),2) as 平均分,
SUM(Fraction) as 总分
from t_score group by name    
union
select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
select 'TOTAL' as name,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
SUM(Fraction) as 总分
from t_score group by Subject )t

  方式二:使用case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select  name as Name,
sum(case when Subject = '语文' then Fraction end) as Chinese,
sum(case when Subject = '数学' then Fraction end) as Math,
sum(case when Subject = '英语' then Fraction end) as English,
sum(fraction)as score
from t_score group by name
UNION ALL
select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
select 'TOTAL' as name,
sum(case when Subject = '语文' then Fraction end) as Chinese,
sum(case when Subject = '数学' then Fraction end) as Math,
sum(case when Subject = '英语' then Fraction end) as English,
sum(fraction)as score
from t_score group by Subject)t

  方法三: with rollup

1
2
3
4
5
6
7
select
        ifnull(name,'TOll') name,
        sum(if(Subject='语文',Fraction,0)) as 语文,
       sum(if(Subject='英语',Fraction,0)) as 英语,
       sum(if(Subject='数学',Fraction,0))as 数学,
       sum(Fraction) 总分
        from t_score group by name with rollup

from:https://www.cnblogs.com/weibanggang/p/9679301.html

龙生   22 Dec 2020
View Details

elementUI中el-table 如何动态生成列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<el-table :data="tableData" style="width: 100%">
  <el-table-column
    v-for="col in cols"
    :prop="col.prop" :label="col.label" >
  </el-table-column>
</el-table>
<el-button raw-type="button" @click="addCol">
  添加一列
</el-button>
data() {
        return {
          tableData: [{
            date: '2016-05-02',
            name: '王小虎',
            address: '上海市普陀区金沙江路 1518 弄'
          }, {
            date: '2016-05-04',
            name: '王小虎',
            address: '上海市普陀区金沙江路 1517 弄'
          }, {
            date: '2016-05-01',
            name: '王小虎',
            address: '上海市普陀区金沙江路 1519 弄'
          }, {
            date: '2016-05-03',
            name: '王小虎',
            address: '上海市普陀区金沙江路 1516 弄'
          }],
          cols: [
          {prop: 'date', label: '日期'},
        {prop: 'name', label: '姓名'},
      ]
        }
      },
  methods: {
      addCol(){
        this.cols.push({prop: 'address', label: '地址'})
    }

from:https://blog.csdn.net/baidu_34692401/article/details/83348130

龙生   22 Dec 2020
View Details

IIS最大并发连接数 = 队列长度 + IIS最大并发工作线程数

深入理解IIS的多线程工作机制 首先让我们来看看IIS里面的这2个数字：最大并发连接数，队列长度。先说这2个数字在哪里看。   最大并发连接数：在IIS中选中一个网站，右键网站名称，在右键菜单中找到并点击【管理网站】->【高级设置】。打开对话框如下图：   队列长度：在IIS中选中【应用程序池】，在应用程序池列表中，右键你想查看的，在右键菜单中选择【高级设置】。打开如下对话框：   这两个数字表面上看是影响我们站点的并发处理能力的，但是具体是如何影响一个网站的并发处理能力的呢？要完全理解IIS的并发处理能力，除了这2个数字，实际上还有一个非常关键的数字：IIS最大并发工作线程数。   1. IIS最大并发工作线程数 在以前很长一段时间，我一直以为IIS的【最大并发连接数】就是影响IIS最大并发工作线程数。我以为将【最大并发连接数】设置为1万，那么当1万个请求同时到来的时候，IIS会开启1万个线程进行处理，如果同时到来2万个请求，由于最大并发连接数只有1万，那么剩余1万个请求就会放在队列里面，当前面的1万个线程中某个完成了请求之后，再从队列里面取一个请求。但，这个理解是完全错误的，相信很多朋友也跟我有同样的理解。   现在，首先让我们来理解什么是【IIS最大并发工作线程数】。这个数字在IIS里面是没有界面进行设置的，我以前根本就不知道有这个数字。这个数字跟操作系统相关，我的win7系统的IIS的值是10，VS2012自带的IIS Express的值是80。对于windows服务器版本的系统的具体值是多少没有测试过，但我猜应该也是有限制的。   这个数字到底是什么意思呢？回到上面举的例子，当1万个请求同时进入IIS的时候，由于win7系统的IIS只有10个工作线程，那么这时1万请求中只有10个请求会在第一时间被处理，剩余9990个请求都需要排队。也就是说，IIS最多能够安排10个线程同时处理请求（win7版本的IIS，有的可能是20）。   所以，如果你用自己的win7系统测试IIS的性能的时候，你可能发现，不管你怎么设置【最大并发连接数】，你的IIS处理能力都很有限。   2. 最大并发连接数 上面讲的IIS最大并发工作线程数，看上去就是IIS的并发处理能力，如果是这样，那么【最大并发连接数】有什么意义呢？   还是上面的例子，如果1万个请求同时到来，而我们的win7系统的IIS最大并发工作线程数只有10，这时如果将【最大并发连接数】设置为100，会有什么效果呢？答案是：只有100个请求会收到正常响应，剩余9900个请求直接返回503（服务不可用）的错误。这时，实际上进入排队等待的只有90个请求。   再换下测试参数，如果将【最大并发连接数】设置为5000，又会有什么效果？答案你可能已经知道了，那就是一开始就有5000个请求直接返回503，剩下5000个请求慢慢正常返回。   这里你看明白了吧，【最大并发连接数】在我们的测试例子中，影响到了排队的数量。这样的话，看上去【队列长度】又不知道什么意思了？   3. 队列长度 在上面的例子中，如果1万个请求同时到来，【最大并发连接数】设置为100。这时我们知道，IIS首先会安排那10个线程去处理10个请求，剩下90个请求都需要排队。这时如果我们将【队列长度】设置为50，那会出现什么情况？答案是，40个请求会直接返回503服务不可用的错误（因为队列只有50个的长度，剩下的40个就无法排队了），最终只有60个请求会被正确处理。   读到这里，你明白了吗？   结论 当很多请求同时到来的时候，IIS会根据【最大并发连接数】来判断是否有多余的请求，多余的请求直接返回503，然后再根据【队列长度】来判断是否有多余的请求排不了队，排不了队的也直接返回503。所以，如何设置【最大并发连接数】和【队列长度】，实际上是有公式可以计算的：   最大并发连接数 = 队列长度 + IIS最大并发工作线程数   最后再说说IIS的默认值对我们网站并发处理能力的影响。IIS默认的【最大并发连接数】为4294967295（42亿多），而【队列长度】默认值为1000。对于windows server版本的IIS，最大并发工作线程数可能几百（猜测，可能没有限制），按照这个默认值，那么IIS同时处理的请求数也就1000多。1000多这个数字才是IIS真正的并发处理能力，而这个能力跟我们的代码没有关系。那么哪些指标是评判我们网站的处理能力的呢？最重要的指标可能莫过于【每秒处理请求数】吧（在性能分析器里面可以查看），这个数字也叫吞吐率。如果每个请求处理速度非常快，那么那么网站吞吐率就大，吞吐率大那么支持的同时在线人数就大。如果要做秒杀，那就看你的秒杀相关的URL支持多大的吞吐率吧。了解了这么多指标，还没有涉及到CPU的计算能力。CPU的计算能力是如何影响网站的处理能力的呢？还是那么多请求，如果CPU很强大，能够缩减每个请求的处理时间，那必然会提高吞吐率。还有很多的请求，如果花在网络传输或者到数据库的传输时间比较多，这部分等待时间CPU是闲置的，如果能够提高CPU的利用率，也可能提高网站的处理能力，最充分的利用服务器的资源。如果不想改代码而想提高CPU利用率，可以在IIS的应用程序池中设置最大工作进程数（默认值为1），可以设置为10如果当前CPU利用率只有百分之几的话，调整这个数值需要特别注意每一个工作进程是独立的应用程序，全局静态变量不共享。原文:你真的了解：IIS连接数、IIS并发连接数、IIS最大并发工作线程数、应用程序池的队列长度、应用程序池的最大工作进程数 吗？   IIS连接数   一般购买过虚拟主机的朋友都熟悉购买时，会限制IIS连接数，这边先从普通不懂代码用户角度理解IIS连接数 顾名思义即为IIS服务器可以同时容纳客户请求的最高连接数，准确的说应该叫“IIS限制连接数” 这边客户请求的连接内容包括： 1、网站html请求，html中的图片资源，html中的脚本资源，其他需要连接下载的资源等等,任何一个资源的请求即一次连接（虽然有的资源请求连接响应很快） 2、如果网页采用框架（框架内部嵌套网页请求），那么一个框架即一次连接 3、如果网页弹出窗口（窗口内部嵌套网页请求），那么一个窗口一个连接   虚拟主机供应商在IIS（6.2版本，以下所有截图均此版本）中  “点击网站”->“右击切换到功能视图”->“点击界面右侧的‘限制’链接”->“编辑网站限制”   限制连接数即为虚拟主机供应公开的IIS连接数标准，如果购买的IIS连接数为50,那么我们不得不考虑网站的内容框架和访问量 如果网站图片够多，弹窗窗口随意（可能连时间选择框、简单条件筛选框也用弹出新窗口），加上不得已的打开新页面浏览内容，那么仅仅能容忍10个人同时操作也很正常，我不会把这个操作描述为很多网站说的“10同时在线”，这很容易让人误解，在用户的一次请求（表面上可能是刷新一次网页，实际上内部请求不止一次，事实上很少只有一次）都完成得到服务器响应完毕之后，连接全部会被释放，当然在你看到展示的页面之前，内部嵌套如果有请求图片等连接请求，连接会早早的被释放 事实上，很多企业门户网站访问量低的惊人，IIS连接数为50也是绰绰有余了 这边给出更加详细参考链接：http://www.west263.com/info/html/IDCzixun/zhujizuyong/20080221/1677.html   IIS并发连接数   “管理网站”->“高级设置”->"限制"->"最大并发连接数" 其实，普通用户常说的“IIS链接数”就是这边的“最大并发连接数”，如果PC端有IIS的朋友，可以测试上面两个图片的设置，是相互影响的 这边默认最大并发连接数为：4294967295，这是一个很惊人的数字，难道这代表着网站能具有并发执行连接数为4294967295的能力？ 这边我做几个假设： 1、很多虚拟主机供应商所说的无并发连接数限制真的成立吗？ 2、每个连接的处理，IIS都会开启一个线程去处理，假设这个处理方式成立，那么4294967295个并发连接请求来了是否IIS会立即启动4294967295个线程去处理？   对于1：很显然不成立，最大并发连接数的设置绝对有上限 对于2：这是很多朋友的误区，假设4294967295并发连接同时来了，IIS不会立即启动4294967295个线程去处理，因为这不现实，对于处理连接，IIS是有“最大并发工作线程数”限制的，这是我下面要介绍的，我从一些资料上查阅到，该数字跟操作系统相关，win7系统的IIS的值是10（或者其他不确定），VS2012自带的IIS Express的值是80。对于windows服务器版本的系统的具体值不清楚，即4294967295个并发连接来了后，（这边以win7下的10为例），iis第一时间只能启动10个工作线程去处理，那么其他4294967285必须排队，排队对用户的体验来说就是网页正在加载，但是什么都不显示，然后此时购买了据虚拟主机供应商所说的无并发连接数限制的客户就要开始狂暴了，为何购买了所谓的“无限并发连接数”，还是会一直在加载的情况，我只能说这就是IIS处理能力有限的问题了   当然服务器没有直接返回“HTTP Error 503. The service is unavailable.”应该也算是一些你花更多钱的安慰吧，因为你只购买了IIS连接数为50的话，那么第50+1个连接请求操作得到的就直接是“HTTP Error 503. […]

龙生   18 Dec 2020
View Details

MYSQL 数据库分区及取消分区

同事MM之前分享了数据库分区的知识，之前没认真研究过，结果现在自己的数据库资料越来越多，让强迫症的我特别难受，所以就对着同事写的SOP一步一步操作 。这里只介绍Range分区，因为我也是刚用到这一个 . 举个例子：要求将数据库dbemployeeinfo_copy 按ID分区，<3的为一个区，其它的为另一个区.，之后取消分区 原Table 的ID栏位为int格式： 实际生成的table在文件中的存储格式： 具体步骤如下： 1. 进入该table设置页，点击Partioning进行分区： 2.查看C盘该table位置，会发现原来的table已经分成两部分了，之后再增加资料，Part0的大小都不会发生变化了。   有时候会发现当初没考虑好，所以分区分错了，需要重新设置，所以第一反应就是把之前在Partitioning里设置的全部取消，然后提交就好了，但是会发现时无法提交的 这时候只能用指令解决了：alter table test.dbemployeeinfo_copy remove partitioning; 再去检查一下C盘对应文件夹里该table的资料，就没有分区了   from:https://blog.csdn.net/feelingdu/article/details/81914641

龙生   15 Dec 2020
View Details

MySQL配置优化

一、全局配置 （1）max_connections 最大连接数。默认值是151，最多2000。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。但是如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值。 查看最大连接数

1
mysql> SHOW VARIABLES LIKE 'max_connections';

查看响应的连接数

1
mysql> SHOW STATUS LIKE 'max%connections';

max_used_connections / max_connections * 100% （理想值≈85%） 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。 （2）back_log MySQL能暂存的连接数量，默认值是80，最多512，可设置为128。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。如果等待连接的数量超过back_log，将不被授予连接资源。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。 （3）key_buffer_size 索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

1
2
3
4
5
6
7
mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 90585564 |
| Key_reads         | 97031    |
+-------------------+----------+

计算索引未命中缓存的概率： key_cache_miss_rate = Key_reads / Key_read_requests * 100%，设置在1/1000左右较好 key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。 默认配置数值是8388608(8M)，主机有4GB内存，可改为268435456(256M) （4）query_cache_size 使用查询缓存(query cache)，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。 最佳选项是将其从一开始就停用，设为0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如Redis或Memcached）。 通过检查状态值qcache_*，可以知道query_cache_size设置是否合理

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 1031360  |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 10302865 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。 查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100% 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。 与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。 query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。 query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。 query_cache_min_res_unit指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。 （5）read_buffer_size 是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 默认数值是131072(128K)，可改为16773120(16M) （6）read_rnd_buffer_size 随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 默认数值是262144(256K)，可改为16777208(16M) （7）sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。 默认数值是10485760(1M)，可改为16777208(16M) （8）join_buffer_size 联合查询操作所能使用的缓冲区大小 read_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100 […]

龙生   15 Dec 2020
View Details

MySQL分区和分表

一、概念 1.为什么要分表和分区？ 日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。 2.什么是分表？ 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。 3.什么是分区？ 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。 4.mysql分表和分区有什么联系呢？ （1）都能提高mysql的性高，在高并发状态下都有一个良好的表现。 （2）分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。 （3）分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。 （4）表分区相对于分表，操作方便，不需要创建子表。 二、分区 1.分区的类型： （1）Range：把连续区间按范围划分 例：

1
2
3
4
5
6
7
8
9
10
11
create table user(
    id int(11),
    money int(11) unsigned not null,
    date datetime
)
partition by range(YEAR(date))(
    partition p2014 values less than (2015),
    partition p2015 values less than (2016),
    partition p2016 values less than (2017),
    partition p2017 values less than maxvalue
);

（2）List：把离散值分成集合，按集合划分，适合有固定取值列的表 例：

1
2
3
4
5
6
7
8
create table user(
    a int(11),
    b int(11)
)
partition by list(b)(
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (2,4,6,8,0)
);

（3）Hash：随机分配，分区数固定 例：

1
2
3
4
5
6
create table user(
    a int(11),
    b datetime
)
partition by hash(YEAR(b))
partitions 4;

（4）Key：类似Hash，区别是只支持1列或多列,且mysql提供自身的Hash函数 例：

1
2
3
4
5
6
create table user(
    a int(11),
    b datetime
)
partition by key(b)
partitions 4;

2.分区管理 （1）新增分区

1
2
ALTER TABLE sale_data
ADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));

（2）删除分区

1
2
--当删除了一个分区，也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201710;

（3）分区的合并 下面的SQL，将p201701 – p201709 合并为3个分区p2017Q1 – p2017Q3

1
2
3
4
5
6
7
8
9
ALTER TABLE sale_data
REORGANIZE PARTITION p201701,p201702,p201703,
p201704,p201705,p201706,
p201707,p201708,p201709 INTO
(
    PARTITION p2017Q1 VALUES LESS THAN (201704),
    PARTITION p2017Q2 VALUES LESS THAN (201707),
    PARTITION p2017Q3 VALUES LESS THAN (201710)
);

3.分区应该注意的事项： （1）做分区时，要么不定义主键，要么把分区字段加入到主键中。 （2）分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL 三、分表 1.垂直分表 把原来有很多列的表拆分成多个表，原则是： （1）把常用、不常用的字段分开放 （2）把大字段独立存放在一个表中 2.水平分表 为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致。 例： （1）按时间结构 如果业务系统对时效性较高，比如新闻发布系统的文章表，可以把数据库设计成时间结构，按时间分有几种结构： （a）平板式 表类似：

1
2
3
article_201701
article_201702
article_201703

用年来分还是用月可自定，但用日期的话表就太多了，也没这必要。一般建议是按月分就可以。 这种分法，其难处在于，假设我要列20条数据，结果这三张表里都有2条，那么业务上很有可能要求读三次表。如果时间长了，有几十张表，而每张表是0条，那不就是要读完整个系统的表才行么?另外这个结构，要作分页是比较难实现的。 主键：在这个系统中，主键是13位带毫秒的时间戳，不要用自动编号，否则难以通过主键定位到表，也可以在查询时带上时间，但比较烦琐。 （b）归档式 表类似：

1
2
article_old
article_new

为了解决平板式的缺点，可以采用时间归档式设计，可以看到这个系统只有两张表。一张是旧文章表，一张是新文章表，新文章表放2个月的信息，每天定期把2 个月中的最早一天的文章归入旧表中。这样一方面可以解决性能问题，因为一般新闻发布系统读取的都是新的内容，旧的内容读取少;第二可以委婉地解决功能问 题，比如平板式所说的问题，在归档式中最多也只需要读2张表就完成了。 归档式的缺点在于旧表容量还是相对比较大，如果业务允许，可对旧表中的超旧内容进行再归档或直接清理掉。 （2）按版块结构 如果按照文章的所属版块进行拆表，比如新闻、体育版块拆表，一方面可以使每个表数据量分离，另一方面是各版块之间相互影响可降到最低。假如新闻版块的数据表损坏或需要维护，并不会影响到体育版块的正常工作，从而降低了风险。版块结构同时常用于bbs这样的系统。 板块结构也有几种分法： （a）对应式 对于版块数量不多，而且较为固定的形式，就直接对应就好。比如新闻版块，可以分出新闻的目录表，新闻的文章表等。

1
2
3
4
news_category
news_article
sports_category
sports_article

可看到每一个版块都对应着一组相同的表结构，好处就是一目了然。在功能上，因为版块之间还是有一些隔阂，所以需要联合查询的需求不多，开发上比时间结构的方式要轻松。 主键：依旧要考虑的，在这个系统中，主键是版块+时间戳，单纯的时间戳或自动编号也能用，查询时要记得带上版块用于定位表。 （b）冷热式 对应式的缺点是，如果版块数量很大而且不确定，那要分出的表数量就太多了。举个例子：百度贴吧，如果按一个词条一个表设计，那得有多少张表呢? 用这样的方式吧。

1
2
3
4
tieba_汽车
tieba_飞机
tieba_火箭
tieba_unite

这个表汽车、火箭表是属于热门表，定义为新建的版块放在unite表里面，待到其超过一万张主贴的时候才开对应表结构。因为在贴吧这种系统中，冷门版块 肯定比热门版块多得多，这些冷门版块通常只有几张帖子，为它们开表也太浪费了;同时热门版块数量和访问量等，又比冷门版块多得多，非常有特点。 unite表还可以扩展成哈希表，利用词条的md5编码，可以分成n张表，我算了一下，md5前一位可分36张表，两位即是1296张表，足够了。

1
2
tieba_unite_ab
tieba_unite_ac

（3）按哈希结构 哈希结构通常用于博客之类的基于用户的场合，在博客这样的系统里有几个特点，1是用户数量非常多，2是每个用户发的文章数量都较少，3是用户发文章不定 期，4是每个用户发得不多，但总量仍非常之大。基于这些特点，用以上所说的任何一种分表方式都不合适，一没有固定的时效不宜用时间拆，二用户很多，而且还 偏偏都是冷门，所以也不宜用版块(用户)拆。 哈希结构在上面有所提及，既然按每个用户不好直接拆，那就把一群用户归进一个表好了。

1
2
3
blog_aa
blog_ab
blog_ac

[…]

龙生   15 Dec 2020
View Details

MySQL优化

一、SQL语句优化 （1）使用limit对查询结果的记录进行限定 （2）避免select *，将需要查找的字段列出来 （3）使用连接（join）来代替子查询 （4）拆分大的delete或insert语句 二、选择合适的数据类型 （1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数 （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar （4）尽可能使用not null定义字段 （5）尽量少用text，非用不可最好分表 三、选择合适的索引列 （1）查询频繁的列，在where，group by，order by，on从句中出现的列 （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列 （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好 （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

1
mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

四、使用命令分析 （1）SHOW查看状态 1.显示状态信息

1
mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

session（默认）：取出当前窗口的执行 global：从mysql启动到现在 （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）

1
mysql> SHOW STATUS LIKE 'com_select';

（b）查看连接数(登录次数)

1
mysql> SHOW STATUS LIKE 'connections';

（c）数据库运行时间

1
mysql> SHOW STATUS LIKE 'uptime';

（d）查看慢查询次数

1
mysql> SHOW STATUS LIKE 'slow_queries';

（e）查看索引使用的情况：

1
mysql> SHOW STATUS LIKE 'handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next：这个值越高，说明查询低效。 2.显示系统变量

1
mysql> SHOW VARIABLES LIKE '%Variables_name%';

3.显示InnoDB存储引擎的状态

1
mysql> SHOW ENGINE INNODB STATUS;

（2）EXPLAIN分析查询

1
mysql> EXPLAIN SELECT column_name FROM table_name;

explain查询sql执行计划，各列含义： table：表名； type：连接的类型 -const：主键、索引； -eq_reg：主键、索引的范围查找； -ref：连接的查找（join） -range：索引的范围查找； -index：索引的扫描； -all：全表扫描； possible_keys：可能用到的索引； key：实际使用的索引； key_len：索引的长度，越短越好； ref：索引的哪一列被使用了，常数较好； rows：mysql认为必须检查的用来返回请求数据的行数； extra：using filesort、using temporary（常出现在使用order by时）时需要优化。 -Using filesort  额外排序。看到这个的时候，查询就需要优化了 -Using temporary 使用了临时表。看到这个的时候，也需要优化 （3）PROFILING分析SQL语句 1.开启profile。查看当前SQL执行时间

1
2
mysql> SET PROFILING=ON;
mysql> SHOW profiles;

2.查看所有用户的当前连接。包括执行状态、是否锁表等

1
mysql> SHOW processlist;

（4）PROCEDURE ANALYSE()取得建议 通过分析select查询结果对现有的表的每一列给出优化的建议

1
mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();

（5）OPTIMIZE TABLE回收闲置的数据库空间

1
mysql> OPTIMIZE TABLE table_name;

[…]

龙生   15 Dec 2020
View Details

MySQL索引类型

一、简介 MySQL目前主要有以下几种索引类型： 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句

1
2
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引 2.index和key为同义词，两者作用相同，用来指定创建索引 3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择 4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值 5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度 6.asc或desc指定升序或降序的索引值存储 三、索引类型 1.普通索引 是最基本的索引，它没有任何限制。它有以下几种创建方式： （1）直接创建索引

1
CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

1
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

1
2
3
4
5
6
7
8
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

  （4）删除索引

1
DROP INDEX index_name ON table

2.唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： （1）创建唯一索引

1
CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

1
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

1
2
3
4
5
6
7
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3.主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

1
2
3
4
5
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

1
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5.全文索引 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。 （1）创建表的适合添加全文索引

1
2
3
4
5
6
7
8
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

1
ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

1
CREATE FULLTEXT INDEX index_content ON article(content)

四、缺点 1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。 五、注意事项 使用索引时，有以下一些技巧和注意事项： 1.索引不会包含有null值的列 只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 2.使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 3.索引列排序 查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 4.like语句操作 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 5.不要在列上进行运算 这将导致索引失效而进行全表扫描，例如

1
SELECT * FROM table_name WHERE YEAR(column_name)&lt;2017;

6.不使用not in和<>操作   from:https://www.cnblogs.com/luyucheng/p/6289714.html

龙生   15 Dec 2020
View Details

使用URLScan隐藏IIS服务器Server头信息

百度搜索 URLScan V3.1，进行软件下载，根据操作系统版本，有32和64位的区分，如下图所示： 以管理员身份运行对应版本的软件，然后勾选“I accept the terms in the License Agreement”，然后点击“Install”，如下图所示： 直至安装完成，点击“Finish”，如下图所示： 我们在浏览器中打开Server服务器上部署的Web应用，然后通过fiddler软件进行查看Header头信息，可以看到Server:Microsoft-IIS/7.5，如下图所示： 此时我们打开URLScan默认安装路径C:\Windows\System32\inetsrv\urlscan，找到UrlScan.ini文件，如下图所示： 使用记事本工具打开UrlScan.ini文件，找到RemoveServerHeader=0配置节然后修改为RemoveServerHeader=1，最后保存文件，如下图所示： 此时重新启动下服务器上的IIS服务，打开命令提示符，输入iisreset即可，如下图所示： 此时我们刷新Web应用地址，重新使用fiddler进行Header信息观察，现在就看不到Server这个Header头配置信息了，如下图所示： from:https://jingyan.baidu.com/article/6d704a1379629a28db51ca37.html   urscanV3.1下载

龙生   14 Dec 2020
View Details
1 … 100 101 102 … 410

Copyright © 2025 Longsheng All rights reserved