mysql数据库sql优化

现在没有牛仔了
226 阅读10分钟

innoDB和MyISAM存储引擎

mysql5.5之后的版本默认为innoDB

  • innoDB
    • 支持事务
    • 支持行锁，适合高并发
    • 支持外键：保证数据的完整性
  • MyISAM
    • 不支持事务
    • 支持表锁
    • 不支持外键

sql优化工具

-- 全局查询操作记录
SHOW GLOBAL STATUS LIKE 'Com_______';

-- 查询当前会话操作记录
SHOW STATUS LIKE 'Com_______';

-- 查询指定存储引擎的操作记录
SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

-- 查看当前mysql正在进行的线程信息
SHOW PROCESSLIST;

image-9.png value对应的操作次数

explain分析执行计划

EXPLAIN SELECT * FROM details;

image-10.png

字段含义
id
EXPLAIN SELECT * FROM users u,roles r,user_role ur WHERE ur.role_id = r.r_id AND ur.user_id = u.u_id;

image-11.png id相同表示从上到下顺序执行

EXPLAIN SELECT * FROM roles r WHERE r.r_id = 
(SELECT ur.role_id FROM user_role ur WHERE ur.user_id = 
(SELECT u.u_id FROM users u WHERE u.u_name='张三'));

image-12.png id不同的情况下，id值越大优先级越高

select_type
  • SIMPLE：简单查询，不包含子查询或表连接
    • EXPLAIN SELECT * FROM details;
  • PRIMARY：外层查询，主查询
    • EXPLAIN SELECT * FROM users WHERE u_id = (SELECT user_id FROM user_role WHERE role_id = 'test');
    • 上面的示例，users为primary
  • UNION：合并语句中后面的查询语句
    • EXPLAIN SELECT * FROM users WHERE u_id = 'zsan' UNION SELECT * FROM users WHERE u_id = 'lsi';
    • 上面的示例，union后面的查询时为union
  • UNION RESULT：从UNION表获取结果的查询
    • EXPLAIN SELECT * FROM users WHERE u_id = 'zsan' UNION SELECT * FROM users WHERE u_id = 'lsi';
    • 上面的示例，从两个结果中获取数据的最终操作为UNION RESULT
  • SUBQUERY：在select和where中的子查询
    • EXPLAIN SELECT * FROM users WHERE u_id = (SELECT user_id FROM user_role WHERE role_id = 'test');
    • 上面的示例，user_role为SUBQUERY
  • DERIVED：在from列表中包含的子查询，查询结果会放在一个临时表
    • mysql5.7针对于5.6版本做了一个优化，针对mysql本身的优化器增加了一个控制优化器的参数叫 derived_merge （派生类合并）。默认是开启状态。当执行带有派生表的查询时，mysql内部进行优化，将派生表合并到外部查询里面，是查询语句进行查单查询SIMPLE。
    • 关闭派生类合并：set global optimizer_switch='derived_merge=off';
type

从上到下效率越来越低

  • null：不访问任何表、索引，直接返回结果
    • SELECT NOW();
  • system：查询系统表
  • const：通过索引一次就找到数据
    • EXPLAIN SELECT * FROM details WHERE d_name = '你好吗';
  • eq_ref：唯一索引或主键索引作为关联条件，且只有一条记录匹配
    • EXPLAIN SELECT * FROM user_role,details WHERE id = d_id
  • ref：非唯一索引查询，返回匹配单个值的行
    • EXPLAIN SELECT * FROM user_role WHERE user_id = 'lsi';
  • range：范围查询
    • EXPLAIN SELECT * FROM user_role WHERE id BETWEEN 1 AND 2;
  • index：遍历索引树
    • EXPLAIN SELECT d_id FROM details;
  • all：全表查询
    • EXPLAIN SELECT * FROM details;
key
  • possible_keys：可能用到的索引，一或多
  • key：实际用到的索引，如果是null，表示没使用索引
  • key_len：索引字段可能最大长度，越短效率越高
rows

扫描的行数

Extra
  • using filesort
    • 文件排序，性能较低
    • EXPLAIN SELECT * FROM users ORDER BY u_name;
  • using temporary
    • 使用临时表保存中间结果，mysql在排序时会使用临时表，如order by、group by，性能较低
    • EXPLAIN SELECT u_age FROM users GROUP BY u_age;
  • using index
    • 表示使用了索引，性能较高
    • EXPLAIN SELECT u_name FROM users ORDER BY u_name;
  • using index condition
    • 索引回表查询，找到索引了，但是需要回到表中查其他列
  • using where
    • 找到索引的情况下，会回表查询

show profile分析sql

-- 查看是否支持profiling
SELECT @@have_profiling;

