Mysql中B+树索引的管理

标签: mysql 索引 管理 | 发表时间:2013-03-29 16:16 | 作者:songxixi
出处:http://blog.csdn.net
索引的创建和删除可以通过两种方法;一种是alter table ,另一种是create /drop index.alter table 创建索引的语法:
alter table tbl_name
|ADD {INDEX|KEY} {INDEX_NAME}
{INDEX_TYPE}(INDEX_COL_NAME,......)[INDEX_OPTION].............
|DROP{INDEX|KEY}INDEX_NAME



alter table tbl_name drop primary key;


create/drop index的语法同样很简单;
create [unique] index index_name
[index_type] on tbl_name(index_col_name...)

drop index index_name on tbl_name;



drop index index_name on tbl_name;


索引可以索引整个列的数据,也可以只索引列的开头部分的数据,如我们前面创建的表t,b列为varchar(8000),我们只索引前100个字段,如;
mysql> alter table t add key idx_b(b(100));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
目前mysql数据库存在的一个普遍的问题是,所有对于索引的添加或者删除操作,mysql数据库是先创建一张新的临时表,然后把数据导入临时表,删除原表,再把临时表重名为原来的表名,。因此对于一张大表,添加和删除索引需要很长的时间。

innodb存储引擎从版本innodb plugin开始,支持一种快速索引创建方法;当然这种方法只限制于辅助索引,对于主键的创建和删除还是需要重新创建一个表,对于辅助索引的创建,innodb存储引擎会对表加上一个S锁,。在创建的过程中,不需要重建表 ,因此速度极快。但是在创建过程中,由于上了S锁,,因此创建的过程中该表只能进行读操作,删除辅助索引操作就更简单,只需要在innodb内部视图进行更新,将辅助索引的空间标记为可用,并删除mysql内部视图上对于该表的索引定义即可;

查看表中索引的信息可以使用show index语句,如我们分析表t,之前加一个联合索引,如:

mysql> alter table t add key idx_a_b(a,c);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t\G;
*************************** 1. row ***************************
        Table: t
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 4
     Sub_part: 100
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_a_b
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_a_b
 Seq_in_index: 2
  Column_name: c
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)

ERROR: 
No query specified

mysql>

表中有3个索引;一个主键索引,A,C列上的索引,和B列上前100个字节构成的索引,以下是每列的定义:

table:索引所在表名;
non_unique:非唯一的索引,可以看到primary key 是0,因为必须是唯一的;
key_name:索引的名称,我们可以通过这个名称来DROP index;
seq_in_index:索引中该列的位置,如果看联合索引idx_a_c就是比较直观;
column_name:索引的列;
collation:列以什么方式存储索引中的,可以是A或者'NULL'。B+树索引总是A,即排序的,如果使用了heap存储引擎,并且建立了hash索引,这里会显示NULL,因为hash根据hash桶来存放索引数据,而不是对数据进行排序;
cardinality:非常关键的值,表示索引中唯一值的数目的估计值。cardinality/(表的行数)应尽可能接近1,如果非常小,那么需要考虑是否需要重建这个索引。
sub_part:是否	是列的部分索引,如果是就显示数字,如果是索引整个列,则该字段为NULL;
Packed:关键字如何被压缩,如果没有被压缩,则为NULL;
null:是否索引的列含有NULL值,可以看到idx_b这里为yes,因为我们定义了b列运行为空;
index_type:索引的类型;innodb存储引擎只支持B+树索引,所以这里显示的都是Btree;
comment:注释;

cardinality的值非常关键,优化器会根据这个值来判断是否使用这个索引,但是这个值并不是事实更新的,并非每次索引的更新,该值都会更新,因为代价大,因此这个值不太准确,只是一个大概的值;
如果需要更新索引cardinality的信息,可以使用analyze table命令;如;
mysql> analyze table t\G;
*************************** 1. row ***************************
   Table: test.t
      Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> show index from t\G;
*************************** 1. row ***************************
        Table: t
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 2
     Sub_part: 100
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_a_b
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_a_b
 Seq_in_index: 2
  Column_name: c
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)

ERROR: 
No query specified

mysql>






作者:songxixi 发表于2013-3-29 16:16:30 原文链接
阅读:68 评论:0 查看评论

相关 [mysql 索引 管理] 推荐:

Mysql中B+树索引的管理

- - CSDN博客数据库推荐文章
索引的创建和删除可以通过两种方法;一种是alter table ,另一种是create /drop index.alter table 创建索引的语法: alter table tbl_name |ADD {INDEX|KEY} {INDEX_NAME} {INDEX_TYPE}(INDEX_COL_NAME,......)[INDEX_OPTION].

[MySQL] B+树索引

- - CSDN博客推荐文章
B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据. 保持树平衡主要是为了提高查询性能,但为了维护树的平衡,成本也是巨大的,当有数据插入或删除时,需采用拆分节点、左旋、右旋等方法.

mysql 索引技巧

- - 小彰
MySQL索引的建立对于MySQL的高效运行是很重要的. 下面介绍几种常见的MySQL索引类型. 在数据库表中,对字段建立索引可以大大提高查询速度. 假如我们创建了一个 mytable表:. CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我们随机向里面插入了10000条记录,其中有一条:5555, admin.

mysql选择索引

- - CSDN博客数据库推荐文章
1、尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列编制索引. 最适合有索引的数据列是那些在where子句中数据列,在联结子句中出现的数据列,或者是在Group by 、Order by子句中出现的数据列. select 后的数据列最好不要用索引. 2、综合考虑各数据列的维度.

mysql 索引详解

- - 行业应用 - ITeye博客
本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题. 特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等. 为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论.

mysql索引认识

- - 数据库 - ITeye博客
数据在磁盘中是以 “块”的形式存储的,所以一张表涉及的数据可能会存在多个块中,而在磁盘中查询数据则会根据字段是否为有序与无序来区分,. 无序情况:1.数值具有唯一性则需要查找 总块数/2.                   2.无序+无唯一性则需要查找  总块数. 有序情况:1.数值唯一性:log2(总块数/2)   (log2是二分查找算法).

MySQL 索引方式

- - zzm
本文配图来自《高性能MySQL(第二版)》. 在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略,本文将分析它们的异同和性能.

MySql索引总结

- - 掘金后端
MySQL 索引底层数据结构.   Mysql索引使用的数据结构主要有 BTree索引 和 Hash索引. 对于Hash索引来说,底层数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,使用Hash索引查询性能最快. 其余大多数场景建议使用BTree索引. 为什么索引能够提高查询速度.

Mysql-innodb-B+索引

- - 掘金后端
这是读书笔记,Mysql,innodb系列一共3篇. Mysql-innodb-B+索引(本篇). Mysql-innodb-锁(预计20200523). Mysql-innodb-事务预计20200530). CREATE TABLE `aid_***_detail` ( //省略所有字段 PRIMARY KEY (`id`), KEY `range_idx` (`range_id`,`is_delete`,`range_detail_num`,`goods_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4复制代码.

MySQL InnoDB B+树索引

- - OurMySQL
B+树索引在DB中有一个特点就是高扇出性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的IO操作即可. 而现在的磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒. InnoDB存储引擎表是索引组织表,即表中数据安装主键顺序存放. 而聚集索引就是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,因此也让聚集索引也是索引的一部分.