索引是对数据表中一列或者多列的值进行排序的一种数据结构(B+ 树),提高数据库中特定数据的查询速度(指针)
MyISAM InnoDB 只支持BTREE索引
Memory/heap 可以支持HASH和BTree索引
- 普通索引 普通索引是mysql的基本索引类型,允许在定义索引的列中插入重复值和空值
- 唯一索引 索引值必须唯一,允许有空值,如果是组合索引,则列的组合必须唯一,主键是特殊的唯一索引(NO null)
- 单列索引 一个索引只包含单个列,一个表可以有多个单列索引
- 组合索引 只有在查询的时候使用到了这些字段的左边字段时,索引才会被使用,遵循最左前缀集合
- 全文索引 (MyISAM) 在索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。(char,varchar,text)
- 空间索引 (MyISAM) 空间索引是对空间数据类型的字段建立的索引,mysql的空间数据类型有4种 分别为 GEOMETRY,POINT,LINESTRING,POLYGON, mysql使用spatial 进行扩展,声明空间索引的列必须为 not null
索引的设计原则
1.控制索引数量 (占用空间,影响数据库执行性能) 2.避免对经常更新的列建索引 (难以维护) 3.数据少的话,就别使用索引了 4。不同值多的建索引,像性别啊,建立索引用处也不大 5.对唯一列使用唯一索引,提高查询速度 6.在频繁分组的列上建索引
创建索引(在创建表的时候创建)
create table table_name (col_name data_type) [UNIQUE| FULLTEXT|SPATIAL] [INDEX|KEY] (index_name) (col_name [length]) | ASC| DESC]
1.创建普通索引
最基本的索引类型,没有唯一性之类的限制,其作用是加快对数据的访问速度 mysql> create table book -> ( -> bookid int not null, -> bookname varchar(255) not null, -> authors varchar(255) not null, -> info varchar(255) not null, -> comment varchar(255) not null, -> year_publication YEAR NOT NULL, -> INDEX(year_publication) -> ); Query OK, 0 rows affected (0.11 sec) mysql> show create table book G *************************** 1. row *************************** Table: book Create Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) NOT NULL, `comment` varchar(255) NOT NULL, `year_publication` year(4) NOT NULL, KEY `year_publication` (`year_publication`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
2.创建唯一索引
mysql> create table t1 -> ( -> id int not null, -> name char(30) not null, -> UNIQUE INDEX uniqIdx(id) -> ); Query OK, 0 rows affected (0.09 sec) mysql> show create table t1 G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, UNIQUE KEY `uniqIdx` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
3.创建单列索引
mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> INDEX SingleIdx(name) -> ); Query OK, 0 rows affected (0.11 sec) mysql> show create table t2 G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `SingleIdx` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
4.创建组合索引
mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index multildx(id,name,age) -> ); Query OK, 0 rows affected (0.11 sec) mysql> show create table t3 G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, KEY `multildx` (`id`,`name`,`age`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> explain select * from t3 where id=1 and name='joe' G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 type: ref possible_keys: multildx key: multildx key_len: 34 ref: const,const rows: 1 Extra: Using index condition 1 row in set (0.00 sec) mysql> 遵循最左前缀原则,索引行中 按 id/name/age 的顺序存放 可以有如下组合 id,name,age id,name id
5.创建全文索引
全文索引适合于大的数据集,只有MyISAM搜索引擎支持全文索引 只有在char,varchar和text列创建索引,不支持局部索引 mysql> create table t4 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> fulltext index FullTxtIdx(info) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> show create table t4 G *************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, FULLTEXT KEY `FullTxtIdx` (`info`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>
6.空间索引
空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空 mysql> create table t5( -> g geometry not null, -> spatial index spatIdx(g) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> show create table t5 G *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spatIdx` (`g`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
在已经存在的表上创建索引
ALTER TABLE table_name ADD [unique | FULLTEXT| SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC | DESC]
1.ALTER table 创建索引
mysql> show index from book G *************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) mysql> desc book; +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | bookid | int(11) | NO | | NULL | | | bookname | varchar(255) | NO | | NULL | | | authors | varchar(255) | NO | | NULL | | | info | varchar(255) | NO | | NULL | | | comment | varchar(255) | NO | | NULL | | | year_publication | year(4) | NO | MUL | NULL | | +------------------+--------------+------+-----+---------+-------+ mysql> alter table book ADD INDEX BkNameIdx (bookname(30)); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from book G *************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: book Non_unique: 1 Key_name: BkNameIdx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)
alter 系列 ALTER TABLE book ADD INDEX BkNameIdx (bookname(30)) ALTER TABLE book ADD UNIQUE index UniqidIdx( bookid ) 建立组合索引 ALTER TABLE book ADD INDEX BkAuAndInfoIdx (authors(30),info(50)) 建立全文索引 ALTER TABLE t6 ADD FULLTEXT infoFTIdx(info); 空间索引比较特殊,其拥有的字段类型有特有的类型 mysql> CREATE TABLE t7 (g GEOMETRY NOT NULL) ENGINE=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t7 G *************************** 1. row *************************** Table: t7 Non_unique: 1 Key_name: spatIdx Seq_in_index: 1 Column_name: g Collation: A Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: 1 row in set (0.00 sec)
使用create index 创建索引
基本语法 create [unique| fulltext|spatial] INDEX index_name on table_name (col_name[length,....]) [ASC|DESC] 新建数据表 mysql> create table book -> ( -> bookid INT NOT NULL, -> bookname VARCHAR(255) NOT NULL, -> authors VARCHAR(255) NOT NULL, -> info VARCHAR(255) NULL, -> comment VARCHAR(255) NULL, -> year_publication YEAR NOT NULL -> ); Query OK, 0 rows affected (0.18 sec) (与ALTER 语法一致) mysql> CREATE INDEX BkNameIdx ON book(bookname); ERROR 1061 (42000): Duplicate key name 'BkNameIdx' mysql> CREATE UNIQUE INDEX UniqueIdx ON book(bookid); Query OK, 0 rows affected, 1 warning (0.08 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> CREATE INDEX UniqueIdx ON book(comment); ERROR 1061 (42000): Duplicate key name 'UniqueIdx' mysql> Create INDEX bkAuAndInfoIdx on book(authors,info); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
索引删除
语法 ALTER TABLE table_name DROP INDEX index_name mysql> alter table book drop index BkNameIdx; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0