标签：log -- where 查询 索引 MySql 优化

MySql优化

慢查询日志分析

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

-- 连接数据库
mysql -h localhost -P 3306 -u root -padmins

-- 查看数据库版本
select @@version;

-- 查看所有日志参数
show variables like '%log%';

常见日志参数含义

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

配置慢查询日志

-- 查看是否开启慢查询日志
show variables like 'slow_query_log';

-- 查看日志储存方式，文件或数据库
show variables like '%log_output%';

-- 设置慢查询日志的位置， D:\workapp\mysql-5.7.23-winx64\data\XDZY-slow.log
set global slow_query_log_file='/var/lib/mysql/mysql-host-slow.log'

-- 开启慢查询日志，使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
set global slow_query_log=1;
-- 配置文件修改如下
slow_query_log=1
slow_query_log_file='/var/lib/mysql/mysql-host-slow.log'

-- 查看记录阈值（默认10s），在mysql源码里是判断大于long_query_time，而非大于等于
show variables like 'long_query_time';
-- 大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉
set global long_query_time=1;
-- 查看修改之后的阈值
show global variables like 'long_query_time';

-- 如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。
show variables like 'log_queries_not_using_indexes';
set global log_queries_not_using_indexes=on;
或 set global log_queries_not_using_indexes=1;

-- log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
show variables like 'log_slow_admin_statements';

-- 查询有多少条慢查询记录
show global status like '%slow_queries%';

-- ---------------------------------------------------
-- 下面是更多永久生效的配置内容（配置在my.ini），需要重启数据库才能生效
-- 是否开启慢查询日志
slow_query_log=1
-- 指定保存路径及文件名，默认为数据文件目录，
slow_query_log_file="bxg_mysql_slow.log"
-- 指定多少秒返回查询的结果为慢查询
long_query_time=1
-- 记录所有没有使用到索引的查询语句
log_queries_not_using_indexes=1
-- 记录那些由于查找了多于1000次而引发的慢查询
min_examined_row_limit=1000
-- 记录那些慢的optimize table，analyze table和alter table语句
log_slow_admin_statements=1
-- 记录由Slave所产生的慢查询
log_slow_slave_statements=1
-- 数据文件目录
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

关闭数据库缓存

-- Query Cache会缓存select查询，安装时默认是开启的，但是如果对表进行INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等操作时，之前的缓存会无效并且删除。这样一定程度上也会影响我们数据库的性能。所以对一些频繁的变动表的情况开启缓存是不明智的。还有一种情况我们测试数据库性能的时候也要关闭缓存，避免缓存对我们测试数据的影响。

-- 查看数据库缓存配置，其中query_cache_type表示是否开启缓存
show VARIABLES like '%cache%';

-- 查看缓存命中情况，Qcache_hits表示缓存数量
show status like '%qcache%';

-- 临时关闭缓存，如果配置文件中为关闭缓存的话，不能通过命令开启缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- 配置文件修改（重启永久生效）
query_cache_type=0
query_cache_size=0

-- 查询语句禁用缓存
-- 不缓存
Select sql_no_cache count(*) from tableName;
-- 缓存（也可以不加，默认缓存已经开启了）
Select sql_cache count(*) from tableName; 

直接打开日志文件查看

-- 表示查询的执行时间
# Time: 2022-07-18T13:42:56.103836Z
-- 执行SQL的主机信息
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
-- SQL的执行信息，Query_time表示SQL的查询时间，Lock_time表示锁定时间，Rows_sent表示所发送的行数，Rows_examined表示锁扫描的行数
# Query_time: 0.642205  Lock_time: 0.000284 Rows_sent: 273375  Rows_examined: 273375
-- SQL执行时间戳
SET timestamp=1658151776;
-- SQL的执行内容
SELECT * from ha03_allcstm_ship_busi_ic;

mysqldumpslow工具查看

-- 这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化锁表现的数据还是比较少。下面命令在mysql数据库所在的服务器上查看，而不是在mysql>命令行中
Windows：mysqldump --help
Linux：mysqldumpslow -h

