MySQL創造索引原則

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 ?

索引的設計原則

1. 選擇唯一性索引。
2. 為經常需要排序，分組和聯合操作的字段建立索引。
3. 為常作為查詢條件的字段建立索引。
4. 限制索引的數目。
5. 盡量使用數據量少的索引。
6. 盡量使用前綴來索引。如果字段的值很長，最好使用值的前綴來索引，如果只檢索子酸的前面的若干字符，可以提高檢索的速度。
7. 刪除不再使用或者很少使用的索引。

原則只是參考而不能拘泥。 ?

創建索引

三種方式：在創建表是創建索引，在已存在的表上創建索引和使用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:?1
select_type:?SIMPLE
table: indextest
type:?ref
possible_keys: index_id
key: index_id
key_len:?5
ref: const
rows:?1
Extra:?Using?where
1?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: singlerow
Create?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=latin1
1?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: stu
Create?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=latin1
1?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_1
Create?Table:?CREATE?TABLE?`index_1` (
`id`?int(11)?DEFAULT?NULL,
`name`?varchar(20)?DEFAULT?NULL,
`class`?int(11)?DEFAULT?NULL
) ENGINE=InnoDB?DEFAULT?CHARSET=latin1
1?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 indexes
mysql>?alter?table?index_1 engine=myisam;
Query OK,?0?rows?affected (0.36?sec)
Records:?0?Duplicates:?0?Warnings:?0

mysql>?alter?table?index_1?add?fulltext?index?index_alter (name?desc);
Query OK,?0?rows?affected (0.13?sec)
Records:?0?Duplicates:?0?Warnings:?0

mysql>?show?create?table?index_1 \G;
***************************?1.?row?***************************
Table: index_1
Create?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=latin1
1?row?in?set?(0.00 sec) ?

刪除索引

語法： drop?index?index_name?on?table_name; mysql>?show?create?table?index_1 \G;
***************************?1.?row?***************************
Table: index_1
Create?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=latin1
1?row?in?set?(0.00 sec)

ERROR:
No?query specified

mysql>?drop?index?index_alter?on?index_1;
Query OK,?0?rows?affected (0.11?sec)
Records:?0?Duplicates:?0?Warnings:?0

mysql>?show?create?table?index_1 \G;
***************************?1.?row?***************************
Table: index_1
Create?Table:?CREATE?TABLE?`index_1` (
`id`?int(11)?DEFAULT?NULL,
`name`?varchar(20)?DEFAULT?NULL,
`class`?int(11)?DEFAULT?NULL
) ENGINE=MyISAM?DEFAULT?CHARSET=latin1
1?row?in?set?(0.00 sec)

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com