MySQL性能优化及存储过程

yoyo 8/7/2021 索引索引原理SQL优化存储过程性能优化高可用数据架构

# 1. 基本概念

# 1.1 索引

# 1.1.1 索引的简介

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

# 1.1.2 索引的优缺点

优点：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。

缺点：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表增删改操作的效率，因为在修改数据表的同时还需要修改索引表。

# 1.2 存储过程

# 1.2.1 存储过程的简介

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

# 1.2.2 存储过程的优缺点

优点：预编译SQL，提升执行效率；可以隐藏执行逻辑，只暴露名称和参数；相较于程序来说，修改起来更加便捷。

缺点：随着SQL行数的增加，维护复杂度呈线性提升；无法调试，迭代过程中风险较高。

# 2. 索引优化

# 2.1 MySQL中关于索引的语法

# 2.1.1 创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);

1
2
3
4
5

在创建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);

1
2
3

注意事项：

1）索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够。

2）创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行。

# 2.1.2 删除索引

DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;

1
2
3

# 2.1.3 查看表中的索引

SHOW INDEX FROM tablename

# 2.1.4 查看查询语句使用索引的情况

// explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

1
2

# 2.2 索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

# 2.2.1 主键索引

即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值。

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');

# 2.2.2 唯一索引

用来建立索引的列的值必须是唯一的，允许空值。

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

# 2.2.3 普通索引

用表中的普通列构建的索引，没有任何限制。

ALTER TABLE 'table_name' ADD INDEX index_name('col');

# 2.2.4 全文索引

用大文本对象的列构建的索引。

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');

# 2.2.5 组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1，<col1,col2>，<col1,col2,col3>三个索引，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引。如下示例表示使用col1的前4个字符和col2的前3个字符作为索引。

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3));

# 2.3 索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等。

# 2.3.1 哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

# 2.3.2 全文索引

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

// 创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

// 创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

1
2
3
4
5
6
7
8
9
10

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

注意事项：

1）对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

2）5.6之前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎全文索引不会生效。5.6之后InnoDB存储引擎开始支持全文索引。

3）在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

4）在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

# 2.3.3 BTree索引和B+Tree索引

[1] BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

每个叶子结点的高度一样，等于h；
每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
叶子结点指针都为null；
非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

BTree的结构如下：

BTree索引

在BTree的结构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

[2] B+Tree索引

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

B+Tree的结构如下：

B+Tree相比于BTree的优点：

1）磁盘读写代价更低

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区，而操作系统的块通常是整数倍的sector，操作系统以页为单位管理内存，一页通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储数据，就可以存储更多的key。

2）查询速度更稳定

由于B+Tree非叶子节点不存储数据，因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

[3] 带顺序索引的B+Tree索引

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

带顺序索引的B+Tree的结构如下：

# 2.3.4 聚簇索引和非聚簇索引

分析了MySQL的索引结构的实现原理，然后再来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

聚簇索引的解释是：聚簇索引的顺序就是数据的物理存储顺序
非聚簇索引的解释是：索引顺序与数据物理排列顺序无关

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。（mysql5.6以后innoDB也支持全文索引）

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别：

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

此外MyISAM和innoDB的区别总结如下：

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

此外，Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引。

# 2.4 索引的使用策略

# 2.4.1 什么时候要使用索引

主键自动建立唯一索引；
经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引；
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引。

# 2.4.2 什么时候不要使用索引

经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快——不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

# 2.4.3 索引失效的情况

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不会用了。
LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(date)<2022，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where date<'2022-01-01'。
在查询条件中使用不等于会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效，如果对主键索引或者整数类型的索引使用<或者>符号不会使索引失效。
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致索引失效，应该改为WHERE email='99999'。
在查询条件中使用OR连接多个条件会导致索引失效，除非OR连接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
如果排序的字段使用了索引，那么SELECT的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 不会导致索引失效。
尽量不要包括多列排序，如果一定要，最好为这些列构建组合索引。

# 2.5 索引的优化

# 2.5.1 最左前缀原则

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3种情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

# 2.5.2 带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化。

为检索的条件构建全文索引，然后使用。

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

# 2.5.3 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

# 3. SQL查询性能优化

# 3.1 关于全表扫描的注意点

[1] != 与 <>：=和<>都是不等于的意思，应尽量避免在 where 子句中使用 != 或 <> 操作符，否则会放弃使用索引而进行全表扫描。

[2] like的‘%%’ 与 ‘%’：like '%a%'将导致全表扫描，like '%a'可以用到索引，但最好是使用instr关键词进行替换查询。

[3] where子句：尽量避免在 where 子句中对字段进行表达式操作或函数操作，否则系统将可能无法正确使用索引。

