MySQL基础——索引

索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调 sql，查询速度就可能提高百倍干倍。

索引的原理

1. 没有索引为什么会慢?因为全表扫描
2. 使用索引为什么会快?形成一个索引的数据结构，比如二叉树
3. 索引的代价
   1.磁盘占用
   2.对 dml(update delete insert) 语句的效率影响
4. 一般还是会建立索引，因为在实际项目中，有超过 90% 的操作都是查询操作

索引的类型

1. 主键索引，主键自动的为主索引（类型Primary key）
2. 唯一索引（UNIQUE）
3. 普通索引（INDEX）
4. 全文索引（FULLTEXT）【适用于MyISAM】，一般开发，不使用 mysql 自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch (ES)

create table t1 (
    id int primary key, --主键,同时也是索引,称为主键索引.
    name varchar(32));

create table t2(
    id int unique, -- id是唯一的，同时也是索引，称为unique索引.
    name varchar(32));

索引使用

1. 添加索引（建小表测试 id , name）：
   create [UNIQUE] index index_name on tbl_name (col_name [(length)] [ASC | DESC], ...);
alter table table_name ADD INDEX [index_ name](index_col_name, ...)
2. 添加主键（索引）： ALTER TABLE 表名 ADD PRIMARY KEY(列名, ...);
3. 删除索引：DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;
4. 删除主键索引比较特别：alter table t_b drop primary key;

添加索引

// 创建测试表
create table temp03(
    id int,
    name varchar(32)
);
// 查询表的索引
show indexes from temp03;
// 创建索引
// 创建唯一索引
create unique index id_index on temp03(id);
// 创建普通索引
create index id_index on temp03(id);
-- 如何选择？
-- 如果选择某列的值，是不重复的，则选择添加唯一索引，否则添加普通索引
// 创建普通索引方法 2
alter table temp03 add index id_index(id);

// 添加主键索引
alter table temp03 add primary key (id);

删除索引

// 删除索引
drop index id_index on temp03;
// 删除主键索引
alter table temp03 drop primary key;
-- 修改索引：先删除索引，然后添加新索引

// 查询索引
show index from temp03;
show indexes from temp03;
show keys from temp03;
desc temp03;

创建索引练习

/*
要求:
1.创建一张订单表order(id，商品名, 订购人，数量).
  要求id号为主键，请使用 2 种方式来创建主键.(提示:为练习方便，可以是order1,order2)
*/
// 方式 1
create table order1 (
    id int primary key,
    name varchar(50),
    user int,
    num int
);
// 方式 2
create table order2 (
    id int,
    name varchar(50),
    user int,
    num int
);
alter table order2 add primary key(id);


/*
要求:
1.创建一张特价菜谱表menu(id号，菜谱名,厨师，点餐人身份证，价格).
  要求id号为主键，点餐人身份证是unique
  请使用两种方式来创建unique.(提示:为练习方便，可以是menu1 , menu2)
*/
// 1
create table menu1(
    id int primary key,
    card_id char(18) unique,
    price double
);
// 2
create table menu2(
     id int primary key,
     card_id char(18),
     price double
);
create unique index card_id_index on menu2(card_id);

/*
要求:
1．创建一张运动员表sportman(id号，名字，特长).
   要求id号为主键，名字为普通索引，请使用 2 种方式来创建索引(提示:为练习方便，可以是不同表名sportman1 , sportman2)
*/
// 1
create table sportman1(
    id int primary key,
    name varchar(32)
);
alter table sportman1 add index name_index(name);
// 2
create table sportman2(
    id int primary key,
    name varchar(32)
);
create index name_index on sportman2(name);

创建索引的规则

1. 较频繁的作为查询条件字段应该创建索引select * from emp where empno = 1
2. 唯—性太差的字段不适合单独创建索引，即使频繁作为查询条件 select * from emp where sex ='男'
3. 更新非常频繁的字段不适合创建索引 select * from emp where logincount =1
4. 不会出现在 WHERE 子句中的字段不该创建索引