MySQL（六）

---

表连接和索引

---

表连接
表的连接分为内连接和外连接。内连接就是利用 where 子句对两种表形成的笛卡儿积进行筛选，内连接也是开发过程中使用最多的连接查询。

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

外连接分为左外连接和右外连接。即：如果联合查询，左侧的表完全显示就是左外连接，右侧的表完全显示就是右外连接。

select 字段名 from 表1 left join 表2 on 连接条件;
select 字段名 from 表1 right join 表2 on 连接条件;

索引
索引特性：提高数据库的性能，索引可以说是物美价廉的好东西。不用加内存，不用改程序，不用调 sql ，只要执行正确的create index，查询速度就能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO，所以索引的价值在于提高一个海量数据的检索速度。

1. 常见索引分为：主键索引（primary key），唯一索引（unique），普通索引（index），全文索引（fulltext）。
2. 索引的说明
   （1）占用磁盘空间；
   （2）当添加一条记录，除了添加到表中，还要维护二叉树，速度有影响，但不大；
   （3）当我们添加一个索引，不能够解决所有的查询问题，需要分别给字段建立索引；
   （4）索引是以空间换时间。
3. 创建索引
   （1）创建主键索引有三种方式，分别如下：

在创建表的时候直接在字段名后面指定主键

create table user1(id int primary key, name varchar(32));

在创建表的最后指定某列或某几列为主键索引

create table user2(id int, name varchar(32), primary key(id));

创建好表以后再追加主键

create table user3(id int, name varchar(32));
alter table user3 add primary key(id);

主键索引的特点：

• 一个表中，最多有一个主键索引，当然可以使用复合主键；
• 主键索引的效率高，但主键不可重复；
• 创建主键索引的列，值不能为空，且不能重复；
• 主键索引的列基本上都是整型（int）。
  （2）唯一索引的创建也有三种方式，分别如下：
  在表定义的时候，在某列后直接指定唯一属性

create table user4(id int primary key, name varchar(32) unique);

创建表时，在表的后面指定某列或某几列为唯一属性

create table user5(id int primary key, name varchar(32), unique(name));

创建好表以后再追加

create table user6(id int primary key, name varchar(32));
alter table user6 add unique(name);

唯一键索引的特点：

• 一个表中，可以有多个唯一索引；
• 查询效率高；
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据；
• 如果一个唯一索引上指定不为空，等价于主键索引。
  （3）普通索引的创建也有三种方式，分别如下：
  在表的定义最后，指定某列为索引

create table user8(id int primary key,
	name varchar(32),
	email varchar(32),
	index(name));

创建完表以后指定某列为普通索引

create table user9(id int primary key,
	name varchar(32),
	email varchar(32));

alter table user9 add index(name);

创建一个索引名为 idx_name 的索引

create table user10(id int primary key,
	name varchar(32),
	email varchar(32));

create index idx_name on user10(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多；
• 如果某列需要创建索引，但是该列有重复的值，则我们应该使用普通索引。
  （4）全文索引的创建
  当对文章字段或有大量文字的字段进行检索时，就会使用全文检索。MySQL提供全文索引机制，但是有要求，要求表的引擎必须是MyISAM,而且默认的全文索引支持英文，不支持中文。如果对中文进行检索的话，可以使用sphinx的中文版(coreseek)。

4. 查询索引分三种方式
   第一种：show keys from 表名
   第二种：show index from 表名
   第三种：desc 表名
5. 删除索引分三种方式
   第一种–删除主键索引：alter table 表名 drop primary;
   第二种–删除其他索引：alter table 表名 drop index 索引名;
   第三种：drop index 索引名 on 表名
6. 索引创建的原则
   （1）比较频繁作为查询条件的字段应该创建索引；
   （2）唯一性太差的字段不适合单独做索引，即使频繁作为查询条件；
   （3）更新非常频繁的字段不适合创建索引；
   （4）不会出现在 where 子句中的字段不该创建索引。