《高性能MySQL》笔记前篇-优快云博客

本文链接：https://blog.youkuaiyun.com/mathphp/article/details/79738168

本文介绍MySQL的基础知识、事务处理、查询性能优化方法，重点讲解如何利用索引提高查询效率，包括哈希索引、前缀索引、聚簇索引、覆盖索引等多种索引类型的应用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、mysql基础知识

Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

三范式：

1NF:字段不可分;
2NF:有主键，非主键字段依赖主键; 
3NF:非主键字段不能相互依赖; 

解释:   
1NF:原子性 字段不可再分,否则就不是关系数据库;  
2NF:唯一性 一个表只说明一个事物;   
3NF:每列都与主键有直接关系，不存在传递依赖;

临时表：

 CREATE TEMPORARY TABLE 表名();  
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

获取MySQL服务器信息：

SELECT VERSION( )   服务器版本信息  
SELECT DATABASE( )  当前数据库名 (或者返回空)  
SELECT USER( )  当前用户名  
SHOW STATUS 服务器状态  
SHOW VARIABLES  服务器配置变量

复制表：

1、CREATE TABLE tmp SELECT * from table1; 
2、create table tmp like table1; 
   insert into tmp (values) select * from table1;

NSERT IGNORE INTO 、REPLACE INTO 与 INSERT INTO 的区别：

INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。
这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。  
INSERT INTO 向数据表中插入重复数据，执行后会出错；  
REPLACE INTO 如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

二、事务

1、事务的ACID概念：

原子性:一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性：一个事务所做的修改再最终提交以前，对其它事务是不可见的；
持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。

2、隔离级别

低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。详解

READ UNCOMMITTED(未提交读)：事务中的修改，即使没有提交，对其它事务也都是可见的；
READ COMMITTED(提交读)：一个事务从开始直到提交之前，所做的任何修改对其它事务都是不可见的；
REPEATABLE READ(可重复读)：在同一个事务中多次读取同样记录的结果是一致的；（MySQL默认事务隔离级别）
SERIALIZABLE(可串行化)：最高的隔离级别。通过强制事务串行化执行，避免幻读的问题。SERIALIZABLE会在读取的每一行数
    据上都加锁，可能导致大量的超市和锁争用的问题。只有在非常需要去报数据一致性且可以接受没有并发的情况下使用。

3、死锁

指两个或多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
innodb解决方法：将持有最少行级排他锁的事务进行回滚。

4、提交事务

MySQL默认采用自动提交（AUTOCOMMIT）模式。如果不是显示开始一个事务，则每个查询都被当作一个事务执行提交操作。
可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式：
mysql> show variables like 'AUTOCOMMIT';
mysql> set AUTOCOMMIT=1;

*注意：alter table , lock tables  命令在执行之前会强制执行COMMIT提交当前的活动事务；

三、剖析查询语句

1、慢查询日志

MySQL慢查询日志是一种轻量而且功能全面的性能剖析工具、CPU开销小；详解

2、定位最差的查询语句：

pt-query-digest工具

(1) 分析慢查询日志并生成剖析报告的工具：pt-query-digest;
(2) 定位报告中的MySQL：找到 # Query 1: … byte 3214 __ ; 
(3) 通过字节偏移值(3214)直接跳转到日志的对应部分，命令： 
    tail -c +3214 保存慢查询日志路径 | head -n100 ;

# 320ms user time, 70ms system time, 26.20M rss, 239.07M vsz
# Current date: Sat Mar 24 19:16:27 2018
# Hostname: localhost.localdomain
# Files: /var/log/mariadb/slow_query_log.txt
# Overall: 2 total, 1 unique, 0.09 QPS, 0.65x concurrency ________________
# Time range: 2018-03-24 18:54:22 to 18:54:45
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            15s      5s     10s      8s     10s      4s      8s
# Lock time              0       0       0       0       0       0       0
# Rows sent              2       1       1       1       1       0       1
# Rows examine           0       0       0       0       0       0       0
# Rows affecte           0       0       0       0       0       0       0
# Query size            31      15      16   15.50      16    0.71   15.50

