mysql订单表设计主键索引 mysql的主键索引怎么创建

转载

mob64ca1400bfa8 2023-10-15 06:53:18

文章标签 mysql订单表设计主键索引主键 mysql 全文索引 文章分类 MySQL 数据库

这是阿鲤对mysql的索引和事务的介绍，适合初学者；

1：索引的创建

2：索引的原理

索引，是提高数据库性能的必要之选，是物美价廉的；不用加内存，调sql；只需要create index就可以了。只要执行了正确的索引，查询速度会提高百倍，千倍；

常见的索引有：主键索引（primary key），唯一索引（unique），普通索引（index），全文索引（fulltext）

我们首先说一下这些索引的创建方法：

主键索引：

//在创建表的时候，直接在字段名后指定primary key
create table user1(id int primary key, name varchar(30));

//在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

//创建表以后再添加主键 
create table user3(id int, name varchar(30)); 
alter table user3 add primary key(id);

主键索引的特点：

1：一个表中，最多有一个主键索引，当然可以使符合主键

2：主键索引的效率高（主键不可重复）

3：创建主键索引的列，它的值不能为 null ，且不能重复

4：主键索引的列基本上是 int

唯一索引：

//在表定义时，在某列后直接指定unique唯一属性。 
create table user4(id int primary key, name varchar(30) unique); 

//创建表时，在表的后面指定某列或某几列为
unique create table user5(id int primary key, name varchar(30), unique(name)); 

//创建表以后再添加唯一键
create table user6(id int primary key, name varchar(30)）； 
alter table user6 add unique(name);

唯一索引的特点：

1：一个表中，可以有多个唯一索引

2：查询效率高

3：如果在某一列建立唯一索引，必须保证这列不能有重复数据

4：如果一个唯一索引上指定 not null ，等价于主键索引

普通索引：

create table user8(
    id int primary key, 
    name varchar(20), 
    email varchar(30), 
    index(name) --在表的定义最后，指定某列为索引 
);

create table user9(
    id int primary key, 
    name varchar(20), 
    email varchar(30)); 
    alter table user9 add index(name); --创建完表以后指定某列为普通索引 

create table user10(
    id int primary key, 
    name varchar(20), 
    email varchar(30)); -- 创建一个索引名为 idx_name 的索引 
   
create index idx_name on user10(name);

普通索引的特点：

1：一个表中可以有多个普通索引，普通索引在实际开发中用的比较多

2：如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引：

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。 MySQL 提供全文索引机制，但是有要求，要求

表的存储引擎必须是 MyISAM ，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用

sphinx 的中文版 (coreseek)。

CREATE TABLE articles ( 
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 、
    title VARCHAR(200), 
    body TEXT, 
    FULLTEXT (title,body) 
)engine=MyISAM;

如果你想看一次查询是否用到了索引：

我们可以使用explain查看一次检索，是否用到了索引

eg：

mysql> explain select * from articles where body like '%database%'\G *************************** 1. row ***************************
        id: 1 
    select_type: SIMPLE 
    table: articles 
    type: ALL 
    possible_keys: NULL 
    key: NULL <== key为null表示没有用到索引 
    key_len: NULL 
    ref: NULL 
    rows: 6 
    Extra: Using where 
1 row in set (0.00 sec)

我们看来索引的闯将方式，接下来让我们看看索引的原理把；

再看索引的原理之前，请你先看一下阿鲤写的这篇B树的文章，将有助于你了解下面的内容。

B树的介绍

首先让我们看看索引的底层数据结构：hash 和 B+树；

但是我们这里注重说B+树，因为mysql更多用的是B+树；如果你了解B树，那么B+树你就很容易理解了；

B+树相比B树多了一下两方面：

1：B+树相比B树去除了非叶子节点的荣誉的一个，他会把一个key对应一个孩子节点

2：B+树把每一层的叶子节点链接了起来，这样就可以提高范围查找的效率

提出一个问题：B+树里一个节点的大小应该是多少？

B+树为了提高IO效率，一般每个节点的大小为一个页的倍数最为合适；那么为什么呢？

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

mysql里的页：

首先Mysql的基本存储结构是页(记录都存在页里边)：各个数据页可以组成一个双向链表；而每个数据页中的记录又可以组成一个单向链表。每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where username='你好'这样没有进行任何优化的sql语句，默认会这样做：

1：定位到记录所在的页

2：需要遍历双向链表，找到所在的页

3：从所在的页内中查找相应的记录

4：由于不是根据主键查询，只能遍历所在页的单链表了

关于数据库的页，阿鲤在mysql基础中有提到过，链接就在该文章的第一行，大家可以看看。

那么B+树在索引匹配的时候是怎样的呢？

1：在B+树中比较查找；

2：最左匹配原则：针对联合索引，优先匹配左边的条件：

eg：如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

以上我们讲的主键索引就是聚合索引，那么什么是非聚合索引呢？