-- 得到返回记录集最多的10个SQL
-- -s是表示按照何种方式排序（c: 访问计数，l: 锁定时间，r: 返回记录，t: 查询时间，al:平均锁定时间，ar:平均返回记录数，at:平均查询时间）
-- -t是top n的意思，即为返回前面多少条的数据
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

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

-- 得到按照时间排序的前10条里面含有左连接的查询语句
-- -g后边可以写一个正则匹配模式，大小写不敏感的
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

-- 更多命令合集：https://www.cnblogs.com/moss_tan_jun/p/8025504.html

mysqlsla工具查看

https://developer.aliyun.com/article/59260

pt-query-digest工具查看

https://blog.csdn.net/seteor/article/details/24017913

Explain执行计划

Explain特点：Explain不考虑各种Cache；Explain不能显示MySql在执行查询时所作的优化工作；部分统计信息是估算的，并非精确值；Explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划；Explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。

-- 经常使用的方式，查看SQL的执行计划
EXPLAIN SELECT 

-- 将执行计划反编译成SELECT语句，运行SHOW WARNINGS，可得到被MySQL优化器优化后的查询语句
EXPLAIN EXTENDED SELECT

-- 用于分区表的EXPLAIN生成QEP的信息，用来查看索引是否正在被使用，并且输出其使用的索引的信息
EXPLAIN PARTITIONS SELECT

-- 案例如下
EXPLAIN SELECT * FROM big_data WHERE cstm_code in (select cstm_code from big_data where year BETWEEN 2019 and 2023);

执行结果如下所示：

各参数含义详解：

1）id：包含一组数字，表示查询中执行select子句或操作表的顺序，id相同执行顺序由上至下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

2）select_type：所使用的SELECT查询类型，包括以下常见类型：

SIMPLE：表示为简单的SELECT，查询中不包含子查询或者UNION。

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY。

UNION：表连接中的第二个或后面的SELECT语句，若第二个SELECT出现在UNION之后，则被标记为UNION。

DERIVED：DERIVED（衍生）用来表示包含在FROM子句中的子查询的SELECT。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。MySql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的。

UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT。

DEPENDENT：意味着SELECT依赖于外层查询中发现的数据。

UNCACHEABLE：意味着SELECT中的某些特性阻止结果被缓存于一个item_cache中。

3）table：所使用的的数据表的名字，他们按被读取的先后顺序排列。

4）type：表示MySql在表中找到所需行的方式，又称访问类型。取值按优劣排序为 NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。

ALL：Full Table Scan全表扫描，MySql将遍历全表以找到匹配的行。

index：Full Index Scan全索引扫描，index与ALL区别为index类型只遍历索引树。

range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<，>查询。当MySql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

ref_or_null：该联接类型如同ref，但是添加了MySql可以专门搜索包含NULL值的行。

index_merge：该联接类型表示使用了索引合并优化方法。

unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) 。unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)。

ref：就是连接程序无法根据键值只取得一条记录，使用索引的最左前缀或者索引不是primary key或unique索引的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件。

const、system：当MySql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySql就能将该查询转换为一个常量。注：system是const类型的特例，当查询的表只有一行的情况下，使用system。

NULL：MySql在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。如：explain select * from address where id = (select min(id) from person);

5）possible keys：指出MySql能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

6）key：显示MySql在查询中实际使用的索引，若没有使用索引，显示为NULL。

7）key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，key_len显示的值为索引字段的最大可能长度，并非实际使用长度。如果键是NULL，则长度为NULL。

8）ref：显示索引的哪一列被使用了，有时候会是一个常量，表示哪些列或常量被用于用于查找索引列上的值，可能值为库.表.字段、常量、null。

9）rows：MySql根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

10）filtered：显示了通过条件过滤出的行数的百分比估计值。

11）Extra：包含不适合在其他列中显示但十分重要的额外信息，提供了与关联操作有关的信息，没有则什么都不写。

Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）。MySql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）。注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using where：表示MySql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。

