文章目录
- MySQL索引优化总结
- select_type
- Extra与type详细说明
- 索引的CURD
- **索引的创建**
- 索引的删除
- 索引的查看
- 索引的类型
- 具体例子
MySQL索引优化总结
mysql> explain select * from t_student_course where stu_id = 2;
– 结果：
id: 1
select_type: SIMPLE – 查询类型（简单查询,联合查询,子查询）
table: user – 显示这一行的数据是关于哪张表的
type: range – 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys: birthday – 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key: birthday – 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。
key_len: 4 – 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好
ref: const – 显示哪个字段或常数与key一起被使用。
rows: 1 – 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
Extra: Using where; Using index – 执行状态说明,这里可以看到的坏的例子是Using temporary和Using
select_type
- simple 简单select(不使用union或子查询)
- primary 最外面的select
- union union中的第二个或后面的select语句
- dependent union union中的第二个或后面的select语句,取决于外面的查询
- union result union的结果。
- subquery 子查询中的第一个select
- dependent subquery 子查询中的第一个select,取决于外面的查询
- derived 导出表的select(from子句的子查询)
Extra与type详细说明
- Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
- Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
- Range checked for each Record（index map:#）:没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
- Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
- Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
- Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
- Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序
- system 表只有一行：system表。这是const连接类型的特殊情况
- const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
- eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
- ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如,利用最左边前缀）时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好+
- range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况+
- index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好,因为索引一般小于表数据）+
- ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免
其中type:
如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。
测试的sql
索引的CURD
索引的创建
- ALTER TABLE
适用于表创建完毕之后再添加
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,当前的索引名就是该字段名;
ALTER TABLE `table_name` ADD UNIQUE (`column_list`)
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`)
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)- CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引
--例,只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)- 另外,还可以在建表时添加
PRIMARY KEY (`id`),
UNIQUE KEY `unique1` (`tel`), -- 索引名称,可要可不要,不要就是和列名一样
KEY `index1` (`name`),
FULLTEXT KEY `intro` (`describe`)索引的删除
DROP INDEX `index_name` ON `talbe_name`
ALTER TABLE `table_name` DROP INDEX `index_name`
-- 这两句都是等价的,都是删除掉table_name中的索引index_name;
ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除索引的查看
show index from tablename索引的类型
UNIQUE唯一索引
不可以出现相同的值,可以有NULL值
INDEX普通索引
允许出现相同的索引内容
PRIMARY KEY主键索引
不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引
fulltext index 全文索引
上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求
create table t_student_info
(
id bigint auto_increment comment 'id'
primary key,
stu_name varchar(50) null comment '姓名',
gender tinyint default 0 null comment '性别',
birthday date null comment '出生日期',
tel varchar(20) null comment '电话号码',
is_delete tinyint default 0 null comment '删除状态 0：正常：1：已删除',
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
update_datetime datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改时间'
)
comment '学生信息表';
create table t_course
(
id bigint auto_increment comment '课程id'
primary key,
course_name varchar(100) null comment '课程名',
is_delete tinyint default 0 null comment '删除状态 0：正常；1：已删除
'
)
comment '课程表';
create table t_student_course
(
stu_id bigint not null comment '学生id',
course_id bigint not null comment '课程id'
)
comment '学生课程关联表';
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (1, '语文', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (2, '数学', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (3, '英语', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (4, '物理', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (5, '生物', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (6, '化学', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (7, '历史', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (8, '地理', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (9, '政治', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (10, '计算机', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (11, '体育', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (12, '美术', 0);
INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (13, '音乐', 0);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 1);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 2);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 3);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (3, 3);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (3, 4);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (4, 3);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (4, 5);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (5, 6);
INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (5, 7);
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (1, '王老大', 0, '2019-04-21', '133', 0, '2019-04-21 04:30:38', '2019-04-21 04:30:43');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (2, '李老二', 0, '2019-04-22', '135', 1, '2019-04-21 12:32:50', '2019-04-21 17:19:40');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (3, '张三', 0, '2019-04-14', '145', 0, '2019-04-21 12:32:50', '2019-04-21 12:32:50');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (4, '李四', 0, '2019-04-07', '167', 0, '2019-04-21 12:32:50', '2019-04-21 12:32:50');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (5, '王五', 0, '2019-04-07', '7654', 1, '2019-04-21 12:32:50', '2019-04-21 17:19:40');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (6, '马六', 0, '2019-04-28', '987', 0, '2019-04-21 12:45:05', '2019-04-21 12:45:05');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (7, '陈七
', 0, '2019-04-01', '789', 1, '2019-04-21 12:45:50', '2019-04-21 17:19:40');
INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (8, '赵八', 0, '2019-04-25', '098', 0, '2019-04-21 12:46:50', '2019-04-21 12:46:50');具体例子
mysql> show index from t_student_info \G;
*************************** 1. row ***************************
Table: t_student_info
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL可以看出t_student_info表只有一个PRIMARY索引，
mysql> explain select * from t_student_info where id = 5 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student_info
partitions: NULL
type: const --注意这里
possible_keys: PRIMARY
key: PRIMARY --注意这里
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student_info
partitions: NULL
type: ALL --注意这里
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
filtered: 12.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)添加is_delete列索引后的变化
mysql> alter table t_student_info add index `is_delete` (`is_delete`);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student_info
partitions: NULL
type: ref
possible_keys: is_delete
key: is_delete
key_len: 2
ref: const
rows: 5
filtered: 12.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)如果再添加索引 student_name
alter table t_student_info add index `student_name` (`stu_name`);结果为
mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student_info
partitions: NULL
type: ref
possible_keys: is_delete,student_name
key: student_name
key_len: 203
ref: const
rows: 1
filtered: 62.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)下面看联合索引
联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是
key index (a,b,c).可以支持a | a,b| a,b,c3种组合进行查找，但不支持b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student_info
partitions: NULL
type: ref
possible_keys: is_delete_stu_name
key: is_delete_stu_name
key_len: 205
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)mysql> explain
-> select s.stu_name, c.course_name
-> from t_student_info s
-> left join t_student_course sc on s.id = sc.stu_id
-> left join t_course c on c.id = sc.course_id \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s
partitions: NULL
type: index
possible_keys: NULL
key: is_delete_stu_name
key_len: 205
ref: NULL
rows: 8
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: sc
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: index_demo.sc.course_id
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)先写这些，自己火候不够，等我再读一遍《高性能mysql》再来补充
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
