MySQL全面介绍

最新推荐文章于 2024-05-03 08:00:00 发布

Jerry--

最新推荐文章于 2024-05-03 08:00:00 发布

阅读量1.2k

点赞数

文章标签： mysql 数据库全文检索

本文链接：https://blog.csdn.net/weixin_44786507/article/details/122128212

版权

MySQL介绍

索引介绍

官方介绍索引是帮助MySQL高效获取数据的数据结构，更通俗的说，数据库索引好比一本书的目录，能加快数据库的查询速度
- 方便查找–检索
- 索引查询内容–覆盖索引
- 排序
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）
通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等等，没有特别说明默认都是使用B+树结构组织。

索引的优势和劣势

优势：

提高数据检索效率，降低数据库IO成本，类似于书的目录 --检索
通过索引对数据进行排序，降低数据排序的成本，降低cpu的消耗 --排序
- 被索引的列会自动排序，包括【单列索引】和【组合索引】，只是后者的排序要复杂一些
- 若按照索引列的顺序排序，对应order by语句来说，效率会提高很多
- where 索引列在存储引擎层处理
- 覆盖索引不需要回表查询

劣势：

索引会占据磁盘空间
**索引虽然会提高效率，但是会降低更新表的效率，**比如每次对表进行增删改操作，MySQL不仅要保存数据，还需要保存或者更新对应的索引文件

索引分类

单列索引

普通索引：MySQL中基本的索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。add index
唯一索引：索引列中的值必须是唯一的，但是允许为空值，add unique index
主键索引：是一种特殊的唯一索引，不允许有空值。 pk

组合索引

在表中的多个字段组合上创建的索引 add index(col1,col2)
组合索引的使用，需要遵循最左前缀原则
一般情况下，建议使用组合索引代替单列索引（主键索引除外，具体原因后面知识点讲解

全文索引

只有在MyISAM引擎、InnoDB(5.6以后) 才能使用，而且只能在 CAHR、VACHAR、TEXT字段类型上使用 fulltext

优先级最高先执行不会执行其他索引

存储引擎决定执行一个索引

空间索引

很少用到

索引的使用

创建索引

单列索引之普通索引

CREATE INDEX index_name	ON TABLE(column(length));
ALTER TABLE table_name ADD INDEX index_name(column(length));

单列索引之唯一索引

CREATE UNIQUE INDEX index_name ON TABLE(column(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_named(column(length));

单列索引之全文索引

CREATE FULLTEXT INDEX index_name ON TABLE(column(length));
ALTER TABLE table_name ADD FULLTEXT index_named(column(length));

组合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10));

删除索引

DROP INDEX index_name ON TABLE;

查看索引

SHOW INDEX FROM TABLE	\G;

索引原理分析

B树和B+树

B树的高度一般都是在2~4 这个高度，树的高度直接影响IO读写的次数
如果是三层树结构—支撑的数据可以到达20G，四层数据结构之称的数据可以达到几十T

B和B+的区别

最大区别在于非叶子节点是否存储数据的问题。

B树是非叶子节点和叶子节点都会存储数据

B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的，索引列 order by

##非聚簇索引（MyISAM）

B+树叶子节点只会存储数据行（数据文件）的指针，简单来说数据和索引不在一起，就是非聚簇索引
非聚簇索引包含主键索引和辅助索引都会存储指针的值

分为主键索引和辅助索引

####主键和辅助索引

两个索引结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。

省去两张图

聚簇索引（InnoDB）

主键索引的叶子节点会存储数据行，也就是说数据和索引是在一起的，这就是聚簇索引
辅助索引只会存储主键值
如果没有主键，使用唯一索引建立聚簇索引；如果没有唯一索引，MySQL会按照一定规则创建聚簇索引

####主键索引

1.InnoDB要求表必须有主键，如果没有显式指定，MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，MySQL自动为InnoDB表生成一个隐含字段作为主键，类型为长整型

辅助索引

2.第二个与MyISAM索引的不同是 InnoDB 的辅助索引 data域存储相应记录主键的值而不是地址。换句话说InnoDB的所有辅助索引都引用主键作为data域

聚簇索引这种实现方式使得主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

select * fromd user whered name = ‘Alice’ 回表查询检索两次非主键索引 — pk — 索引 — 数据

select id,name from user whered name = ‘Alice’ 不需要回表，在辅助索引树上就可以查询到了覆盖索引（多用组合索引）

哪些情况需要创建索引

主键自动建立唯一索引
频繁为查询条件的字段
多表关联查询中，关联字段应该创建索引 on 两边都需要创建索引
查询中排序的字段
频繁查找字段覆盖索引
查询中统计或者分组字段，应该创建索引 group by

哪些情况不需要创建索引

