MySQL最佳实践

2021/5/4 19:27:11

编程Tag： mysql select 查询索引 id name 实践 user 最佳

本文主要是介绍MySQL最佳实践，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

创建高性能的索引

索引类型

B-Tree索引

哈希索引

哈希索引基于哈希表实现，只有精确匹配所有列的查询才有效。

hash索引的限制：

哈希索引只包括哈希值和行指针，而不存储字段值，所以不能用索引中的值来避免读取行。
哈希索引只支持等值比较查询，包括=、IN()、<=>也不支持任何范围查询

InnoDB有个特殊功能，自适应哈希索引。某些索引值被使用的非常频繁，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希表的一些优点。

空间索引

GIS，GIS的解决方案做的比较好的是PostgreSQL的PostGIS

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

索引的优点

优点：

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

三星索引：

索引将相关的记录放到一起获得一星；

如果索引中的数据顺序和查找中的排列顺序一致则获得二星；

如果索引的列中包含了查询中需要的全部列则获得“三星”

对于非常小的表，大部分情况下简单的全表扫描更高效。

对于中大型的表，索引就非常有效。

对于特大型的表，建立和使用索引的代价将随之增长。

高性能的索引策略

独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参考。

例如，下面这个就没法使用dt_tskssj的索引。 where id + 1 > 100这种也不行

where to_char(dt_tskssj, 'yyyy-MM') = to_char(now(), 'yyyy-MM') 
修改后：
 where dt_tskssj>='2018-08-01 00:00:00.000' and dt_tskssj < '2018-08-31 24:00:00.000'

前缀索引和索引选择性

索引的选择性是指，不重复的索引值（也成为基数，cardinality）和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1.

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储的方式。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的缺点：

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致占用更多的磁盘空间。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。

压缩（前缀压缩）索引

MySQL压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7，ance”的形式。

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。冗余索引，例如，创建了索引（A,B）再创建索引A就是冗余索引，因为这只是前一个索引的前缀索引。

索引和锁

InnoDB在二级索引上使用共享锁，但访问主键索引需要排它锁。

查询性能优化

大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

确认应用程序是否在检索大量超过需要的数据。这意味着访问了太多的行，但有时候也可能是访问了太多的列
确认MySQL服务器层是否在分析大量超过需要的数据行。

对于MySQL，最简单的衡量查询开销的三个指标如下：

响应时间
扫描的行数
返回的行数

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的
使用索引覆盖扫描（Extra中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无需再回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录（Extra显示Using where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧优化：

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。
改变库表结构，例如使用单独的汇总表
重写复杂查询，让MySQL优化器能够以更优化的方式执行这个查询。

查询执行的基础

MySQL发送一个请求的时候，做了些什么：

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段（MySQL8弃用了查询缓存）
服务器端进行sql解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

MySQL客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

查询优化处理

题外话，select * from a join b using(id);等价于select * from a join b on a.id = b.id;

查询执行引擎

MySQL只是简单的根据执行计划给出的指令逐步执行。

返回结果给客户端

即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

优化特定类型的查询

优化COUNT（）查询

例如SELECT COUNT(*) FROM t where ID > 5假如扫描了50000多行，

下面这样查询能减少扫描行：

select (select count(*) from t) - count(*) from t where id <= 5;

优化关联查询

确保ON或者USING字句中的列上由索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

优化子查询

关于子查询优化：尽可能使用关联查询代替。

优化GROUP BY和DISTINCT

在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序（filesort）来做分组。

如果没有通过ORDER BY子句显式的指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的排序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。

优化LIMIT分页

1是延迟关联

高级特性

视图

视图是一个虚拟表，是sql的查询结果，其内容由查询定义

优点：

数据库视图允许简化复杂查询
数据库视图有助于限制对特定用户的数据访问。可以通过视图将非敏感数据仅显示给特定用户组。
数据库视图提供额外的安全层。可以创建只读视图。

缺点：

性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的
表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

主从复制

复制有三个步骤：

在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）
备库将主库上的日志复制到自己的中继日志（Relay Log)中
备库读取中继日志中的事件，将其重放到备库数据之上。

sql优化

索引失效的情况

like模糊匹配

有时候我们学习一样东西的时候，还是要亲自去尝试一下，多思考一下。

测试数据来自MySQL官方数据：https://github.com/datacharmer/test_db 导入之后执行下面的创建索引语句。MySQL版本5.7

假设有这样一张表：（数据量30w）

create table employees
(
    emp_no int not null
        primary key,
    birth_date date not null,
    first_name varchar(14) not null,
    last_name varchar(16) not null,
    gender enum('M', 'F') not null,
    hire_date date not null
);

create index employees_first_name_index
    on employees (first_name);

执行左模糊或者全模糊explain select * from employees where first_name like '%a';的时候发现没有走索引，

当改为右模糊的时候

但like左模糊或者全模糊一定不会走索引吗？

还记得有个叫“覆盖索引”的东西吗？比如这样explain select emp_no, first_name from employees where first_name like 'a%';

另外，非字符串类型的列上建了索引，使用右模糊也不会走索引的。

where条件中使用表达式

参加本文前面“独立的列”

使用`!=`或者`<>`或者`not in`

比如

explain select * from employees where first_name not in ( 'Kyoichi');
explain select * from employees where first_name != 'Kyoichi';

OR引起的索引失效

first_name上有索引，last_name无索引，索引会失效。

