MySQL 高级外加调优

  • MySQL调优
  • 查询优化
  • 索引优化之索引失效
  • 索引之优化口诀
  • 优化之小表驱动大表
  • in keyword
  • exists keyword
  • Order by 优化
  • 慢查询分析
  • 存储过程与函数
  • 创建函数
  • 存储过程
  • 附
  • MySQL 优化中的细则(如有错误，烦请指出)
  • 优化指令
  • 神器 `EXPLAIN` 语句
  • PROCEDURE ANALYSE()


MySQL调优

基本思路

  1. 慢查询的开启并捕获执行时间较长的SQL语句
  2. explain 关键字 + SQL 进行分析
  3. show profile查询SQL在MySQL服务器里面执行的细节和生命周期
  4. 表索引的更新优化
  5. SQL数据库服务的参数调优

查询优化

索引优化之索引失效

  1. 组合索引违背最左前缀原则
create index idx_c1_c2_c3 using btree on t(c1,c2,c3);
select * from t where c1=.. and c2=.. and c3=..; #使用索引 全值匹配
select * from t where c1=.. and c2=..;  #使用索引 
select * from t where c1=..; #使用索引
select * from t where c1=.. and c3=..; #部分使用索引
select * from t where c2=.. and c3=..; #违背最左前缀原则，索引失效

假设 index(a,b,c)

Where语句

索引是否被使用

where a = 3

Y,使用了索引a

where a = 3 and b=5

Y,使用了索引(a,b)

where a = 3 and b =5 and c = 4

Y,使用了索引(a,b,c)

where a=3 and c = 12

Y,部分使用索引a

where b=3; where c=3; where b=3 and c =3

N,无法使用索引

  1. 索引列进行（计算，函数，<自动 or 手动>类型转换）
create index idx_name using btree on t(name); # 注意：name字段为varchar类型
select * from t where name=..; #使用索引
select * from t where left(name,4)=..; #索引失效，索引列使用函数
select * from t where name=123; #索引失效,隐式类型转换；数据库会自动将数值类型转换为字符串类型
  1. 存储引擎不能使用索引中范围条件右边的列,针对复合索引。
create index idx_c1_c2_c3 using btree on t(c1,c2,c3);
select * from t where c1=.. and c2=.. and c3=..;#使用索引
select * from t where c1=.. and c2 in(..) and c3=..;#范围之后索引失效
  1. 尽量使用覆盖索引，少使用select * (它会降低性能，增加带宽负担，使得获得字段不明，可读性差)。-- 按需取值
    这种情况下在 explain 下extra列会显示 using index。
  2. MySQL中 在使用不等于(!=或者<>) 的时候会使得索引失效导致全表扫描。
  3. is null or is not null 无法使用索引
  4. like 子句中使用通配符开头的(’%abc…’) MySQL 索引会失效，变成全表扫描
    衍生问题：如何解决%开头的模糊查询索引失效的问题
    答：建立覆盖索引，利用覆盖索引来实现使用索引 <type index>
  5. 字符串不加单引号使得索引失效，会出现隐式类型转换。
  6. 少用 or 使用 or 会使得索引失效

索引之优化口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能少，中间兄弟亦需有；
索引列上勿计算，范围之后全失效；
模糊查询要有右，覆盖索引不写星；
判断空值还有or，索引失效要警惕；
字符引号不可缺，类型转换全表查；
上面金句要牢记，SQL优化也不难。

优化之小表驱动大表

in keyword

select * from A where id in(select id from B);

#equal as:
for select id from B
  for select * from A where A.id=B.id;

故当B表的数据集必然小于A表的数据集时，使用 in

exists keyword

select * from A where exists (select 2 from B where B.id=A.id);

#equal as:
for select * from A
  for select * from B where B.id=A.id;

故当B表的数据集必然大于A表的数据集时，使用 exists

注意: ID 字段使用了索引

Order by 优化

MySQL 支持两种排序方式：filesort, index ，而 index 排序的性能优于filesort 故为排序使用排序索引

select *和 order by一起使用是一个大忌

单路排序、多路排序（了解）

慢查询分析

set global slow_query_log=1; 开启慢查询，本次生效，数据库服务重启后失效。
set global slow_query_log_file='D:/slow_query_log_file/slow.log'; 配置慢查询log文件
set global long_query_time=3.0; 配置判读为慢查询的阈值

my.ini (windows) 或my.cnf (linux) 下[mysqld]增加配置使得永久开启

slow_query_log=1slow_query_log_file='D:/slow_query_log_file/slow.log'long_query_time=3.0

注意：配置要重开一个会话连接才能看到更新的配置

存储过程与函数

创建函数

语法:create function function_name(variable_name variable_type) return return_type

如果在创建函数时候报错: This function has none of DETERMINISTIC…
执行 show variables like 'log_bin_trust_function_createors'; 如果得到的结果是OFF，则要开启log_bin_trust_function_createors 执行 set global log_bin_trust_function_creators=1;，这样设置重启数据库服务器后失效。
或在MySQL配置文件[mysqld]配置下添加log_bin_trust_function_creators=1，这样配置重启数据库服务器仍然生效。

示例：

delimiter $$ # 自定义结束符，mysql默认的结束符为分号
# 随机生成长度为 n (n<255) 的字符串
create function rand_str(n int) returns varchar(255)
begin
  declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  declare return_str varchar(255) default '';
  declare i int default 0;
  if n > 255 then 
    set n = 255;
  end if;
  while i<n do
   set return_str=concat(return_str,substr(chars_str,floor(1+rand()*52),1));
   set i=i+1;
   end while;
