您的位置: 首页  >  文章  >  mysql 高性能索引优化解析（二）

mysql 高性能索引优化解析（二）

分类: 文章 • 2024-03-19 15:36:22

mysql 高性能索引优化解析（一）https://blog.****.net/qq_37776015/article/details/88376916

六、何时改创建索引

    需要建立索引：

  1. 主键自动建立唯一索引

  2. 频繁作为查询条件的字段应该创建索引

  3. 查询中与启停表关联的字段，外键关系建立索引

  4. 频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引数）

  5. Where 条件里用不到的字段不创建索引

  6. 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

  8. 查询中统计或者分组字段

    

    不需要建立索引：

  1. 表记录太少，不需要创建索引；

  2. 经常增删改的表；

  3. 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。

 

注：

  1. 主键自动建立唯一索引

  2. 某些数据包含大量重复数据，因此他建立索引就没有太大的效果。假如一个列只有A或B两种值，那么每个值的选择性为0.5，那么对于这样的列创建索引一般不会提高数据库的查询效率。如果一张表中有1000条数据，其中有990种不同的值，那么这个索引的选择性就为0.99，创建索引可以很大的提高数据库的查询效率。（一个索引的选择性越接近1，那么该索引的查询效率越高）

 

七、性能分析

     

    MySQL查询执行路径：       

  1. 客户端发送一条查询给服务器；

  2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；

  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

  5. 将结果返回给客户端。