[4] select *语句：尽量不要使用 select * from table ，用具体的字段列表替换*，不要返回用不到的字段，避免全盘扫描。

# 3.2 关键字替换查询

[1] in与exists：根据A和B表数据量的大小来选择合适的关键字，如果数据量数量级相近则用哪个都可以。

select * from A where id in (select id from B);                    --适用于A>>B
select * from A where exists (select 1 from B where A.id = B.id);  --适用于B>>A

1
2

注：in里面不要再关联数据量特别大的主表了。

[2] like与instr：like的写法都是低效的，可以用类似于instr(code, 'test') > 0的语句进行替换。

[3] or与union、in：or的写法都是低效的，通通可以用union、in等关键字进行替换。

[4] union与union all：当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union。

# 3.3 MySQL的语句执行顺序

MySQL的语句执行顺序如下：

from -> on -> join -> where -> group by -> avg,sum.... -> having -> select -> distinct -> order by -> limit

# 4. 存储过程

# 4.1 创建存储过程

如下是一个简单的查询存储过程示例（//处不是多余的，注意事项里会讲解）

DELIMITER //
 CREATE PROCEDURE GetAllUsers()
   BEGIN
   SELECT * FROM user;
   END //
 DELIMITER ;

1
2
3
4
5
6

注意事项：

第一个命令是DELIMITER //，与存储过程语法无关。DELIMITER语句将标准分隔符（分号;）更改为另一个符号（示例里用了//）。在END关键字之后，我们使用分隔符// 来标识存储过程的结束。最后一个命令DELIMITER;将分隔符更改回分号。
使用CREATE PROCEDURE 语句来创建一个新的存储过程，在CREATE PROCEDURE 语句后指定存储过程的名称。
BEGIN 和 END 之间的部分称为存储过程的主体。将SQL语句放在这里用于处理业务逻辑。

# 4.2 查看存储过程

可以使用如下命令查看指定的数据库中有哪些存储过程：

select name from mysql.proc where db='数据库名';

使用 SHOW PROCEDURE STATUS 加上存储过程名（注意不带括号）查看具体的存储过程状态。

 SHOW PROCEDURE STATUS LIKE 'GetAllUsers';

# 4.3 调用存储过程

要调用存储过程，使用 CALL 加上存储过程名（需要带上括号）即可。

CALL GetAllUsers();

# 4.4 删除存储过程

要删除存储过程，使用 DROP PROCEDURE 加上存储过程名（注意不带括号）即可。

DROP PROCEDURE GetAllUsers;

# 5. 高可用数据架构

# 5.1 主备式架构

主备式架构是双机部署中最简单的一种架构了，几乎市面上所有的数据库系统都会自带这个主备功能。主机作为日常提供数据读写服务的机器，备机并不提供线上服务，但会实时的将主机的数据同步过来。一旦主机出了故障，通过人工的方式，手动的将主机踢下线，将备机改为主机来继续提供服务。

优点是几乎不需要做什么开发改造，各类数据库就支持这种模式，部署维护起来也简单，并没有引入额外的系统复杂度和瓶颈。缺点是主备架构会造成严重浪费资源，而且当主机出现故障的时候，需要人工去干预，处理还不一定及时。

# 5.2 主从式架构

主从式架构中的从机虽然也在随时随刻提供服务，但是它只提供读服务，并不提供写服务。主机会实时的将线上数据同步到从机，以保证从机能够正常的提供读操作。当主机出现故障后，从机能够自动检测发现，同时从机将自己迅速切换为主机，将原来的主机立即下线服务，或转换为从机状态。

MySQL主从架构

主从式架构的优点是可以保证高可用、水平扩展、读写分离、数据备份。但使用该架构要实现“主从双机自动切换”，在设计时要考虑“主机与从机之间的状态如何判断”、“切换策略问题”、“数据冲突问题”。

# 5.3 主主式架构

主主式架构（又称为互为主从架构）是指两台机器自己都是主机，并且也都是作为对方的从机。两台机器都提供完整的读写服务，因此无需切换，客户机在调用的时候随机挑选一台即可，当其中一台宕机了，另外一台还可以继续服务。

采用互为主从架构有个复杂点就是，因为两台主机都接受写数据，那就需要将写的最新数据实时的同步给对方，需要将数据进行两台主机的双向复制。而双向复制不可避免的会在一定程度上带来数据延迟、极端情况下甚至有数据丢失等问题。在实际业务中，有些业务数据对一致性要求是非常高的，并不能接受数据的延迟、丢失，因此这类业务也不适合互为主从的模式，比如金融业务。