精华内容
下载资源
问答
  • SQL中索引的建立和删除数据字典

    多人点赞 热门讨论 2020-04-11 10:27:43
    在关系数据库中,索引是一种单独、物理对数据库表中一列或多列值进行排序一种存储结构,它是某个表中一列或若干列值集合和相应指向表中物理标识这些值的数据逻辑指针清单。索引的作用相当于图书...

    在SQL中,使用索引可以减少搜索的时间。
    什么是索引呢?
    在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
    建立索引的目的:加快查询速度。
    关系数据库管理系统中常见索引:
    ①顺序文件上的索引
    ②B+树索引
    ③散列(hash)索引
    ④位图索引
    数据库管理员 或 表的属主(即建立表的人)可以建立索引。
    关系数据库管理系统自动完成维护索引的工作。
    关系数据库管理系统自动选择合适的索引作为存取路径,用户不必也不能显式地选择索引。
    建立索引
    语句格式
    CREATE [UNIQUE] INDEX <索引名>
    ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

    <表名>:要建索引的基本表的名字
    索引:可以建立在该表的一列或多列上,各列名之间用逗号分隔
    <次序>:指定索引值的排列次序,升序:ASC,降序:DESC。缺省值:ASC
    UNIQUE:此索引的每一个索引值只对应唯一的数据记录
    例:为学生-课程数据库中的Student,Course,SC三个表建立索引。Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引

      CREATE UNIQUE INDEX  Stusno ON Student(Sno);
       CREATE UNIQUE INDEX  Coucno ON Course(Cno);
       CREATE UNIQUE INDEX  SCno ON SC(Sno ASC,Cno DESC);
    

    修改索引
    ALTER INDEX <旧索引名> RENAME TO <新索引名>

    例:将SC表的SCno索引名改为SCSno

    ALTER INDEX SCno RENAME TO SCSno;
    

    删除索引
    DROP INDEX <索引名>;

    删除索引时,系统会从数据字典中删去有关该索引的描述。

    例:删除Student表的Stusname索引

     DROP INDEX Stusname;
    

    数据字典
    数据字典是关系数据库管理系统内部的一组系统表,它记录了数据库中所有定义信息:
    关系模式定义
    视图定义
    索引定义
    完整性约束定义
    各类用户对数据库的操作权限
    统计信息等
    关系数据库管理系统在执行SQL的数据定义语句时,实际上就是在更新数据字典表中的相应信息。

    展开全文
  • OPTIMIZE TABLE 当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。...这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费。OPTIMIZE TABLE 命令只对 ..

    OPTIMIZE TABLE 当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据,或者如果已经对含有可变长度行的表(含有 VARCHAR 、 BLOB 或 TEXT 列的表)进行了很多更改,就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费 。OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。表优化的工作可以每周或者每月定期执行,对提高表的访问效率有一定的好处,但是需要注意的是,优化表期间会锁定表,所以一定要安排在空闲时段进行。

    一,原始数据

    1. mysql> select count(*) as total from ad_visit_history;  
    2. +---------+  
    3. | total   |  
    4. +---------+  
    5. | 1187096 |                      //总共有118万多条数据  
    6. +---------+  
    7. 1 row in set (0.04 sec)  

     2,存放在硬盘中的表文件大小

    1. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}  
    2. 382020    ad_visit_history.MYD                    //数据文件占了380M  
    3. 127116    ad_visit_history.MYI                     //索引文件占了127M  
    4. 12    ad_visit_history.frm                              //结构文件占了12K  

     3,查看一下索引信息

    1. mysql> show index from ad_visit_history from test1;     //查看一下该表的索引信息  
    2. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    3. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
    4. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    5. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |     1187096 |     NULL | NULL   |      | BTREE      |         |  
    6. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |  
    7. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |  
    8. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |  
    9. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       30438 |     NULL | NULL   | YES  | BTREE      |         |  
    10. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      593548 |     NULL | NULL   | YES  | BTREE      |         |  
    11. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       65949 |     NULL | NULL   | YES  | BTREE      |         |  
    12. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |  
    13. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    14. 8 rows in set (0.28 sec)  

    索引信息中的列的信息说明。

    Table :表的名称。 

    Non_unique :如果索引不能包括重复词,则为0。如果可以,则为1。 

    Key_name :索引的名称。

    Seq_in_index :索引中的列序列号,从1开始。 

    Column_name :列名称。 

    Collation :列以什么方式存储在索引中。在MySQLSHOW INDEX语法中,有值’A’(升序)或NULL(无分类)。 

    Cardinality :索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。 

    Sub_part :如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。 

    Packed :指示关键字如何被压缩。如果没有被压缩,则为NULL。 

    Null :如果列含有NULL,则含有YES。如果没有,则空。 

    Index_type :存储索引数据结构方法(BTREE, FULLTEXT, HASH, RTREE)

    二,删除一半数据

    1. mysql> delete from ad_visit_history where id>598000;          //删除一半数据  
    2. Query OK, 589096 rows affected (4 min 28.06 sec)  
    3.   
    4. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}              //相对应的MYD,MYI文件大小没有变化  
    5. 382020    ad_visit_history.MYD   
    6. 127116    ad_visit_history.MYI  
    7. 12    ad_visit_history.frm  

    按常规思想来说,如果在数据库中删除了一半数据后,相对应的.MYD,.MYI文件也应当变为之前的一半。但是删除一半数据后,.MYD.MYI尽然连1KB都没有减少 ,这是多么的可怕啊。

    我们在来看一看,索引信息

    Mysql> show index from ad_visit_history;    

    1. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    2. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    
    3. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    4. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |    
    5. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |    
    6. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    7. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |    
    8. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       15333 |     NULL | NULL   | YES  | BTREE      |         |    
    9. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      299000 |     NULL | NULL   | YES  | BTREE      |         |    
    10. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       33222 |     NULL | NULL   | YES  | BTREE      |         |    
    11. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    12. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    13. 8 rows in set (0.00 sec)    

    对比一下,这次索引查询和上次索引查询,里面的数据信息基本上是上一次的一半,这点还是合乎常理。

    三,用optimize table来优化一下

    Java代码   收藏代码

    1. mysql> optimize table ad_visit_history;                                             //删除数据后的优化  
    2. +------------------------+----------+----------+----------+  
    3. | Table                  | Op       | Msg_type | Msg_text |  
    4. +------------------------+----------+----------+----------+  
    5. | test1.ad_visit_history | optimize | status   | OK       |  
    6. +------------------------+----------+----------+----------+  
    7. 1 row in set (1 min 21.05 sec)  

     1,查看一下.MYD,.MYI文件的大小

    1. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}  
    2. 182080    ad_visit_history.MYD                                          //数据文件差不多为优化前的一半  
    3. 66024    ad_visit_history.MYI                                             //索引文件也一样,差不多是优化前的一半  
    4. 12    ad_visit_history.frm  

     2,查看一下索引信息

    mysql> show index from ad_visit_history;    

    1. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    2. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    
    3. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    4. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |    
    5. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |    
    6. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    7. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |    
    8. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       24916 |     NULL | NULL   | YES  | BTREE      |         |    
    9. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    10. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       59800 |     NULL | NULL   | YES  | BTREE      |         |    
    11. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    12. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    13. 8 rows in set (0.00 sec)    

    从以上数据我们可以得出,ad_code,ad_code_ind,from_page_url_ind等索引机会差不多都提高了85%,这样效率提高了好多。

    四,小结

    结合mysql官方网站的信息,个人是这样理解的。当你删除数据 时,mysql并不会回收,被已删除数据的占据的存储空间,以及索引位。而是空在那里,而是等待新的数据来弥补这个空缺,这样就有一个缺少,如果一时半 会,没有数据来填补这个空缺,那这样就太浪费资源了。所以对于写比较频烦的表,要定期进行optimize,一个月一次,看实际情况而定了。

    举个例子来说吧。有100个php程序员辞职了,但是呢只是人走了,php的职位还在那里,这些职位不会撤销,要等新的php程序来填补这些空位。招一个好的程序员,比较难。我想大部分时间会空在那里。哈哈。

    五,手册中关于OPTIMIZE的一些用法和描述

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间,并整理数据文件的碎片。

    在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次 即可,只对特定的表运行。

    OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

    注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

    展开全文
  • 一:索引基本概念记录的顺序 :物理顺序:即表中记录的存储顺序。...表文件中的记录被修改或删除时,索引文件可自动更新。索引关键字(索引表达式):用来建立索引的一个字段或字段表达式。注意:①用多个字段建立索...

    一:索引基本概念

    记录的顺序 :

    物理顺序:即表中记录的存储顺序。用记录号表示。

    逻辑顺序:表打开后被使用时记录的处理顺序。

    索 引:

    指按表文件中某个关键字段或表达式建立记录的逻辑顺序。它是由一系列记录号组成的一个列表,提供对数据的快速访问。索引不改变表中记录的物理顺序。表文件中的记录被修改或删除时,索引文件可自动更新。

    索引关键字(索引表达式):用来建立索引的一个字段或字段表达式。

    注意:

    ①用多个字段建立索引表达式时,表达式的计算结果将影响索引的结果;

    ②不同类型字段构成一个表达式时,必须转换数据类型。

    索引标识(索引名):

    即索引关键字的名称。必须以下划线、字母或汉字开头,且不可超过10个字。

    索引类型:主索引、候选索引、普通索引、唯一索引。

    主索引:

    组成主索引关键字的字段或表达式,在表的所有记录中不能有重复的值。主索引只适用于数据库表的结构复合索引中。自由表中不可以建立主索引;数据库中的每个表可以且只能建立一个主索引。

    候选索引:

    在指定的关键字段或表达式中不允许有重复值的索引。在数据库表和自由表中均可为每个表建立多个候选索引。

    普通索引:

    也可以决定记录的处理顺序,但是允许字段中出现重复值。在一个表中可以加入多个普通索引。

    唯一索引:

    参加索引的关键字段或表达式在表中可以有重复值,但在索引对照表中,具有重复值的记录仅存储其中的第一个。

    二:索引的作用

    用途

    采用的索引类型

    排序记录,以便显示、查询或打印使用普通索引、候选索引或主索引在字段中控制重复值的输入并对记录排序对数据库表使用主索引或候选索引,对自由表使用候选索引准备设置表关系依据表在关系中所起的作用,使用普通索引、主索引或候选索引

    三:索引文件的种类

    索引文件种类

    特征

    关键字数目

    限制

    结构复合索引文件

    .CDX

    使用和表文件名相同的基本名,随表的打开自动打开。可以看成表结构的一部分。多关键字表达式,称为标识。有效表达式限制在 240 个字符之内。

    非结构复合索引文件

    .CDX

    必须明确地打开,使用和表名不同的基本名。其中不能创建主索引多关键字表达式,称为标识。有效表达式限制在 符之240 个字内。

    独立索引文件

    .IDX

    必须明确地打开,文件的基本名由用户定义。一般作为临时索引文件。单关键字表达式。有效表达式限制在 100 个字符之内。

    结构复合索引文件(扩展名为.CDX)的特点:

    .在创建索引标识时自动创建。

    .在打开表时自动打开。

    .在同一索引文件中能包含多个排序方案,或索引关键字。

    .在添加、更改或删除记录时自动维护。

    四:创建结构复合索引

    VFP中创建索引文件有两种方式:表设计器方式和命令方式。

    ①表设计器方式

    打开表文件 →从显示菜单中选择表设计器 → 在表设计器中单击索引 → 输入索引名并选择索引类型 → 选择索引的方向(按升序或降序排列记录) → 在表达式框中输入作为排序依据的索引关键字 → 在筛选框中输入筛选表达式 → 单击确定,完毕。

    ②命令方式

    命令

    功能

    INDEX ON TAG [FOR 条件 ] [ASCENDING | DESCENDING] [CANDIDATE | UNIQUE]

    用INDEX ON 命令建立一个索引文件ALTER TABLE ADD PRIMARY KEY TAG

    用SQL命令创建主索引

    ALTER TABLE ADD UNIQUE TAG

    用SQL命令创建候选索引

    注意:

    ①备注型字段和通用型字段不能作为索引关键字段;

    ②不要建立无用的索引,以免降低系统性能;

    ③及时清理已无用索引标识,提高系统效率。

    ④在复合索引的多个索引中,某一时刻只有一个索引对表起作用。

    五:索引的修改和删除

    修改: 打开表设计器,在索引对话框中进行所需修改;

    或用命令重新建立一个相同标识名而索引表达式不同的索引。

    删除: 打开表设计器,在索引对话框中删除不需要的索引标识即可;

    或 用命令: DELETE TAG ALL | 索引标识1 [, 索引标识2 ] …

    删除不需要的索引标识,ALL表示全部标识。

    六:索引的使用

    功能

    命令格式

    打开表的同时指定主控索引 USE ORDER 为已打开的表确定主控索引 SET ORDER TO [ASCENDING | DESCENDING] ]搜索某张已建立索引的表 FIND 搜索表中首次出现的记录 SEEK [ ] SEEK

    展开全文
  • 关于mysql 删除数据后物理空间未释放(转载)OPTIMIZE TABLE 当您删除了大量数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。...

    OPTIMIZE TABLE 当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据,或者如果已经对含有可变长度行的表(含有 VARCHAR 、 BLOB 或 TEXT 列的表)进行了很多更改,就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费 。OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。表优化的工作可以每周或者每月定期执行,对提高表的访问效率有一定的好处,但是需要注意的是,优化表期间会锁定表,所以一定要安排在空闲时段进行。

    一,原始数据

     
    1. mysql> select count(*) as total from ad_visit_history;  
    2. +---------+  
    3. | total   |  
    4. +---------+  
    5. 1187096 |                      //总共有118万多条数据  
    6. +---------+  
    7. 1 row in set (0.04 sec)  

     2,存放在硬盘中的表文件大小

     
    1. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}  
    2. 382020    ad_visit_history.MYD                    //数据文件占了380M  
    3. 127116    ad_visit_history.MYI                     //索引文件占了127M  
    4. 12    ad_visit_history.frm                              //结构文件占了12K  

     3,查看一下索引信息

     
    1. mysql> show index from ad_visit_history from test1;     //查看一下该表的索引信息  
    2. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    3. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
    4. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    5. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |     1187096 |     NULL | NULL   |      | BTREE      |         |  
    6. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |  
    7. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |  
    8. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |  
    9. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       30438 |     NULL | NULL   | YES  | BTREE      |         |  
    10. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      593548 |     NULL | NULL   | YES  | BTREE      |         |  
    11. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       65949 |     NULL | NULL   | YES  | BTREE      |         |  
    12. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |  
    13. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
    14. 8 rows in set (0.28 sec)  

    索引信息中的列的信息说明。

    Table :表的名称。 Non_unique :如果索引不能包括重复词,则为0。如果可以,则为1。 Key_name :索引的名称。 Seq_in_index :索引中的列序列号,从1开始。 Column_name :列名称。 Collation :列以什么方式存储在索引中。在MySQLSHOW INDEX语法中,有值’A’(升序)或NULL(无分类)。 Cardinality :索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。 Sub_part :如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。 Packed :指示关键字如何被压缩。如果没有被压缩,则为NULL。 Null :如果列含有NULL,则含有YES。如果没有,则为空。 Index_type :存储索引数据结构方法(BTREE, FULLTEXT, HASH, RTREE)

    二,删除一半数据

     
    1. mysql> delete from ad_visit_history where id>598000;          //删除一半数据  
    2. Query OK, 589096 rows affected (4 min 28.06 sec)  
    3.   
    4. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}              //相对应的MYD,MYI文件大小没有变化  
    5. 382020    ad_visit_history.MYD   
    6. 127116    ad_visit_history.MYI  
    7. 12    ad_visit_history.frm  

    按常规思想来说,如果在数据库中删除了一半数据后,相对应的.MYD,.MYI文件也应当变为之前的一半。但是删除一半数据后,.MYD.MYI尽然连1KB都没有减少 ,这是多么的可怕啊。

    我们在来看一看,索引信息

    Mysql> show index from ad_visit_history;    
    1. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    2. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    
    3. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    4. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |    
    5. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |    
    6. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    7. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |    
    8. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       15333 |     NULL | NULL   | YES  | BTREE      |         |    
    9. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      299000 |     NULL | NULL   | YES  | BTREE      |         |    
    10. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       33222 |     NULL | NULL   | YES  | BTREE      |         |    
    11. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    12. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    13. 8 rows in set (0.00 sec)    

    对比一下,这次索引查询和上次索引查询,里面的数据信息基本上是上次一次的一本,这点还是合乎常理。

    三,用optimize table来优化一下

    Java代码  收藏代码
    1. mysql> optimize table ad_visit_history;                                             //删除数据后的优化  
    2. +------------------------+----------+----------+----------+  
    3. | Table                  | Op       | Msg_type | Msg_text |  
    4. +------------------------+----------+----------+----------+  
    5. | test1.ad_visit_history | optimize | status   | OK       |  
    6. +------------------------+----------+----------+----------+  
    7. 1 row in set (1 min 21.05 sec)  

     1,查看一下.MYD,.MYI文件的大小

     
    1. [root@BlackGhost test1]# ls |grep visit |xargs -i du {}  
    2. 182080    ad_visit_history.MYD                                          //数据文件差不多为优化前的一半  
    3. 66024    ad_visit_history.MYI                                             //索引文件也一样,差不多是优化前的一半  
    4. 12    ad_visit_history.frm  

     2,查看一下索引信息

    mysql> show index from ad_visit_history;    
    1. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    2. | Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    
    3. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    4. | ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |    
    5. | ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |    
    6. | ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    7. | ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |    
    8. | ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       24916 |     NULL | NULL   | YES  | BTREE      |         |    
    9. | ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    10. | ad_visit_history |          1 | port_ind          |            1 | port          | A         |       59800 |     NULL | NULL   | YES  | BTREE      |         |    
    11. | ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |    
    12. +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    
    13. 8 rows in set (0.00 sec)    

    从以上数据我们可以得出,ad_code,ad_code_ind,from_page_url_ind等索引机会差不多都提高了85%,这样效率提高了好多。

    四,小结

    结合mysql官方网站的信息,个人是这样理解的。当你删除数据 时,mysql并不会回收,被已删除数据的占据的存储空间,以及索引位。而是空在那里,而是等待新的数据来弥补这个空缺,这样就有一个缺少,如果一时半 会,没有数据来填补这个空缺,那这样就太浪费资源了。所以对于写比较频烦的表,要定期进行optimize,一个月一次,看实际情况而定了。

    举个例子来说吧。有100个php程序员辞职了,但是呢只是人走了,php的职位还在那里,这些职位不会撤销,要等新的php程序来填补这些空位。招一个好的程序员,比较难。我想大部分时间会空在那里。哈哈。

    五,手册中关于OPTIMIZE的一些用法和描述

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间,并整理数据文件的碎片。

    在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次 即可,只对特定的表运行。

    OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

    注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

    展开全文
  • MySQL删除数据1.Deletedelete from TABLE_NAMEa.DELETE属于数据库DML操作语言,只删除数据不删除表的结构,会走...因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除...
  • Database 数据库创建与删除 表的创建删除和修改 索引的创建和修改 --exp1 and exp2 --1)在SQL Server建立一个StuDB数据库:(请先在D盘下创建DB文件夹) --有一个数据文件:逻辑名为StuData,文件名为“d:\db\...
  • 1、几乎所有的索引都是建立在字段之上,索引指的是系统根据某种算法,将已有的数据(未来可能新增的数据),单独建立一个文件文件能够实现快速匹配的数据,并且能够快速的找到对应表中的记录。 2、索引的意义:提高...
  • 4,实现表中元组的删除和修改功能,维护索引文件。 (1)实现删除数据库记录的功能。 a)没有WHERE条件,删除关系中的所有元组。 b)指定WHERE条件,删除满足条件的元组。 (2)实现修改数据库记录的功能。 a)没有...
  • 索引存储在文件中.3. 更多索引将影响添加,删除和修改效率. (一个最多可以包含16个索引)(下图是计算机组成原理. 每次查询索引节点时索引数据结构,都会执行磁盘IO读取,即查找和旋转)第二,为什么MySQL索引...
  • OPTIMIZE TABLE 当您中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除表的一大部分数据,或者如果...
  • 如果表中查询列有一个索引,mysql能快速到达某个位置去搜寻数据文件,而不必查看所有数据索引简介  索引是对数据库表中一列或多列值进行排序一种结构,使用索引可提高数据库中特定数据的查询速度。  ...
  • 恢复费当前还原空间实际上就是删除原来还原空间和对应的数据文件,然后重建还原空间和与之对应的数据文件。因为非当前还原空间不在数据库备份行列,对非当前还原空间,oracle系统根本不会用到...
  • 2多表查询时,执行连接时加快了表中的行匹配的行的搜索。3对特定的索引列找出MAX()和MIN()。4如果排序或分组在一个可用索引的最左面前缀上进行(例如,ORDERBYkey_part_1,key_part_2),排序或分组一个表。如果所有...
  • ①顺序文件上的索引:针对按照属性值的升序或者是降序存储的关系,在该属性上简历一个一个顺序索引文件索引文件与属性(表中的一列为一个属性)值和相应的元组(表中的一行就是一个元组)指针组成 ②B+树索引:...
  • 假定一个新创建的表空间包含了五个表表一……二……三……四……五……未用空间当我们删除表时候,就有如下结果一……二……三……空闲空间段……五……未用空间很明显,ORACLE需要有一个机制来...
  • 表相当于文件表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段 id,name,qq,age称为字段,其余的,一行内容称为一条记录 本节重点: 1 创建表 2 查看表结构 3...
  • 删除表中重复记录

    2012-03-13 14:31:35
    问题提出: 当我们想要为... rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中  那些具有最大rowid的就可以
  • 2.创建索引需要占据磁盘的空间,如果有大量的索引,可能比数据文件更快达到最大文件尺寸 3.当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速 Mysql索引的数据类型? MYSQL...
  • .1.索引是什么 ’ 索引是一种特殊的文件,包含着对数据...可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现 2.索引的效果 (1).加快查找的效率 (2).拖慢数据插入,删除和修改的效率 ...
  • 文件索引结构

    千次阅读 2017-06-24 22:46:47
    文件索引结构是指一个文件的信息存放在若干不连续物理块中,系统为每个文件建立一个专用的数据结构——索引表,并将这些块块号存放在索引表中。 优点是保留了链接结构优点,同时解决了其缺点,即能顺序存取,...
  • 目录 一、索引的介绍 二、索引的分类 三、索引的创建与删除 ...索引并非越多越好,过多的索引会导致CPU使用率降低,由于数据的改动会引起索引文件的改动,过多的索引会引起磁盘I/O频繁,造成CPU负荷太重...
  • 表的结构:对于MySQL把有存储引擎都是把结构定义存放到.frm文件中。但对于Innodb同时有一个内部字典存放到空间。...在.frm文件只是用来定义表的结构,Innodb把数据索引都存放到了空间。...
  • 摘要:本文讲解了MySQL中表的操作方法。通过本文学习您应该掌握如何在MySQL创建删除表,如何改变表的结构、名字,如何使用show实用程序...视类型而定,索引数据将在其他文件中存储。你能用ALTER TABLE语...
  • 大量删除数据必然会在数据文件中造成不连续空白空间,而当插入数据时,这些空白空间则会被利用起来 。对于不同存储引擎整理碎片方式不一样。myisam可以有以下方式:mysql> show table status from test ...
  • 索引在数据库有很重要的地位,它可以极大的提高数据的查询速度,但是过多的索引也会降低插入、删除、更新的速度。因为在执行这些写操作时,还要操作索引文件。Django框架Model如何建立索引?例如这样一张购物...
  • 堆(heap)组织表数据行在堆存储,没有任何特定顺序,向一个全新没有做过更新和删除的插入一行时候,总是 append 到堆表文件的最后一页当中。因为不用考虑排序,所以插入速度会比较快。但是要查找符合某个条件...
  • ES删除数据操作

    千次阅读 2020-06-16 16:15:23
    删除数据分为两种:一种是删除索引(数据和结构同时删除,作用同MySQL DROP TABLE “表名” ),另一种是删除数据(不删除结构,作用同MySQLDelete 语句)。 一:删除索引: 删除单个索引可以使用命令 ...
  • database:数据库(11张) 数据库,简单来说是本身可视为电子化的文件柜——存储电子文件的处所,用户可以对文件中的数据进行新增、截取、更新、删除等操作。数据库指的是以一定方式储存在一起、能为多个用户共享、具有...
  • 问题提出: ... rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中  那些具有最大row...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,085
精华内容 434
关键字:

删除数据表中的索引文件