return return_str;
end $$ # 函数完成截止
delimiter ; # 重新恢复结束符为分号
# 测试函数
select rand_str(10);

存储过程

语法:create procedure procedure_name(这里面细节自行百度)

示例：

delimiter $$ # 自定义结束符，mysql默认的结束符为分号
# 简单的显示一下当前时间，内部调用了now()
create procedure get_date()
begin
  select now() as date;
end $$
delimiter ; # 重新恢复结束符为分号

附

MySQL 优化中的细则(如有错误，烦请指出)

  • 表关联查询时务必遵循 小表驱动大表 原则；
  • 使用查询语句 where 条件时，不允许出现 函数，否则索引会失效；
  • 使用单表查询时，相同字段尽量不要用 OR，因为可能导致索引失效，比如：SELECT * FROM table WHERE name = '手机' OR name = '电脑'，可以使用 UNION 替代；
  • LIKE 语句不允许使用 % 开头，否则索引会失效；
  • 组合索引一定要遵循 从左到右 原则，否则索引会失效；比如：SELECT * FROM table WHERE name = '张三' AND age = 18，那么该组合索引必须是 (name,age)或(age,name) 形式；
  • 索引__不宜过多__，根据实际情况决定，尽量不要超过 10 个；
  • 每张表都必须有 主键，达到加快查询效率的目的；
  • 分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到分表的目的；
  • 分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到分库的目的；
  • 表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询速度，可以配合 分表 + 表分区 结合使用；
  • 一条数据的查询可以加limit 1来起到停止游标的作用 ，如果只是判断存在的话select 1不错
  • 删除或更新单条数据的时候加上limit 1能保护数据同时，也能提高性能
  • 组合索引,__最左前缀原则__要遵从
  • select / insert 应该指定需要的字段，避免全字段
  • 注意左模糊查询，会使得索引失效
  • 同一个字段的 or 必须改写为 in，同时in内的候选值数目少于50，因为 or 可能会使得索引失效，而 in 则会使用 type 为 range 的索引
  • 对于封装 JDBC 并使得编译期检测的异常转换为运行时异常的框架，应用程序应该捕获SQL异常
  • 避免隐式类型转换会使得索引失效
  • 注意 null 在索引建立时的问题，尽量让字段默认值不为null
  • not in,<>,!= 操作会使得索引失效

优化指令

神器 EXPLAIN 语句

EXPLAIN 显示了 MySQL 如何使用索引来处理 SELECT 语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在 SELECT 语句前加上 EXPLAIN 即可，如：

EXPLAIN SELECT * FROM tb_item WHERE cid IN (SELECT id FROM tb_item_cat)



mysqL包含列表_子查询

  • id： SELECT 识别符。这是 SELECT 的查询序列号
  • select_type：
    SELECT类型,可以为以下任何一种
  • SIMPLE: 简单 SELECT(不使用 UNION 或子查询)
  • PRIMARY: 最外面的 SELECT
  • UNION: UNION 中的第二个或后面的 SELECT 语句
  • DEPENDENT UNION: UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询
  • UNION RESULT: UNION 的结果
  • SUBQUERY: 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT,取决于外面的查询
  • DERIVED: 导出表的 SELECT(FROM 子句的子查询)
  • table： 输出的行所引用的表
  • partitions： 表分区
  • type：
    联接类型。下面给出各种联接类型，按照 从最佳类型到最坏类型进行排序
  • system: 表仅有一行(=系统表)。这是 const 联接类型的一个特例。
  • const: 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const 表很快,因为它们只读取一次!
  • eq_ref: 对于每个来自于前面的表的行组合, 从该表中读取一行。这可能是最好的联接类型, 除了 const 类型。
  • ref: 对于每个来自于前面的表的行组合, 所有有匹配索引值的行将从这张表中读取。
  • 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)
  • range: 只检索给定范围的行,使用一个索引来选择行。
  • index: 该联接类型与 ALL 相同,除了只有索引树被扫描。这通常比 ALL 快,因为索引文件通常比数据文件小。
  • ALL: 对于每个来自于先前的表的行组合, 进行完整的表扫描。
  • possible_keys： 指出 MySQL 能使用哪个索引在该表中找到行
  • key： 显示 MySQL 实际决定使用的键(索引)。如果没有选择索引, 键是 NULL。
  • key_len： 显示 MySQL 决定使用的键长度。如果键是 NULL, 则长度为 NULL。
  • ref： 显示使用哪个列或常数与 key 一起从表中选择行。
  • rows： 显示 MySQL 认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
  • filtered： 显示了通过条件过滤出的行数的百分比估计值。
  • Extra：
    该列包含 MySQL 解决查询的详细信息
  • Distinct: MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists: MySQL 能够对查询进行 LEFT JOIN 优化, 发现 1 个匹配 LEFT JOIN 标准的行后, 不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #): MySQL 没有发现好的可以使用的索引, 但发现如果来自前面的表的列值已知, 可能部分索引可以使用。
  • Using filesort: MySQL 需要额外的一次传递, 以找出如何按排序顺序检索行。
  • Using index: 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary: 为了解决查询, MySQL 需要创建一个临时表来容纳结果。
  • Using where: WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(…), Using union(…), Using intersect(…): 这些函数说明如何为 index_merge 联接类型合并索引扫描。
  • Using index for group-by: 类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引,可以用来查询 GROUP BY 或 DISTINCT 查询的所有列, 而不要额外搜索硬盘访问实际的表。

PROCEDURE ANALYSE()

语法: select * from tableName where ... procedure analyse([max_elements],[max_memory]);

作用: 对表进行分析，并给出合理的优化表字段的建议