[一. 索引介绍] [1.1 什么是Mysql索引] MySQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。 MySQL在存储数据之外,数据库系统中还维护着满足特定查找算法的数据结构,这些数据结构以某种引用(指向)表中的数据,这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。 简单理解为“排好序的可以快速查找数据的数据结构”。
[1.2 索引数据结构] 下图是二叉树的索引方式 [1.3 索引优势] [1.4 索引劣势] 索引实际上也是一张表,保存了主键和索引的字段,并且指向实体表的记录,所以索引也是需要占用空间的。 在索引大大提高查询速度的同时,却会降低表的更新速度,在对表进行数据增删改的同时,MySQL不仅要更新数据,还需要保存一下索引文件。 每次更新添加了的索引列的字段,都会去调整因为更新带来的减值变化后的索引的信息。
[1.5 索引使用场景] 哪些情况需要创建索引: 哪些情况不推荐建立索引: 表记录太少 经常增删改的表 Where条件里用不到的字段不建立索引
[三. 性能分析] [3.1 MySQL常见瓶颈] [3.2 Explain] 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。其作用: 表的读取顺序 哪些索引可以使用 数据读取操作的操作类型 那些索引被实际使用 表之间的引用 每张表有多少行被优化器查询
EXPLAIN关键字使用起来比较简单: explain + SQL语句:
id字段介绍: select查询的序列号,表示查询中执行select子句或操作表的顺序。 id相同时,执行顺序由上至下。 id不同,如果是子查询,id的序号会递增,id值越大优先级越高,则先被执行。 id相同和不同都存在时,id相同的可以理解为一组,从上往下顺序执行,所有组中,id值越大,优先级越高越先执行。
select_type字段介绍: 查询的类型,常见值有: SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。 PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为Primary。 DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。(mysql5.7+过后) SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
table字段介绍: 显示这一行的数据是关于哪张表的。 type字段介绍: 访问类型排序(从左往右索引效率越高): System:表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计。 Const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快,如将主键置于where列表中,MySQL就能将该查询转换为一个常量。 eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。 ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。 range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。 Index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的。 all:Full Table Scan,将遍历全表以找到匹配的行。
从最好到最差依次是:system>const>eq_ref>ref>range>index>All 。一般来说,最好保证查询能达到range级别,最好能达到ref。 possible_keys字段介绍: 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上如果存在索引,则该索引将会被列出来,但不一定会被查询实际使用上。 key字段介绍: 查询中实际使用的索引,如果为NULL,则没有使用索引。 key_len字段介绍: 查询中实际使用索引的性能,越大越好。 ref字段介绍: 显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。 rows字段介绍: rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。 extra字段介绍: 一些常见的重要的额外信息: Using filesort:MySQL无法利用索引完成的排序操作称为“文件排序”。 Using temporary:Mysql在对查询结果排序时使用临时表,常见于排序order by和分组查询group by。 Using index:表示索引被用来执行索引键值的查找,避免访问了表的数据行,效率不错。 Using where:表示使用了where过滤。
尽量避免Using filesort!
[四. 查询优化] [4.1 索引失效] 最佳左前缀法则:如果索引了多列,要遵循最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。 不在索引列上做任何计算、函数操作,会导致索引失效而转向全表扫描。 存储引擎不能使用索引中范围条件右边的列。 Mysql在使用不等于时无法使用索引会导致全表扫描。 is null可以使用索引,但是is not null无法使用索引。 like以通配符开头会使索引失效导致全表扫描。 字符串不加单引号索引会失效。 使用or连接时索引失效。
[4.2 复合索引练习] [4.4 关联查询优化] 内连接时,mysql会自动把小结果集的选为驱动表,所以大表的字段最好加上索引。左外连接时,左表会全表扫描,所以右边大表字段最好加上索引,右外连接同理。我们最好保证被驱动表上的字段建立了索引。 [4.5 排序优化] 尽量避免使用Using FileSort方式排序。 order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。 where子句中如果出现索引范围查询会导致order by索引失效。
[4.6 分组优化]
[4.7 慢查询日志] 介绍:MySQL的慢查询日志是MySQL提供的一种日志记录,他用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。 默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置参数: 查看是否开启:show variables like '%slow_query_log%'; 开启日志:set global slow_query_log = 1; 设置时间: set global long_query_time = 1; 查看时间: SHOW VARIABLES LIKE 'long_query_time%';
查看超时的sql记录日志:Mysql的数据文件夹下/data/...-slow.log;在Navicat中输入show variables like '%slow_query_log%命令',就可以得到文件目录; 注意:非调优场景下,一般不建议启动改参数,慢查询日志支持将日志记录写入文件,开启慢查询日志会或多或少带来一定的性能影响。
|