Using temporary：表示MySql需要使用临时表来存储结果集，常见于排序和分组查询。这个值表示使用了内部临时（基于内存的）表。一个查询可能用到多个临时表。有很多原因都会导致MySql在执行查询期间创建临时表。两个常见的原因是在来自不同表的查询上使用了DISTINCT或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：内部临时表占用的空间超过min(tmp_table_size,max_heap_table_size)系统变量的限制；使用了TEXT/BLOB列。

Using filesort：MySql中无法利用索引完成的排序操作称为“文件排序”。 

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

Index merges：当MySql决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
Using sort_union(...)
Using union(...)
Using intersect(...)

Profiler诊断分析

要想优化一条Query，就须要清楚这条Query的性能瓶颈到底在哪里，是消耗的CPU计算太多，还是需要的IO操作太多，要想能够清楚地了解这些信息，可以通过Query Profiler功能得到。Query Profiler是MySql自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU、IO等，以及该SQL执行所耗费的时间等。

-- 开启QueryProfiler功能
set global profiling=on;

-- 查看相关变量
show VARIABLES like '%profiling%';

-- 设置保存数量默认15条，最大值为100
set profiling_history_size=100;

-- 在开启Query Profiler功能之后，MySql就会自动记录所有执行的Query的profile信息，下面执行n条Query作为测试
select * from big_data limit 1000,100;

-- 获取当前系统中保存的多个Query的profile的概要信息
show profiles;

-- 针对单个Query获取详细的profile信息。可以根据概要信息中的Query_ID来获取某个Query在执行过程中详细的profile信息。例如查看cpu和io的详细信息
show profile cpu,block io for query 501;

-- 显示所有信息
show profile ALL for query 501;

-- 查看的参数如下
BLOCK IO：块设备IO输入输出次数
CONTEXT SWITCHES：上下文切换相关开销
CPU：用户和系统的CPU使用情况
IPC：显示发送和接收消息的相关消耗
MEMORY：内存消耗情况
PAGE FAULTS：显示主要和次要页面故障相关的开销
SOURCE：显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS：显示交换次数相关的开销
注意：profiling被应用在每一个会话中，当前会话关闭后，profiling统计的信息将丢失。

查看SQL执行时间

-- 查上一个查询的代价，而且它是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。last_query_cost对于简单的查询可以精确的得到计算，但于包含子查询或union的复杂查询值是0。
show status like 'last_query_cost';

-- 如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要自己按回车执行，这样就不准了。这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准。
set @d=now();
select id from person where lname='x8RJWmQX';
select timestampdiff(second,@d,now());

-- 第三方MySql客户端工具都自带sql执行时间显示功能，如navicat、sqlyog等等。

数据库查询优化

常见语句优化

-- 1）SELECT子句中避免使用*号
-- 获取什么字段就写入什么字段，尽量全部大写SQL。从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

-- 2）避免在where子句中使用!=或<>操作符
-- 应尽量避免在where子句中使用!=或<>操作符，否则引擎放弃使用索引而进行全表扫描。

-- 3）对查询进行优化，应尽量避免全表扫描
-- 首先应考虑在where及order by涉及的列上建立索引，用索引可以提高查询。

-- 4）用UNION来代替OR
-- 采用UNION语句，返回的结果一样，但是速度要快些。

-- 5）like语句避免前置百分号，前置百分号会导致索引失效

-- 6）避免where子句中使用参数
-- 如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
-- 可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

-- 7）避免在where子句中对字段进行表达式操作
-- 应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
-- 应改为：
select id from t where num=100*2

-- 8）避免在where子句中对字段进行函数操作
-- 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
-- name以abc开头的 id
select id from t where substring(name,1,3)=’abc’;
-- ’2005-11-30′生成的id
select id from t where datediff(day,createdate,’2005-11-30′)=0;
-- 应改为：
select id from t where name like ‘abc%’; 
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′;