# Profile
# Rank Query ID           Response time  Calls R/Call V/M   Item
# ==== ================== ============== ===== ====== ===== ======
#    1 0xF9A57DD5A41825CA 15.0024 100.0%     2 7.5012  1.67 SELECT

# Query 1: 0.09 QPS, 0.65x concurrency, ID 0xF9A57DD5A41825CA at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.67
# Time range: 2018-03-24 18:54:22 to 18:54:45
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       2
# Exec time    100     15s      5s     10s      8s     10s      4s      8s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent    100       2       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Query size   100      31      15      16   15.50      16    0.71   15.50
# String:
# Databases    db_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ################################################################
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(10)\G

3、剖析单条查询；

1、使用SHOW PROFILE；

mysql> set profiling = 1; #每个查询信息都会保存到一张临时表；
mysql> select * from zhang;
mysql> show profiles;
mysql> show profile for query 临时表id; # 提高精度，确定单条语句花费的时间在哪个步骤

*缺点：无法对花费的时间order by 排序；

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000033 |
| Waiting for query cache lock   |  0.000009 |
| init                           |  0.000008 |
| checking query cache for query |  0.001796 |
| checking permissions           |  0.000102 |
| Opening tables                 |  0.000660 |
| After opening tables           |  0.000017 |
| System lock                    |  0.000061 |
| Table lock                     |  0.000024 |
| Waiting for query cache lock   |  0.000101 |
| init                           |  0.000291 |
| optimizing                     |  0.000188 |
| statistics                     |  0.000245 |
| preparing                      |  0.000423 |
| executing                      |  0.000110 |
| Sending data                   | 18.697863 |
| end                            |  0.000044 |

耗费时间排序解决方案：

2、使用SHOW STATUS

说明：show status 命令返回会话级别的计数器，但不是剖析工具；可以用来猜测哪些操作代价较高或者消耗的时间较多。最有用的计数器包括句柄计数器（handler counter）、临时文件和表计数器等；

mysql> flush status; #将会话级别的计数器重置为0
mysql> select * from zhang;
mysql> show status where variable_name like 'Handler%' or variable_name like 'Created%';

四、索引

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

1、哈希索引

1.1 什么是哈希索引？

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。
哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。（只有Memory引擎显式支持哈希索引）

1.2 InnoDB 的“自适应哈希索引”

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。
这是一个完全自动的、内部的行为，用户无法控制或者配置。

2、伪哈希索引

创建伪哈希索引策略：

1、使用这个策略前，要先了解哈希索引的优劣和原理；
2、思路：在B-Tree基础上创建夜歌伪哈希索引，这和真正的哈希索引不是一回事。它是使用哈希值而
不是键本身进行索引查找，需要在查询的where子句中手动指定使用哈希函数；

实例

需要存储大量的url ， 并需要根据url进行搜索查找。直接用B-Tree来存储url存储的内容就会很大，
正常情况的查询有：
mysql> select id from url where url="http://www.mysql.com";
若删除原来的url列上的索引，而新增一个被索引的url_crc列 ，使用CRC32做哈希，就可以使用下面的方式查询：
mysql> select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");
 这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找
（上例中索引值为：1544886819）。

缺陷：需要维护哈希值。可以手动维护，也可以使用触发器实现。

1、使用触发器实现在插入和更新是维护url_crc列：

创建如下表：
create table pseudohash(
    id int unsigned not null auto_increment,
    url varchar(255) not null,
    url_crc int unsigned not null default 0,
    primary key(id)
);

创建触发器，先临时修改一下语句分隔符，这样就可以在触发器定义中使用分号：
mysql> delimiter //
mysql> create trigger pseudohash_crc_ins before insert on pseudohash for each row begin set new.url_crc=crc32(new.url); end;//
mysql> create trigger pseudohash_crc_upd before update on pseudohash for each row begin set new.url_crc=crc32(new.url); end;//
mysql> delimiter ;#注意这里的空格键

