代码学堂(uxuew.cn)提供付费解决PHP WEB开发等技术服务,如果需要请 点击加我QQ:1662935793.
>数据库 > MySQL教程 > mysql索引优化详解

mysql索引优化详解

MySQL教程 围观4187次 更新日期:2017-06-28 22:23:46 留下足迹

索引是数据库调优重要的一方面,索引占用大量的表空间,所以索引必须要定期来维护。就目前的数据库而言,索引大概可以分为两个类型,以oracle、db2,msql为代表的行索引,而以sysbase为代表的列索引(位索引),下面就以行索引和大家做一详细的探讨。

1、索引占用大量的空间:

----------建立测试表
if object_id('ppp') is null
drop table ppp
go
create table ppp (a int , b xml)
insert into ppp
select 1,'dsfsd'

-------------查看表空间
exec  sp_spaceused  ppp
/*
ppp 1           16 KB 8 KB 8 KB 0 KB
 */
-------建立非聚凑索引
create index index_name on ppp(a)

-------------查看表空间
exec  sp_spaceused  ppp
/*
ppp 1           32 KB 8 KB 24 KB 0 KB
*/

从上边的例子看出 索引占用大量表空间

2、建立主键的时候,数据库引擎会自动建立聚凑索引,这对索引是个浪费,因为很少有人把主键当搜索条件。

3、索引的填充因子是索引页面的填充率的概念,如果填充小子设置的大,就容易插入,这样的索引页面的数据比较松散,查询比较困难,如果填充因子小,查询容易,插入就困难,一般我建议用40-50就可以。但是sql 2005在新建索引的时候,索引的填充因子默认为0

4、要定期对索引进行分析,下边以例子来说明:

---查询索引的名字
sp_helpindex join_product_ty

---分析索引
dbcc  showcontig (join_product_ty,PK_join_product_ty)
/*

DBCC SHOWCONTIG 正在扫描 'join_product_ty' 表...
表: 'join_product_ty'(1030176803);索引 ID: 1,数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 1475
- 扫描扩展盘区数...............................: 188
- 扩展盘区开关数...............................: 188
- 每个扩展盘区上的平均页数.....................: 7.8
- 扫描密度[最佳值:实际值]....................: 97.88%[185:189]
- 逻辑扫描碎片.................................: 0.07%
- 扩展盘区扫描碎片.............................: 2.13%
- 每页上的平均可用字节数.......................: 4324.2
- 平均页密度(完整)...........................: 46.58%
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

*/

参数的作用,请参考msdn。

5、要定期对索引进行重建

DECLARE @database_name sysname
DECLARE @syntax sysname

DECLARE CUR_database CURSOR FOR
 SELECT name FROM sysobjects where xtype = 'u'
OPEN CUR_database

FETCH NEXT FROM CUR_database INTO @database_name
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @syntax = 'DBCC DBREINDEX (, '''',0)'
EXEC (@syntax)

PRINT @database_name + '重建索引完成'
FETCH NEXT FROM CUR_database INTO @database_name
END

--release memory
CLOSE CUR_database
DEALLOCATE CUR_database

GO
DBCC CHECKDB
GO

转载请注明:代码学堂>数据库 > MySQL教程 > mysql索引优化详解

喜欢 (411) or 分享 (88)