-- 9）避免无意义查询
-- 不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
-- 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

-- 10）用exists代替in
-- 很多时候用exists代替in是一个好的选择：
select num from a where num in(select num from b)
-- 用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

-- 11）尽量使用数字型字段
-- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

-- 12）使用varchar/nvarchar代替char/nchar
-- 尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

-- 13）大临时表使用select into代替create table
-- 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

-- 14）临时表先truncate table，然后drop table
-- 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

-- 15）存储过程使用SET NOCOUNT ON
-- 在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONEINPROC消息。

-- 16）避免向客户端返回大数据量
-- 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

-- 17）应尽量避免在where子句中对字段进行is null值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用IS NOT NULL。where子句中使用or来连接条件，也会导致引擎放弃使用索引而进行全表扫描。
-- 如可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
-- 在MySql5.7版本中该条建议已经不用考虑了，因为null判断也能使用索引了

-- 18）有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键。

-- 19）sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了。

-- 20）如果应用程序可以保证数据库的完整性，可以不需要按照三大范式来设计数据库，反范式设计。

-- 21）其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间。

-- 22）如果是jdbc的话，使用PreparedStatement不使用Statement，来创建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement对象SQL语句会预编译在此对象中，PreparedStatement对象可以多次高效的执行。

-- 23）in和not in也要慎用，否则会导致全表扫描。

Join语句优化

-- 1）尽可能减少Join语句中Nested Loop的循环总次数
-- 最有效的办法是让驱动表的结果集尽可能地小，“永远用小结果集驱动大结果集”。比如，当两个表（表A和表B）Join时，如果表A通过WHERE条件过滤后有10条记录，而表B有20条记录。如果选择表A作为驱动表，也就是被驱动表的结果集为20，那么我们通过Join条件对被驱动表（表B）的比较过滤就会进行10次。反之，如果选择表B作为驱动表，则须要进行20次对表A的比较过滤。

-- 2）优先优化Nested Loop的内层循环
-- 不仅在数据库的Join中应该这样做，实际上在优化程序语言时也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很少的资源，就能在整个循环中节约很多的资源。

-- 3）保证Join语句中被驱动表的Join条件字段已经被索引
-- 其目的正是基于上面两点的考虑，只有让被驱动表的Join条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是内层循环的实际优化方法。

-- 4）不要太吝惜Join Buffer的设置
-- 当无法保证被驱动表的Join条件字段被索引且内存资源充足时，不要太吝惜Join Buffer的设置。在Join是 All、Index、range或index_merge类型的特殊情况下，Join Buffer才能派上用场。在这种情况下，Join Buffer的大小将对整个Join语句的消耗起到非常关键的作用。

GROUP BY语句优化

-- 1）group by实质是先排序后分组，遵照索引的最佳左前缀。

-- 2）当无法使用索引列，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置。

-- 3）where高于having，能写在where限定的条件就不要去having去限定了。

大表查询优化

-- 1）使用limit进行分页，翻到10000多页后效率低。原因在于limit offset会逐行查找，是先查询再跳过。
-- 当ID是连续的时候，可以使用下面语句进行优化，中间的数据不能删，否则id为9999的数据并不是第9999个记录
select * from person where id>9999 limit 100;
-- 或延迟关联，这样只查询id列，实现了索引覆盖，就会很快
select id from person limit 9999,100;
-- 通过内连接再获取分页后每条记录的详细信息
select p.* from person p inner join (select id from person limit 999900 ,100) as tmp on
p.id=tmp.id;

-- 2）限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。

-- 3）读写分离：经典的数据库拆分方案，主库负责写，从库负责读。

-- 4）添加数据缓存机制，使用redis等中间件。

-- 5）垂直拆分数据库表：垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

-- 6）水平拆分数据库表：水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。 

数据库索引优化

索引的概念

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引。任何标准表最多可以创建16个索引列 。

最左前缀原则

1）MySql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
如果有一个2列的索引(col1,col2)，则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
如果想索引最大化，则继续追加索引(col2,col3)；

