Mysql索引

Mysql索引

索引的概念

是一个排序的列表，存储着索引值和这个值所对应的物理地址
无须对整个表进行扫描，通过物理地址就可以找到所需数据
是表中一列或者若干列值排序的方法
索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容

索引的副作用：需要额外的磁盘空间

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率

当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度

可以降低数据库的IO成本，并且还可以降低数据库的排序成本

通过创建唯一性索引保证数据表数据的唯一性

可以加快表与表之间的连接

在使用分组和排序时，可大大减少分组和排序时间

创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会
先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位

记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。

经常与其他表进行连接的表，在连接字段上应该建立索引。

唯一性太差的字段不适合建立索引。

更新太频繁地字段不适合创建索引。

经常出现在where子句中的字段，特别是大表的字段，应该建立素引。
select name,score from ky20 where id=1

索引应该建在选择性高的字段上。

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

MySQL的优化哪些字段/场景适合创建索引，哪些不适合
1、小字段
2、唯一性强的字段
3、更新不频繁，但查询率很高的字段
4、表记录超过300+行
5、主键、外键、唯一键

索引的分类

1、普通索引

mysql -u root -p
create database info;
use info;
 create table ky20 (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);


insert into ky20 values (1,'zhangsan','123','111111','nanjing','this is vip');
insert into ky20
values (4,'lisi',1234',444444','nanjing','this is normal');
insert into ky20 values (2,'wangwu','12345','222222','benjing','this is normal');
insert into ky20 values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert into ky20 values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from ky20;

创建索引
create index phone_index on ky20 (phone);
查看索引
show create table ky20;

第二种方法添加索引(修改表的方式)
alter table ky20 add index id_index (id);

第三种方法添加索引
 create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table test;

2、唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须僵一。添加唯一键将自动创建唯一索引。

(1)直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名（列名）；

例：UNIQUE
create unique index address_index on member (address);
create unique index name_index on member (name);
show create table member;

(2)·修改表方式创建

(3)·创建表的时候指定

CREATE TABLE 表名（字段1数据类型，字段2数据型[，··.]，UNIQUE 索引名（列名）)；
create table amd2 (id int,name varchar(20),unique id index (id));
show create table amd2;
create table test2 (id int,name varchar(40),age int(5),primary key (id));

3、主键索引

是一种特殊的唯一索引，必须指定为"PRIMARY KEY"。
个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引。
创建表的时候指定
CREATE TABLE表名([.··]，PRIMARY KEY(列名))；
例：create table testl(id int primary key,name varchar(20))；
create table test2 (id int,name varchar(20),primary key (id));
show create table testl;
show create table test2;
·修改表方式创建
ALTER TABLE表名ADD PRIMARY KEY(列名)；

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

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select语句查询时where条件使用的字段顺序必须和组合素引中的排序一致，否则索引将不会生效。

CREATE TABLE表名（列名1数据类型，列名2数据类型，列名3数据类型，INDEX索引名
(列名1，列名2，列名3))：
se1ect*from表名where列名1=‘…·AND列名2=’…·AND列名3=‘…’：
create table amdl (id int not null,name varchar(20),cardid varchar(20),index index amd
(id,name））;
show create table amdl;
insert into amdl values (1,‘zhangsan’,‘123123’);
insert into amd1 values(1,‘zhangsan’,‘123123’);

select id,name from zhao;

从左往右的排序，触发索引，（id，name）反过来虽然也可以查到，但是不会触发索引。

5、全文索引(ULLTEXT)

适合在进行模糊查询的时候使用，可用于在一筒文章中检素文本信息。
在ySQL5.6版本以前FULLTEXT素引仅可用于MyISAM引擎，在5.6版本之后innodb引擎也支持
FULLTEXT索引。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。每个表只允许有一个全文索引。

·直接创建索引
CREATE FULLTEXT INDEX素引名ON表名（列名）：
例：select+from member;
create fulltext index remark index on member (remark);

·修改表方式创建
ALTER TABLE表名ADD FULLTEXT索引名（列名）：
1
·创建表的时候指定索引
CREATE TABLE表名（字段1数据类型[，··】，FULLTEXT索素引名（列名）)：
数据类型可以为CHAR、VARCHAR或者TEXT
·使用全文索引查询
SELECT+FROM表名WHERE MATCH(列名)AGAINST(‘查询内容’)：

查看索引：show index from ky20\G;

show keys from ky20\G

小结：
索引分为：
普通索引：针对所有字段，没有特殊的需求/规则
唯一索引：针对唯一性的字段，仅允许出现一次空值
组合索引（多列/多字段组合形式的索引）
全文索引(varchar char text)
主键索引：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引


创建索引：
在创建表的时候，直接指定index
alter修改表结构的时候，进行add添加index
直接创建索引index
Ps:主键索引 一》直接创建主键即可

6、删除索引

·直接册别除索引
DROP INDEX索引名ON表名：
例：drop index name_index on member;
●修改表方式别除索引
ALTER TABLE表名DROP INDEX索引名：
例：alter table member drop index id_index;
show index from member;
●别除主键索引
ALTER TABLE表名DROP PRIMARY

·直接册别除索引
DROP INDEX索引名ON表名：
例：drop index name_index on member;
●修改表方式别除索引
ALTER TABLE表名DROP INDEX索引名：
例：alter table member drop index id_index;
show index from member;
●别除主键索引
ALTER TABLE表名DROP PRIMARY