-- 查看是否开启profiling
SELECT @@profiling;

-- 设置为开启
SET profiling=1;

-- 执行一个慢查询
SELECT * FROM details WHERE d_name = '鬼刀一开看不见500000';

-- 查看sql执行列表
SHOW PROFILES;

-- 查看指定sql执行详细信息
SHOW PROFILE FOR QUERY 1;

-- 查看指定sql执行CPU使用情况
SHOW PROFILE CPU FOR QUERY 1;

image-13.png

sending data 状态表示Mysql线程开始访问数据库行并把结果返回给客户端这一整个区间的时间长度。

优化器执行计划信息查看

-- 打开优化器跟踪
set optimizer_trace = "enabled=on",end_markers_in_json=on;
-- 最大能使用的内存大小
set optimizer_trace_max_mem_size=1000000

-- 执行sql语句
/** ... */

-- 查询执行计划信息
select * from information_schema.optimizer_trace\G;

复合索引匹配规则

CREATE INDEX idx_name_mess ON details(d_name,d_message);

全值匹配 索引生效：

SELECT * FROM details WHERE d_name = '鬼刀一开看不见1000003' AND d_message='走位走位1000003';

最左前缀法则 索引生效：

SELECT * FROM details WHERE d_name = '鬼刀一开看不见1000003';

索引生效：

-- 只要包含最左列即可，与顺序无关
SELECT * FROM details WHERE  d_message='走位走位1000003' AND d_name = '鬼刀一开看不见1000003';

索引失效：

SELECT * FROM details WHERE  d_message='走位走位1000003';

总结，复合索引的查询要遵循最左前缀法则，首先查询条件中必须包含最左列，其次如果第二个查询条件为复合索引的第三列，那么第二个查询条件索引失效，也就是说不能出现跳跃。

索引失效的情况

  1. 条件包含范围查询，那么范围条件之后的条件全部索引失效。下面的查询，最后一个条件失效
EXPLAIN SELECT * FROM details WHERE d_name>'鬼刀一开看不见1000' AND d_message='走位走位1000';
  1. 对索引列进行运行操作
  2. 字符串不加单引号，这也相当于间接做了运算操作
  3. 如果or前面的字段是索引，后的不是，那么索引全部失效
  4. mysql在执行sql语句的时候如果评估使用全表扫描更快，那么不适用索引，此时索引失效

索引的正确使用

  • 尽量使用索引列覆盖select *及非索引列，不用回表查询
  • like模糊查询尽量使用前缀匹配，否则索引失效
    • EXPLAIN SELECT * FROM details WHERE d_name LIKE '%00%';不走索引
    • EXPLAIN SELECT * FROM details WHERE d_name LIKE '%00';不走索引
    • EXPLAIN SELECT * FROM details WHERE d_name LIKE '00%';走索引
    • EXPLAIN SELECT d_name FROM details WHERE d_name LIKE '%00%';使用覆盖索引查询都走索引
  • 对于范围查询来说，mysql在执行sql语句的时候如果评估使用全表扫描更快，那么不适用索引，此时索引失效。
  • 在条件复合多个字段查询的时候，尽量使用复合索引，因为如果单列索引mysql只会选择一个最优的索引来使用，不会使用全部的单列索引

批量导入数据到表中

load data local infile '/root/filname' into table 'tablename' fields terminated by ',' lines terminated by '\n';

其中fields表示指定字段间的分隔符，lines指定换行符

data文件中尽量将主键列设为有序的，否则导入过程中维护索引的效率会降低。

insert语句优化方案

  • 使用多行插入减少客户端与数据库的连接、关闭
    • insert into table values(),(),();
  • 开启事物然后再进行多词插入操作
  • 数据有序插入，提高索引维护的效率

order by语句优化方案

  • 查询结果列尽量使用索引覆盖
  • 在对多个字段排序时要么全部使用升序，要么全部使用降序
  • 多字段排序字段顺序需要和复合索引的字段顺序保持一致
  • 如果不符合索引排序的条件，那么可以通过增大排序区的大小提高FileSort的效率

group by语句优化方案

  • 通过再结尾追加order by null可以省去groupby的隐藏排序操作提高效率
  • 使用索引

嵌套查询（子查询）优化方案

  • 使用多表连接替换子查询

or优化方案

  • 使用union代替or
  • 为每个or的条件都加上索引

limit分页优化方案

  • 在查询语句中按索引排序，然后根据主键查询其他内容。这样就解决了mysql默认分页将当前页之前的排序数据丢弃的问题。（个人感觉效率并不高，可能版本问题）
  • 直接跨越相应数据量的空间查询，但是局限性很大，维护成本太大，不切实际
    • SELECT * FROM details WHERE d_id>2800000 LIMIT 10;

