2.4 组合索引（单列索引与多列索引）
可以是单列上创建的索到，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where 条件是依次从左往右执行的，所以在使用select 语句查询时 where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
select * from member where name='zhangsan'and sex='man'and age=20; create index XXX index on member(name,sex,age); name,sex,age的组合索引顺序必须要和查询的顺序一致才能生效
2.4.1直接创建组合索引
CREATE INDEX 索引名 on 表名(字段1,字段2，字段3); mysql> create index name_cardid_phone_index on member(name,cardid,phone); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table member\G; *************************** 1. row *************************** Table: member Create Table: CREATE TABLE `member` ( `id` int(10) NOT NULL DEFAULT '0', `name` varchar(10) DEFAULT NULL, `cardid` int(18) DEFAULT NULL, `phone` int(11) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), KEY `name_cardid_phone_index` (`name`,`cardid`,`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2.4.2修改表方式创建组合索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2); mysql> alter table member1 add index name_phone_cardid_index(name,phone,cardid); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table member1\G; *************************** 1. row *************************** Table: member1 Create Table: CREATE TABLE `member1` ( `id` int(10) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `cardid` int(18) DEFAULT NULL, `phone` int(11) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, KEY `name_phone_cardid_index` (`name`,`phone`,`cardid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2.4.3 创建表的时候指定组合索引
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
注意：使用的时候要注意 where 的最左原则
select * from 表名 where 字段1=值 and 字段2=值 and ....;
2.5 全文索引（FULLTEXT）
适合在进行模糊查询的时候使用可用于在一篇文章中检索文本信息。在MySQL5.6版本以前FULLTEXT 索引仅可用于MyISAM引擎，在5.6版本之后innodb引擎也支持FULLTEXT索引。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。每个表只允许有一个全文索引。
2.5.1 直接创建全文索引
create fulltext index 索引名 on 表名 (字段); mysql> alter table member engine=MyISAM; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create fulltext index remark_index on member(remark); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table member\G; *************************** 1. row *************************** Table: member Create Table: CREATE TABLE `member` ( `id` int(10) NOT NULL DEFAULT '0', `name` varchar(10) DEFAULT NULL, `cardid` int(18) DEFAULT NULL, `phone` int(11) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), KEY `name_cardid_phone_index` (`name`,`cardid`,`phone`), FULLTEXT KEY `remark_index` (`remark`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2.5.2 修改表的方式创建索引
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名); mysql> alter table member1 engine=MyISAM; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table member1 add fulltext remark_index (remark); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table member1\G; *************************** 1. row *************************** Table: member1 Create Table: CREATE TABLE `member1` ( `id` int(10) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `cardid` int(18) DEFAULT NULL, `phone` int(11) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, KEY `name_phone_cardid_index` (`name`,`phone`,`cardid`), FULLTEXT KEY `remark_index` (`remark`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2.5.3 创建表的时候指定全文索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
2.5.4 使用全文索引查询
#插入数据测试 mysql> insert into member values(1,'zhangsan',123123,123123,'nanjing','this is vip'); mysql> insert into member values(2,'lisi',123123,123123,'suzhou','this is vvip'); mysql> insert into member values(3,'wangwu',123123,123123,'wuxi','this is vvvip'); mysql> select * from member; +----+----------+--------+--------+---------+---------------+ | id | name | cardid | phone | address | remark | +----+----------+--------+--------+---------+---------------+ | 1 | zhangsan | 123123 | 123123 | nanjing | this is vip | | 2 | lisi | 123123 | 123123 | suzhou | this is vvip | | 3 | wangwu | 123123 | 123123 | wuxi | this is vvvip | +----+----------+--------+--------+---------+---------------+ 3 rows in set (0.00 sec) SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
2.6查看索引
show index from 表名; show keys from 表名; mysql> show index from member\G; *************************** 1. row *************************** Table: member Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: member Non_unique: 1 Key_name: name_cardid_phone_index Seq_in_index: 1 Column_name: name Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: member Non_unique: 1 Key_name: name_cardid_phone_index Seq_in_index: 2 Column_name: cardid Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: member Non_unique: 1 Key_name: name_cardid_phone_index Seq_in_index: 3 Column_name: phone Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: member Non_unique: 1 Key_name: remark_index Seq_in_index: 1 Column_name: remark Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: 5 rows in set (0.00 sec)
字段 |
含义 |
Table |
表的名称 |
Non_unique |
如果索引不能包括重复词，则为0;如果可以，则为1 |
Key_name |
索引的名称 |
seq_in_index |
索引中的列序号，从1开始 |
column_name |
列名称 |
collation |
列以什么方式存储在索引中。在 MySQL中，有值’A(升序）或 NULL(无分类) |
Cardinality |
索引中唯一值数目的估计值 |
sub_part |
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL |
Packed |
指示关键字如何被压缩。如果没有被压缩，则为NULL |
Null |
如果列含有NULL，则含有YES。如果没有，则该列含有NO |
lndex_type |
用过的索引方法(BTREE,FULLTEXT,HASH,RTREE) |
comment |
备注 |
2.7 删除索引
2.7.1 直接删除索引
DROP INDEX 索引名 ON 表名; mysql> drop index name_index on member;
2.7.2 修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名; mysql> alter table member drop index phone_index;
2.7.3 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY; mysql> alter table member drop primary key; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc member; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(10) | NO | | 0 | | | name | varchar(10) | YES | MUL | NULL | | | cardid | int(18) | YES | | NULL | | | phone | int(11) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | | remark | text | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
总结
1.索引 有什么 作用 ？
加快查询速度，字段排序
副作用：占用额外的磁盘空间，插入或修改索引字段时会花费更多时间消耗更新性能，因为索引也会随之改变
2.如何提高查询速度？
索引包含着对数据表里所有记录的引用指针，使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
3.select 语句 慢查询 问题
explain select … 分析一下 这个 select语句是否有用到索引或者索引是否正确，如果没有用索引或者索引用的不正确，可以使用 create index 或者 alter table 表名 add index 添加索引去优化查询速度