2）范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。MySql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySql的查询优化器会帮你优化成索引可以识别的形式。

聚簇、非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。表数据是和主键一起存储的，主键索引的叶结点存储行数据（包含了主键值），二级索引（普通索引）的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录（数据内容）。

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，非聚簇索引也不一定要回表查询，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据。

聚簇索引优点：当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址，即多一次I/O。使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

聚簇索引不一定是主键索引：InnoDB主键使用的是聚簇索引，MyISAM不管是主键索引，还是二级索引使用的都是非聚簇索引。在InnoDB引擎中，每张表都会有一个聚簇索引，一般情况下聚簇索引等于主键索引，但聚簇索引又不完全等于主键索引，因为一张表中没有主键索引，那么聚簇索引会使用第一个唯一索引（此列必须为 not null），如果以上情况都不满足，那么InnoDB则会默认创建一个隐藏的row_id作为聚簇索引。

覆盖索引

如果索引包含满足查询的所有数据（所有查询的字段加上索引），就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：索引项通常比记录要小，所以MySQL访问更少的数据；索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。对于索引覆盖查询（index-covered query），使用EXPLAIN时，可以在Extra一列中看到Using index（覆盖索引）。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少 IO，提高了效率。

回表查询

-- 如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。如：
select * from user where id = 1;

-- 如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据（回表查询）。先通过普通索引age=30定位到主键值id=1，再通过聚集索引id=1定位到行记录数据（查询了所有字段，不是所有字段加了索引）。如age加上索引：
select * from user where age = 30;

-- 索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。如id、age都有索引：
select id,age from user where age = 10;

-- 回表查询：先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。如age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值（和查询全部类似）。此时的Extra列的NULL表示进行了回表查询。通过全部加上索引变成索引覆盖可优化，实现：
select id,age,name from user where age = 10;

索引的分类

-- 1）单列索引
-- 一个索引只包含单个列，但一个表中可以有多个单列索引。单列索引又分为：
-- 普通索引（Normal）：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
Index(xx)或者key(xx)
-- 唯一索引（Unique）：索引列中的值必须是唯一的，但是允许为空值。
UNIQUE INDEX UniqIdx(xx)
-- 主键索引：是一种特殊的唯一索引，不允许有空值。
PRIMARY KEY(id)

-- 2）组合索引
-- 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
INDEX MultiIdx(id,name,age)

-- 3）全文索引（Full Text）
-- 全文索引，只有在MyIsam引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。
FULLTEXT INDEX FullTxtIdx(info)
SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr')

-- 4）空间索引（SPATIAL）
-- 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyIsam，创建空间索引的列，必须将其声明为NOT NULL。
SPATIAL INDEX spatIdx(g)

索引操作语句

-- 创建索引，name是索引名称，括号里面是字段名称
-- CREATE INDEX可对表增加普通索引或UNIQUE索引，但是不能创建PRIMARY KEY索引
CREATE INDEX name ON stu_info(sName);

-- 创建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构，唯一索引
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 
-- 创建表的时候直接指定唯一索引
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)));

-- 添加索引，age是索引名称，括号里面是字段名称
-- ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE stu_info ADD index age(sAge);
-- 添加组合索引
ALTER TABLE mytable ADD INDEX name_city_age(username(10),city,age);

-- 删除索引方法，age是索引名称
ALTER TABLE stu_info DROP INDEX age;
-- 删除索引方法，name是索引名称
DROP INDEX name ON stu_info;

-- 查看索引
SHOW INDEX FROM stu_info;

-- 创建前缀索引
-- 使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引
ALTER TABLE stu_info add index name(sName(100))
-- 通过从调整prefixLen的值（即下面的100，从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）
SELECT COUNT(DISTINCT sName)/count(*) AS a,COUNT(DISTINCT LEFT(sName,100)) AS b, COUNT(DISTINCT LEFT(sName,110)) AS c FROM stu_info

索引使用原则

1）并不是所有索引对查询都有效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