其他优化方案

  • use index：为sql语句指定建议使用的索引
  • ignore index：告诉mysql忽略指定的一个或多个索引
  • force index：强制mysql使用指定的索引

mysql减轻mysql服务器压力方案

  • 使用连接池，减少连接次数
  • 减少对数据库的访问次数
  • 使用负载均衡
    • 主从复制，读写分离。增删改走主节点，查询走从节点
  • 分布式数据库架构

mysql查询缓存

查询缓存配置

SHOW STATUS LIKE 'Qcache%';
-- 是否支持查询缓存
SHOW VARIABLES LIKE 'have_query_cache';
-- 是否开启了查询缓存
SHOW VARIABLES LIKE 'query_cache_type';
-- 查看查询缓存的空间大小
SHOW VARIABLES LIKE 'query_cache_size';
-- 查看查询缓存的详情
SHOW STATUS LIKE 'Qcache%';

query_cache_type取值

  • OFF或0：关闭查询缓存
  • ON或1：缓存符合条件的查询
  • DEMAND或2：只有显示的指定SQL_CACHE的查询语句才会被缓存
  • 使用sql_cache、sql_no_cache显示指明是否缓存
    • 不缓存：select sql_no_cache * from table;
    • 缓存：select sql_cache * from table;

查询缓存失效情况

  • 第二次的sql语句与第一次的sql语句不完全相同（如字母大小写不同）
  • 查询不能够确定的数据时
  • 不是从表中查询数据时
  • 查询mysql系统数据库的表时
  • 在存储过程、触发器、函数执行查询时
  • 表更改时，缓存会被删除

mysql内存优化

  • 合理增加mysql缓存空间
  • 合理增加排序区、连接区等缓存空间给每个session会话专用
InnoDB存储引擎内存优化

innoDB使用一块内存区做IO缓存池，用来缓存索引和数据

  • innodb_buffer_pool_size：缓存池。单位M
    • innodb_buffer_pool_size=128M。
    • 值越大，存储的数据越多，命中率就越高，磁盘的IO就越少，性能就越高。

mysql并发参数

  • max_connections：允许的最大连接数
  • back_log：允许最大的处于请求等待连接状态下的线程数
  • table_open_cache：表示多个线程执行的sql同时可以使用的表缓存的数量
  • thread_cache_size：客户服务线程缓存大小，为了加快连接数据库的速度，会缓存一定数量的客户服务线程已备使用
  • innodb_lock_wait_timeout：行锁等待超时时间

查询上述参数的语法

SHOW VARIABLES LIKE 'max_connections';

锁分类

  • 按操作粒度
    • 表锁
    • 行锁
  • 按操作类型
    • 读锁（共享锁）
    • 写锁（排他锁）
事务的特性
  • A原子性
  • C一致性
  • I隔离性
  • D持久性
并发事务问题
  • 丢失更新：当两个事务同时操作一行数据，第一个事务先做了修改操作，第二个事务又做了修改，那么第二个事务的修改操作就覆盖了第一个事务的修改操作
  • 脏读：当一个事务对数据进行了修改还未提交，第二个事务拿到了修改后的数据使用
  • 不可重复读：一个事务读取了数据后，另一个事务修改了数据，这时第一个事务再次读取数据发现和第一次读取的数据不一样
  • 幻读：事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。
并发事物问题的解决-事物隔离级别
  • 读未提交：解决丢失更新问题
  • 读已提交：解决丢失更新和脏读问题
  • 可重复读（默认）：解决不可重复度问题
  • 序列化：解决幻读问题
InnoDB存储引擎行锁
  • InnoDB行锁分类
    • 共享锁：可以多个事物共享一把锁读数据
    • 排他锁：其他事物不能读也不能写
      • 当执行更新语句，会自动为相关的数据加排他锁

行锁升级表锁的情况 在执行sql语句的时候，如果不通过索引检索数据，那么InnoDB将对整张表加锁。要尽量避免这种情况

间隙锁 当使用范围查询，比如1-10，假设4-6之间出现了断层，也就是说没有5，那么InnoDB依然会为它加锁，这个就叫间隙锁，此时如果添加5那么将会处于阻塞状态。

查看行锁的征用情况

show status like 'innodb_row_lock%';
  • Innodb_row_lock_current_waits 当前正在等待的线程数
  • Innodb_row_lock_time 锁定总时长
  • Innodb_row_lock_time_avg 平均等待时长
  • Innodb_row_lock_time_max 最大等待时长
  • Innodb_row_lock_waits 总的等待次数
avatar
文章
阅读
粉丝