索引管理

索引管理

创建索引多 反而会降低搜索效率 会占用磁盘空间

索引的类型（算法）

1.BTREE:B树索引（Btree，B+tree，B*tree）

2.HASH：HASH索引

3.FULLTEXT：全文索引

4.RTREE：R树索引

BTREE 精确查询

BTREE范围查询

B+TREE 精确与范围查询

B+TREE比BTREE

1.在叶子节点，添加了相邻节点的指针

2.优化了，范围查询

索引分类

1.主键索引（聚集索引）

2.普通索引

• 联合索引
• 前缀索引

3.唯一索引（唯一键）

【主键：唯一 、 非空 primary key】

【唯一键：唯一 、 可以为空 unique key】

【primary key = unique key + not null】

索引创建规则

1.一个字段可以创建一个索引

2.多个字段可以创建一个索引

3.多个字段可以创建多个索引，但是不能是主键

mysql> alter table student add index idx_all(name,age,gender);

mysql> desc student;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | NO   | PRI | 0       |       |
| name     | varchar(30)   | YES  | MUL | NULL    |       |
| age      | tinyint(4)    | YES  |     | NULL    |       |
| gender   | enum('m','f') | YES  |     | NULL    |       |
| cometime | datetime      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
#这样看看不出来

mysql> show index from student;
+---------+------------+----------+--------------+-------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| student |          0 | PRIMARY  |            1 | id          |
| student |          1 | idx_all  |            1 | name        |
| student |          1 | idx_all  |            2 | age         |
| student |          1 | idx_all  |            3 | gender      |
+---------+------------+----------+--------------+-------------+
#这样看就能看到idx_all  三个字段创建了一个索引

索引操作

1.创建主键索引 primary key

mysql> desc student;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | YES  |     | NULL    |       |
| name     | varchar(30)   | YES  |     | NULL    |       |
| age      | tinyint(4)    | YES  |     | NULL    |       |
| gender   | enum('m','f') | YES  |     | NULL    |       |
| cometime | datetime      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

mysql> alter table student add primary key pri(id);

mysql> desc student;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | NO   | PRI | 0       |       |
| name     | varchar(30)   | YES  |     | NULL    |       |
| age      | tinyint(4)    | YES  |     | NULL    |       |
| gender   | enum('m','f') | YES  |     | NULL    |       |
| cometime | datetime      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

2.创建普通索引 index

mysql> desc student2;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(20)         | NO   |     | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender   | enum('f','m')       | YES  |     | NULL              |                |
| cometime | datetime            | YES  |     | CURRENT_TIMESTAMP |                |
+----------+---------------------+------+-----+-------------------+----------------+

mysql> alter table student2 add index idx_name(name);

mysql> desc student2;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(20)         | NO   | MUL | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender   | enum('f','m')       | YES  |     | NULL              |                |
| cometime | datetime            | YES  |     | CURRENT_TIMESTAMP |                |
+----------+---------------------+------+-----+-------------------+----------------+

mysql> show create table student2;
| student2 | CREATE TABLE `student2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
  `name` varchar(20) NOT NULL COMMENT '学生姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('f','m') DEFAULT NULL COMMENT '学生性别',
  `cometime` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='入学时间' 

3.创建唯一索引 unique key

mysql> alter table student add unique key uni_age(age);

mysql> desc student;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | NO   | PRI | 0       |       |
| name     | varchar(30)   | YES  | MUL | NULL    |       |
| age      | tinyint(4)    | YES  | UNI | NULL    |       |
| gender   | enum('m','f') | YES  |     | NULL    |       |
| cometime | datetime      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

当一列里有重复值的时候就不能创建唯一索引 比如：name里面有好多msy 那name就不能创唯一索引
如何判断，某列是否可以创建唯一索引？

distinct()
count()

#总共9行 去重之后还有2行 说明重复了 不能创建
mysql> select count(name) from student4;
+-------------+
| count(name) |
+-------------+
|           9 |
+-------------+

mysql> select count(distinct(name)) from student4;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                     2 |
+-----------------------+


#一共239行 去重之后还有239行 说明没重复的 可以创建
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
|         239 |
+-------------+

mysql> select count(distinct(name)) from country;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                   239 |
+-----------------------+

mysql> alter table country add unique key uni_name(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.查看索引

mysql> show index from student2;
mysql> desc student2;
mysql> show create table student2;

5.删除索引

mysql> alter table student2 drop index idx_all;
mysql> alter table student2 drop index uni_age;

前缀索引

当你创建索引的时候BTREE默认会对你的数据进行排序 但是在数据量特别大的情况下排序 会非常的满

这个时候可以用到前缀索引 给他个排序值

给表中数据量大的列，创建前缀索引

#创建前缀索引
只对前三个字符进行排序
减少创建和查询时间
mysql> alter table student2 add index idx_name(name(3));

1.避免对大列建索引
2.如果有，就使用前缀索引

联合索引

前面说索引会占用磁盘空间 所以少创建索引 如果很多列需要索引 那就创建联合索引

id 名字 性别 长相 身材 身高 QQ 微信 手机 收入 ...

mysql> create table xiangqin(id int,name varchar(10),gender enum('0','1'),face varchar(10),height int,weight int,salary int,hobby varchar(10),QQ varchar(11));

按照在意程度做一下联合索引
mysql> alter table xiangqin add index idx_all(gender,salary,face,weight);
+----------+------------+----------+--------------+-------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name |
+----------+------------+----------+--------------+-------------+
| xiangqin |          1 | idx_all  |            1 | gender      |
| xiangqin |          1 | idx_all  |            2 | salary      |
| xiangqin |          1 | idx_all  |            3 | face        |
| xiangqin |          1 | idx_all  |            3 | weight      |
+----------+------------+----------+--------------+-------------+

联合索引，走索引情况

上面按照A B C D 的顺序创建了 后面就按这个顺序去查

A:gender B:salary C:face D:weight

全部走索引：

select * from xiangqin where A;

select * from xiangqin where A B;

select * from xiangqin where A B C;

select * from xiangqin where A B C D;

部分走索引：

select * from xiangqin where A C D; （A 走 ,C D 不走）

select * from xiangqin where A B D; （A B 走 ,D 不走）

select * from xiangqin where A D; （A 走 ,D 不走）

全不走索引

select * from xiangqin where B C D;

select * from xiangqin where C D;

select * from xiangqin where D;