mysql 高性能索引优化解析（二）

 

    MySql场景瓶颈：

  1. 内存：内存是大项，高查询消耗大量的查询缓存，内存必须足够，并且给系统本身要预留一些。

  2. 磁盘：配备高速磁盘+RAID会有更好的读写速度，并且SSD成本逐渐降低，升级成本会在可接受范围。

  3. 网络：目前市场上千兆万兆网卡已很常见。

  4. CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候，虽然很多情况下CPU用不完，但也不能让它成为瓶颈。

  5. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

 

    Explain：

        （一）是什么：使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

        

        （二）能干嘛：

  1. 表的读取顺序(取决于explain 里面的id）

  2. 数据读取操作的操作类型(取决于explain 里面的select_type）

  3. 哪些索引可以使用

  4. 哪些索引被实际使用

  5. 表之间的引用

  6. 每张表有多少行被优化器查询

 

        （三）怎么用：explain + SQL语句

例：explain select * from user;

mysql 高性能索引优化解析（二）

 

explain select * from user\G

  mysql 高性能索引优化解析（二）

      

（四）看什么（名词解析）：

 

id（关系到表的执行顺序）

 id相同，执行顺序由上至下，id不同

如果是子查询，id的序号会递增，id值越大优先级越高，越先执行

select_type（数据读取操作的操作类型）

 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

  1. Simple: 简单的select查询，查询中不包含子查询或者Union

  2. Primary: 查询中若包含任何复杂的子部分，最外层查询则被标记为primary

  3. Subquery:在select 或where列表中包含了子查询

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

  5. Union：若第二个select出现在union之后，

  6. Union result: 从union表获取结果的select

table（显示这一行的数据是属于哪个表的）

 

type（显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL）

  1. System：当MySQL对查询某部分进行优化，这个匹配的行的其他列值可以转换为一个常量来处理。如将主键或者唯一索引置于where列表中，MySQL就能将该查询转换为一个常量

  2. Const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快如将主键配置于列表中，mysql就能将该查询转换为一个常量

  3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。产检于主键或唯一索引扫描

  4. ref：非唯一性索引扫描，返回匹配某个单独值得所有行。本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  5. range：只检索给定范围的行 ，一般在where语句中出现了between < > in等的查询。这种范围索引扫描比全表扫描好。

  6. idnex： Full Index Scan . index 与 ALL 区别为index类型只遍历索引树。这通常比All 快，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的）

  7. all：Full Table Scan ,将遍历全表以找到匹配的行

  8. null：MySQL不用访问表或者索引就可以直接得到结果

 

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

 

possible_keys (理论上使用的索引)

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key（实际上使用的索引）

如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

  1. 理论上没用到索引，实际也没用到索引NULL

  2. 理论上没用到索引，实际上用到了索引

  3. 理论上用到索引了，实际上没用到索引

key_len

  1. 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

  2. Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows

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

extra（包含不适合在其他列中显示但十分重要的额外信息）

  1. Using filesort : 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序读取。mysql中无法利用索引完成的排序操作成为“文件排序”。出现这种情况是非常危险的，需要优化。

  2. Using temporary :(特别慢) 使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。这种情况非常危险，这个是拖慢sql的元凶。

  3. Using index : 表示相应的select操作中使用了覆盖索引（Coving Index）,避免访问了表的数据行，效率不错！如果同时出现using where ，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

  4. using where： 表明使用了where 过滤

  5. using join buffer： 使用了连接缓存

  6. impossible where： where 子句的值总是false ,不能用来获取任何元组

  7. select tables optimized away： 在没有group by 子句的情况下，基于索引优化MIN/MAX操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段完成优化

  8. distinct： 优化distinct 操作，在找到第一匹配的元组后即停止找同样值的动作

  9. const row not found：类似于select …. from tbl_name，而表记录为空

  10. FirstMatch：半连接（semi-jion）去重执行优化策略，当匹配了第一个值之后立即放弃之后记录的搜索。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生

 

注：

  1. 覆盖索引（Coving Index）：就是select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列被所建索引覆盖

  2. 注：如果要使用覆盖索引，一定要注意select 列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

  3. 半连接（semi-jion）： 当一张表在另一张表找到匹配的记录之后，半连接返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN或EXISTS 作为连接条件。

覆盖索引：如果一个索引包含了（或者说覆盖了）所有需要查询的字段的值，我们就称之为“覆盖索引”。

优点：

  1. 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySql将极大减少数据访问

  2. 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询回比随机从磁盘中读取每一行数据的I/O要少的多

注：并不是所有类型的索引都可以成为覆盖索引。覆盖索引必须在索引中存储索引列的值，而哈希索引、空间索引和全文索引都不支持存储索引列的值，索引mysql只能在使用B-Tree类型的索引做覆盖索引

 

八、索引失效

  1. 全职匹配我最爱

  2. 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

  3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

  4. 存储引擎不能使用索引中范围条件右边的列

  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

  6. Mysql在使用不等于（！=或者<>） 的时候无法使用索引会导致全表扫描

  7. Is null,is not null也无法使用索引。注：B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null  都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。

  8. Like以通配符开头（‘%abc…’） mysql索引失效会变成全表扫描的操作。注：可以使用覆盖索引解决（不要使用* 和索引不包括的字段）

  9. 字符串不加单引号索引失效

  10. 少用or,用它来连接时会索引失效。注：除非or两边的条件都是用索引

 

九、查询优化

 

（一）exists 和 in

  1. in()适合B表比A表数据小的情况。select * from A where id in(select id from B)

  2. exists()适合B表比A表数据大的情况。selecct * from A where exists(select 1 from B where B.id = A.id)

  3. 当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.

注：

  1. EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用

  2. selecct * from A where exists(select 1 from B where B.id = A.id)的语法可以理解为：将主查询的数据放到子查询中条件验证，根据验证的结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。子查询只返回TRUE或FALSE，因此子查询的select清单会被忽略。

 

（二）sort by 和 group by

        sort by：只进行排序

        group by：实质是先排序后进行分组

 

Mysql支持2种方式的排序，FileSort和Index，Index效率高。它指MySql扫描索引本身完成排序。FileSort方式效率较低。Order by 满足两种情况，会使用Index方式排序 ：

  1. Order by 语句使用索引最左前列

  2. 使用where 子句与Orderby 子句条件列组合满足索引最左前列

 

1.Using filesort文件排序：Using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。

   Using filesort 是通过相应的排序算法将取得的数据在内存中进行排序，所使用的内存区域也就是通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个 Thread 独享的，可能同一时刻在 MySQL 中存在多个 sort buffer 内存区域。

MySQL 中 filesort 的实现算法有两种：

① 双路排序：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在 sort buffer 中进行排序，按照排序完的值重新从列表中读取对应的数据输出。双路排序的开销可能会非常巨大，因为他会读取表两次，第二次读取会引发大量的随机IO

② 单路排序：是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序，  然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。  

 

注：在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

    

    优化  Using  filesort 方法：

    ① 修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。

    ② 使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。

    

    当无法避免排序操作时，很显然应该尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机 IO 操作，很大幅度地提高排序工作的效率。

    ① 加大 max_length_for_sort_data 参数的设置（max_length_for_sort_data的默认值是1024）

当所有返回字段的最大长度小于这个参数值时，MySQL 就会选择改进后的单路排序，反之，则选择老式的双路排序。所以，如果有充足的内存让 MySQL 存放须要返回的非排序字段，就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。

    ② 去掉不必要的返回字段

当内存不是很充裕时，不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，否则可能会造成 MySQL 不得不将数据分成很多段，然后进行排序，这样可能会得不偿失。此时就须要去掉不必要的返回字段，让返回结果长度适应 max_length_for_sort_data 参数的限制。

    ③ 增大 sort_buffer_size 参数设置

增大 sort_buffer_size 并不是为了让 MySQL 选择改进版的排序算法，而是为了让 MySQL 尽量减少在排序过程中对需要排序的数据进行分段，因为分段会造成 MySQL 使用临时表来进行交换排序。

    ④ 增加 read_rnd_buffer_size 变量值，以便一次读取更多行。

如果你取出很少字段的数据（小于max_length_for_sort_data）,行数据将会全部存储在sort buffer里，因此将不需要read_rnd_buffer_size这个参数。而如果你查询的字段数据很长（这些字段很可能含有Text/Blob字段），比max_length_for_sort_data还长，read_rnd_buffer_size这个参数将派上用场。

 

2.Using temporary：

    内部临时表产生的时机有以下几种：

    ① 使用 ORDER BY 子句和一个不一样的 GROUP BY 子句（经过笔者实验，应该是GROUP BY一个无索引列,就会产生临时表），或者 ORDER BY 或 GROUP BY 的列不是来自JOIN语句序列的第一个表，就会产生临时表(经笔者实验，应该是使用JOIN时， GROUP BY 任何列都会产生临时表)

    ② DISTINCT 和 ORDER BY 一起使用时可能需要临时表（笔者实验是只要用了DISTINCT（非索引列），都会产生临时表）

    ③用了 SQL_SMALL_RESULT, mysql就会用内存临时表。定义：SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT,

 

MySQL有些情况服务器会直接使用磁盘临时表

 

① 表里存在BLOB或者TEXT的时候(这是因为MEMORY引擎不支持这两种数据类型，这里笔者补充一下，并非只要查询里含有BLOB和TEXT类型的列就会产生磁盘临时表，按照高性能MYSQL里的话，应该这么说：“Because the Memory storage engine doesn't support the BLOB and TEXT types, queries that use BLOB or TEXT columns and need an implicit temporary table will have to use on-disk MyISAM temporry tables, even for only a few rows.”也就是说如果我们的查询中包含了BLOB和TEXT的列，而且又需要临时表，这时候临时表就被强制转成使用磁盘临时表，所以此书一直在提醒我们，如果要对BLOB和TEXT排序，应该使用SUBSTRING（column， length）将这些列截断变成字符串，这样就可以使用in-memory临时表了

② GROUP BY 或者 DISTINCT 子句大小超过 512 Bytes

③ 使用了UNION 或 UNION ALL 并且 SELECT 的列里有超过512 Bytes的列

 

注： ① 如果内置内存临时表创建后变得太大，MySQL会自动将它转换成磁盘临时表。内存临时表的大小取决与 tmp_table_size参数和max_heap_table_size参数的值。用 CREATE TABLE 产生的内存临时表的大小取决与 max_heap_table_size来决定是否要将其转换成磁盘临时表

        ②当服务器生成一个内存临时表，Created_tmp_tables状态变量值会增加，当服务器创建了一个磁盘临时表时，Created_tmp_disk_tables状态变量值会增加。(这几个变量可以通过 show status命令查看得到)

Tips: internal temporaray table 的大小受限制的是tmp_table_size和max_heap_table_size的最小值；而 user-created temporary table的大小只受限与max_heap_table_size,而与tmp_table_size无关

 

（三）慢查询日志

1.是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

 

2.怎么用？

slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。

  1. show VARIABLES like "%slow_query_log%"mysql 高性能索引优化解析（二）

  2. SET GLOBAL slow_query_log = ON             mysql 高性能索引优化解析（二）

log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

 

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

  1. show VARIABLES like "%slow_query_log_file%"   mysql 高性能索引优化解析（二）

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，默认为10s。

  1. show VARIABLES like "%long_query_time%"mysql 高性能索引优化解析（二）

  2. SET GLOBAL long_query_time = 3    注：当设置慢查询阈值的时候需要重新打开连接才能看到变化

 

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

 

log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

 

查看有多少慢查询sql

  1. show global status like '%slow_queries%'

日志分析工具mysqldumpslow

 

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。查看mysqldumpslow的帮助信息：

  1. s: 是表示按照何种方式排序

  2. c: 访问计数

  3. l: 锁定时间

  4. r: 返回记录

  5. t: 查询时间

  6. al: 平均锁定时间

  7. ar: 平均返回记录数

  8. at: 平均查询时间

  9. t： 是top n的意思，即为返回前面多少条的数据；

  10. g: 后边可以写一个正则匹配模式，大小写不敏感的；

 

例子：

  1. 得到返回记录集最多的10个SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

  2. 得到访问次数最多的10个SQL。mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

  3. 得到按照时间排序的前10条里面含有左连接的查询语句。mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

  4. 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

 

 

 

分析诊断工具：show profiles

　SQL导致服务器慢，要么是因为cpu 运算复杂，要不就是频繁IO。分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。

 

是否支持，看看当前的Mysql版本是否支持：Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

show version()

 

开启功能，默认是关闭，使用前需要开启

show variables like "%profiling%";

set profiling=1;

 

分析：

1.首先需要运行sql

select * from emp group by id%10 limit 1500000

 

2.使用show profiling 查看

mysql 高性能索引优化解析（二）

 

3.诊断sql语句

show profile cpu, block io for query 39（上一步前面的问题SQL数字号码）

mysql 高性能索引优化解析（二）

 

注：具体查看语法：SHOW PROFILE [type [, type] ... ]  [FOR QUERY n]  [LIMIT row_count [OFFSET offset]]  

type:  

    ALL                --显示所有的开销信息  

  | BLOCK IO           --显示块IO相关开销  

  | CONTEXT SWITCHES   --上下文切换相关开销  

  | CPU                --显示CPU相关开销信息  

  | IPC                --显示发送和接收相关开销信息  

  | MEMORY             --显示内存相关开销信息  

  | PAGE FAULTS        --显示页面错误相关开销信息  

  | SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息  

  | SWAPS              --显示交换次数相关开销的信息   

 

也可以使用 show profile [type]直接查看上一条执行的sql（show profile之类的语句不会被profiling，即自身不会产生Profiling ）

mysql 高性能索引优化解析（二）

相关推荐