那么，新增或更新url同时url_crc也回自动更新；

2、注意

    采用这种方式切记不要使用SHA1()和MD5()作为哈希函数。这两个函数是强加密函数，计算出来的hash值时非常长的字符
串，会浪费大量空间，比较时也会更慢。它们设计目标是最大限度消除冲突（这里不需要这样高的要求）。如果数据表非常大，
crc32()会出现大量的哈希冲突，可以考虑实现一个简单的64位哈希函数（返回整数），例如：
mysql> select CONV(RIGHT(MD5('http://www.mysql.com/'),16),16,10) as hash64;

为了避免哈希冲突导致查询无法正常工作,where条件包含常量值url='http://www.mysql.com'：
select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");

3、前缀索引

什么是前缀索引？

有时候需要索引很长的字符列，这会让索引变得很大且慢。一个方法使用伪哈希索引，另外还可以是前缀索引；
前缀索引：索引开始的部分字符。可以大大节约索引空间，从而提高索引效率。但会降低索引的选择性(基数)；

3.1 计算不同前缀长度的选择性：

计算索引基数实例

如果继续增加前缀长度，选择性提升的幅度已经很小就不需在加；

2、创建前缀索引

alter table zhang add index `indexname`(col(6)); // 选择索引前缀长度为6
亲测,前缀索引和普通索引:alter table zhang add index `indexname`(col);
普通索引查询速度更快(可能测试列的字符较少，可比性低)，但两种索引都可以提升查询性能；

4、聚簇索引

什么是聚簇索引？

聚簇索引并不是一种单独索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
如果没有定义主键，InnoDB会选择唯一的非空索引替代。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚集的数据优点：

1、可以把相关的数据保存在一起；
2、数据访问更快；
3、使用覆盖索引扫描的查询可以直接使用主键值；

缺点

1、如果数据全部放到内存中，聚簇索引没什么优势；
2、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
3、二级索引可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列；
4、二级索引访问需要两次索引查找；


* 二级索引叶子结点保存的不是指向行的物理位置的指针，而是行的主键值；意味着通过二级索引查找行，
存储引擎需要找到二级索引的叶子结点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。
两次B-Tree查找，自适应哈希索引能够减少这样的重复工作。（好处：在移动行数据时无须更新二级索引的这个“指针”）

5、覆盖索引

什么是覆盖索引？

如果一个索引包含所有需要查询的字段的值，我们就称“覆盖索引”；

覆盖索引的好处

1、索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量；
2、如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询；

覆盖索引查询

在EXPLAIN的Extra列可以看到“Using index”信息；

+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | zhang | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

6、顺序主键什么时候会造成更坏的结果？

对于高并发工作负载，在innodb中主键顺序插入可能会造成明显的争用。主键的上界会成为"热点"，并发插入可能导致间隙锁竞争。
另一个热点可能是AUTO_INCREMENT锁机制；
解决：重新设计表或应用，或更改innodb_autoinc_lock_mode配置(可能不支持)；

7、索引条件下推

5.6版本的索引条件下推：参考

索引条件下推可以解释联合索引查询快： 
走索引的查询，只能走最左前缀列，其他联合索引的列在索引后再通过where过滤；

五、查询性能优化

1、查看执行计划重构后的查询：

explain extended 查询语句；
show warnings;

2、关于IN() 子查询

在IN()里面使用子查询，性能会非常糟糕；因为MySQL会将相关的外层表压倒子查询中，先对外表做全表扫描，
然后根据返回的字段逐个执行子查询；

例子：

select * from new_zhang a where id in(select b.id from new_zhang b where name like '%2018-01-26%');

MySQL重构查询：
select * from new_zhang a where exists(select b.id from new_zhang b where name like '%2018-01-26%' and a.id=b.id);

优化策略：

   1、直接使用重构查询的方法；省略重构查询步骤
   2、使用子查询：
   select * from new_zhang a inner join new_zhang b using(id) where b.name like '%2018-01-26%'；
   3、使用函数GROUP_CONCAT()构造由逗号分隔的列表，分两次查询；