2）索引并不是越多越好
索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3）避免更新聚簇索引数据列
应尽可能的避免更新clustered索引数据列，mysql默认的clustered索引为主键，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引。

4）若是这张表增删改多而查询较少的话，就不要创建索引了
因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，若是删，则会把索引中以这个字段为名的索引的子集删掉。所以，索引会减慢增删改的执行速度，若是这张表增删改多而查询较少的话，就不要创建索引了。

5）当数据多且字段值有相同的值得时候用普通索引，当字段多且字段值没有重复的时候用唯一索引。

6）当有多个字段名都经常被查询的话用复合索引，普通索引不支持空值，唯一索引支持空值。

7）更新太频繁地字段不适合创建索引，不会出现在where条件中的字段不该建立索引。

8）索引的建立要根据业务特点进行，不能凭空想象的设置索引。经常作为查询条件的列才有建立索引的必要性。

索引失效情况

1）使用以%开头的LIKE模糊匹配语句，%加在后面是可以的

2）使用OR语句前后搜索条件没有同时加上索引

3）数据类型出现隐式转化，如varchar不加单引号的话可能会自动转换为int型

4）不符合最左前缀原则的语句

索引优化口诀

全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；VAR引号不可丢，SQL高级也不难！

索引B+树简介

数据库索引底层常用就是用就是B树或者是B+树这种结构，索引的原理很简单，就是把无序的数据变成有序的查询，先把创建了索引的列的内容进行排序，再对排序结果生成倒排表，在倒排表内容上拼上数据地址链，在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

树结构	分析
树	其实树就是从一个根节点出发，其可以有很多子节点，而子节点又可以有很多子节点，这样就像我们现实生活中的树一样，不过我们这颗树是倒立的！因为树的分支太多且没有规律所以很难控制，要想让树发挥他的作用就得在基本的树结构上加上一些特性，让有了特性的树成为帮助我们解决问题的结构，最常用的就是二叉树了，二叉树听名字就知道是一个节点至多只有两个节点，这样对数进行了一定的限制，整棵树看起来就顺眼多了。
二叉搜索树	二叉搜索树的节点满足一个规律，父节点的左孩子的键值小于父节点的键值，而右孩子的键值大于父节点的键值，这样当我们在这颗数中查询某个键值时就可以根据当前节点的键值和要寻找的键值的大小比较，确定该忘哪条路走下去。二叉搜索树还有一个特点就是中序遍历的时候其键值是按大小排序的。
平衡二叉树	由于我们要插入的数据可能是本身就排好序的，所以会导致插入数据时树变成线性的结构，只有一条路。于是我们需要保证二叉树的平衡，当发现这棵树要出现往一边倒的情况时就要想某种方式让其保持平衡（叶子节点的高度差最大为1），这就涉及到一些节点的旋转，变换了。
红黑树	红黑树也是一种平衡二叉树，不过加入了一些新的特性，听名字就知道，在红黑树中节点的颜色要么是红色要么是黑色的，当然还有其他的一些特性，当插入或者删除数据破坏了红黑树的这些特性时，我们需要进行一些操作（一般是颜色改变和树的旋转）红黑树保持其原有的特性。
B树	由于二叉树是二叉的，所以当树的节点不断增加时就会导致树的高度不断的增加，所以查询的效率就很低了，当我们面对海量数据（像数据库中保存的数据）的时候这种结构是不行的，所以我们又衍生出了新的树结构。B数一样拥有自平衡的特性，最大的区别在于B树不是二叉的，而是多叉的，具体有多少个叉要根据树的阶数来判断。
B+树	和B树相比，B+树又增加了一些特性，B+树主要是为了方便查询一个区间的数据集合，因为我们使用B树的时候要想查询某个区间内的数据得使用中序遍历将树中的数据全部遍历一遍，这样的时间复杂度是O(n)，效率太低了。而B+树只用叶子节点保存具体值的地址，非叶子节点只保存其子节点的指针，叶子节点之间通过指针链接起来，是有序的，所以在查找一个范围内的数据是很有效的。其时间复杂度为O(logn+M)，M为要查找的数据个数。

