语法：
创建索引
CREATE [UNIQUE] INDEX indexName ON mytable(username(length));
删除:
DROP INDEX [indexName] ON mytable;
查看：
SHOW INDEX FROM table_name;
使用ALTER 命令添加和删除索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
创建两张表
user
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键',
`user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
`create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) DEFAULT NULL COMMENT '修改时间',
`version` int(11) DEFAULT 1 COMMENT '版本号',
`deleted` tinyint(2) DEFAULT 0 COMMENT '是否删除(0否1是)',
`cid` bigint(20) DEFAULT NULL COMMENT 'card表的主键',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
初始数据
INSERT INTO user (id, user_name, age, email,create_time,update_time,cid) VALUES
(1, 'Jone', 18, 'test1@baomidou.com', '2021-06-26 18:08:56', '2021-06-26 18:08:56',1),
(2, 'Jack', 20, 'test2@baomidou.com', '2023-06-26 18:08:56', '2023-06-26 18:08:56',2),
(3, 'Tom', 28, 'test3@baomidou.com', '2020-06-26 18:08:56', '2020-06-26 18:08:56',3);
card
CREATE TABLE `card` (
`cid` bigint(20) NOT NULL COMMENT '主键',
`cname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '卡片名',
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `card` VALUES (1, '健身卡');
INSERT INTO `card` VALUES (2, 'KTV卡');
INSERT INTO `card` VALUES (3, '校园卡');
查看是否有索引
show INDEX from user
image.png
每个字段的解释：
1.Table:表的名称。
2.Non_unique:如果索引不能包括重复词，则为0。如果可以，则为1。
3.Key_name:索引的名称。
4.Seq_in_index:索引中的列序列号，从1开始。
5.Column_name:列名称。
6.Collation:列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
7.Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
8.Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
9.Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。
10.Null:如果列含有NULL，则含有YES。如果没有，则该列含有NO。
11.Index_type:用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
12.Comment:备注。
13.index_comment:为索引创建时提供了一个注释属性的索引的任何评论
哪些字段需要建立索引呢？
1、主键自动建立索引
2、频繁作为查询条件的字段
3、查询中与其他表关联的字段，外键关系建立索引
4、排序字段
5、查询中统计或者分组字段
哪些字段不需要建立索引呢？
1、表记录少
2、经常增删改的表(因为mysql不仅要保存数据，还有保存索引文件)
3、数据重复并分布平均的表字段。
Explain
1、如何使用Explain
explain的使用很简单，就是在select 语句之前增加 explain关键字就ok了。MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL。比如这样：
#explain + sql
explain select * from table where id = 1;
2、Explain执行计划能做什么？
确定表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
3、执行计划
我们在Navicat里随便执行一个查询语句，看看都会返回哪些内容。
explain select * from user where id = 1;
执行后的结果不是查询的数据而是执行计划的解释，一共有id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra这些字段，每个都代表不同的含义，下面详细介绍。当然每个mysql的版本不一样就可能就会不一样。
image.png
这是我当前的mysql版本
image.png
id
select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
三种情况：
- id相同时，执行顺序由上至下
explain select * from user , card where user.cid=card.cid
image.png
- 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
explain select * from user where cid = (select cid from card where cid = 3)
image.png
- id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
explain select user.* from (select c.cid from card c where cid = 3) c1, user where c1.cid = user.cid
image.png
select_type
select查询的类型
1、简单类型
SIMPLE：最简单的select查询，就是查询中不包含子查询或者union，表里如一。
explain select * from user where id = 1;
image.png
2、嵌套类型
PRIMARY：查询中若包含任何复杂的子查询，最外层的部分被标记为PRIMARY。
explain select * from user where cid = (select cid from card where cid = 3)
image.png
SUBQUERY：出现在select或者where后面中的子查询被标记为SUBQUERY。
explain select * from user where cid = (select cid from card where cid = 3)
image.png
DERIVED：在from后面的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
explain select user.* from (select c.cid from card c where cid = 3) c1, user where c1.cid = user.cid
image.png
3、组合类型
UNION：若第二个select出现在union之后，则标记为UNION；如果UNION包含在from 子句的子查询中，外层select将被标记为DERIVED。
UNION RESULT：从 UNION表获取结果的select。
explain select id,user_name from user union all select cid,cname from card;
image.png
table
显示这一行的数据时关于哪张表的。
但是有两个特殊的情况：
（1）当 from 子句中有子查询（派生表）时，那table就会以 < derivedN > 格式出现。因为此时查询所依赖的表是一个我们派生出来的表，即依赖一个 id 为 N 的子查询的。
image.png
（2）当使用 union 时，UNION RESULT 的 table 值为 <union1,2>，1和2表示参与 union 的 select 行id。
image.png
type
访问类型，表示MySQL查询是如何访问数据的。最好到最差依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说记住这几个就行：system > const > eq_ref > ref >range > index > ALL。查询至少达到range级别，最好能达到ref。下面也就介绍这几个
1. system
表里只有一行记录，这个属于const类型的特例，一行数据平时很少出现，可以忽略不计。
2. const
表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个常量。
explain select * from (select * from user where id = 1) user1;
image.png
3. eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
explain select user.* from user left join card on user.cid = card.cid;
image.png
4. ref
非唯一索引扫描，返回匹配某个单独值的所有行。
#创建普通索引 user表的user_name
create index index_user_name on user(user_name);
explain select * from user where user_name = "Tom";
image.png
5. range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
explain select * from user where id > 1;
image.png
6. index
Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
explain select id,user_name from user
image.png
id是主键索引，user_name是普通索引。
7. all
全表扫描，意味MySQL需要从头到尾去查找所需要的行。通常情况下就需要增加索引来进行优化了。
explain select * from user
image.png
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出，但并不一定被查询实际使用。
key
实际使用的索引，如果为NULL则没有使用索引。查询中若使用覆盖索引，则该索引只出现在key列表中。
explain select id,user_name from user
image.png
可以看到possible_keys为null，但key为index_user_name。
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，根据表定义计算而得，不是通过表内检索出的。
explain select * from user where user_name = "Tom";
image.png
可以看到该索引的长度为93。那是怎么计算出来的呢？
在建表的时候user_name的长度为30，字符编码使用utf8,utf8字符集占用3个字节。
那key的长度就为key_len=30 * 3=90。只有90啊，还有三个字节从哪里来呢？
可以发现user_name字段的定义为DEFAULT NULL，所以MySQL需要1个字节来标识NULL。
还有就是user_name是varchar类型，是变长字段所以需要+2。
key_len=30 * 3+1+2=93
user_name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
ref
显示索引的那一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。
explain select * from user left join card on user.cid = card.cid where user_name ="Tom"
image.png
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
#将之前创建的index_user_name索引删除
DROP INDEX index_user_name ON user;
#执行
explain select * from user left join card on user.cid = card.cid where user_name ="Tom"
image.png
rows为：6
#再创建索引
create index index_user_name on user(user_name);
explain select * from user left join card on user.cid = card.cid where user_name ="Tom"
image.png
rows为：2
Extra
包含不适合在其他列中显示但十分重要的额外信息：Using filesort，Using temporary，Using index，Using where Using index，``
1、Using filesort
MySQL对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序” 。这种情况下一般也是要考虑使用索引来优化的。
explain select * from user order by create_time
image.png
记得上面写到哪些上面时候需要建索引其中有一条为：排序字段。
2、Using temporary
mysql需要创建一张临时表来处理查询，常见于order by 和 group by。出现这种情况要考虑使用索引来优化的。
explain select * from user GROUP by deleted
image.png
3、Using index
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高，如果同时出现Using where，表明索引被用来执行索引键值的查找，如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
explain select user_name from user where user_name = 'Tom'
image.png
explain select user_name from user
image.png
覆盖索引（Covering Index）就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所建的索引覆盖。
4、using where
表明使用了where过滤
5、using join buffer
使用了连接缓存
6、impossible where
where字句的值总是false,不能用来获取任何值。
explain select user_name from user where user_name = 'Tom' and user_name = 'Jack'
image.png
7、
如果你mysql版本变为了5.7.30
image.png
就多了两个字段:
partitions和fiterred
网友评论