MYSQL索引优化小记

最新推荐文章于 2024-06-27 18:41:33 发布

niubility锐

最新推荐文章于 2024-06-27 18:41:33 发布

阅读量520

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_28325291/article/details/118977376

版权

mysql 专栏收录该内容

10 篇文章

订阅专栏

前言

最近断断续续学习某机构MYSQL优化视频，只看不复习记录纯属耍流氓。以下为参考别人思维导图及自己所记所记录

提示：记录学习，有误欢迎指正

一、什么是索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。（摘抄自网上）

简而言之：索引就是一种数据结构，方便快速查找数据，提升查询速度

二、索引类型

主键索引

特殊的唯一索引，也叫主索引，根据主键建立的索引，不允许重复，不允许空值

CREATE INDEX index_name ON table(column(length))

唯一索引

索引列的值必须唯一，允许有空值

CREATE UNIQUE INDEX indexName ON table(column(length))

普通索引

没有唯一性限制的索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

组合索引

最左优先，以最左边的为起点任何连续的索引都能匹配上
遇到范围查询(>、<、between、like%)后面的字段就会停止匹配
原理：先按照第一个字段排序，第一个字段相同就按照第二个排序

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

都只写了添加索引sql，需要删除索引、修改索引等sql的话自行百度吧，这些都简单

三、EXPLAIN执行计划（重要）

在真正的工作中我们优化sql 的依据主要是看执行计划。执行计划就是sql在mysql中sql解析器是如何解析sql进行查询的

语法

explain   <具体执行sql>

id

查询的序列号，id越大执行越先，顺序相同则从上往下顺序执行

select_type：

查询的类型，主要是区别普通查询、联合查询、子查询之类的复杂查询

SIMPLE：简单的 SELECT，不使用 UNIONE 或者子查询。
PRIMARY：最外层 SELECT。
UNION：第二层，在 SELECT 之后使用了 UNION。
DEPENDENT UNION: UNION 语句中的第二个 SELECT，依赖于外部子查询。
UNION RESULT: UNON 的结果。
SUBQUERY：子查询中的第一个 SELECT。
DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取決于外面的查询。
DERIVED：导出表的 SELECT (FROM 子句的子查询）

type（重要）

访问类型，保证查询至少达到range级别，最好能达到 ref

all：全表扫描。
const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
fulltext：进行全文索引检索。
index：全索引扫描。
index_merge：询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
index_ subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
rang：索引范围扫描。
ref: Join 语句中被驱动表索引引用的查询。
ref_ or_null：！与 ref 的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
system：系统表，表中只有一行数据。
unique_ subquery：子询中的返回结果字段组合是主键或唯一约束。

possible_keys

可能用到的索引

key(重要)

实际用到的索引

key_len

索引中使用的字节数（越短越好）

ref

显示索引的哪一列被使用了（最好是个常数）

rows

找到所需记录需要读取的行数（越少越好）

Extra

Using filesort：使用了文件排序，不好
Using temporary：使用了临时表，非常耗性能
Using index：使用了【覆盖索引】，效果不错
覆盖索引就是select的数据列只从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读数据文件，减少回表次数，提高查询效率
Using where｜impossible where：前者用到了where条件，后者没有