SQL性能分析
SQL执行频率
在对SQL进行优化的时候，我们需要知道该数据库主要是哪些语句执行次数多，将优化重心就放在执行次数多的语句当中，查询SQL执行次数语句如下
SHOW [GLOBAL|SESSION] STATUS LIKE 'Com_______';
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认关闭，需要在MySQL的配置文件（etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志开关
slow_query_log = 1
# 设置慢日志的超时时间为2s
long_query_time = 2profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数，能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling;默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:
SET profiling = 1;# 会查看到每一条SQL的执行时间
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;
explain执行计划
EXPLAIN或者DESC命令可以获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中如何连接和连接顺序。
语法：
# 在select语句前直接加EXPLAIN或是DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
字段解释
id： select查询的序列号，表示查询中执行select子句或是操作表的顺序，执行顺序从上到下（id相同，从上到下，id不同，值越大越先执行）
select_type：表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all。
- NULL：不查询表的时候性能为NULL，项目中不可能优化到NULL
- system：查询系统表的时候为system
- const：查询主键或唯一索引时为const
- ref：查询非唯一性的索引为ref
- index：使用了索引，但还是遍历了整个索引树
- all：全表扫描
possible_key：显示可能应用在这张表上的索引，一个或多个
key：表示实际使用的索引，如果为NULL，则没有使用索引。
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
索引的使用
最左前缀法则
如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)[索引字段存在即可，在语句中的位置顺序不重要]
比如说：将一张表的name，age，sex字段创建一个联合索引，那么在查找时，name字段必须存在查询条件中，如果不包含name字段，只查询age与sex字段，那么将会全表扫描。如果查询name与sex字段，那么只有name字段会走索引，而sex字段的索引失效，因为跳过了age字段。
范围查询
联合索引中，出现范围查询（>，<），范围查询右侧的索引失效。
比如说，在查询条件中，加入的age>18的条件，那么sex的索引将会失效。
解决方法：在业务允许的情况下，能够使用>=或<=的情况，不要使用>和<。
索引列运算
字符串加引号
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，那么索引失效。
or连接的条件
用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
比如说age>=18，如果表中大多数数据都满足这个条件，那么即使age字段存在索引，那么也不会使用。
当age>=18这个条件表中大多数数据不满足时，才会走索引。
SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
比如说，name字段存在单列索引，也和age、sex存在联合索引，那么在只查询name字段时，可能会走联合索引。此时我们可以人为干预name字段走单例索引。
使用语法
# use index 在搜索时使用该索引
explain select * from 表名 use index(索引名) where ……;
# ignore index 在搜索时不使用该索引名
explain select * from 表名 ignore index(索引名) where ……;
# force index 强制使用该索引
explain select * from 表名 force index(索引名) where ……;覆盖索引
尽量使用覆盖索引（查询中使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *的使用。
比如说：name与age字段建立了索引在查找语句时where里对name与age进行条件查询，在返回的字段中只填写name、age字段那么在使用explain查看SQL执行计划时，extra字段的信息显示的时using where;using index（可能会随着MySQL版本不同而显示不同）。
如果返回的字段为name、age与sex字段，但是没有在where里使用sex字段，即使sex建立了索引extra显示的信息也为using index condition。
前缀索引
当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
# 最对应字段的后面括号中指定前多少字符建立索引
create index idx_xxx on table_name(column(n));可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
# 计算选择性
select count(distinct 字段) / count(*) from 表名;
# 计算前五个字符当索引的选择性
select count(distinct substring(字段,1,5)) / count(*) from 表名;前缀索引查询流程
首先，根据创建索引时指定的索引长度，截取条件中的的对应长度字符取索引中获取第一次出现该索引的id，然后区聚集索引中根据id获取到行信息，从行信息中获取对应字段信息与语句中的信息对比，如果相同，则返回该行信息。如果不同，则在辅助索引中链表中向后查询有无相同的索引，如果没有，则返回空。
索引设计原则
- 针对于数据量较大(百万级时)，且查询比较频繁的表建立索引。
- 针对于常作为查询条件 (where)、排序(order by)、分组 (group by) 操作的字段建立索引。
- 尽量选择区分度高（比如说手机号，邮箱等）的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
SQL优化
insert优化
- 选择批量插入：当一条一条语句进行插入时，需要频繁的向数据库建立连接，释放连接，性能较低。其次，批量插入时，尽量选择一次插入500到1000条数据，当插入的数据过多时，建议选择将一条语句拆分成多条语句。
- 手动事务提交：MySQL中，默认的事务提交规则是自动提交，每执行一条语句就需要开启事务，提交事务。频繁的开始事务与提交事务也会损耗性能。
- 主键顺序插入：与表数据存储结构有关，具体原因查看主键优化。
大批量插入数据时比如说百万级数据，使用insert性能不高，可以选择另一个指令load
# 客户端连接服务端时，加上参数 --local-infile。意为需要加载本地文件
mysql --local-infile -u root -p
# 设置全局参数local infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sal1.log' into table 'tb_user' fields terminated by ',' lines terminated by 'n';
#意思是将/root/sal1.log文件加载到tb_user表中，字段之间使用,分隔，数据之间使用n分隔。主键优化
在InnoDB中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
非叶子节点起到索引数据的作用，而叶子节点存储数据。而无论非叶子节点还是叶子节点都是存放在page当中的。接下来，我们基于page查看向表中插入数据的流程
页分裂
首先我们需要知道，page可以为空，也可以填充一半，也可以全部填充。每个页至少包含2条数据（如果一条数据过大，会出现行溢出现象）。
当一个页中已经无法满足主键为9的数据插入时，会申请第二个页，来存放主键为9的数据。
最后插入情况如下所示。
如果主键乱序插入的话，则是另一种情况
在已经插入好的数据中，需要插入主键为50的数据，此时，第一个页当中不满足将主键为50的数据插入。
此时，会开辟第三个页，并且将第一个页中超过百分之五十之后的数据移动到第三个页当中，并将主键为50的数据插入到第三个页当中。
乱序插入时，出现的情况就是页分裂。
页合并
当删除一行数据时，时间上数据并没有被物理删除，而是会被标记(flaged)为被删除数据，被标记的数据空间可以被其他数据使用。
当页中被删除的数据达到页合并阈值时（默认为页的百分之50），InnoDB会开始寻找最靠近的页（前或后），查看是否可以将两个页合并以优化空间使用。
比如说，下图中，第二个页中的主键为13，14，15，16的数据已经被标记删除了，被删除数据达到页合并阈值。
主键设计原则
在满足业务需求的情况下，尽量降低主键的长度。这是从索引的角度考虑，二级索引会存储主键信息，当索引过多时，会需要额外的磁盘IO。
插入数据时，尽量选择顺序插入，选择使用AUTO-INCREMENT自增主键。这是为了避免页分裂。
尽量不要使用UUID做主键，或者是其他自然主键，如身份证号。
order by优化
- Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
- Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
当我们需要对字段进行排序时，如果可以通过索引直接返回，此时效率高。
比如说，我们对一张user表的user与phone建立联合索引。默认的排序方式为升序，此时，我们执行order by user,phone。就是通过索引直接返回的数据。
但是如果执行order by user asc,phone desc。那么无法通过索引直接返回结果，需要对user相同的数据进行phone倒序排列，此时效率就低。又或是执行order phone,user。这样也是无法通过索引直接返回结果的。
使用Order By 的注意事项：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
group by优化
在使用分组操作时，如果不是通过索引获取的数据，那么EXPLAIN中的Extra字段会出现Using temporary，意为使用临时表。这种效率不高。
比如说我们对user表中的age与sex建立联合索引，当我们执行
select age,sex,count(*) from user group by age,sex;时，我们可以通过索引拿到对应的数据，因此效率就高，如果执行
select age,sex,count(*) from user group by sex;时，不满足最左前缀法则，因此需要走临时表，但是如果执行的是
select age,sex,count(*) from user where age =18 group by sex;时，走的也是索引，因为我们对age进行了过滤，用到了age索引。
limit优化
在执行limit时，需要将返回的数据之前的数据进行排序后，在返回，比如说返回limit 1000000,10。我们只需要返回1000000-1000010数据，但是需要排序1000010个数据。这样会造成极大的损失。对limit的优化，官方推荐覆盖查询加子查询的方式。比如说user表中存在500w数据，我需要返回2000000-2000010条数据，我只需要这十条数据的id即可，通过执行
select * from user limit 2000000,10;通过索引拿到10条数据的id。然后通过另一条查询语句获取这十条数据的信息。
需要注意的是，limit不支持在in()中执行，因此我们只能通过多表查询的方式进行查询。具体语句为
select u.* from user u,(select id from user limit 2000000,10) l where l.id = u.id;count优化
在MyISAM引擎当中，把一个表的总行数存储在了磁盘上，因此在执行count(*)时，会直接返回这个数。
但在InnoDB中没有，它需要一行行读取数据后，累计计数返回。
对于count并没有很好的优化方案。我们可以选择自己计数。比如说我们可以基于内存的key，value数据结构的数据库比如Redis自己保存记录条数。执行insert时就加1。
count的使用
InnoDB会遍历整张表，把每一行的主键id取出来，返回给服务层。服务层拿到主键后，直接按行进行累加。
- 没有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
- 有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
InnoDB不会把全部字段取出来，而是进行了优化，不取值，服务层直接按行进行累加。
update优化
update的优化主要在于MySQL中使用的是表级锁还是行级锁。
比如说有张user表，存在id与name字段，此时除了id没有其他索引。加入现在并发两条sql语句如下
update user set name = "张三" where id =1;
update user set name = "李四" where id =2;此时不会有任何问题，因为InnoDB是支持行级锁的，第一条SQL语句锁住的是id为1的数据，而第二条SQL锁住的是id为2的数据，他们互不干扰。
update user set name = "张三" where id =1;
update user set name = "李四" where name = "王五";第一条语句还是锁住id为1的数据。但是第二条数据需要锁住整张表，因为name字段没有索引，需要整表扫描。此时其他sql语句就无法接着进行。
InnoDB的行锁是针对索引加锁，不是针对记录加锁，并且索引不能失效。否则会从行锁升级到表锁。
原文地址:https://blog.csdn.net/zmbwcx/article/details/134712766
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_18233.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
