MySQL基础之 索引
MySQL索引讲解
索引的好处：
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引的缺点：
过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
索引的设计原则
1、选择唯一性索引，也就是说多选择那些设置为主键，唯一性UNIQUE的字段来建立索引
2、为经常排序、分组的字段建立索引
3、常用作查询条件的字段建立索引，也就是常在WHERE等等子句中指定查询的列。
4、建立合适数量的索引，并不是索引越多越好
5、尽量使用数据量少的字段建立索引
6、尽量使用前缀来建立索引，比如TEXT和BLOG类型的字段，对前面的几个字符建立索引即可
7、不用的索引及时删除。
创建索引的两种方式
1、创建新表的同时创建索引。
2、如果对已存在的表创建索引，使用CREATE INDEX命令或者ALTER TABLE命令
现在来分析这两种用法
一、创建新表的同时创建索引。
CREATE TABLE TABLE_NAME(字段名 数据类型 ,
................,
[UNIQUE | FULLTEXT ] INDEX | PRIMARY KEY,
[INDEX_NAME](fileld1，field2........ [(length)] [ ASC|DESC ]);
);
UNIQUE | FULLTEXT INDEX：这两个选取其中任意一个，比如选择UNIQUE INDEX表示创建唯一索引，FULLTEXT INDEX表示创建全文索引。也可以直接就写INDEX表示创建普通索引。
PRIMARY KEY：表示创建主键索引。
INDEX_NAME：索引的名称。
filed：最后跟上field，表示对哪个字段创建的索引，还可以跟多个列表示使用多列索引。
length：索引的长度，必须是字符串类型的字段才可以使用。
二、已存在的表使用CREATE INDEX命令或者ALTER TABLE命令
创建普通索引
1、CREATE INDX
CREATE INDEX INDEX_NAME ON TABLE_NAME(’COLUMN_NAME’);
注释：COLUMN_NAME是要创建索引的列。
如果创建不止一个索引，多个COLUMN_NAME之间用逗号隔开即可。
2、ALTER TABLE 修改表结构(添加索引)
ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(‘COLUMN_NAME’);
3、删除索引
DROP INDEX INDEXNAME ON TABLE_NAME;
ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
创建唯一索引
1、CREATE INDEX
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(’COLUMN_NAME’);
如果创建不止一个索引，多个COLUMN_NAME之间用逗号隔开。
2、ALTER TABLE修改表结构（添加索引）
ALTER TABLE TABLE_NAME ADD UNIQUE INDEX_NAME(‘COLUMN_NAME’)
3、删除索引
ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
创建主键索引
主键只能作用在一个列上，添加主键索引时，主键默认不为空（NOT NULL）。
1、ALTER TABLE修改表结构
ALTER TABLE TABLE_NAME MODIFY TABLE_NAME DEFINATION NOT NULL;
ALTER TABLE TABLE_NAME ADD PRIMARY KEY('COLUMN_NAME');
2、删除索引
ALTER TABLE TABLE_NAME DROP PRIMARY KEY; #因为一个表中只能有一个主键，所以不需要指定索引名
总结：不能用CREATE INDEX语句创建PRIMARY KEY索引。
显示索引信息
SHOW INDEX显示信息，\G格式化输出
SHOW INDEX FROM TABLE_NAME;\G
联合索引
联合索引就是对多个字段同时建立的索引，多列索引使用的时候是有要求的，就是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
举个例子：
mysql> desc test1_1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | grade | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table test1_1 add index id_g (id,grade); # id为联合索引的第一个字段 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test1_1 where id =2 \G # 使用联合索引的第一个字段进行查询 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ref possible_keys: id_g #使用了索引 key: id_g key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test1_1 where grade = 90 \G #使用联合索引的第二个字段查询， *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ALL possible_keys: NULL # 发现没有使用索引 key: NULL key_len: NULL ref: NULL rows: 6 filtered: 16.67 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test1_1 where id = 3 AND grade=95 \G; # 使用联合索引的两个字段都作为查询条件 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ref possible_keys: id_g # 使用了联合索引 key: id_g key_len: 6 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR: No query specified
删除索引
删除索引一般我们要使用建立索引时的索引名，可以使用show create table table_name;\G命令来查看之前建立索引的名称。
删除索引的语法：DROP INDEX INDEX_NAME ON TABLE_NAME;
删除主键索引的语法：ALTER TABLE TABLE_NAME DROP PRIMARY KEY；
