- 博客(1)
- 资源 (6)
- 收藏
- 关注
RSS订阅原创 关于中文和分词，计算机通讯的思考
中文分词 为什么不用四角号码呢(方块字四个角加一个补码)？ 10604 30104 22770 80221 26000 67127 12013 42913 44214 30112 12900 21182 27106 77217 西塞山前白鹭飞，桃花流水鳜鱼肥. 要知道中文的四角号码之间是有天然的联系的,比如词\成语等，对中文分...
2019-05-21 09:40:01
217
完美解决SQL server 5173问题（一个或多个文件与数据库的主文件不匹配）
消息 5173，级别 16，状态 2，第 1 行
一个或多个文件与数据库的主文件不匹配。如果是尝试附加数据库，请使用正确的文件重试该操作。如果这是现有数据库，则文件可能已损坏，应该从备份进行还原。
如果出现这个错误，不用急，这是mdf和ldf不一致导致，ldf文件损坏了，按本尊方法新建ldf文件，就能完美解决 ！！！！
2022-07-04
gitBook 安装文件 及安装文档一条流
包含：(Git-2.11.1-64-bit.exe/node-v4.0.0-x64.msi/GitBook.Editor.Setup.exe/gitbook 发布文档.docx)
流程说明详见：https://blog.csdn.net/ocean42234111/article/details/89022836
2019-04-04
唯一非聚集索引变量传入时索引失效解决方案
场景一:
确认1.碰到了一个一个非常慢的SQL server语句，发现是变量查询时很慢
SQL语句:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'
SELECT [Mac2]
FROM SF_Cp_Detail
WHERE [Mac2] = @SN
确认2.查看索引是:唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
确认3.执行计划如下:
执行计划
SET STATISTICS IO ON ;
(0 行受影响)
Table 'SF_Cp_Detail'. Scan count 33, logical reads 1267942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
场景二:
为什么用不到索引IX_SF_CP_Detail_MAC2]呢?
尝试1:
把唯一非聚集索引改为非聚集索引, Done,用到索引了.
Drop index …….
CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
尝试2:
可是还需要验证唯一性怎么办呢?
试了半天未达到目标, 根据别人提示, 恢复唯一非聚集
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
然后
修改查询:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'
SELECT [Mac2]
FROM SF_Cp_Detail
WHERE [Mac2] = @SN option (recompile)
预计查询计划
看样子不行, 但是,看一下实际查询计划:
OK ,Done,可以了.
尝试3.
但是我不可能去每个程序加option (recompile)呀,
而且随着数据量的增大,每次重新编译索引, 本身就导致SQL语句变慢.
最终解决方案:
唯一非聚集索引留着, 再添加一个 非聚集索引,保留两个索引, 终于搞定了.
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
2015-07-21
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人