数据库表结构优化

1）永远为每张表设置一个ID。我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。就算是你users表有一个主键叫email的字段，你也别让它成为主键 。 使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如集群、分区。在这里，只有一个情况是例外（中间表），那就是关联表的外键，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做外键。比如有一个学生表有学生的ID，有一个课程表有课程ID，那么，成绩表就是关联表了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫外键其共同组成主键。

2）有限值字段使用ENUM而不是VARCHAR。ENUM类型是非常快和紧凑的。实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如性别、国家、民族、状态、部门，你知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR。MySQL也有一个建议，告诉你怎么去重新组织你的表结构。当你有一个VARCHAR字段时 ， 这个建议会告诉你把其改成ENUM类型 。使用PROCEDURE ANALYSE()你可以得到相关的建议。

3）固定长度的表会更快。如果表中的所有字段都是固定长度的，整个表会被认为是static或fixed-length。例如这些类型的字段：VARCHAR、TEXT、BLOB。只要你包括了其中一个这些字段，那么这个表就不是固定长度静态表了，这样，MySQL引擎会用另一种方法来处理。固定长度的表会提高性能，因为MySQL搜寻会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。使用垂直分割技术，你可以分割你的表成两个，一个是定长的一个则是不定长的。

4）尽可能的使用NOT NULL。除非你有一个很特别的原因去使用NULL值，你应该总是让你的字段保持NOT NULL。首先，问问你自己Empty和NULL有多大的区别（如果是INT，那就是0和NULL)，如果你觉得它们之间没有什么区别，那么你就不要使用 NULL。不要以为NULL不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

优化案例解析

发现问题：发现网站页面打开非常慢，首先登录服务器使用top查看当前进程信息，发现排名第一的是MySql，占用cpu达到了100%以上，这就明确了是MySql的问题。登录MySql，使用show processlist查看下当前执行状态，发现了大量LOCK操作，也有多个Copying to tmp table的操作，说明有SQL出现了问题，操作过于复杂，对临时表使用频繁，把其他操作阻塞了。

解决问题：

1）调整MySql配置（my.cnf），重启生效

-- 1、临时表
-- 既然涉及了到了临时表，就先查看下目前临时表的信息
-- 查看临时表的使用状态
show global status like 'created_tmp%';
-- 发现created_tmp_disk_tables值过高，需要增加此值。再看一下现在临时表的大小
show variables like '%tmp_table_size%';
-- 在现在值的基础上增加一些，重新设置临时表大小

-- 2、线程缓存数
-- 看当前线程情况
show global status like 'Thread%';
-- 发现threads_created的值过大，表明MySql服务器一直在创建线程
-- 查看当前值
show variables like 'thread_cache_size';
-- 此参数需要调高

-- 3、打开表数量
-- 查看打开表的情况
show global status like 'open%tables%';
-- 发现opened_tables数量过大，说明table_cache的值可能太小。
-- 查看当前值
show variables like 'table_cache';
-- 此参数需要调高

-- 4、最大连接数
-- 查看当前允许的最大连接数
show variables like 'max_connections';
-- 查看服务器连接数的峰值
show global status like 'Max_used_connections';
-- 峰值还没到最大限制，不需要修改
join buffer 和 sort buffer
-- 查看现有值
SELECT @@sort_buffer_size;
SELECT @@join_buffer_size;
-- 是默认值，需要修改

-- 确定了要修改的参数后，修改my.cnf，例如
table_cache = 64
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 300
thread_concurrency = 8
tmp_table_size = 246M

2）SQL语句优化

从show processlist结果集中找出主要的复杂语句，对其进行explain和profile分析，进行索引优化，把复杂的SQL根据业务拆分为多个小的SQL等。

标签：log,--,where,查询,索引,MySql,优化
From： https://www.cnblogs.com/xdzy/p/17036195.html