mysql-进阶-优快云博客

本文链接：https://blog.youkuaiyun.com/zhang_peng0609/article/details/147957299

文章目录

索引介绍

主键索引（PRIMARY KEY）：非空唯一，推荐使用主键自增
唯一索引（UNIQUE）：唯一，允许空值，并可以有多个
普通索引（NORMAL）：
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
- 复合索引：即一个索引包含多个列
说明：如果某张表中需要对3个字段进行where条件过滤，我们一般对这3个字段创建复合索引，而不是分别创建3个单值索引
全文索引（FULLTEXT）：主要是针对文本的检索，如：文章，全文索引只针对MyISAM引擎有效，并且只针对英文内容生效。 了解就行
空间索引（SPATIAL）：地理位置领域使用的一种索引，其他场景用的很少。了解就行
适合创建索引的字段
- where中频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 分组，排序，统计字段可以建立索引
不适合创建索引的字段
- where条件中用不到的字段不适合建立索引
- 频繁更新的字段不适合建立索引
- 表中数据可以确定比较少的不需要建索引
- 数据重复且发布比较均匀的的字段不适合建索引，如：性别

sql优化

避免使用select *
用union all代替union
小表驱动大表
多用limit
in中值不能太多
最左匹配原则
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）
用连接查询代替子查询
表连接尽量少（需要在表设计阶段，多考虑表的结构）
mysql在使用不等于（!=或者<>）的时候无法使用索引
is null, is not null 也无法使用索引
like “%xxx” 也无法使用索引
字符串不加单引号索引失效
少用or，用它来连接时会索引失效
索引优化

sql性能分析

explain + sql语句
在这里插入图片描述

id:执行编号
值越大，越先执行。值相同，从上倒下，依次执行
select_type:查询类型
区别普通查询、联合查询、子查询等的复杂查询
table:执行的表名
type:数据操作类型
访问类型排列（常用）：system > const > eq_ref > ref > range > index > ALL
一般来说，要保证查询至少达到 range 级别，最好能达到 ref。
possible_keys:可能应用在这张表中的索引
key:实际使用的索引，如果为NULL，则没有使用索引（要么没建，要么索引失效）
key_len:消耗的字节数
key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL。使用的索引的长度,在不损失精确性的情况下，长度越短越好
ref:表之间的引用
显示使用哪个列或常数与 key 一起从表中选择行
rows:预估查询行数
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数（越小越好）
Extra:额外信息
该列显示 MySQL 在查询过程中的一些详细信息，MySQL 查询优化器执行查询的过程中对查询计划的重要补充信息。
信息种类：
- Using filesort (优化):说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"
- Using temporary :(优化):使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
- Using index
- Using where
- Using join buffer
- impossible where
- select tables optimized away
- distinct