MySQL之索引
1.索引相关概述
索引就像书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度，这是对MySQL的优化#索引的作用
约束和加速查找#常见的索引
主键索引，普通索引，唯一索引，联合索引(多列)#有无索引的区别
无索引：需要从前往后一条一条的查询
有索引：创建索引的时候，就会创建一个额外的文件，查询的时候，先去这个额外的文件找，定好位置，再去原始表中直接查询2.主键索引
查询数据库，按主键查询速度是最快的，每个表只能有一个主键列，可以有多个普通索引列，主键列要求的所有内容必须唯一#建立主键索引方法
#1.在建表时，增加建立主键索引
mysql> create table student (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age tinyint(4) not null,
-> primary key(id),
-> key index_name(name));
Query OK, 0 rows affected (0.02 sec)
mysql> show index from student; #查看索引
#提示：
1.primary key(id) #在id字段创建主键
2.key index_name(name) #在name字段创建普通索引#2.如果在创建表的时候没有添加主键，使用alter添加
#alter添加语法：alter table 表名 add primary key(列名)
mysql> alter table test add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)#删除主键索引
#语法：alter table 表名 drop primary key;
alter table 表名 modify 列名 int,drop primary key;
mysql> alter table test drop primary key; #删除主键索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #查看时已经没有了
Empty set (0.00 sec)
mysql> alter table test add primary key(id); #再次添加
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test modify id int,drop primary key; #删除主键索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #查看
Empty set (0.00 sec)3.普通索引
#作用：加速查找
#语法格式：create index 索引名字 on 表名(列名)
alter table 表名 add index 索引名字(列名)
#查看帮助：help create index
mysql> help create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part:
col_name [(length)] [ASC | DESC]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
Normally, you create all indexes on a table at the time the table
itself is created with CREATE TABLE. See [HELP CREATE TABLE]. This
guideline is especially important for InnoDB tables, where the primary
key determines the physical layout of rows in the data file. CREATE
INDEX enables you to add indexes to existing tables.
CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html.
URL: https://dev.mysql.com/doc/refman/5.7/en/create-index.html#创建普通索引
#1.创建表的时候就创建普通索引
mysql> create table student (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age tinyint(4) not null,
-> primary key(id),
-> key index_name(name));
Query OK, 0 rows affected (0.02 sec)
mysql> show index from student; #查看索引#2.如果建表的时候没有创建普通索引，可以使用以下方式创建
mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index ix_age on student(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0#删除普通索引
#语法：alter table 表名 drop index 索引名字；
drop index 索引名字 on 表名；
#例子：删除name和age字段索引
mysql> alter table student drop index index_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index ix_age on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 04.唯一索引
#作用：加速查找和唯一约束
#语法：create unique index 索引名 on 表名(列名)
#创建唯一索引
#1.在创建表的时候就创建唯一索引
mysql> create table test (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age varchar(4) not null,
-> primary key(id),
-> unique index ix_name(name)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show index from test; #查看索引#2.如果建表时没有创建，使用create进行创建
mysql> create unique index ix_age on test(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #查看索引#删除唯一索引
#语法：drop index 索引名字 on 表名；
mysql> drop index ix_age on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 05.联合索引
#联合索引是将n个列组合成一个索引
#应用场景：需要对多个列进行查询的时候，如：where name='guo' and age='20';
#语法：create index 索引名 on 表名(列名1，列名2)
#例子：为name,dept一起创建索引
mysql> create index ix_name_dept on test(name,dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0#指定前n个字符创建索引
#例如1.指定前8个字符创建索引
mysql> create index ix_dept on test(dept(8));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | ix_dept | 1 | dept | A | 0 | 8 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+#最左前缀
#如果使用联合索引，如将name和dept组合索引时，当查询name字段时会使用索引，查询name和dept时会使用索引，查询dept时不会使用索引，因为当查左边那个才会使用索引
create index ix_name_dept on test(name,dept);
select * from test where name='guo'; #使用索引
select * from test where name='guo' and dept = 'computer'; #使用索引
select * from test where dept = 'computer' #不使用索引6.索引注意事项
1.避免使用select *
2.count(1)或count(列) 代替count(*)
3.创建表时尽量使用char代替varchar
4.表的字段顺序固定长度的字段优先
5.组合索引代替多个单列索引（经常使用多个条件查询时）
6.尽量使用短索引 （create index ix_title on tb(title(16));特殊的数据类型 text类型）
#思考：能不能给所有的列建索引呢？
#答案是否定的，因为索引不但会占用系统空间，更新数据库时还需要维护索引数据，因此并不是越多越好，例如数十到几百行的小表上无需建立索引7.执行计划explain
#使用explain判断有没有索引
#查看帮助：help explain
mysql> help explain
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: https://dev.mysql.com/doc/refman/5.7/en/explain.html#查看有没有创建索引
mysql> explain select * from test where name='wu';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)#上面的name字段没有创建索引，所以查询的时候就会进行全表扫描，就会慢，如果有索引的时候就很快
#创建索引再次查找
mysql> create index ix_name on test(name); #创建索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix_name
key: ix_name
key_len: 20
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)#当创建了索引之后，这次就扫描了一行
#参数详解
select_type：
查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
table：
正在访问的表名
type：
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from userinfo;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from userinfo where email = 'alex112@oldboy'
select * from userinfo where email = 'alex112@oldboy' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX ： 全索引扫描，对索引从头到尾找一遍
select nid from userinfo;
RANGE： 对索引列进行范围查找
select * from userinfo where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE： 合并索引，使用多个单列索引搜索
select * from userinfo where name = 'alex' or nid in (11,22,33);
REF： 根据索引查找一个或多个值
select * from userinfo where name = 'alex112';
EQ_REF： 连接时使用primary key 或 unique类型
select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;
CONST：常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select id from userinfo where id = 2 ;
SYSTEM：系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select id from userinfo where id = 1) as A;
possible_keys：可能使用的索引
key：真实使用的
key_len： MySQL中使用索引字节长度
rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra：
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020-03-02 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
