目录
一、可以从哪些方面进行数据库调优？
- 索引失效、没有充分利用到索引一-索引建立
- 关联查询太多JOIN (设计缺陷或不得已的需求)--SQL优化
- 服务器调优及各个参数设置(缓冲、线程数等)--调整my.cnf
- 数据过多——分库分表
虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成 物理查询优化和 逻辑查询优化两大块。
- 物理查询优化是通过 索引和 表连接方式 等技术来进行优化，这里重点需要学握索引的使用。
- 逻辑查询优化就是通过 SOL 等价变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。
二、优化步骤
1、查看系统性能参数
- Connections: 连接MySQL服务器的次数。
- Uptime: MySQL服务器的上线时间。
- slow_queries: 慢查询的次数
- Innodb_rows_read: Select查询返回的行数
- Innodb_rows_inserted: 执行INSERT操作插入的行数
- Innodb_rows_updated: 执行UPDATE操作更新的行数
- Innodb_rows_deleted: 执行DELETE操作删除的行数
- Com_select: 查询操作的次数。
- Com_insert: 插入操作的次数。对于批量插入的INSERT 操作，只累加一次
- Com_update: 更新操作的次数
- Com_delete: 删除操作的次数。
SHOW STATUS LIKE '参数';
2、比较页的开销
- last_query_cost：用到的页数量。（用来比较页的开销）
3、定位执行慢的SQL语句：查询慢日志
- long_query_time：SQL语句运行时间超过该参数的值时，就称之为慢查询。
3.1 打开慢查询日志（默认是关闭的）
# 临时修改 # 打开慢查询日志 SET slow_query_log = ON; # 修改慢查询门槛阈值 SET GLOBAL long_query_time=秒数; SET long_query_time=秒数; # 永久修改 修改配置my.cnf配置文件，在[mysqld]下修改参数，然后重启服务器。3.2 分析慢查询语句
# 查看已有多少条慢查询语句 SHOW variables LIKE 'slow_queries';# 使用mysqldumpslow来查看慢查询语句。 mysqldumpslow -s -a t /var/lib/mysql/table-slow.log
4、查看SQL执行成本：Show Profile
# 打开show profile功能 SET profiling = 'ON'; # 查看最近执行的查询语句 SHOW profiles; # 查看某一条查询语句 SHOW profile for query 1;
5、分析查询语句：EXPLAIN
1 基本语法
EXPLAIN SELECT * FROM table; DISCRIBE SELECT * FROM table;2 EXPLAIN语句输出的列作用
列名 描述 id 本条select语句对应的专属ID select_type 查询类型 table 表名 partitions 匹配的分区信息 type 针对单表的访问方法 possible_keys 可能用到的索引 key 实际上使用的索引 key_len 实际上使用的索引的长度 ref 当索引列等值查询时，与索引列进行等值匹配的对象信息 rows 语句的需要读取的记录的条数 filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 extra 一些额外信息
3、EXPLAIN的四种输出格式
- 传统格式
- JSON格式
- TREE格式
- Worldbench可视化输出
EXPLAIN FORMAT='格式' SELECT * FROM table1;
id详解
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id越大，优先级就越高，越先执行
- id的每个号码，都表示一趟独立的查询，查询趟数越少越好。
select_type详解
类型 描述 SIMPLE PRIMARY UNION UNION RESULT SUBQUERY DEPENDENT SUBQUERY DEPENDENT UNION MATERIALIZED UNCACHEABLE SUBQUERY UNCACHEABLE UNION type详解
- 结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index merge > unique_subquery > index_subquery > range > index > ALL
- 其中比较重要的几个提取出来 (见上图中的蓝色) 。SQL 性能化的目标: 至少要达到 range 级别，要求是ref级别，最好是 consts级别。(阿里巴巴开发手册要求)
key_len详解
- 主要针对联合索引
- 长度越长越好。
Extra详解
略
三、索引优化与查询优化
1、索引失效的情况
- 带有运算
- 使用函数
- LIKE使用%XXX左模糊查询，因为mysql是最左原则，使用XXX%右模糊查询是可以使用索引的，但是左模糊违背了最左原则所以不行
- 使用范围运算，not in，in > ,<都不行
- 查询的字段不是索引的最左字段，同样是因为最左原则
- 字段类型不匹配，常见的隐式数据类型转换，mobile=1356不会走索引，会转换为字符串可以查询但是，mobile='1356'会走索引
- or条件左边的是索引字段，右边的不是。也不会走索引，因为or是一个并集
一般性建议：
- 对于单列索引，金陵选择针对当前query过滤性更好的索引。
- 在选择联合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择联合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
- 在选择联合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
- 总之，书写SQL语句时，尽量避免造成索引失效的情况。
2、关联查询优化
- 使用JOIN时，优先在被驱动表中添加索引。
- 对于内连接来说，查询优化器可以决定谁是驱动表，谁是被驱动表。（一般是小表驱动大表）。
- 能够直接用多表关联尽量直接关联，不使用子查询（减少查询的趟数）
- 不建议使用子查询，而是将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询。
3、JOIN语句的底层原理
- 使用小表驱动大表（本质是减少外层循环的数据数量）
-- 推荐写法 select tb1.b tb2.* from tb1 straight_join tb2 on (tb1.b=tb2.b) where tb2.id <= 100;- 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
- 增大 join buffer size 的大小（一次缓存的数据越多，那么内层包含的扫描次数就越少）
- 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）
- 使用 Hash Join
4、子查询优化
- 尽量使用JOIN代替子查询
5、排序优化
两种排序方式，分别是 FileSort 和 Index排序
- Index排序中，索引可以保证数据的有序性，不需要在进行排序，效率高，占用资源少。
- FileSort排序则一般在内存中进行，占用CPU比较多，如果待排序结果较大，甚至会IO到磁盘中进行排序，效率较低。
优化建议
- SQL中，可以在where子句和order by子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。
- 尽量使用Index完成Order BY排序。如果WHERE和ORDER BY后面是相同的列就是用单列索引，如果不是就是用联合索引。
- 无法使用Index时，需要对FileSort方式进行调优。
- 提高 sort_buffer_size
- 提高max_length_for_sort_data
- 使用Order BY时不要select *
- 避免索引失效的情况，例如升序降序掺杂、丢失最左边的索引、丢失中间的索引、使用非索引排序、使用IN()等范围查询。
6、GROUP BY分组优化、LIMIT分页优化
GROUP BY分组优化
- group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条用到索引，也可以直接使用索引。·group by 先排序再分组，遵照索引建的最佳左前缀法则
- 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size 参数的设置
- where效率高于having，能写在where限定的条件就不要写在having中了
- 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
- 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
LIMIT分页优化
# 优化之前，不推荐 SELECT * FROM tb1 LIMIT 2000000, 10; # 优化一，在索引上完成分页，然后根据主键回表 SELECT * FROM tb1 t1, (SELECT id FROM tb1 ORDER BY id LIMIT 2000000, 10) t2 WHERE t1.id = t2.id; # 优化二，如果主键是自增的，那么可以直接使用WHERE定位到具体位置 SELECT * FROM tb1 WHERE id > 2000000 LIMIT 10;
7、覆盖索引
定义：在索引中已经包含查询所需要的信息时，就不需要回表了。
好处：
- 避免InnoDB表进行索引的二次查询（回表）
- 可以把随机IO编程顺序IO加快查询效率
8、索引条件下推（ICP）
解释：在使用非聚簇索引时，查询语句在回表之前，筛选多次，减少回表的数据量。
使用条件：
- 如果表访问的类型为 range、 ref、 eq_ref和ref_or_null 可以使用ICP
- ICP可以用于 InnoDB 和MyISAM表，包括分区表InnoDB 和 MyISAM 表
- 对于InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少I/O 操作当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP 不会减少I/O。4.
- 相关子查询的条件不能使用ICP
9、其他查询优化策略
- EXISTS和IN的区分
# 当B表小时，使用IN SELECT * FROM A WHERE cc IN (SELECT cc FROM B); # 当A表小时，使用EXISTS SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc);- COUNT(*) 和 COUNT，MYISAM和InnoDB有区别
四、数据库其他调优策略
1、调优的目标
- 尽可能 节省系统资源，以便系统可以提供更大负荷的服务。(量更大)
- 合理的结构设计和参数调整，以提高用户操作 响应的速度。(响应速度更快)
- 减少系统的瓶颈，提高MySQL数据库整体的性能。
2、如何定位调优问题
- 用户反馈（主要）
- 日志分析（主要
- 服务器资源使用监控
- 数据库内部状况监控
3、调优的维度和步骤
- 首先选择一个合适的数据库。
- 优化表设计
- 表结构尽量遵循三范式原则
- 如果查询比较多，尤其是多个表联查的时候，可以采用反范式来提高查询的效率。
- 数据类型的选择。
- 优化逻辑查询
- 优化物理查询
- 使用redis或者memcached作为缓存
- 库级优化
- 读写分离
- 数据分片
4、优化MySQL服务器
优化服务器硬件
- 配置较大的内存，减少磁盘IO次数，或者增加缓冲区容量。
- 配置告诉磁盘系统
- 合理分配磁盘IO
- 配置多处理器
优化MySQL参数
- innodb_buffer_pool_size：表和索引的最大缓存
- key_buffer_size：所以缓冲区大小
- table_cache：同时打开的表的个数
- query_cache_size：查询缓冲区的大小。
- query_cache_type：牵扯到是否使用查询缓存区
- sort_buffer_size：需要进行排序的线程分配的缓冲区的大小
- join_buffer_size = 8M：联合查询操作所能使用的缓冲区大小
- read_bufer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区的大小
- innodb_flush_log_at_trx_commit：何时将缓冲区的数据写入日志文件
- innodb_log_buffer_size：事务日志所使用的缓冲区
- max_connections：允许连接到MySQL的最大数量
- back_log：控制监听TCP端口时设置的积压请求栈大小
- thread_cache_size：线程池缓存线程数量的大小
- wait_timeout：一个请求的最大连接时间
- interactive_timeout：表示服务器在关闭连接前等待行动的秒数
5、优化数据库结构
- 拆分表：冷热数据分离
- 增加中间表
- 增加冗余字段
- 优化数据类型
- 整数类型的优化
- 在文本类型和整数类型之间选择，优先考虑整数类型
- 避免使用TEXT，BLOB数据类型
- 避免使用ENUM，因为ORDER BY效率低
- 使用时间戳来储存时间
- 使用DECIMAL定点数来代替浮点数
- 优化插入记录的速度
- 提前禁用索引
- 提前禁用唯一性检查
- 使用批量插入
- 尽量使用LOAD DATA INFLE代替INSERT
- 提前禁用外键检查
- 提前禁止自动提交
- 使用非空约束
- 分析表、检查表、优化表
# 分析表，立即更新表索引的区分度 ANALYZE TABLE tb1; # 检查表 CHECK TABLE # 优化表，但只优化字节数多的类型 OPTIMIZE TABLE- 以上方式都是有利有弊的，需要权衡利弊谨慎优化。
6、大表优化
- 限定查询的范围
- 读写分离
- 垂直分库、垂直分表
- 水平拆分
7、其他调优操作
- 服务器语句超时处理
- 创建全局通用表空间
- 隐藏索引
2423
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
