[TOC]
MySQL索引及执行计划
什么是索引？
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
给指定字段创建索引，索引会将该字段中所有数据进行排序
索引不是越多越好,也不是每个字段都必须加索引，如果每个字段都有索引，那加索引就没意义了，都是一样的效率
创建索引，会占用磁盘空间
索引的排序方式(类型)
-
BTREE:B+树索引
-
HASH：HASH索引
-
FULLTEXT：全文索引
-
RTREE：R树索引
BTtree算法
以下三种btree算法统称为Btree
- BTree
三路Btree
- 根节点
- 枝节点
- 叶子节点
### 查询顺序
1.先去根节点找到指定要查询数的范围内查询
2.然后去对应范围的枝节点找到指定要查询数的范围内查询
3.最后去对应范围的叶子节点找到指定要查询的数
- B+Tree
1）在叶子节点添加了相邻节点的指针（这些优化就是为了减少磁盘IO）
2）优化了范围查询，提升了范围查询的执行效率
- B*Tree
索引管理
索引分类
-
主键索引（聚簇索引）
特性：唯一且非空
- 联合索引
-
唯一键索引
特性：唯一 可以为空
-
前缀索引
-
联合索引
-
-
普通索引
特性：可以不唯一，可以为空
- 前缀索引
- 联合索引
索引的增删查
表数据准备
## 建表
root@localhost:hg>create table student(
-> id int,
-> name varchar(10),
-> age tinyint,
-> gender enum('m','f'),
-> phone char(11)
-> );
## 查看表结构
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 插入数据
root@localhost:hg>insert into student values
-> (1,'zls',18,'m',11111111111),
-> (2,'a',12,'f',11111131111),
-> (3,'b',13,'f',11241131111),
-> (4,'a',14,'m',11121131111);
## 查看表数据
root@localhost:hg>select * from student;
+------+------+------+--------+-------------+
| id | name | age | gender | phone |
+------+------+------+--------+-------------+
| 1 | zls | 18 | m | 11111111111 |
| 2 | a | 12 | f | 11111131111 |
| 3 | b | 13 | f | 11241131111 |
| 4 | a | 14 | m | 11121131111 |
+------+------+------+--------+-------------+主键索引
## 増
## 添加主键索引语法：
alter table 表名 add primary key(字段); （主键索引不能指定索引名）
# 例：
root@localhost:hg>alter table student add primary key(id);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
# 删
## 删除主键索引语法：
#（注意）：当在建表创建主键时 要是加了Extra（自增） 就无法删除主键 需要先删除Extra
alter 表名 test drop primary key;
# 例：
root@localhost:hg>alter table student drop primary key;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
#查
# 用desc查看索引
desc 表名;
查看Key字段 以下就是对应的索引类型
PRI ：主键索引
UNI ：唯一键索引
MUL ：普通索引
## 查看索引（***必须先有索引才能查***）：
show index from 表名;
# 例：
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 查不到???救救孩子吧
root@localhost:hg>show index from student;
Empty set (0.00 sec)
## 你猜为什么查不到？？？
root@localhost:hg>alter table student add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings:
## 你再查试试
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
唯一键索引
# 增
## 添加唯一键索引（***要求：添加唯一键字段的数据不能有重复的***）
alter table 表名 add unique key 自定义索引名(要做成索引的字段名);
# 去重统计：
1.先统计要创建唯一键索引字段总共有多少行
root@localhost:hg>select count(name) from student;
+-------------+
| count(name) |
+-------------+
| 4 |
+-------------+
2.再统计，去重后，该字段有多少行
root@localhost:hg>select count(distinct(name)) from student;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 3 |
+-----------------------+
# ？？？怎么报错了？？？
root@localhost:hg>alter table student add unique key uni_name(name);
ERROR 1062 (23000): Duplicate entry 'a' for key 'uni_name'
##哥们看看要求吧
root@localhost:hg>select count(age) from student;
+------------+
| count(age) |
+------------+
| 4 |
+------------+
root@localhost:hg>select count(distinct(age)) from student;
+----------------------+
| count(distinct(age)) |
+----------------------+
| 4 |
+----------------------+
## 1.不给索引起名
root@localhost:hg>alter table student add unique key(age);
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | age | 1 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 2.给索引起名
root@localhost:hg>alter table student add unique key uni_phone(phone);
root@localhost:hg>desc student;
root@localhost:hg>show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | age | 1 | age | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 0 | uni_phone | 1 | phone | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#删
## 删除索引
# 删除唯一索引和普通索引语法：
alter table 表名 drop index 索引名;
alter table 表名 drop key 索引名;
root@localhost:hg>alter table student drop index age;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | uni_phone | 1 | phone | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key uni_phone;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
普通索引
# 増
## 创建普通索引（DDL）
# 语法：
alter table 表名 add 自定义索引名(要做成索引的字段名);
# 1.不起名，以字段名，命名
root@localhost:hg>alter table student add index(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# 2.起名
root@localhost:hg>alter table student add key idx_name(name);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 删
root@localhost:hg>alter table student drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
前缀索引
## 増
## 添加主键索引语法：
alter table 表名 add primary key(字段); （主键索引不能指定索引名）
# 例：
root@localhost:hg>alter table student add primary key(id);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
# 删
## 删除主键索引语法：
#（注意）：当在建表创建主键时 要是加了Extra（自增） 就无法删除主键 需要先删除Extra
alter 表名 test drop primary key;
# 例：
root@localhost:hg>alter table student drop primary key;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
#查
# 用desc查看索引
desc 表名;
查看Key字段 以下就是对应的索引类型
PRI ：主键索引
UNI ：唯一键索引
MUL ：普通索引
## 查看索引（***必须先有索引才能查***）：
show index from 表名;
# 例：
root@localhost:hg>desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 查不到???救救孩子吧
root@localhost:hg>show index from student;
Empty set (0.00 sec)
## 你猜为什么查不到？？？
root@localhost:hg>alter table student add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings:
## 你再查试试
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |# 增
## 添加唯一键索引（***要求：添加唯一键字段的数据不能有重复的***）
alter table 表名 add unique key 自定义索引名(要做成索引的字段名);
# 去重统计：
1.先统计要创建唯一键索引字段总共有多少行
root@localhost:hg>select count(name) from student;
+-------------+
| count(name) |
+-------------+
| 4 |
+-------------+
2.再统计，去重后，该字段有多少行
root@localhost:hg>select count(distinct(name)) from student;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 3 |
+-----------------------+
# ？？？怎么报错了？？？
root@localhost:hg>alter table student add unique key uni_name(name);
ERROR 1062 (23000): Duplicate entry 'a' for key 'uni_name'
##哥们看看要求吧
root@localhost:hg>select count(age) from student;
+------------+
| count(age) |
+------------+
| 4 |
+------------+
root@localhost:hg>select count(distinct(age)) from student;
+----------------------+
| count(distinct(age)) |
+----------------------+
| 4 |
+----------------------+
## 1.不给索引起名
root@localhost:hg>alter table student add unique key(age);
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | age | 1 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 2.给索引起名
root@localhost:hg>alter table student add unique key uni_phone(phone);
root@localhost:hg>desc student;
root@localhost:hg>show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | age | 1 | age | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 0 | uni_phone | 1 | phone | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#删
## 删除索引
# 删除唯一索引和普通索引语法：
alter table 表名 drop index 索引名;
alter table 表名 drop key 索引名;
root@localhost:hg>alter table student drop index age;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | uni_phone | 1 | phone | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key uni_phone;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+普通索引
# 増
## 创建普通索引（DDL）
# 语法：
alter table 表名 add 自定义索引名(要做成索引的字段名);
# 1.不起名，以字段名，命名
root@localhost:hg>alter table student add index(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# 2.起名
root@localhost:hg>alter table student add key idx_name(name);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 删
root@localhost:hg>alter table student drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
前缀索引
# 増
## 创建普通索引（DDL）
# 语法：
alter table 表名 add 自定义索引名(要做成索引的字段名);
# 1.不起名，以字段名，命名
root@localhost:hg>alter table student add index(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# 2.起名
root@localhost:hg>alter table student add key idx_name(name);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 删
root@localhost:hg>alter table student drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+什么是前缀索引？
创建索引时，按照指定数值对列进行排序
为什么使用前缀索引？
1）给大列创建索引时，可以减少排序时间，提升创建索引速度
2）insert，update，delete插入数据时，提升写入速度
3）提升查询速率
## 普通前缀索引的创建语法：
alter table 表名 add 自定义索引名(要做成索引的字段名(数据长度));
#例：
root@localhost:hg>alter table student add key idx_name(name(2));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | 2 | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
## 唯一键前缀索引创建
#例：
root@localhost:hg>alter table student add unique key uni_phone(phone(7));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 0 | uni_phone | 1 | phone | A | 4 | 7 | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | 2 | NULL | YES | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# 删
root@localhost:hg>alter table student drop key uni_phone;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | 2 | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
root@localhost:hg>alter table student drop key idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+联合索引
## 主键联合索引
root@localhost:hg>alter table student add primary key(id,name);
ERROR 1068 (42000): Multiple primary key defined
## 又报错了一个表只能有一个主键
#先删除
root@localhost:hg>alter table student drop primary key;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
##创建
root@localhost:hg>alter table student add primary key(id,name);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
##查看
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
###普通索引创建联合索引
root@localhost:hg>alter table student add index idx_all(gender,age,phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
KEY `idx_all` (`gender`,`age`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
### 唯一键联合索引创建
root@localhost:hg>alter table student add unique key uni_all(gender,age,phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
UNIQUE KEY `uni_all` (`gender`,`age`,`phone`),
KEY `idx_all` (`gender`,`age`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
## 查询规则
alter table student add index idx_all(a,b,c,d);
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
网站访问速度慢，如何排错，如何解决？
## 主键联合索引
root@localhost:hg>alter table student add primary key(id,name);
ERROR 1068 (42000): Multiple primary key defined
## 又报错了一个表只能有一个主键
#先删除
root@localhost:hg>alter table student drop primary key;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
##创建
root@localhost:hg>alter table student add primary key(id,name);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
##查看
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
###普通索引创建联合索引
root@localhost:hg>alter table student add index idx_all(gender,age,phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
KEY `idx_all` (`gender`,`age`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
### 唯一键联合索引创建
root@localhost:hg>alter table student add unique key uni_all(gender,age,phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:hg>show create table student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
UNIQUE KEY `uni_all` (`gender`,`age`,`phone`),
KEY `idx_all` (`gender`,`age`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
## 查询规则
alter table student add index idx_all(a,b,c,d);
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引网络问题
设备配置
代码bug
应用（nginx、lb、proxy）
数据库慢查询
使用explain分析SQL语句
慢查询，开启慢查询日志，记录执行速度慢的SQL语句
找出执行慢的SQL语句后，使用explain进行分析
再查询语句前面加上explain
root@localhost [ljy] > explain select * from student;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | student | index | NULL | idx_all | 33 | NULL | 4 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
root@localhost [ljy] > explain select * from world.city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
root@localhost [world] > select * from city where district='shanghai';
+------+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+----------+-------------+----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
+------+----------+-------------+----------+------------+
type：SQL语句的扫描，有没有走索引
扫描类型：(越往下效率越高)
- 全表扫描
- ALL
root@localhost [world] > explain select * from city where district='shanghai';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
- 索引扫描
- index 全索引扫描
root@localhost [world] > explain select countrycode from city;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | city | index | NULL | CountryCode | 3 | NULL | 4188 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
- range 范围扫描(一般来说一条sql语句能达到range级别,证明扫描级别很高了)
root@localhost [world] > explain select * from city where population < 100;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
- ref
root@localhost [world] > explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| 1 | PRIMARY | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
| 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const | 274 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
- eq_ref:只有在连表查询时，使用join on才能出现
- const/system(同级别):主键精确查询时
- null:空表查询
root@localhost [world] > explain select * from city where population>1000000000000000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+没有走索引的原因？
1）该字段没有创建索引
2）创建索引了，但是没有走
# 1.使用select * 查询数据，不接where条件（优化：谁写杀谁）
root@localhost [world] > explain select * from city;
# 2.查询结果集大于25%
root@localhost [world] > explain select * from city where population > 1000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | idx_pop | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
## 优化：使用limit分页查询方式优化结果集
root@localhost [world] > explain select * from city where population > 1000 limit 60;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 4067 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
# 3.使用字段列做计算（优化：谁写杀谁）
root@localhost [world] > explain select * from city where id-1=12;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
# 4.隐式转换导致索引失效
创建表时，数据类型和查询时不一致导致，不走索引
字符串类型：加引号
root@localhost [world] >explain select * from user where phone='21111111111';
数字类型：不加引号
root@localhost [world] >explain select * from user where phone=21111111111;
# 5.使用like模糊查询时，%在前面的
root@localhost [world] > explain select * from city where countrycode like '%H';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
root@localhost [world] > explain select * from city where countrycode like 'H%';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 22 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
如果需求是必须%在前面，不要使用MySQL
请使用elastsearch
# 6.使用 <> 或者 not in查询数据（不等于）（优化：使用limit分页查询方式优化结果集）
root@localhost [world] > explain select * from city where countrycode not in ('CHN','USA');
root@localhost [world] > explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | CountryCode | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
root@localhost [world] > explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA' limit 10;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 3439 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
# 7.联合索引，where条件后面没有按照创建索引的顺序查询
1) 按顺序查询
2) 用户行为分析(数据分析)
# 8.索引损坏、失效（优化：删除索引，重建时使用前缀索引）索引创建原则
1）索引不是越多越好
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
2）有必要添加索引的列，如何选择索引？
删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
# 1.优先选择，唯一键索引（效率仅次于主键）
# 2.给经常需要排序、分组这种查询的列，创建联合索引
# 3.普通索引尽量使用前缀索引
