MySQL索引

索引的概念

数据库索引

1、是一个排序的列表，存储着索引值和这个值所对应的物理地址
2、无需对整个表进行扫描，通过物理地址就可以找到所需数据；
3、是表中一列或者若干列值排序的方法；
4、需要额外的磁盘空间；
索引需要的额外的磁盘空间伴随着表直接存在
阈值：300行以上的才建立索引，不然浪费磁盘空间

索引的作用/优势

1、数据库利用各种快速定位技术，能够大大加快查询速率；
2、当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度；
3、可以降低数据库的IO成本，并且还可以降低数据库的排序成本；
4、通过创建唯一性索引保证数据表数据的唯一性
IO：输入（写入、更改数据），输出（读取数据）；
5、可以加快表与表之间的连接；
6、在使用分组和排序时，可大大减少分组和排序时间

索引的分类

1）、普通索引

最基本的索引类型，没有唯一性之类的限制
1、创建表时创建
create table test(……index id_index(id));
2、直接创建
create index name_index on test(name);
3、修改表结构方式创建
alter table test add index id_index(id);

2）、唯一性索引

与“普通索引”基本相同
与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
1、创建表时创建
create table test(……unique index id_index(id));
2、直接创建
create unique index name_index on test(name);
3、修改表结构方式创建
alter table test add unique index id_index(id);

3）、主键索引

是一种特殊的唯一索引，指定为“primary key”
一个表只能有一个主键，不允许有空值（非空且唯一）
1、创建表时创建
create table test(……primary key(id));
2、修改表时创建
alter table test add primary key(id);

4）、组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引
最左原则，从左往右依次执行
创建组合索引：create table test(……index ff(id,name,score));

5）、全文索引

1、创建表时创建全文索引
create table test(……fulltext (name));
2、在已存在的表上创建全文索引
create fulltext index name_index on test(name)；
3、通过SQL语句alter table创建全文索引
alter table test add fulltext index name_index(score);

创建索引的原则

1、表的主键、外键必须有索引；
2、数据量超过300行的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、唯一性太差的字段不适合建立索引；
5、更新太频繁地字段不适合创建索引；
6、经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引；
7、索引应该建在选择性高的字段上；
8、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

查看索引的方法

show index from 表名；
show index from table_name\G；（\G：竖着显示）
show key form 表名；
show key form 表名\G;

删除索引的方法

1、drop index index_name on table_name;
2、alter table table_name drop index index_name;索引

索引的分类和创建

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;

mysql> use test;
Database changed
mysql> create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
Query OK, 0 rows affected (0.01 sec)
mysql> desc member;

mysql> insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
Query OK, 1 row affected (0.00 sec)
mysql> insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
Query OK, 1 row affected (0.00 sec)
mysql> insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
Query OK, 1 row affected (0.01 sec)
mysql> insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
Query OK, 1 row affected (0.00 sec)
mysql> insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
Query OK, 1 row affected (0.01 sec)
mysql> select * from member;

直接创建索引

mysql> create index phone_index on member (phone);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table member;

mysql> select phone from member;

修改表方式创建

mysql> alter table member add index id_inex (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select id from member;

mysql> select id,name from member;

创建表的时候指定索引

mysql> create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test;

直接创建唯一索取

mysql> create unique index index_phone on member (phone);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create unique index index_name on member (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table member;

创建表的时候指定

mysql> create table test2 (id int,name varchar(20),unique id_index(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test2;

mysql> create table test3 (id int,name varchar(40),age int(5),primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test3;

主键索引

创建表的时候指定

mysql> create table txt1(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.01 sec)
mysql> create table txt2(id int,name varchar(30),primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table txt1;

mysql> show create table txt2;

组合索引

mysql> insert into test values(1,'zhangsan','123123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test where name='zhangsan'and id=1;

全文索引

直接创建索引

mysql> create fulltext index index_remak on member (remark);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用全文索引查询

mysql> select * from member where match(remark) against('this is vip');