MYSQL（四）

一、索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

索引：

         字典的目录，便于数据的查找

默认没有索引的就是一张表，创建索引就是创建了额外的文件

索引存放数据如下：用了B+tree

索引查找数据：

从上往下每一层数据为2的n次方，查找数据只要找每层就可以找到

 

创建索引需要看索引的分类：

普通索引：比如创建了name列，那么只能查找name；普通索引只能帮助查找
唯一索引：能帮助查找，还能约束内容不允许重复，在列里面允许设置null；可以设置多个列各自唯一
还可以做联合唯一，创建了多个列，可以让这多个列设置联合唯一，比如创建a和b两个列，在a ， b中插入a ，c然后还可以在这两个列中插入a ,d
主键索引：能帮助查找，内容不允许重复，不允许为null，一张表只能有一个主键
        支持两个列放到一起作为主键
组合索引：
        多列共同组成一个索引，
普通的多列索引（name,email）没有约束
        联合唯一索引（name,email）有约束
全文索引：（分词）
        分词：对中文支持不够好，对逗号空格
        对中文匹配不够好
现在用的全文索引都是：
solr,lucence,sphix等自己搭建的

二、mysql创建索引

1、普通索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)
上面的ix_name 代表索引名，(name)代表name列

create index index_name on table_name(column_name)
index_name 是创建索引的名字，table_name表名  column_name 列名

drop index_name on table_name;

show index from table_name;
show create table tb4;     查看tb4表中的创建代码

create table tb4(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(32) not null,
extra text
)engine=innodb default charset=utf8
查看索引
show index from tb4;

mysql> explain select * from tb4 where name="bb";查看是否是索引查找
如果下面的type等于all表示的是全表扫描，如果是type等于ref 表示的是索引查找
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   
  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb4   | NULL       | ref  | ix_name       | ix_name | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

create unique index 索引名 on 表名(列名)

drop unique index 索引名 on 表名

insert into tb5 (name,email,extra) values("a","a@a","a"),("b","b@b","b");

mysql> explain select * from tb5 where name="a";
查看type  为const
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions    | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb5   | NULL  | const | name          | name | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


这里的const会查找的更快，比ref快
explain select * from tb5 where name="a"; type:const  速度几乎是最快的
explain select * from tb5 where name="a";  type ：all  全表扫描

组合索引

最左前缀才会走索引

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

create index ix_name_email on in3(name,email);

比如
tb7中创建了下面索引
index(name,email,ex):普通组合索引  
inique(name,email):组合唯一索引  name和email为唯一索引
primary key(nid,name):主键组合索引
select * from tb7 name=”aa”   >>走索引
select * from tb7 where name =”a” and email=”a@a”  >>走索引
注意：
select * from tb7 where email=”a@a”   >>这里不走索引因为没有按照最左前缀法则

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。