MySQL学习笔记简单分享(4)

最新推荐文章于 2025-04-11 14:26:02 发布

ZLS_Pri

最新推荐文章于 2025-04-11 14:26:02 发布

阅读量999

点赞数 21

文章标签： mysql 学习笔记

本文链接：https://blog.youkuaiyun.com/ZLS_Pri/article/details/135942317

版权

本文详细介绍了MySQL索引的概念、不同类型、创建与使用规则，以及索引对SQL性能的影响。重点讲解了索引优化速度提升、索引机制、索引分类和选择性，同时涵盖了SQL性能优化技巧如慢查询日志和explain执行计划分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql索引

index索引是帮助MySQL高效获取数据的数据结构（有序）。

在MySQL中，除数据外，DB系统还维护着满足特定查找算法的数据结构，这些结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引优化速度

极大提高了数据库性能(速度)

创建索引

create index emp_index(索引名) on emp(empno) 表名(列)

索引会占用存储空间(空间换时间),查询效率的提高只对创建了索引的列有效

创建索引后，根据索引字段查询速度大大提升

索引机制

当没有索引时

查询一张表的一条记录时，会从表的第一条记录开始一次比较记录，进行全表扫描

select * from emp where id = 1; 即使id=1在第一条数据中查到了，仍然会进行全表扫描，因为无法确定下面的记录是否还有id=1的记录

创建了索引

该字段形成一个索引的数据结构(二叉树、B树、B+树...)，查询id时从中间字段依次比较，时间复杂度从o(n)变成了o(log2^n)

比较30次，覆盖的记录范围为2^30-1个记录

索引的优点

1.提高数据检索的效率,降低数据库的IO成本；

2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的代价

1.磁盘占用，index存储需要空间

2.在DML操作的同时会对索引进行维护，会对DML语句效率有所影响

虽然索引有所代价，但是在项目中，DML操作只占很小的一部分，查询的占比极大

索引结构

常见索引结构

1.B+Tree索引：最常见的索引类型,大部分引擎都支持B+树索引

2.Hash索引底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效，不支持范围查询

3.R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少

4.Full-text(全文索引)是一种通过建立倒排索引,快速匹配文档的方式，类似于Lucene,Solr,ES

索引 InnoDB MyISAM Memory

B+tree索引支持支持支持

Hash 索引不支持不支持支持

R-tree索引不支持支持不支持

Full-text 5.6版本之后支持支持不支持

InnoDB存储引擎为何选择使用B+tree索引结构

1.相对于二叉树，层级更少，搜索效率高;

2.对于B tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页(对应一个节点)中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;

3.相对于hash索引(只支持等值匹配)，B+ tree结构支持范围匹配和排序

索引分类

1.主键索引：主键自动的就是一个主键索引

2.唯一索引(unique):避免同一表中同列数据有重复，唯一约束会自动为这个字段添加唯一索引

3.普通索引(index)：快速定位特定数据

4.全文索引(fulltext)：适用于MyISAM Mysql自带，但是不好用，一般用Solr，ElasticSearch(ES)，查找的文本中的关键词，而不是比较索引的值

在InnoDB存储引擎中，根据索引的存储形式，索引又分为以下两种：

1.聚集索引(Clustered Index)：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 该索引必须有，且只能有一个

2.二级索引(Secondary Index)：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键该索引可以有多个

InnoDB的指针占6个字节，主键(值)即使为bigint也为8个字节

聚集索引选取规则

1.如果表有主键，那么主键默认就是聚集索引

2.如果没有主键，那么选第一个创建的unique索引为聚集索引

3.如果表没有主键或合适的unique索引，则InnoDB会默认生成一个rowid作为聚集索引。

回表查询

例：一张表emp，id为主键，name创建unique约束(索引)，那么id字段为聚集索引，name字段为二级索引

查询：select * from emp where name = 'Aram';

首先根据name字段(二级索引)查询到该1Aram所对应的主键，再根据主键（聚集索引）查询到主键对应的行数据，需要扫描两个索引这个过程为回表查询。

索引的语法

create [unique|fulltext] index index_name on table_name(index_col,...)

添加唯一索引

create (unique) index id_index on 表(列)

alter table 表 add index id_index(列);

删除索引

drop index id_index on 表

alter table 表 drop primary key

修改索引：删除再新建

查询索引

show index from 表;

show indexs from 表;

show keys from 表;

desc 表名;很粗糙

索引的使用(重点)

联合索引 ：1、最左前缀法则：如果索引了多列（联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

2、如果最左边的列不存在，则不经过索引查询；

3、最左边的列存在，右边的列不全都存在或者不存在时，还是经过索引查询！

范围查询：在使用了范围条件(>,<等)时，右边的索引列就会失效；在业务允许的情况下使用>=，能规避此情况；

运算：不要在索引列进行运算操作，否则索引将会失效

字符串类型：如果字符串类型的字段查询时没加''、""时，触发类型隐式转换，索引失效

模糊查询：使用模糊查询，如果仅仅是尾部或中间模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or连接的条件：用or分割开的条件,其中一个列没有索引,那么涉及的索引都不会被用到。

数据分布影响：如果MySQL评估运行一条sql语句时使用索引比全表更慢，则不使用索引。

SQL提示：SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

用法：use index: explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';(use只是建议，mysql会进行评估，不一定会使用指定的索引)

ignore index: explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index: explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引 (避免回表查询)：尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到) ,减少select *。

例：select id,name,job where name='张三' ;

已知id为主键，如果优化该sql？

给name、job建立联合索引

前缀索引：当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

用法：create index index_name on table_name(column(n)) //与普通索引创建相比多了(n)截取长度sub part

前缀长度(n) ：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

查看选择性：select count(distinct substring(索引字段,从第几位开始截取,截取长度))/count(*) from table_name;

单列索引与联合索引单列索引：即一个索引只包含单个列。联合索引:即一个索引包含了多个列。在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

索引创建规则

1.较频繁的作为查询条件的字段应该创建索引;

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

3.更新非常频繁的字段不适合建索引

4.不会在where子句中出现的字段不需要创建索引

1.针对于数据量较大，且查询比较频繁的表建立索引。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。

4.如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。

5.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。

6.要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。

7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL性能优化

SQL执行频率

show global/session status like "Com______" 查询各语句执行频率

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf)中配置：

开启MySQL慢日志查询开关

slow_query_log=1

设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

long_query_time=2

将针对在慢查询日志中执行效率低的语句进行优化

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

select @@have_profiling; //查看是否支持

select @@profiling;//查看是否打开

set profiling = 1;

查看每一条SQL的耗时基本情况 show profiles;

查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id;

查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序

explain执行计划EXPLAIN 执行计划各字段含义

id ：select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下; id不同,值越大,越先执行)

select_type ：表示SELECT的类型,常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY (主查询,即外层的查询) 、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等

type ：表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all

一般使用主键或唯一索引进行查询type为const，用一般索引则为ref

possible_key ：显示可能应用在这张表上的索引，一个或多个

Key ：实际使用的索引,如果为NULL,则没有使用索引。

Key_len ：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows ：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered ：表示返回结果的行数占需读取行数的百分比,filtered的值越大越好。