mysql 高性能索引优化解析（一）https://blog.csdn.net/qq_37776015/article/details/88376916
六、何时改创建索引
需要建立索引：
-
主键自动建立唯一索引
-
频繁作为查询条件的字段应该创建索引
-
查询中与启停表关联的字段，外键关系建立索引
-
频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引数）
-
Where 条件里用不到的字段不创建索引
-
单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
-
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
-
查询中统计或者分组字段
不需要建立索引：
-
表记录太少，不需要创建索引；
-
经常增删改的表；
-
数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。
注：
-
主键自动建立唯一索引
-
某些数据包含大量重复数据，因此他建立索引就没有太大的效果。假如一个列只有A或B两种值，那么每个值的选择性为0.5，那么对于这样的列创建索引一般不会提高数据库的查询效率。如果一张表中有1000条数据，其中有990种不同的值，那么这个索引的选择性就为0.99，创建索引可以很大的提高数据库的查询效率。（一个索引的选择性越接近1，那么该索引的查询效率越高）
七、性能分析
MySQL查询执行路径：
-
客户端发送一条查询给服务器；
-
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
-
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
-
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
-
将结果返回给客户端。
MySql场景瓶颈：
-
内存：内存是大项，高查询消耗大量的查询缓存，内存必须足够，并且给系统本身要预留一些。
-
磁盘：配备高速磁盘+RAID会有更好的读写速度，并且SSD成本逐渐降低，升级成本会在可接受范围。
-
网络：目前市场上千兆万兆网卡已很常见。
-
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候，虽然很多情况下CPU用不完，但也不能让它成为瓶颈。
-
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
Explain：
（一）是什么：使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
（二）能干嘛：
-
表的读取顺序(取决于explain 里面的id）
-
数据读取操作的操作类型(取决于explain 里面的select_type）
-
哪些索引可以使用
-
哪些索引被实际使用
-
表之间的引用
-
每张表有多少行被优化器查询
（三）怎么用：explain + SQL语句
例：explain select * from user;
explain select * from user\G
（四）看什么（名词解析）：
| id（关系到表的执行顺序） | id相同，执行顺序由上至下，id不同 如果是子查询，id的序号会递增，id值越大优先级越高，越先执行 |
| select_type（数据读取操作的操作类型） | 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
|
| table（显示这一行的数据是属于哪个表的） |
|
| type（显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL） |
注：一般来说，得保证查询至少达到range级别，最好能达到ref
|
| possible_keys (理论上使用的索引) | 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。 |
| key（实际上使用的索引） | 如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中
|
| key_len |
|
| ref | 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值 |
| rows | 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 |
| extra（包含不适合在其他列中显示但十分重要的额外信息） |
注：
|
覆盖索引：如果一个索引包含了（或者说覆盖了）所有需要查询的字段的值，我们就称之为“覆盖索引”。
优点：
-
索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySql将极大减少数据访问
-
因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询回比随机从磁盘中读取每一行数据的I/O要少的多
注：并不是所有类型的索引都可以成为覆盖索引。覆盖索引必须在索引中存储索引列的值，而哈希索引、空间索引和全文索引都不支持存储索引列的值，索引mysql只能在使用B-Tree类型的索引做覆盖索引
八、索引失效
-
全职匹配我最爱
-
最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
-
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
-
存储引擎不能使用索引中范围条件右边的列
-
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
-
Mysql在使用不等于（！=或者<>） 的时候无法使用索引会导致全表扫描
-
Is null,is not null也无法使用索引。注：B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。
-
Like以通配符开头（‘%abc…’） mysql索引失效会变成全表扫描的操作。注：可以使用覆盖索引解决（不要使用* 和索引不包括的字段）
-
字符串不加单引号索引失效
-
少用or,用它来连接时会索引失效。注：除非or两边的条件都是用索引
九、查询优化
（一）exists 和 in
-
in()适合B表比A表数据小的情况。select * from A where id in(select id from B)
-
exists()适合B表比A表数据大的情况。selecct * from A where exists(select 1 from B where B.id = A.id)
-
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.
注：
-
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用
-
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方式排序 ：
-
Order by 语句使用索引最左前列
-
使用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表示关闭。 |
|
| log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log |
|
| slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log |
|
| long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，默认为10s。 |
|
| 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 |
|
日志分析工具mysqldumpslow
在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。查看mysqldumpslow的帮助信息：
-
s: 是表示按照何种方式排序
-
c: 访问计数
-
l: 锁定时间
-
r: 返回记录
-
t: 查询时间
-
al: 平均锁定时间
-
ar: 平均返回记录数
-
at: 平均查询时间
-
t： 是top n的意思，即为返回前面多少条的数据；
-
g: 后边可以写一个正则匹配模式，大小写不敏感的；
例子：
-
得到返回记录集最多的10个SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
-
得到访问次数最多的10个SQL。mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
-
得到按照时间排序的前10条里面含有左连接的查询语句。mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
-
另外建议在使用这些命令时结合 | 和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 查看
3.诊断sql语句
show profile cpu, block io for query 39（上一步前面的问题SQL数字号码）
注：具体查看语法：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 ）
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