表记录太少
经常进行增删改查操作的表
频繁更新的字段
where条件里使用频率不高的字段

为什么使用组合索引

MySQL 创建组合索引的规则是：

首先会对组合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name,cid 这样的一种排序规则。

为了节省MySQL索引存储空间以及提升搜索性能，可以建立组合索引（能使用组合索引就不使用单例索引）

例如：

创建组合索引（相当于建立了col1、col2、col3三个索引）

ALTER TABLE 'table_name' ADD INDEX index_name(col1、col2、col3);

一颗索引树上创建了三个索引：省空间

三颗索引树上分别创建一个索引

更容易实现覆盖索引

###使用遵循最左前缀原则

前缀索引 like a% 不可以写成%a
从左向右匹配直到遇到范围查询 > < between like

建立组合索引 (a、b、c、d)

where a=1 and b=1 and c>3 and d=1

到c>3停止了所以d用不到索引了

怎么办？

把索引顺序改成（a、b、d、c）

索引失效

查看执行计划

介绍

MySQL提供了一个EXPLAIN命令****，可以对SELECT 语句的执行计划进行分析****，并且输出SELECT执行的详细信息，以供开发人员针对性优化.

使用explain命令查看一个SQL语句的执行计划，查看该SQL语句有没有使用上索引，有没有做全表查询.

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用.

EXPALIN命令十分简单，在SELECT语句前加上explain就好了

参数说明

explain出来的信息有十列，分别是

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

案例表

--用户表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);

--部门表
create table tdep(
id int primary key,
name varchar(100)
);

--地址表
create table taddr(
id int primary key,
addr varchar(100)
);

--普通索引
alter table tuser add index idx_indep(dep);

--唯一索引
alter table tuser add unique index idx_loginname(loginname);

--组合索引
alter table tuser add index idx_name_age_sex(name,age,sex);

--全文索引
alter table taddr add fulltext ft_addr(addr);

id

每个SELECT语句都会自动分配的一个唯一标识符
表示查询中操作表的顺序，有三种情况：
- id相同：执行顺序由上到下
- id不同：如果是子查询，id号会自增；id越大，优先级越高
- id相同的不同的同时存在
id列为null表示这是一个结果集，不需要使用它来进行查询

selecet_type（重点）

查询类型，主要用于区别普通查询、联合查询（union、union all）、子查询等复杂查询。

simple 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple。

mysql> explain select * from tuser;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows |  | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tuser | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |  | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

####primary

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary，且只有一个

mysql> explain select (select name from tuser) from tuser;
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | | Extra       |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | tuser | NULL       | index | NULL          | idx_indep        | 5       | NULL |    1 |  Using index |
|  2 | SUBQUERY    | tuser | NULL       | index | NULL          | idx_name_age_sex | 413     | NULL |    1 | | Using index |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+

####subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是 subquery

mysql> explain select * from tuser where id = (select max(id) from tuser);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows |  | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | tuser | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |    | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+

其余的了解下：

table

显示的查询表名，如果查询使用了别名，那么显示别名
如果不涉及对数据表的操作，那么显示null
如果显示为尖括号括起来的就表示这个是临时表，后边的N是执行计划中的id，表示结果来自这个查询产生
如果显示为尖括号括起来的**<union M,N>**，与前者类似也是一个临时表，表示结果来自与union查询的id为M，N的结果集

type（重要）

从好到差顺序：

system、const、eq_ref、fulltext、ref_of_null、unique_subquery、index_subquery、range、index_merge、index、ALL

除了ALL之外，其他的type都可以使用索引，除了index_merge之外，其他的type只可以用到一个索引

注意事项

最少要索引使用到range级别

system

表中只有一行数据或者是空表

system和const的查询效率都很高，不过一般都是可遇不可求的。

const（重要）

使用唯一索引或者主键，返回记录一定是一行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

eq_ref（重要）

关键字连接字段主键或者唯一索引

此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的操作通常是 “=”

查询效率较高

ref（重要）

针对非唯一索引，使用等值（=）查询非主键，或者使用到了最左前缀规则索引的查询

fulltext（MySQL用的较少）

ref_of_null

与ref方法类似，只是增加了null值得比较。实际用的不多

####unique_subquery

用于where中的in形式子查询，子查询返回不重复唯一值

####index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重

range（重要）

索引范围扫描，常见于使用

> < is null betwwen in like 等运算符的查询中

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见 and or 的条件使用了不同的索引

官方排序这个在ref_or_null 后，但是实际上由于读取所有索引，性能可能大部分时间都不如range

index（重要）

关键子：条件是出现在索引树中的节点，可能没有完全匹配索引

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询，可以使用索引排序或者分组的查询

ALL（重要）

这个就是全表扫描数据文件，然后在server层进行过滤返回符合要求的记录

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询