Mysql---索引优化

最新推荐文章于 2025-03-07 17:37:31 发布

DY_浅陌初心

最新推荐文章于 2025-03-07 17:37:31 发布

阅读量98

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_41653753/article/details/112967278

版权

1.SQL慢（执行时间长/等待时间长）

sql语句写的不好
索引失效
关联查询太多join
服务器调优及各个参数设置

2.常见的Join

2.1.sql的执行顺序

from （笛卡尔积）
on （主表保留）
join （不符合On也添加）
where （非聚合非别名）
group by （改变对表引用）
having （可作用与分组后）
select
distinct
union
order by （可使用别名）
limit (rows offset)

2.2 join图

①A B共有 inner join

select<select_list> from TableA A inner join TableB B on A.key=B.key;

②A B共有A全有 left join

select<select_list> from TableA A left join TableB B on A.key=B.key;

③A B共有B全有 right join

select<select_list> from TableA A right join TableB B on A.key=B.key;

④A B共有A独有 left join ....where ...B is null

select<select_list> from TableA A left join TableB B on A.key=B.key where B.key is null;

⑤A B 共有B独有 right join ... where ...A is null

select<select_list> from TableA A right join TableB B on A.key=B.key where A.key is null;

⑥A B全有 full outrt join

select<select_list> from TableA A full outer join TableB B on A.key=B.key;

⑦A B独有 full outer join where A is null or B is null

select<select_list> from TableA A full outer join TableB B on A.key=B.key where A.key is null or B.key is null;

3.索引

3.1.定义

索引是帮助Mysql高效获取数据的数据结构，可以简单的理解为“排好序的快速查找数据结构”。对于查找和order by都有作用。

数据本身之外，数据库还维护这一个满足特定查找算法的数据结构，这些数据结构已某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

3.2 优势

提高数据检索效率，降低数据的IO成本；降低数据排序成本，降低了CPU的消耗

3.3劣势

虽然索引大大提高了查询速度，同事却降低更新表的速度

3.4.Mysql的索引结构

B+树
Hash
全文

3.5.性能分析

MySQL Query Optimizer(MySQL自身的查询优化器)
Mysql常见瓶颈：cpu在饱和的时候一般发生在数据装入内存或者从磁盘上读取数据时/IO 发生在装入数据大于内存容量的时候/服务器硬件的瓶颈：top、free、iostal\vmstat来查询系统的性能状态
Explain：

explain字段分析

（1）id：select查询的序列号，包含一组数字，标识查询中执行select字句或者操作表的顺序；

值有三种情况：

id相同的时候，执行顺序由上到下；下图执行顺序是t1->t23->t2

id不同，如果是子查询，id的顺序会递增，id值越大优先级越高，越先被执行；下图执行顺序是t3->t1->t2

id相同不同同时存在，id值越大，优先级越高，越先执行

（2）select_type:查询的类别，主要用于区分普通查询、联合查询、子查询等复杂的查询

值的类型：

SIMPLE:简单的select查询，查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的字部分，最外层查询则被标记为

SUBQUERY：在select或者where列表中包含了子查询

DERIVED：在from列表中包含的子查询被标记为DERIVED(衍生)，mysql会递归执行这些子查询，把结果放在临时表中

UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层的select被标记为DERIVED

UNION RESULT:从UNION表中获取的查询结构的select

（3）type ：最好到最差的依次排序：system>const>eq_ref>ref>range>index>All

system：表是有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计（其实一般就是mysql自身带的表）；

const:标识通过索引一次就能找到，一般主键索引或者唯一索引来查找，因为只匹配一行数据，索引很快；

eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见与主键或者索引扫描；

ref:非唯一索引扫描，返回匹配某个独立值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该数据查找和扫描的混合体；

range:只检索给定范围的行，使用一个索引开选择行，key列显示使用了哪个索引，一般就是在where语句中出现的between、<、>、in等的查询

index:全索引扫描；

ALL:全表扫描

一般来说，能保证查询至少到达range级别，最好能达到ref

（4）possible_keys/key：是否使用到了所用或者多个索引中，到底使用到哪个索引

possible_keys：可能使用到的索引，但不一定被实际使用；

key:实际使用到的索引

（5）key_len：标识索引中使用的字节数，可通过该列计算查询中的索引长度，在不损失精确性的情况下（查询结果相同的情况下），长度越短越好

（6）ref:显示索引的哪个列被使用了，如果可能的话，是一个常数，哪些列或者常数被用于查询索引列的值；

（7）row：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读的行数；

（8）extra：

Using filesort（文件排序）:说明对数据使用一个外部的索引排序，而不是按照表内的索引顺序呢进行读取,需要优化的；

Using temporary：使用了临时表保存了中间结果，group by、order by 比较容易出现这样的情况，需要优化；

Using index：使用了覆盖索引，如果同时出现了Using where 表明索引被用来执行索引键值的查询，如果没有出现Using where ，表明索引用来读取数据并非执行查询数据；

Using where:

Using join buffer :使用了连接缓存；

impossible where：查询条件是比较错乱的（别查询一个性别是女同时性别是男）

select table optimized away：

distinct：

3.6.建索引情况

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系创建索引
频繁更新的字段不适合创建索引
where条件里用不到的字段不创建索引
单键/组合索引的问题
查询中排序的字段可以建立索引
查询中统计或者分组字段

3.7.不建立索引情况

表记录太少的
频繁增删改的表
某一列重复内容特别多的，不要在改列建立索引

3.8.日常建立索引注意：

左连接时，如果需要在on 的字段建立索引，那么要在右边的表建立索引；

索引（a,b,c）查询条件是c,b,a也是能走索引的

3.9 索引失效

全值匹配（最好）
最左前缀法则
不在索引列上有任何操作（计算、函数、手动或者手动类型转换），会导致全文扫描
联合索引中范围条件右边的列都失效 index(name ,age,sex) （where name="11" and age>12 and sex="女"遇到范围查询，导致sex失效
尽量使用覆盖索引（只访问索引的查询，查询的列和索引的列一致，减少select *
!= 或者<> 无法使用索引
is null ,is not null 无法使用索引
like 以通配符开头的（'%abc' 和'%abc%'）无法使用索引，会使索引失效，like abc% 可以走索引，但后面的失效；如果生产中一定要使用%abc%这种，要怎么解决索引失效的问题？？？？使用覆盖索引，建的索引和查询的字段一致
字符串不加单引号会使索引失效（自动的类型转换了）
少用or ,or 会使索引失效