Mysql——索引优化概述
索引存在形式:索引在数据库启动的时候会加载到内存中,存储则是在文件中;mysql文件可以配置大小,如果不足会使用磁盘交换;
多重索引结构:在同一张表中,不同的索引走不同的B+树,利用非索引拿到主键索引后需要进行回表查询;每个索引是为数据建立不同的索引树,索引的保存是需要消耗磁盘写入IO的;
索引结构分布:索引在B+树中的非叶子结点存储的是索引上相关字段的值;在叶子结点中,存储的是主键值或者数据;输出索引值,又名覆盖索引,不必回表;
索引搜寻原理:利用二分法思想结合B树多路平衡特点进行增删改查,(具体搜寻,插入请看B+树原理)
索引终点设计:B+树由于所有数据都在叶子结点,同时由于有链表结构,只需要找到首尾,通过链表就能把所有数据取出来了解決了跨层问题,也得益于适合做范围查询因为索引值是离散连续的,给定端点只需要不断遍历即可;
Mysql——优化基本能力
slow log慢查询捕捉分析
全局查看配置
show variables like “%slow%”
show global VARIABLES like ‘%slow_query_log%’
show global VARIABLES like ‘long_query_time%’
show global status like ‘%Slow_queries%’
全局修改配置
set global slow_query_log=0 开启
set global long_query_time=3 时间
会话配置
show VARIABLES like ‘%slow_query_log%’
show VARIABLES like ‘long_query_time%’
set long_query_time=3 开启
set slow_query_log=0 时间
永久配置
[mysqld]
slow_query_log = ON
slow_query_log_file = /tmp/slow_querys.log #定义慢查询日志的路径
long_query_time = 1 #定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
#log-queries-not-using-indexes #用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-slow-admin-statements 管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
Ps:永久生效修改配置文件;
慢查询分析
tail -f slow_query.log
第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息,用户和连接IP 第三行,记录了一些我们比较有用的信息,如下解析:
- Query_time,这条SQL执行的时间,越长则越慢
- Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
- Rows_sent,查询返回的行数
- Rows_examined,查询检查的行数,越长就当然越费时间
第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间。
第五行及后面所有行(第二个# Time:之前),执行的sql语句记录信息,因为sql可能会很长。
show profile语句分析
show profiles应用场景
是Jeremy Cole捐献给MySQL社区版本的一个功能;这个命令只是在本会话内起作用,即无法分析本会话外的语句。查看分析sql的生命周期,一条sql究竟卡在这里花的时间比较多
show profiles全局指令
全局查看近期所有的语句以及queryId,这些信息保存就内存中;
show profiles详情指令
SHOW PROFILE for QUERY 66 – 显示用户CPU时间、系统CPU时间
SHOW PROFILE ALL for QUERY 66 – 显示所有性能信息
SHOW PROFILE BLOCK IO for QUERY 66 – 显示块IO操作的次数
SHOW PROFILE CONTEXT SWITCHES for QUERY 66 – 显示上下文切换次数,不管是主动还是被动
SHOW PROFILE cpu for QUERY 66 – IPC 显示发送和接收的消息数量
~
~
SHOW PROFILE MEMORY for QUERY 66 – MEMORY [暂未实现] SHOW PROFILE PAGE
FAULTS for QUERY 66 – 显示页错误数量 SHOW PROFILE SOURCE for QUERY 66 –
SOURCE 显示源码中的函数名称与位置 SHOW PROFILE SWAPS for QUERY 66 – SWAPS
显示SWAP的次数 Ps:如果没有指定FOR QUERY,那么输出最近一条语句的信息。
dump slow统计分析
mysqldumpslow命令本质
用于打开慢查询日志,并自动根据格式进行统计分析,按照要求进行排序、统计;
mysqldumpslow命令合集
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log(得到返回记录集最多的10个SQL)
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log(得到访问次数最多的10个SQL)
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log(得到按照时间排序的前10条里面含有左连接的查询语)
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more(另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况)
-s:是表示按照何种方式排序,子参数如下:
c、ar:被捕获次数正序、被捕获次数倒序
t、at:被捕获时间正序、被捕获时间倒叙
l、al:查询时间正序、查询时间倒序
r、ar:返回记录数正序、返回记录倒序
-t:返回前面多少条的数据,这里意思就是返回10条数据了(也可以说是前十)
pt(Percona Toolkit)命令配置
用将日志整理成文件输出统计信息
pt(Percona Toolkit)命令合集
pt-query-digest slow_querys.log >t.txt
global catch全局查询分析
更狠,直接全部查询一起输出到日志进行分析;
general_log=1
general_log_file=/path/logflie
log_output=file
set global general_log=1
set global log_output=’TABLE’
select * from mysql.general_log
Explan查询计划分析
索引分析——查询参数
【ID】
Id相同,上面的先执行;
Id不同,大的先执行;
【SELECT TYPE】
simple:简单的 select 查询,不包含任何嵌套部份 -------> 子查询或者union
primary:查询中若包含任何复杂的子部分,最外层查询则被标记为primary
derived:在from后面列表中包含的子查询被标记为derived(衍生)mysql会递归执行这些子查询, 把结果放在临时表里
subquery:select或where后面包含了子查询,与外层无关联
dependent subquery:基于外层关联关系的子查询,性能差;
union:
union result:
【TABLE】
显示这一行的数据是关于哪张表的,可能多个
索引分析——索引相关信息
【TYPE】
用于表示此次查询和索引的关系
system:表中只有一行记录
const:唯一或者主键的索引值是个常量,而不是语句相关的变量;
eq_reft:唯一或者主键的索引值是变量,但是一个索引只对应一条数据;
ref:不唯一,用一个索引找出多行记录;
range:范围查询,where语句中出现了between、<、>、in等的查询,告诉索引的开始和索引末尾即可;
index:只全表扫描非聚集索引的数据行,不涉及回表,这个文件比较实际的数据文件小,所以性能更高;利用索引进行了排序分组也会发生这个;
index_merge:采用了多个索引结构;当多条件联合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引
ref_or_null:null值的情况被考虑进来了
index_subquery: 作为子查询输出的是索引字段的遍历
unique_subquery :作为子查询输出的唯一键的遍历
【possible_keys】
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
【Key】
实际使用的索引。如果为NULL,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠
【Key_len】
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,同一行数据利用多个索引确定和利用少量索引确定是不一样的;
key_len字段能够帮你检查是否充分的利用上了索引,同一个索引利用了几次!
【ref】
针对于非唯一性索引被使用的情况;索引值是常量还是一个与查询语句有关的变量,这个会将所有索引的ref写出来;一个const或者一个变量
【Rows】
rows列显示MySQL认为它执行查询时必须检查的行数。
索引分析——Extra信息
using filesort: 在组合索引中不存在某个字段的排序索引即外部索引,就会触发这个;
双路排序:mysql4.1之前,先利用IO获取排序类以及指针,然后将排序好的指针数组去顺序读取数据;(频繁IO)
单路排序:一次型取出所有数据,然后在buffer中进行orderby排序,但会占用过多的空间;而sort_buffer大小是固定的,超出后会进行多次io,因此有以下调优策略(需要内存支持)
(1)不要用select *,会把sort-buffer缓存区用完非常容易
(2)max_length_for_sort_data,超出后就采用多路排序;
(3)sort_buffer_size,针对每个进程buffer的容量大小
using temporary :MySQL在对查询结果如果排序不符合要求,排序时使用临时表。常见于排序 order by 和分组查询 group by。
using index:
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。
using join buffer:使用了连接缓存
Mysql——索引优化场景
单表优化(转化、覆盖、下推)
转化
1、索引是字符串,但是数据直接写成数字,不加单引号索引失效;mysql里面会将字符转成数字,所以字符建立索引不能比较数字,因为所有字符转数字没有建立这种索引
2、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描;
3、is not null 也无法使用索引,但是is null是可以使用索引的;首先,问问你自己“Empty”和“NULL”有多大的区别(如果是 INT,那就是 0 和 NULL)?
操作
1、在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
2、某些查询确定只有一条时,在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL 数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据
3、 or 、in、not in; 非操作不要很难搞;对于连续的数值,能用 between 就不要用 in 了
连表
如果你要把DECIMAL 字段和一个 INT 字段 Join 在一起,MySQL 就无法使用它们的索引。对
于那些 STRING 类型,还需要有相同的字符集才行。
复合
1、断层避免,根据左匹配原则,当中间的索引使用了范围、非确定性模糊则会导致右边的索引全部失效;
2、当前Query中过滤性最好字段在索引字段顺序中,位置越靠前越好,可以过滤条很多路线;
3、如果某个字段可经常范围查询时,尽量把这个字段放在索引次序的最后面;
4、覆盖索引(双边like例子):两边都是like只能用覆盖索引解决,因为覆盖索引只会遍历索引树
select * from test where b like ‘b%’ range
select * from test where b like ‘%b%’ All
select b from test where b like ‘%b%’ index
5、索引条件下推,此次查询条件中用了非索引字段则会利用主键id拿到数据行直接做判断,explain select name from test where name=‘2’ and c>1
设计
extra中的额外信息,Using index condition
1、如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或 “部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM而不是 VARCHAR。
2、注意在不必要的字段上加组合索引会引起索引失效
3、记录存储是按行存储的,数据块大小又是固定的(16K),每条记录越小,相同的块存储的记录就越多。此时应该把大字段拆走(不经常用时),这样应付大部分小字段的查询时,就能
4、任何标准表最多可以创建 16 个索引列,建议每个表8个
子查询
很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)
用下面的语句替换:elect num from a where exists(select 1 from b where num=a.num)
IN适合于外表大而内表小的情况,一次把所有的小的查出来过滤外面大的;EXISTS适合于外表小而内表大的情况,用小的作为驱动找到大的。
No Primary key count(1)>count()
Yes Primary key count(key)>count(1)>count()
关联优化(驱动表)
左外连接:左表全表扫描;left join,右边匹配交集;
右外连接:右表全表扫描;right join,左边匹配交集;
内连接:左边全表扫描,最后匹配,两边匹配交集;
Ps:由此可见全表扫描的一方,必须是小表不然就导致很多没必要的数据IO
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
ALTER TABLEbook
ADD INDEX Y (card
);
Ps:只是少部分数据过滤,全表扫描效率更高;每个阶段都会产生一个虚拟表,该虚拟表会作为下一个阶段的输入
排序分组(左前缀确定)
排序的本质:只要你此次查新也是用索引字段作为条件,那就具备按照这个字段的天然排序功,因为二分法左边小,右边大;而如果是复合索引树,则是比较左边,然后比较中间这样决定左右放,因此按照索引树取出来的时候已经是我们所需要的排序结果;复合索引中,必须先按照左边开始排;
分组的本质:分组之前必排序,所以如果此时排序失败则会产生临时表来临时存储内部生成的排序;using temp
降序索引:mysql中如果经常要利用查询时间做操作,可以利用时间作为倒序索引,mysql8.0反向扫描和正向扫描的支持;也因此终于不再对group by进行隐式排序;反向扫描?不一定从顶层?这一层研究确实我没够知识储备,学C看源码把!
UsingFileSort:此次索引利用中索引顺序不是我们要的顺序规则则会产生这个;
左前缀确定:
复合排序中,只有左前缀非范围和模糊查询,那么后面的排序索引才会生效;因此这些条件应该滞后;
index(catogory,comments,views)
explain select id.author_id from article, where catogoryjd = 1 and comments >1 order by views desc limit 1;
explain select id,author_id from ‘article’ where categoryjd= 1 and comments <=3 order by views desc limit 1
index(catogory,views,comments)