SQL索引详解(优化数据库)
SQL索引一步到位
SQL索引在数据库优化中占有一个非常大的比例，一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。
1.1 什么是索引？
SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间
下面举两个简单的例子：
图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引
字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.
看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
1.2 索引的存储机制
首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼
音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是
具有连续性的，a后面就是b，b后面就是c，聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。
原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库
被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列
整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了
建立索引的原则：
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8) 对于定义为text、image和bit的数据类型的列不要建立索引。
9) 对于经常存取的列避免建立索引
9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
1.4 如何创建索引
1.41 创建索引的语法：
CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name ON {table_name | view_name} [WITH [index_property [,....n]]
说明：
UNIQUE: 建立唯一索引。
CLUSTERED: 建立聚集索引。
NONCLUSTERED: 建立非聚集索引。
Index_property: 索引属性。
UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。
1.42 删除索引语法：
DROP INDEX table_name.index_name[,table_name.index_name]
说明：table_name: 索引所在的表名称。
index_name : 要删除的索引名称。
1.43 显示索引信息：
使用系统存储过程：sp_helpindex 查看指定表的索引信息。
执行代码如下：
Exec sp_helpindex book1;
1.5 索引使用次数、索引效率、占用CPU检测、索引缺失
当我们明白了什么是索引，什么时间创建索引以后，我们就会想，我们创建的索引到底效率执行的怎么样？好不好？我们创建的对不对？
首先我们来认识一下DMV，DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时，微软介绍了许多被称为dmvs 的系统视图，让您可以探测SQL Server 的健康状况，诊断问题，或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的，并且在SQL Server每次启动的时候，统计数据将会被重置。当你删除或者重新创建其组件时，某些dmv的统计数据也可以被重置，例如存储过程和表，而其它的dmv信息在运行dbcc命令时也可以被重置。
当你使用一个dmv时，你需要紧记SQL Server收集这些信息有多长时间了，以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间，你可能不想去使用一些dmv统计数据，因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面，SQL Server只能维持一定量的信息，有些信息在进行SQL Server性能管理活动的时候可能丢失，所以如果SQL Server已经运行了相当长的一段时间，一些统计数据就有可能已被覆盖。
因此，任何时候你使用dmv，当你查看从SQL Server 2005的dmvs返回的相关资料时，请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的，你才能变更数据库或者应用程序代码。
下面就看一下dmv到底能带给我们那些好的功能呢？
1.51 ：索引使用次数
我们下看一下下面两种查询方式返回的结果（这两种查询的查询用途一致）
①----
declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.nam e, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc
返回查询结果
②：使用多的索引排在前面
SELECT https://www.wendangku.net/doc/3e8613917.html, ,
https://www.wendangku.net/doc/3e8613917.html, ,
https://www.wendangku.net/doc/3e8613917.html, ,
user_seeks ,
user_scans ,
user_lookups ,
partition_stats.row_count
FROM sys.dm_db_index_usage_stats stats
LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id
LEFT JOIN sys.databases databases ON databases.database_id = stats.d atabase_id
LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id
AND stats.object_id = indexes.object_id
LEFT JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id
AND indexes.index_id = partition_ stats.index_id
WHERE 1 = 1
--AND databases.database_id = 7
AND https://www.wendangku.net/doc/3e8613917.html, IS NOT NULL
AND https://www.wendangku.net/doc/3e8613917.html, IS NOT NULL
AND user_scans>0
ORDER BY user_scans DESC ,
stats.object_id ,
indexes.index_id
返回查询结果
user_seeks : 通过用户查询执行的搜索次数。
个人理解：此统计索引搜索的次数
user_scans: 通过用户查询执行的扫描次数。
个人理解：此统计表扫描的次数，无索引配合
user_lookups: 通过用户查询执行的查找次数。
个人理解：用户通过索引查找，在使用RID或聚集索引查找数据的次数，对于堆表或聚集表数据而言和索引配合使用次数
user_updates: 通过用户查询执行的更新次数。
个人理解：索引或表的更新次数
我们可以清晰的看到，那些索引用的多，那些索引没用过，大家可以根据查询出来的东西去分析自己的数据索引和表
1.52 ：索引提高了多少性能
新建了索引到底增加了多少数据的效率呢？到底提高了多少性能呢？运行如下SQL可以返回连接缺失索引动态管理视图，发现最有用的索引和创建索引的方法：
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
'CREATE INDEX ix_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +
' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' +
ISNULL(' INCLUDE (' + included_columns + ')', '')
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle =
c.group_handle
WHERE avg_user_impact > = 40
返回结果
虽然用户能够修改性能提高的百分比，但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了
1.53 ：最占用CPU、执行时间最长命令
这个和索引无关，但是还是在这里提出来，因为他也属于DMV带给我们的功能吗，他可以让你轻松查询出，那些sql语句占用你的cpu最高
SELECT TOP 100 execution_count,
total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [object name 1],
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;
返回结果：
执行时间最长的命令
SELECT TOP 10 COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int))+'*',
'Resource') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC ;
看到了吗？直接可以定位到你的sql语句，优化去吧。还等什么呢？
1.54：缺失索引
缺失索引就是帮你查找你的数据库缺少什么索引，告诉你那些字段需要加上索引，这样你就可以根据提示添加你数据库缺少的索引了
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
查询结果如下：
1.6 适当创建索引覆盖
假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)
上创建了一个索引，假设ProductID列是一个高选中性列，那么任何在where子句中使用索引列(ProductID)的select查询都会更快，如果在外键上没有创建索引，将会发生全部扫描，但还有办法可以进一步提升查询性能。
假设Sales表有10,000行记录，下面的SQL语句选中400行(总行数的4%)：
SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112
我们来看看这条SQL语句在SQL执行引擎中是如何执行的：
1)Sales表在ProductID列上有一个非聚集索引，因此它查找非聚集索引树找出ProductID=112的记录;
2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值，即SalesID);
3)针对每一个主键(这里是400)，SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;
SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。
在上面的步骤中，对ProductID = 112的每个主键记录(这里是400)，SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate，SalesPersonID)。
如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不会执行上面的第3和4步，直接从非聚集索引树查找ProductID列速度还会快一些，直接从索引页读取这三列的数值。
幸运的是，有一种方法实现了这个功能，它被称为“覆盖索引”，在表列上创建覆盖索引时，需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子：
CREATE INDEX NCLIX_Sales_ProductID--Index name
ON dbo.Sales(ProductID)--Column on which index is to be created
INCLUDE(SalesDate, SalesPersonID)--Additional column values to include
应该在那些select查询中常使用到的列上创建覆盖索引，但覆盖索引中包括过多的列也不行，因为覆盖索引列的值是存储在内存中的，这样会消耗过多内存，引发性能下降。
1.7 索引碎片
在数据库性能优化一：数据库自身优化一文中已经讲到了这个问题，再次就不做过多的重复地址：https://www.wendangku.net/doc/3e8613917.html,/AK2012/archive/2012/12/25/2012-1228.html
1.8 索引实战（摘抄）
之所以这章摘抄，是因为下面这个文章已经写的太好了，估计我写出来也无法比这个好了，所以就摘抄了
人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。
笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。
在对它们进行适当的优化后，其运行速度有了明显地提高！
下面我将从这三个方面分别进行总结：
为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均表示为（< 1秒）。----
测试环境: 主机：HP LH II---- 主频：330MHZ---- 内存：128兆----
操作系统：Operserver5.0.4----
数据库：Sybase11.0.3
一、不合理的索引设计----
例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：
---- 1.在date上建有一非个群集索引
select count(*) from record where date >'19991201' and date < '19991214'a nd amount >2000 (25秒)
select date ,sum(amount) from record group by date(55秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH') ( 27秒)
---- 分析：----
date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。
---- 2.在date上的一个群集索引
select count(*) from record where date >'19991201' and date < '19991214' a nd amount >2000 （14秒）
select date,sum(amount) from record group by date（28秒）
select count(*) from record where date >'19990901' and place in ('BJ','SH')（14秒）
---- 分析：---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。
---- 3.在place，date，amount上的组合索引
select count(*) from record where date >'19991201' and date < '19991214' a nd amount >2000 （26秒）
select date,sum(amount) from record group by date（27秒）
select count(*) from record where date >'19990901' and place in ('BJ, 'SH')（< 1秒）
---- 分析：---- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL 没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。
---- 4.在date，place，amount上的组合索引
select count(*) from record where date >'19991201' and date < '19991214' a nd amount >2000(< 1秒)
select date,sum(amount) from record group by date（11秒）
select count(*) from record where date >'19990901' and place in ('BJ','SH')（< 1秒）
---- 分析：---- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。
---- 5.总结：----
缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。
一般来说：
①.有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；
②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
二、不充份的连接条件：
例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：select sum(a.amount) from account a,card b where a.card_no = b.card_no （20秒）
select sum(a.amount) from account a,card b where a.card_no = b.card_no an d a.account_no=b.account_no（< 1秒）
---- 分析：---- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：
外层表account上的22541页+（外层表account的191122行*内层表card上对应外层表第一行所要查找的3页）=595907次I/O
在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account 上的索引，其I/O次数可由以下公式估算为：外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）= 33528次I/O
可见，只有充份的连接条件，真正的最佳方案才会被执行。
总结：
1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。
三、不可优化的where子句
1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
select * from record wheresubstring(card_no,1,4)='5378'(13秒)
select * from record whereamount/30< 1000（11秒）
select * from record whereconvert(char(10),date,112)='19991201'（10秒）
分析：
where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；
如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select * from record where card_no like'5378%'（< 1秒）
select * from record where amount< 1000*30（< 1秒）
select * from record where date= '1999/12/01'（< 1秒）
你会发现SQL明显快起来！
2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：
select count(*) from stuff where id_no in('0','1')（23秒）
分析：---- where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为
id_no ='0' or id_no='1'来执行。
我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。
实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：
select count(*) from stuff where id_no='0'select count(*) from stuff where id _no='1'
得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。
或者，用更好的方法，写一个简单的存储过程：
create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=c
ount(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert( char(10),@c)print @d
直接算出结果，执行时间同上面一样快！
---- 总结：---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。
1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
3.要善于使用存储过程，它使SQL变得更加灵活和高效。
从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
mysql数据库索引优化
我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的[url=javascript:;]技术[/url]，但是最有效的莫过于恰当地使用索引了。在MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。 本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。） 索引对单个表查询的影响 索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000 行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。 例如对下面这样的一个student表： mysql>SELECT * FROM student +------+---------+---------+---------+---------+ | id | name | english | chinese | history | +------+---------+---------+---------+---------+ | 12 | Tom | 66 | 93 | 67 | | 56 | Paul | 78 | 52 | 75 | | 10 | Marry | 54 | 89 | 74 | | 4 | Tina | 99 | 83 | 48 | | 39 | William | 43 | 96 | 52 | | 74 | Stone | 42 | 40 | 61 | | 86 | Smith | 49 | 85 | 78 | | 37 | Black | 49 | 63 | 47 | | 89 | White | 94 | 31 | 52 | +------+---------+---------+---------+---------+ 这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生： mysql>SELECT name,english FROM student WHERE english<60; +---------+---------+ | name | english | +---------+---------+ | Marry | 54 | | William | 43 | | Stone | 42 | | Smith | 49 |
如何优化数据库,提高查询效率
龙源期刊网 https://www.wendangku.net/doc/3e8613917.html, 如何优化数据库，提高查询效率 作者：代鸿彬 来源：《学习与科普》2019年第10期 摘要：随着信息时代的到来，生活和工作当中已经无法避免的需要和计算机打交道，和 计算机打交道的同时就必须要用到数据库。数据库系统是计算机当中的一项重要系统，储存在用户的关键信息，不仅对个人影响很大，同时对企事业单位也有着重要影响。 关键词：信息时代；数据库；索引 数据库是信息的载体也是数据的最佳表现形式，它的共享性导致了数据会被大量的搜索查询，为了提高查询的效率，就不得不对数据库进行优化。 一、利用索引进行优化。 索引是数据库的重要组成部分，也是使用者根据需要进行查询最直接的方法，优化索引可以提高查询的效率。当前的数据库当中大部分还是使用国际商业机器公司以前的索引顺序存取方法，对于用户来说肯定会选择方便、快捷的索引方式，怎么方便怎么来。在建立索引的时候针对不同的内容，需要建立不同的连接方式，但是随着用户的增多，查询内容和方向的多元化，这就造成了在实际工作当中经常会有使用频率很少的索引出现，甚至也会出现没有查询所需的索引，这种情况可以通过查询优化器进行自动生成的索引进行查询。对于使用频率较为频繁的列，需要对其进行排序或者分组的列上建立索引时，要优化索引提高效率，对于使用频率很少的列可以不建立索引。 二、简化排序进行优化。 对于部分企事业单位需要排序的内容很多时，就要使用大型数据表来满足查询需求，但是大型数据表涉及的内容很多，为了避免出现重复排序的现象需要对数据表进行简化。在大型数据表当中有一部分的内容可以自动进行排序的次序输出，这时就可以直接利用查询优化器进行优化，将复杂的排序简单化，从而提高索引查询效率。需要排序的列对索引优化影响较大，就像语言当中的ORDER BY 或者GROUP BY句子当中的列次序和索引当中的列次序基本是不同的，但是排序的列可通过表的不同形式表现出来。通过简化排序避免了重复的排序，并且将数据库进行了合理的合并。如果不进行简化排序，就需要将排序的范围进行缩小简化，从而提高查询使用的效率。 三、大型表行数据库存取的合理消除。 数据库系统的存储量是有上限的，所有的索引内容都占有数据库空间，尤其是大型数据表占有的空间更大，将会造成索引时间变长。但是大型表行数据有些内容是不必要的，在进行索引查詢时，数据表当中的存取顺序对查询的效率有直接的影响。例如需要采用存取策略时，通
数据库及SQL代码优化方案
1.1、数据库及SQL代码优化方案 （1）每周检查统计信息是否及时更新。 （2）每周检查各索引是否有效。 （3）每周检查分区是否正确。 （4）每周检查执行计划是否正确。 （5）每天检查RAC和ASM是否正常运行。 （6）每天检查相关日志是否正常备份。 （7）每天检查相关文件系统和表空间的占用率是否在国家税务总局规定的阀值以下。 （8）在每月申报高峰等业务繁忙期采样并找出消耗I/O资源和CPU资源较多的SQL语句。 （9）分析上述SQL语句，与软件服务商充分沟通后，提出优化建议。 （10）在每月申报高峰期每隔15分钟检查一次数据库连接数，发现异常及时处理。 1.1.1、系统数据库索引、表分区和对象优化方案 数据库对象的优化主要包括：表、索引和sequence等对象，通过优化对象参数、调整对象属性（例如分区表、分区索引、反转索引等等）等方法来实现对数据库对象的优化改造。 1.1.1.1表和索引并行参数优化 数据库的表和索引的并行参数值的设置对相关的sql语句的执行计划会造成影响，表和索引的degree值大于1，执行计划就偏向于使用全表和全索引扫描，另外如果并行参数值过大，短时间内也会对主机和数据库的资源造成很大的压力，因此在oltp的数据库下建议将表和索引的degree值设为1。 1.1.1.2热点大表的分区改造 对访问量很大、表的记录数很多、存在热块争用的表，可以考虑对表和索引进行适当的分区改造，分散访问压力，提高数据访问的性能。 对以下表的记录数超过1000万并且记录数持续增长的大表，建议进行分区
改造（地区+时间）： 1.1.1.3分区索引的清理 对最近30天数据库分区索引访问情况进行统计，对访问次数为0的分区索引和应用部门进行确认，若确认为多余的索引，建议进行删除清理。 1.1.1.4Sequence序列优化 加大sequence 的 cache，并使用noorder选项。在RAC中经常会遇到SQ 锁等待，这是因为在RAC环境下，sequence也成为全局性的了，不同节点要生成序列号，就会产生对sequence资源的争用。而目前大多数系统中，sequence 大多数被作为主键发生器来使用，使用的频率十分高，在RAC环境中，需要设置较大的 sequence cache，否则会造成较为严重的争用，从而影响业务。 1.1.2、SQL硬解析优化方案 1.1. 2.1相关知识点介绍 1.1. 2.1.1Oracle的硬解析和软解析 Oracle对sql的处理过程：当发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程： 1、语法检查(syntax check) 检查此sql的拼写是否语法。 2、语义检查(semantic check) 诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。 3、对sql语句进行解析(prase) 利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。 4、执行sql，返回结果(execute and return) 其中，软、硬解析就发生在第三个过程里。 Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache
SQLSERVER索引及优化详解
SqlServer索引及优化详解 （一）深入浅出理解索引结构 实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。 我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。 （二）何时使用聚集索引或非聚集索引 下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
MySQL优化自学手册
/* * ------------------------------------------------------------------- * |-标题：MySQL优化自学手册 * |-整理: 杨白玉 * |-时间: 2015年9月25日 * ------------------------------------------------------------------- */ mysql优化 前提：数据库性能的优劣直接影响到程序的性能，所以数据库的设计与参数配置至关重要。 数据库优化的方式： 1、数据库设计 2、sql语句的优化 3、数据库参数的配置（扩展数据库的缓存或者数据库的空间） 4、恰当的硬件资源（钱的问题，有钱就能满足）
第一章数据库的设计 一、数据库的设计： 数据库的设计指的就是表的设计。设计要符合三范式（规范的模式），有时我们也需要适当的逆范式； 二、什么是三范式？ 第一范式：1NF是对属性（可理解为字段）的原子性约束，要求属性具有原子性，不可再分。第二范式：2NF是对记录的唯一性约束，要求记录有唯一的标识，即实体的唯一性； 第三范式：3NF是对字段冗余的约束，即任何字段不能由其他字段派生出来，要求字段没有冗余，这是可以做到的。 然而，没有冗余的数据库未必是好的数据库，有时候为了提高运行的效率，我们也会使用适当的逆范式，方法就是：增加字段。 一般来说，1NF在关系型数据库中是自动满足的； 2NF通常通过主键自增的唯一性来约束。而且，记录本身也很少会完全一样； 3NF主要是在主从表中，不会出现相同的字段与字段值；
第二章 SQL语句的优化 一、SQL语句优化的步骤： 1、通过show status 命令了解各种sql的执行频率； 2、定位执行效率较低的SQL语句，主要集中在查询语句 3、通过explain分析低效率的sql语句的执行情况 4、确定问题并采取相应的优化措施 二、sql语句有几类？ ddl(数据定义语句)[create alter drop] dml(数据操作语句)[insert delete update] select dtl(数据事物语句)[commit rollback savepoint] dcl(数据控制语句)[grant revoke] show status命令 该命令可以显示mysql数据库当前的状态，我们主要重点关注“Com”开头的指令。 1、显示数据库开启本次会话后到目前的信息： show status like “Com%”; <=> show session status like “Com%”; 2、显示数据库从启动到目前的信息： Show global status like “Com%”;
大数据库优化(SQLServer)
SQL SERVER性能优化综述 近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在 网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现，有很多似是而非或 者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以 前的经验和测试结果进行总结了。 我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。 一、分析阶段 一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能 性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能 有各种需求的量化的指标。 另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。 二、设计阶段 设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能 调优的过程—数据库设计。 在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效 率的代码，为整个系统的性能打下良好的基础。 以下是性能要求设计阶段需要注意的： 1、数据库逻辑设计的规范化 数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式： 第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。 第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组 成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。 第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。 更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三
sql优化方案讲解
Sql优化方案 一．数据库优化技术 1.索引（强烈建议使用） 1.1优点 创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 1.2 缺点 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 1.3 使用准则 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在这些列上创建索引。 第一，在经常需要搜索的列上，可以加快搜索的速度；
第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 1.4 总结 1）索引提高了数据库的检索性能，但一定程度上牺牲了修改性能。因此适用于“多查询少修改”（insert，update，delete）的表。 2）对此类表中的外键，需要分组，排序或作为检索条件的字段建立索引 3）对此类表中查询使用少，字段取值少，字段数据量大的不应创建索引
MYSQL索引和优化详细说明教程
MYSQL索引和优化详细说明教程 2008-05-16 15:59 MYSQL索引和优化 一、什么是索引？ 索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。 假设我们创建了一个名为people的表： 然后，我们完全随机把1000个不同name值插入到people表。 可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name 列的索引，MySQL将在索引中排序name列： 对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL 命令为“SELECT peopleid FROM people WHERE name=\’Mike\’;”），MySQL 能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。 二、索引的类型 MySQL提供多种索引类型供选择： 普通索引 这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通 过以下几种方式创建：
SQL数据库优化方法
SQL数据库优化方法
目录 1 系统优化介绍 (1) 2 外围优化 (1) 3 SQL优化 (2) 3.1 注释使用 (2) 3.2 对于事务的使用 (2) 3.3 对于与数据库的交互 (2) 3.4 对于SELECT *这样的语句， (2) 3.5 尽量避免使用游标 (2) 3.6 尽量使用count(1) (3) 3.7 IN和EXISTS (3) 3.8 注意表之间连接的数据类型 (3) 3.9 尽量少用视图 (3) 3.10 没有必要时不要用DISTINCT和ORDER BY (3) 3.11 避免相关子查询 (3) 3.12 代码离数据越近越好 (3) 3.13 插入大的二进制值到Image列 (4) 3.14 Between在某些时候比IN 速度更快 (4) 3.15 对Where条件字段修饰字段移到右边 (4) 3.16 在海量查询时尽量少用格式转换。 (4) 3.17 IS NULL 与IS NOT NULL (4) 3.18 建立临时表， (4) 3.19 Where中索引的使用 (5) 3.20 外键关联的列应该建立索引 (5) 3.21 注意UNion和`UNion all 的区别 (5) 3.22 Insert (5) 3.23 order by语句 (5) 3.24 技巧用例 (6) 3.24.1 Sql语句执行时间测试 (6)
1系统优化介绍 在我们的项目中，由于客户的使用时间较长或客户的数据量大，造成系统运行速度慢，系统性能下降就容易造成数据库阻塞。这是个非常痛苦的事情，用户的查询、新增、修改等需要花很多时间，甚至造成系统死机的现象。速度慢的原因主要是来自于资源不足。 数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来最多只占数据库系统性能提升的40%左右(我将此暂时称之为外围优化)；其余大部分系统性能提升来自对应用程序的优化，对于应用程序的优化可以分为对源代码的优化及数据库SQL语句的优化。在本文档只介绍外围优化及SQL语句的优化，对于源代码的优化需要相关方面的专家，形成统一的规范。 一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。规范的代码和高性能的语句，功在平时，利在千秋。 2外围优化 1、将操作系统与SQL数据库的补丁打到最高版本，WIN2003最高补丁是SP4， SQL SERVER2000最高补丁是SP4(版本号：2039)。 2、在服务器上不要安装与VA程序任何无相关的软件，甚至一些与VA运行 无关的服务都可以停掉。一般只安装SQL数据库、VA服务端服务及杀毒 软件。 3、杀毒软件避免对大文件进行扫描，特别是数据库（MDF和LDF）文件，一 定要从杀毒软件的范围内排除掉。 4、在进行服务器分区时，分区不要太多，两三个分区就可以了。分区最好 都使用NTFS格式。
mysql优化笔记
◆Mysql数据库的优化技术<大型网站优化技术> 对mysql优化时一个综合性的技术，主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/delete/add]分离 e: 存储过程[模块化编程，可以提高速度] 数据库的三层结构： f: 对mysql配置优化[配置最大并发数my.ini, 调整缓存大小] g: mysql服务器硬件升级 h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) ◆什么样的表才是符合3NF (范式) 表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF 1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF ?数据库的分类 关系型数据库: mysql/oracle/db2/informix/sysbase/sql server 非关系型数据库: (特点: 面向对象或者集合) NoSql数据库: MongoDB(特点是面向文档) 2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现id primary key ; 3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF：显示推导处理
SQL索引详解(优化数据库)
SQL索引一步到位 SQL索引在数据库优化中占有一个非常大的比例，一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。 1.1 什么是索引？ SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 下面举两个简单的例子： 图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引 字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引. 看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。 1.2 索引的存储机制 首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼 音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是 具有连续性的，a后面就是b，b后面就是c，聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。 原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库 被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列 整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了
浅谈MySQL索引分析和优化
MySQL索引分析和优化列：
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！ 那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
下面我们就来看看这个EXPLAIN分析结果的含义。 table：这是表的名字。 type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明： “对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一种好的连接类型。” 在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。 如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。 possible_keys： 可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。 Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。 key_len：索引中被使用部分的长度，以字节计。在本例中，key_len是102，其中firstname 占50字节，lastname占50字节，age占2字节。如果MySQL只使用索引中的firstname部分，则key_len将是50。 ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。 rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。 Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL 只是提醒我们它将用WHERE子句限制搜索结果集。 索引的缺点 到目前为止，我们讨论的都是索引的优点。事实上，索引也是有缺点的。 首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。 第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。 【结束语】在大型数据库中，索引是提高速度的一个关键因素。不管表的结构是多么简单，一次500000行的表扫描操作无论如何不会快。如果你的网站上也有这种大规模的表，那么你确实应该花些时间去分析可以采用哪些索引，并考虑是否可以改写查询以优化应用。要了解更多信息，请参见MySQL manual。另外注意，本文假定你所使用的MySQL是3.23版，部分查询不能在3.22版MySQL上执行。
oracle11g基于SQL的优化之索引优化篇
Oracle11g 基于SQL语句性能优化 通过索引对SQL进行优化 主讲人：马飞 所在部门：运维部
一、概述 本文所介绍的索引案例是在使用的是Oracle11g 11.2.0.4 数据库运行的。索引是使用最为普遍的一种优化SQL的方法，不同索引均有各自的优缺点。实际优化中需要综合考虑各种环境因素对运行慢的SQL进行优化。常见环境因素有：数据库表及索引的统计信息、列的柱状图，优化器的模式，表上是否有触发器，表上是否创建了物化视图日志，SQL语句是否使用提示符，当前会话的等待事件等。 Oracle数据库中索引可分为B-TREE索引、BitMap索引、全文索引三大类。按索引列的数量不同可分为，单列索引，多列索引。按列值是否唯一可分为唯一索引和非唯一性索引。 二、B-TREE索引 B-TREE索引常常用在OLTP数据库中，为了提高查询性,但同时一个表中索引数据多时会影响DML语句的性能，所以需要全面考虑增加索引后利弊。 2.1索引分类 主键索引、唯一键索引、非唯一键索引、多列组合索引。当表在创建主键时系统会自动为主键列或列的组合上创建唯一索引，主键索引性能最好。其它索引性能好坏取决于单列或多列的数据选择性，如果索引访问的数据小，性能相对较高，因为访问索引和表的块较少因而性能好。 2.2扫描方式 索引唯一扫描、索引范围扫描，全索引扫描，快速全索引扫描，索引跳跃扫描。 2.3上机实践 2.3.1 索引唯一扫描例子：
unique.txt 注意：由于唯一索引的列中可为空值。如果查询条件中有如下写法，则无法走索引扫描。因为b-tree索引中不存储空值。 (1)select * from tab where col is null (2)select * from tab where col is not null (3)select count(0) from tab; 其中(3)中的语句是否走索引取决于唯一索引的列上是否为非空，如果是非空，则会走“INDEX FAST FULL SCAN”快速索引扫描（采用并行索引扫描方式进行取读索引块，效率非常高）。 2.3.2 索引范围扫描例子 在非唯一性索引上的扫描通常都采用索引范围的扫描方式进行。 scan.txt scan2.txt 2.3.3 全索引扫描例子 全索引扫描指的是查询语句的所有列均在索引列中，同时需要访问全表的数据时使用。 indexfull.txt 2.3.4 快速全索引扫描例子 fast_fullscan.txt 2.3.5 索引跳跃扫描例子 skip.txt 2.4索引利弊 优点：当访问表中少量数据时可以提高查询的性能。
MYSQL-innodb性能优化学习总结
MYSQL-innodb性能优化学习总结 BSS测试部：newhackerman
数据库参数 MYSQL数据库的参数配置一般在my.ini配置文件中修改/添加（部分参数也可以用set global 参数名=值做临时调整，重启后失效），配置完后需要重启数据库才生效。 参数1：innodb_buffer_pool_size = Gb/MB 说明：此参数类似于oracle的SGA配置，当主机做为mysql数据库服务器时，一般配置为整机内存的60%~80%。 参数2：innodb_buffer_pool_instances=N 说明：内存缓冲池实例数，将innodb_buffer_pool_size配置的内存分割成N份，此参数当配置内存大小于1G时才生效，当数据库有多个会话进行数据库操作时，用于并行在多个内存块中处理任务，一般配置值《=服务器CPU的个数。 参数3：max_connections = 2000 说明：最大连接数，当数据库面对高并发时，这个值需要调节为一个合理的值，才满足业务的并发要求，避免数据库拒绝连接。 参数4：max_user_connections=1000 说明：设置单个用户的连接数。 参数5：innodb_log_buffer_size =32M 说明：日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据日志就行了，mysql 默认1秒写一轮询写一次日志到磁盘。 参数6：innodb_flush_log_at_trx_commit 说明：（这个配置很关键）一般的实时业务交易配置为2，取值0,1,2 0:数据操作时，直接写内存，并不同时写入磁盘； 2:数据操作时，直接写内存，并不同时写入磁盘； 1:就每个事务提交就会要刷新到磁盘后才算提交完成,这种情况是保证了事务的一致性,但性能会有很大的影响。 0与2的区别： 0：当mysql挂了之后，可能会损失前一秒的事务信息 2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失。 参数7：innodb_read_io_threads = 16 说明：数据库读操作时的线程数，用于并发。 参数8：innodb_write_io_threads = 16 说明：数据库写操作时的线程数，用于并发。
mysql优化测试
1、清阐述索引的原理； 索引的本质是帮助mysql高效获取数据的数据结构,提取句子主干,索引是一种 数据结构,为了使查询的性能更好,我们会使用多种算法进行优化,索引主要是基于二叉树结构,而这种数据二叉树结构也正是索引 2、请阐述索引的分类和各种索引的使用方式； 索引类型有:1)唯一索引;2)主键索引;3)聚集索引 唯一索引是不允许其中任何两行具有相同的索引值的索引 主键索引是数据库经常有一列或列组合,其唯一标识表的每一行,该列称为表的主键,在数据库关系图中,为表定义主键将自动创建主键索引聚集索引:在聚集索引中,其中行的屋里顺序于键值的逻辑顺序相同,一个表只能包含一个聚集索引 3、请聊聊复合索引 两个或多个列上的索引被称作为复合索引,利用索引中的附加列可以缩小搜索范围,范时勇一个具有两列的索引,复合索引的结构也于电话本类似,任命由姓和名组成,电话簿首先根据姓氏进行排序,然后根据名字对相同姓氏的人进行排序,所以复合索引的创建,应该考虑列的顺序,对索引中的所有列执行所有或进队前几列做艘多复合索引很有效,而对于后面的任意搜索执行无效 4、聊聊MYSQL主从的原理？ Mysql支持单向异步的赋值,而在赋值过程中一个主服务器,而一个或多个其它服务器充当从服务器,mysql赋值基于主服务器在二进制日志中跟踪所有对数据库的更改,因此要进行主从同步,必须先在祝福器上启用二进制日志,每个从服务器接收主服务器已经记录到二进制日志的保存的更新,当一个从服务器连接主服务器的时候,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置5、MYSQL主从同步会不会有同步引起的延迟？怎么处理的？ Mysql主从同步会出现同步引起的延迟,,而针对于不同的情况处理的方法也是不同的例如:实例滚个配置过小而导致的延迟,这种延迟是由于io线程和sql 线程消耗掉了所有的io资源,所以当制度节点配置不够时,则出现延迟,解决方案是,升级制度实例规格,防止由于只读实例的规格较小导致的数据延迟,还有一个就是主库执行大事务时导致的延迟,主库执行了一条insert *** select** 非常大的插入操作,该操作产生了近几百亿的binlog日志文件传输到只读节点,进而导致只读节点出现应用binlog延迟
基于索引的SQL语句优化
基于索引的SQL语句优化 一、尽量避免非操作符的使用 通常情况下，为了对指定列建立特定的条件，需要在WHERE子句中使用诸如NOT、!=、<>、!<、!>等操作符，在索引列上使用这些非操作符，DBMS是不使用索引的，可以将查询语句转换为可以使用索引的查询。例： SELECT * FROM ORDERS WHERE ORDERDATE<>1997－l2 转化为：SELECT * FROM ORDERS WHERE ORDERDATE
mysql索引优化面试题
曾经偷偷的面试了两个单位，都提到了Mysql的优化问题，所以以后要多多学习数据库的优化知识了。建设数据库的优化大概主要就是索引的优化了吧，因为我们不可能修改数据结构的情况下，提高数据库的查询效率似乎也只能用索引了。当然这也是建立在你sql语句写的比较科学的情况下，如果你的sql语句本身就写的比较垃圾，神仙也救不了你！ 下边是在网上找到的一些资料，保留下来备用吧 1，创建索引 对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。 2，复合索引 比如有一条语句是这样的：select * from users where area=’beijing’ and age=22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。 3，索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。 4，使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。5，排序的索引问题 mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 6，like语句操作 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 7，不要在列上进行运算 select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01’; 8，不使用NOT IN和<>操作
- 数据库-SQL优化
- 整理的数据库查询(SQL)效率分析、优化、注意事项
- SQL Server 2000数据库优化方案参考
- SQL Server数据库查询速度慢原因及优化方法
- 数据库实验四数据库设计与查询优化
- 优化SQL查询：如何写出高性能SQL语句
- 数据库查询性能优化
- SQL数据库优化方法
- SQLServer数据查询的优化方法
- 高级SQL优化(一)
- 教你优化SQL Server数据库 加快查询速度
- 数据库优化方面表设计sql优化
- SQL优化的几个方面
- 大数据库优化(SQLServer)
- 数据库优化查询计划的方法
- 数据库性能优化之SQL语句优化
- SQL Server数据库查询速度慢原因及优化方法
- MySQL数据库性能(SQL)优化方案
- 数据库优化方案
- 数据库及SQL代码优化方案