MySQL创造索引原则
时间：2021-07-01 10:21:17
帮助过：69人阅读
MySQL创建索引原则 索引是一种特殊的数据结构，可以用来快速查询数据库的特定记录，建立是一中提高数据库性能的重要方式。 ? 内容：索引的意义，索引的设计，创建和删除 ? 可参考的资料： http://www.51cto.com/specbook/52/12911.htm ? 索引简介 索引是建立
MySQL创建索引原则
索引是一种特殊的数据结构，可以用来快速查询数据库的特定记录，建立是一中提高数据库性能的重要方式。
?
内容：索引的意义，索引的设计，创建和删除
?
可参考的资料：http://www.51cto.com/specbook/52/12911.htm
?
索引简介
索引是建立在表上的，有一列或者多列组成，并对这一列或者多列进行排序的一种结构。
?
所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括B型树索引和哈希索引。
?
索引的优点是可以提高检索的速度，但是创建和维护索引需要耗费时间，这个时间随着数据量的增加而增加。
?
索引可以提高查询的速度，但是会影响插入的速度，当要插入大量的数据时，最好的办法是先删除索引，插入数据后再建立索引。
?
MySQL的索引分为：普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引。
?
目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引。则需要升级mysql。例如：从5.1升级到5.5，再升级到5.6版本。详细见mysql手册。http://dev.mysql.com/doc/refman/5.6/en/upgrading.html
?
索引的设计原则
- 选择唯一性索引。
- 为经常需要排序，分组和联合操作的字段建立索引。
- 为常作为查询条件的字段建立索引。
- 限制索引的数目。
- 尽量使用数据量少的索引。
- 尽量使用前缀来索引。如果字段的值很长，最好使用值的前缀来索引，如果只检索子酸的前面的若干字符，可以提高检索的速度。
- 删除不再使用或者很少使用的索引。
原则只是参考而不能拘泥。
?
创建索引
三种方式：在创建表是创建索引，在已存在的表上创建索引和使用alter table语句创建索引。
?
mysql>?show?tables;+----------------+| Tables_in_kiwi |+----------------+| stu |+----------------+1?row?in?set?(0.00 sec)mysql>?create?table?indexTest(id?int,?name?varchar(20), sex?boolean,?index?index_id(id));Query OK,?0?rows?affected (0.08?sec)mysql>?desc?indextest;+-------+-------------+------+-----+---------+-------+| Field |?Type?|?Null?|?Key?|?Default?| Extra |+-------+-------------+------+-----+---------+-------+| id |?int(11) | YES | MUL |?NULL?| ||?name?|?varchar(20) | YES | |?NULL?| || sex | tinyint(1) | YES | |?NULL?| |+-------+-------------+------+-----+---------+-------+3?rows?in?set?(0.01?sec)mysql>?explain?select?*?from?indextest?where?id?=?1?\G;***************************?1.?row?***************************id:?1select_type:?SIMPLEtable: indextesttype:?refpossible_keys: index_idkey: index_idkey_len:?5ref: constrows:?1Extra:?Using?where1?row?in?set?(0.00 sec)
?
创建单列索引，subject(10)是为了不查询全部信息而提高检索的速度。
mysql>?create?table?singleRow(id?int,name?varchar(20),subject?varchar(30),index?index_st(subject(10)));Query OK,?0?rows?affected (0.17?sec)mysql>?show?create?table?singlerow\G;***************************?1.?row?***************************Table: singlerowCreate?Table:?CREATE?TABLE?`singlerow` (`id`?int(11)?DEFAULT?NULL,`name`?varchar(20)?DEFAULT?NULL,`subject`?varchar(30)?DEFAULT?NULL,KEY?`index_st` (`subject`(10))) ENGINE=InnoDB?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)
多列索引，空间索引类似。
?
在已存在的表上建立索引
语法为： create [unique|fulltext|spatial] index index_name on table_name (property_name[length] [asc|desc]);
mysql>?desc?stu;+--------+-------------+------+-----+---------+----------------+| Field |?Type?|?Null?|?Key?|?Default?| Extra |+--------+-------------+------+-----+---------+----------------+| id |?int(10) |?NO?| PRI |?NULL?| auto_increment || s_num |?int(10) | YES | MUL |?NULL?| || course |?varchar(20) | YES | |?NULL?| || score |?varchar(4) | YES | |?NULL?| |+--------+-------------+------+-----+---------+----------------+4?rows?in?set?(0.05?sec)mysql>?show?create?table?stu \G;***************************?1.?row?***************************Table: stuCreate?Table:?CREATE?TABLE?`stu` (`id`?int(10)?NOT?NULL?AUTO_INCREMENT,`s_num`?int(10)?DEFAULT?NULL,`course`?varchar(20)?DEFAULT?NULL,`score`?varchar(4)?DEFAULT?NULL,PRIMARY?KEY?(`id`),UNIQUE?KEY?`id` (`id`),UNIQUE?KEY?`index_id` (`id`),KEY?`grade_fk` (`s_num`)) ENGINE=MyISAM?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)
?
使用alter table创建索引
语法为：
alter?table?table_name?add?[unique|fulltext|spatial]?index?index_name(property_name[length] [asc|desc]);
?
mysql>?create?table?index_1(id?int,?name?varchar(20),?class?int);Query OK,?0?rows?affected (0.11?sec)mysql>?show?tables;+----------------+| Tables_in_kiwi |+----------------+| index_1 || singlerow || stu |+----------------+3?rows?in?set?(0.00 sec)mysql>?show?create?table?index_1 \G;***************************?1.?row?***************************Table: index_1Create?Table:?CREATE?TABLE?`index_1` (`id`?int(11)?DEFAULT?NULL,`name`?varchar(20)?DEFAULT?NULL,`class`?int(11)?DEFAULT?NULL) ENGINE=InnoDB?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)mysql>?alter?table?index_1?add?fulltext?index?index_alter (name?desc);ERROR?1214?(HY000): The used?table?type?doesn't support FULLTEXT indexesmysql>?alter?table?index_1 engine=myisam;Query OK,?0?rows?affected (0.36?sec)Records:?0?Duplicates:?0?Warnings:?0mysql>?alter?table?index_1?add?fulltext?index?index_alter (name?desc);Query OK,?0?rows?affected (0.13?sec)Records:?0?Duplicates:?0?Warnings:?0mysql>?show?create?table?index_1 \G;***************************?1.?row?***************************Table: index_1Create?Table:?CREATE?TABLE?`index_1` (`id`?int(11)?DEFAULT?NULL,`name`?varchar(20)?DEFAULT?NULL,`class`?int(11)?DEFAULT?NULL,FULLTEXT?KEY?`index_alter` (`name`)) ENGINE=MyISAM?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)
?
删除索引
语法：
drop?index?index_name?on?table_name;
mysql>?show?create?table?index_1 \G;***************************?1.?row?***************************Table: index_1Create?Table:?CREATE?TABLE?`index_1` (`id`?int(11)?DEFAULT?NULL,`name`?varchar(20)?DEFAULT?NULL,`class`?int(11)?DEFAULT?NULL,FULLTEXT?KEY?`index_alter` (`name`)) ENGINE=MyISAM?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)ERROR:No?query specifiedmysql>?drop?index?index_alter?on?index_1;
Query OK,?0?rows?affected (0.11?sec)Records:?0?Duplicates:?0?Warnings:?0mysql>?show?create?table?index_1 \G;***************************?1.?row?***************************Table: index_1Create?Table:?CREATE?TABLE?`index_1` (`id`?int(11)?DEFAULT?NULL,`name`?varchar(20)?DEFAULT?NULL,`class`?int(11)?DEFAULT?NULL) ENGINE=MyISAM?DEFAULT?CHARSET=latin11?row?in?set?(0.00 sec)
