长沙 网站建设公司,做电影网站危险吗,注册公司名字大全,网站速度诊断 慢目录
Explain关键字 索引性能分析
Id ——select的查询序列号
Select_type——select查询的类型
Table——表名称
Type——select的连接类型
Possible_key ——显示可能应用在这张表的索引
Key——实际用到的索引
Key_len——实际索引使用到的字节数
Ref ——索引命…目录
Explain关键字 索引性能分析
Id ——select的查询序列号
Select_type——select查询的类型
Table——表名称
Type——select的连接类型
Possible_key ——显示可能应用在这张表的索引
Key——实际用到的索引
Key_len——实际索引使用到的字节数
Ref ——索引命中的列或常量
Rows——预计select语句要检查的行数
Filtered——返回结果的行数占读取行数的百分比
Extra——显示额外的信息
索引的使用规则
SQL提示
最左前缀法则
索引失效情况
索引的设计原则 Explain关键字 索引性能分析
Explain可以应用于SELECT、DELETE、INSERT、REPLACE、UPDATE语句
通过Explain关键字可以看到SELECT语句的执行计划即可以查看到MySQL如何处理SELECT语句通过Explain显示的结果来决定如何优化
具体的作用有
查看表的读取顺序查看此语句可以使用哪些索引此语句实际使用了哪些索引查看此语句查询了多少行数据
explain语法
在任意的SELECT语句之前加上关键字 Explain或者Desc
EXPLAIN SELECT * FROM 表名;
使用Explain后返回的结果 查询结果的各个字段
Id ——select的查询序列号 表示查询中SQL执行的顺序id相同时的执行顺序为从上到下id不同时值越大越先执行 对于单表查询查询一次一般会产生一个id的一行信息 explain select * from user; 对于多表查查询一次一般会产生相同id的多行信息 explain select * from career,user where career.id user.career_id; 对于子查询查询一次一般会产生不同id的多行信息 explain select * from user where user.career_id (select id from career where id1); Select_type——select查询的类型 常见的取值有 SIMPLE简单的select查询类型查询语句中不包含子查询或UNION PRIMARY当查询中包含子查询或UNION时即外层的查询为此查询类型 SUBQUERY在SELECT或WHERE中包含了子查询时会被标记为此查询类型 DERIVED在FROM列表中包含的子查询被标记为此查询类型MySQL会将此子查询的查询结果作为临时表—派生表 explain select * from (select origo,count(*) as number from staff1 group by origo) as emp where emp.number 2; #根据居住地分组并查询居住地人数大于2的派生表的别名为emp UNION在UNION中的第二个和随后的SELECT语句被标记为UNION如果UNION被FROM子句中的子查询包含则它的第一个SELECT会被标记为DERIVED explain select origo,count(*) as number from staff2 group by origo union select origo,count(*) as number from staff1 group by origo; explain select origo,count(*) as number from staff2 group by origo union select * from (select origo,count(*) as number from staff1 group by origo) as emp where emp.number 2; UNION RESULT表示对应UNION的结果UNION和UNRESULT一般会成对出现 Table——表名称
显示这一行的数据是关于哪张表的显示结果可能为表的名称、derivedX、unionX1X1等
derivedX
当from子句中有子查询时table列为derivedX的格式x为id值对应子查询返回的临时表派生表
unionX1X1
当存在union时union result的table列为unionX1X1的格式X1和X2表示参与union的表的id序号
Type——select的连接类型 select的连接类型是查看索引执行情况的一个重要指标就是MySQL如何查找数据表中的记录 连接类型的性能由好到差为NULL、system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all 重点关注的是NULL、system、const、eq_ref、ref、range、index、all 在优化时尽量优化为性能好的当查询时不查询任何表时才会出现NULL 主键或唯一索引查询会出现const使用非唯一性索引查询时会出现ref 一般我们最好保证查询时type达到range、ref级别 All和Index都是读全表只是Index读的是索引树All读的是数据表 不同连接类型代表的含义通过Staff1表来模拟现象 Fulltext 当查询使用到全文索引时的连接类型 Ref_or_null 类似于ref也是非唯一性索引扫描不过MySQL还会扫描哪些行包含了NULL Index_merge 表示使用了索引合并优化即一个中使用到了多个索引 Unique_subquery类似于eq_ref唯一性索引扫描但是使用了IN查询并且子查询查询字段为主键或唯一索引 Index_subquery 类似于unique_subquery不过子查询查询字段为非唯一索引 Null 查询时不查询任何表MySQL在优化阶段会分析查询语句以此来判断是否需要访问表或者在查询的值在此字段找不到并且此字段建立了唯一索引 explain select min(id) from staff1; #查看主键的最小id System表只有一行记录是Cost的特殊情况平时不会出现可忽略 Const 表示通过索引一次就找到了要查询的记录一般存在于单表查询时主键或唯一索引作为查询条件 explain select * from staff1 where number2021004; Eq_ref 唯一性索引扫描对于每个索引键表中只有一条记录与之匹配一般存在于多表查询时使用主键或唯一索引扫描作为查询条件 explain select s1.*,a1.* from staff1 s1, account a1 where s1.id a1.id; Ref 非唯一性索引扫描返回匹配某个单独值的所有行可能会找到多个符合条件的行属于查找和扫描的混合体用于常规索引、联合索引情况 explain select * from staff1 where origo重庆; Range范围查询当给一个字段添加索引之后使用范围作为此字段的条件进行数据查询时的连接类型般就是在where语句中出现了between、、、in等的查询 explain select * from staff1 where number2021001; Indexindex类型值遍历索引树通过遍历索引树来查找数据需要查找的字段都已经建立了索引-主键索引、唯一索引、常规索引等 explain select id,number from staff1; ALL将遍历全表已找到匹配的行没有使用索引 Possible_key ——显示可能应用在这张表的索引
该值为一个或多个
此字段显示的索引不一定会被查询使用到可能会出现索引失效的问题
当Select语句发现可以使用多个索引的时候可以通过SQL提示来建议Mysql语句使用指定的索引可以避免SQL使用了性能比较低的索引例如如果同时存在唯一索引和常规索引可以建立SQL使用唯一索引
Key——实际用到的索引
如果没有使用索引则为NULL
哪些情况会导致有可用索引但是实际上没有使用到索引呢
1、对于联合索引来说没有遵守最左前缀法则
2、范围查询时使用到或会导致范围查询右侧的列索引失效
3、在Where之后的索引列上进行运算操作包含函数、比较运算符、谓词等
4、字符串类型字段的值使用时如果不加引号存在隐式类型转换索引将失效
5、当对头部进行模糊匹配时索引会失效即Like(%字符)或者Like(_字符)
6、用or分隔开的条件如果or前条件中的列有索引而后面的列中没有索引那么or前面的索引不会被用到
7、数据分布影响如果MySQL评估使用索引查询比全表查询更慢则不使用索引使用全表查询
如何规避索引失效呢————具体在索引的优化介绍
1、联合索引遵守最左前缀法则在创建联合索引时尽量将使用频率高的字段放在最左端
2、在范围查询时尽量使用过或者来规避范围查询
3、尽量不对索引列进行运算操作
4、在使用属于字符串类型的字段时需要对其值加上引号
5、尽量使用尾部模糊匹配来代替头部模糊匹配当对尾部进行模糊匹配时则索引不会失效即Like(字符%)或者Like(字符_)
6、只有当or前后都是用到索引时索引才会失效
Key_len——实际索引使用到的字节数
表明了在索引中使用的字节数通过此值可以大致估算出使用了索引中的哪些列
Key_len的计算规则
当字段允许为Null时比不允许为Null大1个字节
不同的数据类型占用的字节数时不同的详情可以参考以下官方文档介绍的是不允许为空的情况
MySQL :: MySQL 8.0 Reference Manual :: 11.7 Data Type Storage Requirements
对于字符串数据类型来说其占用的字节数还跟使用的字符编码有关
GBK 2字节
UTF8 3字节
ISO8859-1 1字节
GB2312 2字节
UTF-16 2字节
Ref ——索引命中的列或常量 表进行数据查找时使用字段、常量、函数的结果等 常量 const 空 NULL 字段 数据库名.表名.字段名 函数的结果func如果要想查看是哪个函数可以在Explain语句后跟上SHOW WARNING语句 explain select origo from staff1 where origo重庆; explain select * from staff1 where id in (select id from staff1 where id 2); Rows——预计select语句要检查的行数
mysql估计要读取并检查的行数并不是结果的行数
在innoDB引擎中的表中是一个估计值不是很准确
Filtered——返回结果的行数占读取行数的百分比
该值越大越好
Extra——显示额外的信息
通过此字段显示的额外信息也可以进行查询的优化不同MySQL版本显示的内容可能会有些许差别
Using Index查找使用了索引并且返回所需要的数据在该索引列中就可以找到无需回表查询
Using Where先读取整行数据再按照Where条件进行查询符合就留下不符合则丢弃
Using Join Buffer表示查询使用了连接缓冲多用于多表连接查询
Using Index Condition查找使用了索引但是需要回表查询数据—此种情况一般需要优化可以使其满足覆盖索引条件来避免回表查询
Using Temporary查找使用了临时表多见于group by语句--此种情况一般需要优化优先通过建立索引解决
Using Filesort通过表的索引或者全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序多见于order by语句----此种情况一般需要优化优先通过建立索引解决
一般需要将Using Filesort、Using Temporary、Using Index Condition 等优化为Using Index 索引的使用规则
通过遵守索引的使用规则避免索引失效并且可以手动选择索引进行索引查询使得索引的到最大利用
SQL提示 是优化数据库的一个重要手段就是在SQL语句中加入一些人为的提示来达到优化操作的目的 SQL提示的字段 USE INDEX 建议数据库使用哪个索引当一列属于多个索引类型式建议此列使用哪种类型的索引MySQL可能不会采用此建议 IGNORE INEDX 告诉数据库不要用哪个索引 FORCR INDEX 告诉数据库必须使用哪个索引 SQL提示的格式 SELECT 字段列表 FROM 表名 USE INDEX (索引名称) WHERE 判断条件; 情景模拟 针对上述表我们为origo、age创建了联合索引现在我们再针对origo创建一个常规索引 explain select origo from staff1 where origo重庆 and age 18; #此时我们查询此语句走的是联合索引 我们可以通过语句修改使其走单列索引 explain select origo from staff1 use index (as_origo) where origo重庆 and age 18; 最左前缀法则 主要针对联合索引如果索引为联合索引则要遵守最左前缀法则 最左前缀法则的要求 在使用联合索引进行查询时查询从联合索引的最左列开始并且不跳过索引中的列可以跳过最右边的一列或多列 在查询时如果中途跳过了联合索引中的某一列索引部分失效此列之后的列索引失效即无法进行索引查询只可以进行全文查询 在查询时如果最左边的列不存在则不走索引走全文扫描即进行联合查询时必须包含最左列 在查询时不用关心顺序只要存在就可以了 create index as_origo on staff1(origo,age,name); #创建时由左到右创建左边一般为查询频率高的 explain select * from staff1 where origo重庆; explain select * from staff1 where origo重庆 and age 22 ; #查询时也是从左到右查询此时使用了索引 explain select * from staff1 where origo重庆 and name老六; #此时origo使用了索引name没有使用索引通过key_len使用索引的字节数判断 explain select * from staff1 where age 22 ; #此时没有使用索引没有包含origo字段 索引失效情况
范围查询
在联合索引中出现范围查询或时范围查询右侧的列索引失效
尽量使用过或者来规避范围查询
运算操作
在索引列上进行运算操作时索引列将失效运算包括使用函数、比较运算符、谓词等
字符串数据类型
字符串类型字段使用时如果不对其值加引号则存在隐式类型转换索引将失效
Like字段模糊查询
当对尾部进行模糊匹配时则索引不会失效即Like(字符%)或者Like(字符_)
当对头部进行模糊匹配时索引会失效即Like(%字符)或者Like(_字符)
or连接条件
用or分隔开的条件如果or前/后的条件中的列有索引而后/前面的列中没有索引那么or前/后面的索引不会被用到
只有当or前后都是用到索引时索引才会失效
数据分布影响
如果MySQL评估使用索引查询比全表查询更慢则不使用索引使用全表查询一般用于大小判断的时候会出现
由于B树是顺序链表当第一个叶子就符合或者前几个叶子就符合时后面的叶子就必然也符合此时MySql就判断使用全表查询更快就会不适用索引使用全表查询了
即当要查询的结果占全表很大的比例时可能就进行全表查询了 索引的设计原则
针对数据量较大100多万数据及以上、且查询比较频繁的表建立索引很少查询没有必要建立索引针对于常作为查询条件Where、排序Order By、分组Group By操作的字段建立索引尽量区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高如果是字符串类型的字段字段的长度较长可以针对字段的特点建立前缀索引尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率控制索引的数量索引越多维护索引结构的代价也就越大会影响增删改的效率如果索引列不能存储Null就在创建表时使用NOT NULL约束此字段当MySQL优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询
总结
1、查询效率不高首先使用explain分析
如果发现没有索引可以创建索引
如果发现是单列索引要注意是否存在索引失效
如果发现是联合索引要注意是否遵守最左匹配原则
2、尽可能地使得查询语句扫描更少地行数、表、列
3、如果对字符串创建了索引尽可能减少字符串的长度即为较短的字符串建立前缀索引
4、尽量使得索引查询满足覆盖索引避免回表查询