explain select * from employees where first_name = 'Kyoichi' or last_name = 'Georgi';

如果为last_name也建了索引，可以使用a，b上的索引。

如果都是使用first_name来查询也是可以使用索引的。

is null/ is not null

explain extended  select * from employees where first_name is  null;

不管是使用is null还是is not null，都不走索引。

那如果用覆盖索引呢？

explain extended select first_name from employees where first_name is not null is not null可以走索引，is null不走索引。

子查询优化

易混点

exists和in的区别

exists适合于外查询数据少子查询数据多的场景，in则适合外查询数据多子查询数据少的情况。

注意，not in的集合中不能包含null

具体更详细的参考：in exists

重点：

外层大表内层小表，用in。外层小表内层大表，in和exists效率差不多（甚至 in 比 exists 还快，而并不是网上说的 exists 比 in 效率高）

小表做外表的情况下。对于主键来说， not exists 比 not in 快。对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

大表做外表的情况下，对于主键来说， not in 比 not exists 快。对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

至于走哪个索引，我建议还是看实际explain结果吧，那么多情况，记起来有点麻烦。

两个表user（10w）/user_group（1w）

建表语句如下：

create table mysql_learning.user
(
    id int not null
        primary key,
    name varchar(45) null,
    update_time datetime null
)
charset=utf8;

create index idx_name
    on mysql_learning.user (name);
create table mysql_learning.user_group
(
    id int not null
        primary key,
    user_id int not null,
    group_id int not null,
    remark varchar(255) null
)
charset=utf8;


create index idx_user_id
    on mysql_learning.user_group (user_id);

使用存储过程插入数据

delimiter ;;
create procedure insertData()
begin
    declare i int;
    set i = 1;


    while (i < 100000)
        do
        insert into user(id, name) values (i, concat(substr(MD5(RAND()), 1, 5), i));
        set i = i + 1;
        end while ;
end ;;
delimiter ;
call insertData();

delimiter ;;
create procedure insertData2()
begin
    declare i int;
    set i = 1;


    while (i < 10000)
        do
        insert into user_group(id, user_id, group_id) values (i, i, (i %3) + 1);
        set i = i + 1;
        end while ;
end ;;
delimiter ;
call insertData2();

执行如下sql

select count(*) from user where id in (select id from user_group);
select count(*) from user where exists(select 1 from user_group where user.id = user_group.id);

执行会发现差距明显，因此也说明了上面的结论：外表大内表小，用in要快些。

当在上面的sql语句每句前面加上explain extended的时候，发现了一个有意思的东西：

/* select#1 */ select count(0) AS `count(*)` from `mysql_learning`.`user_group` join `mysql_learning`.`user` where (`mysql_learning`.`user`.`id` = `mysql_learning`.`user_group`.`id`)

in 被优化成了join

然后，我们再将user_group（小表）作为外表试试

select count(*) from user_group where id in (select id from user);
select count(*) from user_group where exists(select 1 from user where user.id = user_group.id);

原以为大表在外的时候，exists吃了亏，这个时候会搬回一局，然而事实却是，in还是那么优秀，甚至比exists还要强点。

至于not exists，not in,大表在外，仍然是not in快于not exists

select count(*) from user where id not in (select id from user_group);
select count(*) from user where not exists(select 1 from user_group where user.id = user_group.id);

如果再切换下外表，发现not in和not exists差不多（not in稍快）。

总结下：

无论外表大小，in、not in都要强于相应的exists和not exists.

外表大，内表小，用exists是效率比较低的。

结果如何，跟MySQL版本，以及查询的参数列是主键，还是普通列，以及普通列是否有索引有关。要优化还是要依据实际情况。

顺便再插一个知识点,如果not in 里面包含null的话，会发现没有对应的值，执行下面的语句，就会发现，

explain extended select id from user where id not in (null);

console里是这样的（其实如果用id != 1也会变成id <>1）

/* select#1 */ select `mysql_learning`.`user`.`id` AS `id` from `mysql_learning`.`user` where (`mysql_learning`.`user`.`id` <> 1)

用的是<>null来进行判断的，如果是true的话才返回，但关键是任何任何值跟null做等于判断，结果都是false。

杂谈

index_merge

因为执行explain extended select first_name, last_name from employees where first_name = 'Georgi' or last_name = 'Bezalel';发现有了type为index_merge如果first_name和last_name上都有索引的话，便简单看了几篇文章，不做深入了解。

index_merge一般有两种，一种是使用or来取index的并集（union），一种是用and来取index的交集（intersect)

or一般会使用index_merge，因为一个条件不满足，还要找另一个条件的数据；但and有时候会出现不使用index_merge的情况。

比如explain extended select first_name, last_name from employees where first_name = 'Georgi' and last_name = 'Facello1';

Facello1在employees中不存在对应的数据，此时使用last_name上的索引扫描一遍发现没有匹配的数据，那就没必要进行index_merge了。倒是如果给定的first_name和last_name的值在索引上都能找到，那没办法，只能进行index_merge了，例如：

explain extended select first_name, last_name from employees where first_name = 'Georgi' and last_name = 'Facello'

Metadata Locking

MySQL使用metadata locking来管理对数据库对象的并发访问和用来保证数据的一致性。

参考

《高性能MySQL》

https://www.pianshen.com/article/4642323804/

in not in 和 null , in 判断范围中可以包含null，而not in判断不能包括null

MySQL索引与查询优化

这篇关于MySQL最佳实践的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！