MYSQL的索引粗略讲解

# MYSQL的索引粗略讲解 --参考 MYSQL5.1 帮助档。




# MSSQL MYSQL 数据库索引
#索引 共同点：主要 是为了提高查找效率，同时也可能提高IDU效率，（主要体现在并发，对记录的查找以及锁定），但也可能影响IDU的效率。（因为要维护索引）
#索引 都 有一些 长度限制，以及个数限制
#索引 都有一些特定的类型，按大类 有B树，R树,HASH等各种类型的索引




# 1. MYSQL索引 的类型
-- 在MYSQL 中 INDEX TYPE 主要三大类 B树索引，以及HASH 索引 还有R树的空间索引。
-- MSSQL 当中 也有这几类型的索引。


-- MYSQL 中索引详细类型
-- 聚集，非聚集，唯一 ，全文索引，空间索引。在索引当中特别的是 有字符前缀索引。
-- 聚集索引就是PRIMARY KEY ,MSSQL当中默认是，但可以指定另外的。


-- MSSQL 中
-- 聚集，非聚集，唯一  ，全文索引，空间索引，筛选索引，XML索引，分区索引 在索引当中 2K5以后多了一类包含索引，
-- 还能对某些视图进行索引。


-- HASH 索引 都体现 在内存数据库当中,MSSQL是在2014支持内存


#2。MYSQL中索引的长度限制以及个数限制。


-- MYSQL 5.1 说明 ，后面版本 innodb也支持 全文索引。
/*
所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。


根据存储引擎定义每个表的最大索引数和最大索引长度。参见第15章：存储引擎和表类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。


在索引定义中用col_name(length)语法，你可以创建一个只使用CHAR或VARCHAR列的第1个length字符的索引。按这种方式只索引列值的前缀可以使索引文件小得多。


MyISAM和InnoDB存储引擎还支持对BLOB和TEXT列的索引。当索引一个BLOB或TEXT列时，你必须为索引指定前缀长度。例如：


CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
在MySQL 5.1中，对于MyISAM和InnoDB表，前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。


还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部(前缀)索引。详情参见12.7节，“全文搜索功能”。


也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。


默认情况MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引。


*/




-- MSSQL 相关说明
/*
下表列出了适用于聚集索引、非聚集索引、空间索引、筛选索引和 XML 索引的最大值。除非另有指定，否则下列限制应用于所有索引类型。


最大索引限制  值  其他信息  
每个表的聚集索引数 1  
 
每个表的非聚集索引数 249
 包括筛选索引和使用 PRIMARY KEY 或 UNIQUE 约束创建的非聚集索引，但不包括 XML 索引。
 
每个表的 XML 索引数
 249
 包括 xml 数据类型列的主 XML 索引和辅助 XML 索引。


XML 数据类型列的索引 
 
每个表的空间索引
 249
 使用空间索引（数据库引擎） 
 
每个索引的键列数
 16*
 如果表中还包含主 XML 索引或空间索引，则聚集索引不得超过 15 列。


索引键的最大大小.
 
索引键记录大小
 900 字节*
 不适用于 XML 索引或空间索引。


为了使表支持空间索引，最大索引键记录大小应当为 895 个字节。


索引键的最大大小.
 


* 通过在索引中包含非键列可以避免受非聚集索引的索引键列和记录大小的限制。有关详细信息，请参阅具有包含列的索引。
*/










#3. MYSQL 索引的创建 ，删除，查看


-- 参考 5.1帮助档


-- 创建 
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
 
index_col_name:
    col_name [(LENGTH)] [ASC | DESC]


在MySQL 5.1中：


·         只有当您正在使用MyISAM, InnoDB或BDB表类型时，您可以向有NULL值的列中添加索引。


·         只有当您正在使用MyISAM, BDB或InnoDB表类型时，您可以向BLOB或TEXT列中添加索引。


一个index_col_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储




语法比较简单，没有一些其他选项，MSSQL 选项比较多。




-- 删除
DROP INDEX index_name ON tbl_name


上面的语句 都被映射到 ALTER TABLE 的操作。


-- 没有ALTER INDEX 操作。




-- 查看
SHOW INDEX FROM tbname [ FROM dbname ]
可以使用db_name.tbl_name作为tbl_name FROM db_name语法的另一种形式。这两个语句是等价的：


mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
SHOW KEYS是SHOW INDEX的同义词。也可以使用mysqlshow -k db_name tbl_name命令列举一个表的索引。