1. 创建索引的语法
单列索引语法
|
1 2 3 4 |
ALTER TABLE <table> ADD PRIMARY KEY [index-name] (<column>); # 创建主码索引 ALTER TABLE <table> ADD [UNIQUE] KEY|INDEX [index-name] (<column>); #创建非主码索引，KEY和INDEX关键字可以互换 |
多列索引语法
|
1 2 |
ALTER TABLE <table> ADD PRIMARY KEY [index-name] (,,...) ; ALTER TABLE <table> ADD [UNIQUE] KEY|INDEX [index-name] (,,...); |
2. 索引的基数
当查询使用不止一个索引的时候，mysql会找到一个最高效的索引。
举例说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain select * from artist where type='Band' and founded=1980\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: artist type: index_merge possible_keys: type,founded key: founded,type key_len: 2,1 ref: NULL rows: 385 Extra: Using intersect(founded,type); Using where 1 row in set (0.05 sec) |
在上面的例子中，mysql必须在possible_keys中，做出选择，可以看到，相比type，founded是更高效的索引。我们可以使用索引基数来确定最佳索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> show index from artist\G ….. *************************** 3. row *************************** Table: artist Non_unique: 1 Key_name: type Seq_in_index: 1 Column_name: type Collation: A Cardinality: 9 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: artist Non_unique: 1 Key_name: founded Seq_in_index: 1 Column_name: founded Collation: A Cardinality: 212 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: |
从上面的信息中，可以看到founded有更高的索引基数，Cardinality值最大。索引基数可以通过公式：
(索引的唯一性数量/索引的总行数) 来计算。理想情况下，值为1，比如主键索引。
尽管索引基数是唯一性的一个重要指标，但Mysql也会参考有关唯一值的范围和容量等统计信息。有时需要select count(*) 来确定最佳索引。
3. 如果查找的词是以通配符开头，则mysql不会使用索引。
举例说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain select * from artist where name like '%Queen%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: artist type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 583131 Extra: Using where 1 row in set (0.10 sec) |
如果你需要经常查询一个以通配符开头的查询，常用的方法是在数据库中保存需要查询的值的反序值。例如，假设你查找所有.com结尾的电子邮箱地址，当搜索email like ‘%.com’ 是不会用到索引的。但是，而搜索reverse_email like REVERSE(‘%.com’) 就可以使用定在在reverse_email上面的索引。
4. mysql不支持基于索引的函数。
举例说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain select * from artist where UPPER(name) = UPPER('Queen')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: artist type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 583131 Extra: Using where 1 row in set (0.06 sec) |
因为使用了name列的UPPER函数，索引不会应该name索引。
5. 删除重复索引
任何包含在其他索引的最左边的索引都是重复索引。例如：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Table | Create Table | album | CREATE TABLE `album` ( `album_id` int(10) unsigned NOT NULL, `artist_id` int(10) unsigned NOT NULL, `album_type_id` int(10) unsigned NOT NULL, `name` varchar(255) NOT NULL, `first_released` year(4) NOT NULL, `country_id` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`album_id`), KEY `name` (`name`), KEY `artist_id` (`artist_id`), KEY `album_type_id` (`album_type_id`), KEY `country_id` (`country_id`), KEY `m2` (`country_id`,`album_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
从上面看出，country_id就是重复索引，因为已经存在m2索引。我们再来看以下的语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> explain select * from album where country_id=112\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: album type: ref possible_keys: country_id,m2 key: country_id key_len: 3 ref: const rows: 11 Extra: Using where 1 row in set (0.34 sec) mysql> explain select * from album use index (m2) where country_id=112\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: album type: ref possible_keys: m2 key: m2 key_len: 3 ref: const rows: 11 Extra: Using where 1 row in set (0.03 sec) |
可以看到使用索引country_id和m2效果是一样的，影响的行数都是11。use index是索引提示的语法，还可以用到的关键字是ignore和force。具体的语法如下：
|
1 2 3 |
USE {INDEX | KEY} [FOR {JOIN | GROUP BY | ORDER BY}] ([index_list]) IGNORE {INDEX | KEY} [FOR {JOIN | GROUP BY | ORDER BY}] ([index_list]) FORCE {INDEX | KEY} [FOR {JOIN | GROUP BY | ORDER BY}] ([index_list]) |
6. 在InnoDB中，主码的值会附加在非主码索引的每个对应的记录的后面，因此没有必要在在非主码索引中指定主码。这一重要特性意味着InnoDB中的所有非主码索引都隐含有主码列了。
7. 覆盖索引
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
mysql> alter table artist add index (founded); Query OK, 0 rows affected (1.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select artist_id, name, founded from artist where founded = 1969\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: artist type: ref possible_keys: founded key: founded key_len: 2 ref: const rows: 1036 Extra: Using index condition 1 row in set (0.00 sec) mysql> alter table artist drop index founded, add index founded_name (founded, name); Query OK, 0 rows affected (1.80 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select artist_id, name, founded from artist where founded = 1969\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: artist type: ref possible_keys: founded_name key: founded_name key_len: 2 ref: const rows: 1035 Extra: Using where; Using index 1 row in set (0.00 sec) |
在Extra中显示Using index时，这并不意味着在访问底层表数据时使用到了索引，这表示只有这个索引才是满足查询所要求的。这种索引称为覆盖索引，比如上面的founded_name。
覆盖索引得名于它满足了查询中给定表用到的所有的列。想要创建一个覆盖索引，这个索引必须包含指定表上包括WHERE语句，ORDER BY语句，GROUP BY语句以及SELECT语句中的所有列。
不要在sql查询中使用select * 。如果在select指定真正用到的列，那么就可以创建合适的索引提高性能。
8.局部索引
如果MySQL需要获取大量行中的更多列的数据，那么创建具有更小行宽度的小型索引会更加有效。
|
1 2 3 |
mysql> alter table artist add index (name(20)); Query OK, 0 rows affected (1.63 sec) Records: 0 Duplicates: 0 Warnings: 0 |
顶一下