当前位置: article > 正文

mysql优化总结

作者：2023面试高手 | 2024-06-05 00:43:13

踩

mysql优化总结

前言：欢迎各位朋友一起探讨

天下最大的库：数据库>>> BigData

一个库不要超过300-400个表; OLTP单表1G-1.5G以内，约500-1000万行数据。

1.1。水平划分

按照ID ，时间，范围等分表，尽量不要取模（可扩展性差）

1.2。垂直划分

1.2.1 按业务领域划分

1.2.2按列划分（表字段少，IO更高效，并发更高，速度更快）

2，表字段设计

2.1 字段类型，如果能用整型，最好用整型，存储小，查询快

tinyint：1Byte无符号：0-255有符号：-128至127

smallint ：2Byte 无符号：0-65535

mediumint：3Byte无符号：0-16777215

int：4Byte 无符号：0-42 亿+

bigint：8Byte

浮动：4Byte

双倍：8Byte

TIMESTAMP ：4Byte （1970-01-01 00:00:00 '到2037 年）：存储的是从Unix 纪元（格林威治时间

1970年1月1日00:00:00）到指定时间的秒数

日期时间：8Byte

优化1 ：将IP 地址char（15），采用int unsigned 存储，通过IPUtil.java 或者inet_aton ，inet_ntoa

转换

优化2 ：将createtime char（14），采用int 存储，通过java 类，UNIX_TIMESTAMP ，FROM_UNIXTIME

转换

优化3 ：将部分长度> 1 的状态值等，转成tinyint / int 存储

关于char（14），timestamp ，int 的比较

CREATE TABLE`test`（

`id` int（11）NOT NULL AUTO_INCREMENT，

`time1` char（14）NOT NULL DEFAULT'00000000000000'，

`time2` timestamp NOT NULL DEFAULT'0000-00 00:00:00'，

`time3` int（11）NOT NULL DEFAULT'0'，

`status` tinyint（1）NOT NULL DEFAULT'0'，

PRIMARY KEY（`id`）

）ENGINE = MyISAM AUTO_INCREMENT = 2529842 DEFAULT CHARSET = gbk;

只含时间1 ：68.5M

只含时间2：18.7M

只含时间3 ：18.7M

mysql>从test中选择count（*）;

+ ---------- +

| count（*）|

+ ---------- +

| 2529841 |

+ ---------- +

1排（0.00秒）

mysql>从test中选择SQL_NO_CACHE *，其中time1> ='20110903000000'和time1 <='20120303000000'按id限制200000，2;

+ --------- + ---------------- + --------------------- + ------------ + -------- +

+ --------- + ---------------- + --------------------- + ------------ + -------- +

| 1094577 | 20120220080634 | 2012-02-20 08:06:00 | 1329696360 | 8 |

| 1094578 | 20111101131834 | 2011-11-01 13:18:00 | 1320124680 | 7 |

+ --------- + ---------------- + --------------------- + ------------ + -------- +

2行（0.85秒）

mysql>从test中选择SQL_NO_CACHE *，其中time3> = 1314979200，time3 <= 1330704000 order by id limit 200000,2;

+ --------- + ---------------- + --------------------- + ------------ + -------- +

+ --------- + ---------------- + --------------------- + ------------ + -------- +

| 1094575 | 20120103192534 | 2012-01-03 19:25:00 | 1325589900 | 3 |

| 1094577 | 20120220080634 | 2012-02-20 08:06:00 | 1329696360 | 8 |

+ --------- + ---------------- + --------------------- + ------------ + -------- +

2行（0.62秒）

2.2字段定义避免为空，在MySQL的中，含有空值的列很难进行查询优化，因为它们使得索引，索引的统计信息以及比较运算更加复杂。你应该用0，一个特殊的值或者一个空串代替空值。

'A' CHAR（32）默认为空错

'b'int（11）not null 错

'c'int（11）not null default 0 对

尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。

2.3尽量不要使用文本/ BLOB，TEXT / BLOB类型处理能力较差，强制生产临时表，浪费更多空间

解决办法：

一，能否使用VARCHAR替换

湾垂直拆分表

创建表t（

id int not null auto_increment，

数据文本不为空，

主键（id）

）发动机=的myisam

2.4一个误区：TINYINT（1）和TINYINT（3）其实是一样的，只是显示宽度不一样

2.5 关于主键：通常整型是最好选的择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT 。尽量避免使用字符串，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂，随机访问磁盘

2.6。不建议使用ENUM ，SET 类型，使用TINYINT 来代替。

ENUM，有三个问题：添加新的值要做DDL，默认值问题（将一个非法值插入ENUM（也就是说，允许的值列之外的字符串），将插入空字符串以作为特殊错误值），索引值问题（插入数字实际是插入索引对应的值）

3.关于索引：

3.1 索引可以改善查询，但会减慢更新，索引不是越多越好，最好不超过字段数的20％（在数据增，删，改比较频繁的表中，索引数量不应超过5 个。

3.2离散程度越小，不适合加索引，例如：不要给性别建索引

test.status 取值范围：0-9 ，在状态列建索引引

mysql> select sql_no_cache * from test where status = 3 order by id limit 80000,2;

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

| 795783 | 20110825150959 | 2011-08-25 15:09:00 | 1314256140 | 3 |

| 795789 | 20120829052359 | 2012-08-29 05:23:00 | 1346188980 | 3 |

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

2排（1.26秒）

删除状态索引后

mysql> select sql_no_cache * from test where status = 3 order by id limit 80000,2;

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

| 795783 | 20110825150959 | 2011-08-25 15:09:00 | 1314256140 | 3 |

| 795789 | 20120829052359 | 2012-08-29 05:23:00 | 1346188980 | 3 |

+ -------- + ---------------- + --------------------- + - ----------- + -------- +

2行（0.37秒）

3.3。避免在空值（NULL）很多的字段上建立索引，大量空值会降低索引效率

3.4。避免在数据值分布不均的字段上建立索引，个别数据值占总数据量的百分率明显比其它数据值占总数据量的百分率高，表明该字段数据值分布不均，容易引起数据库选择错误索引，生成错误的查询执行计划。

3.5。在数据量较少且访问频率不高的情况下，如只有一百行记录以下的表不需要建立索引。因为在数据量少的情况下，使用全表扫描效果比走索引更好。

3.6。字符字段必须建前缀索引

单字母区分度：26

4 个字母区分度：26 * 26 * 26 * 26 = 456976

6个字母区分度：26 * 26 * 26 * 26 * 26 * 26 = 308915776

CREATE TABLE`test1`（

`id` int（11）NOT NULL AUTO_INCREMENT，

`a`char（20）NOT NULL DEFAULT''，

`b` varchar（14）NOT NULL DEFAULT'00000000000000'，

`c` varchar（14）DEFAULT'0000000000000000'，

PRIMARY KEY（`id`），

KEY`a`（`a`（6））

）ENGINE = MyISAM AUTO_INCREMENT = 12534199 DEFAULT CHARSET = gbk;

mysql>从test1中选择sql_no_cache count（*）;

+ ---------- +

| count（*）|

+ ---------- +

| 12534198 |

+ ---------- +

1排（0.00秒）

mysql>从test1中选择sql_no_cache count（*），其中a ='tR6cDjx0frXx45yURG3m';

+ ---------- +

| count（*）|

+ ---------- +

| 1 |

+ ---------- +

1排（0.00秒）

3.7。不在索引列做运算，尽量不用外键（InnoDB ）

3.8。唯一索引：在建立索引的字段所有数值都具有唯一性特点的情况下，建立唯一索引（唯一索引）代替普通索引，唯一索引（唯一索引）查询效率比普通索引查询效率更高，可以大幅提升查询速度。

4.组合索引

4.1。避免建立两个或以上功能相同索引。例如已经建立字段A ，B 两个字段的索引，应该避免再建立字段A 的单独索引。两个索引之间，对相同的查询都会起到相同的作用。建立两个功能相同的索引，反而会容易引起数据库产生错误的查询计划，降低查询效率。

4.2。选择正确的组合索引字段顺序，最常用的查询字段和选择性，区分度较高的字段，应该作为索引的前导字段使用。

假设存在组合索引it1c1c2（c1，c2），查询语句select * from t1其中c1 = 1且c2 = 2能够使用该索引。查询语句select * from t1 where c1 = 1也能够使用该索引。但是，查询语句select * from t1其中c2 = 2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

4.3。合适的字段数，组合索引的字段数不适宜较多，较多的组合索引字段数会降低索引查询效率，组合索引字段数应不多于3个，如业务特点需要建立多字段的组合主键例外。

关于一个B树索引的例子：

假设有如下一个表：

CREATE TABLE人（

last_name varchar（50）not null，

first_name varchar（50）not null，

dob date not null，

性别枚举（'m'，'f'）不为空，

key（last_name，first_name，dob）

）;

其索引包含表中每一行的last_name ，first_name 和dob 列。

索引存储的值按索引列中的顺序排列。可以利用B树索引进行全关键字，关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀（最左边的索引的前缀）来进行查询。

（1）匹配全值（匹配的全部价值）：对索引中的所有列都指定具体的值例如，上图中索引可以帮助你查找出生于。1960年1月1日的古巴艾伦。

（2）匹配最左前缀（匹配最左前缀）：你可以利用索引查找姓氏为Allen的人，仅仅使用索引中的第1列。

（3）匹配列前缀（匹配一个列前缀）：例如，你可以利用索引查找姓以Ĵ 开始的人，这仅仅使用索引中的第1 列。

（4）匹配值的范围查询（匹配一系列值）：可以利用索引查找姓氏在Allen和Barrymore之间的人，仅仅使用索引中第1列。

（5）匹配部分精确而其他部分进行范围匹配（精确匹配一部分并匹配另一部分的范围）：可以利用索引查找姓氏为艾伦，而名字以字母K 开始的人。

（6）仅对索引进行查询（Index-only queries）：如果查询的列都位于索引中，则不需要读取元组的值。

由于B- 树中的节点都是顺序存储的，所以可以利用索引进行查找（找某些值），也可以对查询结果进行ORDER BY 。当然，使用B-tree 索引有以下一些限制：

（1）查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。

（2）不能跳过某一索引列。例如，你不能利用索引查找姓氏为史密斯且出生于某一天的人。

（3）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name =“Smith”AND first_name LIKE'J％'AND dob ='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

另一个例子：

创建表`朋友'（

`id` int（10）unsigned NOT NULL AUTO_INCREMENT，

`uid`bigint（20）unsigned NOT NULL DEFAULT'0'，

`fuid`bigint（20）unsigned NOT NULL DEFAULT'0'，

`fname` varchar（50）NOT NULL DEFAULT''，

`fpicture` varchar（150）NOT NULL DEFAULT''，

`fsex` tinyint（1）NOT NULL DEFAULT'0'，

`status` tinyint（1）NOT NULL DEFAULT'0'，

PRIMARY KEY（`id`），

KEY`uid_fuid`（`uid`，`fuid`）

）ENGINE = MyISAM DEFAULT CHARSET = gbk;

5. 覆盖索引（覆盖指数）

如果索引包含满足查询的所有数据，就称为覆盖索引覆盖索引是一种非常强大的工具，能大大提高查询性能只需要读取索引而不用读取数据有以下一些优点。：

（1）索引项通常比记录要小，所以MySQL的访问更少的数据;

（2）索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I / O;

（3）大多数据引擎能更好的缓存索引。比如MyISAM数据只缓存索引。

（4）覆盖索引对于InnoDB的表尤其有用，因为InnoDB的使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

注意：覆盖索引不能是任何索引，只有B-TREE 索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引（Memory 和Falcon 就不支持）。

对于索引覆盖查询（index-covered query），使用EXPLAIN时，可以在Extra一列中看到“使用索引”

创建表`朋友'（

`id` int（10）unsigned NOT NULL AUTO_INCREMENT，

`uid`bigint（20）unsigned NOT NULL DEFAULT'0'，

`fuid`bigint（20）unsigned NOT NULL DEFAULT'0'，

`fname` varchar（50）NOT NULL DEFAULT''，

`fpicture` varchar（150）NOT NULL DEFAULT''，

`fsex` tinyint（1）NOT NULL DEFAULT'0'，

`status` tinyint（1）NOT NULL DEFAULT'0'，

PRIMARY KEY（`id`），

KEY`uid_fuid`（`uid`，`fuid`）

）ENGINE = MyISAM DEFAULT CHARSET = gbk;

6. 排序

MySQL的中，有两种方式生成有序结果集：

一个。文件排序糟糕

湾指数排序好

什么时候使用索引排序？

当索引的顺序与ORDER BY 中的列顺序相同且所有的列是同一方向（全部升序或者全部降序）时，可以使用索引来排序。其它情况都会使用filesort 。

什么时候使用filesort ？

当MySQL不能使用Index排序时，就会利用自己的排序算法在内存（sort buffer）中对数据进行排序各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。

当对连接操作进行排序时，如果ORDER BY 仅仅引用第一个表的列，MySQL 对该表进行filesort 操作，然后进行连接处理，此时，EXPLAIN 输出“ 使用filesort ”;

否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“使用临时;使用filesort”。

通过索引优化来实现MySQL的ORDER BY语句优化例子：

1，ORDER BY的索引优化。如果一个SQL语句形如：

SELECT [column1]，[column2]，.... FROM [TABLE] ORDER BY [sort];

在[sort]这个栏位上建立索引就可以实现利用索引进行的优顺。

2，WHERE + ORDER BY的索引优化，形如：

SELECT [column1]，[column2]，.... FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

建立一个联合索引（columnX，sort）来实现by优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化

SELECT [column1]，[column2]，.... FROM [TABLE] WHERE [columnX] IN（[value1]，[value2]，...）ORDER BY [sort];

3 ，WHERE + 多个字段ORDER BY

SELECT * FROM [table] WHERE uid = 1 ORDER x，y LIMIT 0,10;

建立索引（uid，x，y）实现顺序由的优化，比建立（x，y，uid）索引效果要好得多。

MySQL Order By不能使用索引来优化排序的情况：

1，对不同的索引键做ORDER BY :( key1，key2分别建立索引）

SELECT * FROM t1 ORDER BY key1，key2;

2，用于where语句的索引和ORDER BY的不是同一个：（key1，key2分别建立索引）

SELECT * FROM t1 WHERE key2 = constant ORDER BY key1;

3 ，同时使用了 ASC 和 DESC ：（key_part1，key_part2 建立联合索引），通过哪里语句将命令由中索引列转为常量，则除外

SELECT * FROM t1 ORDER BY key_part1 DESC，key_part2 ASC;

4 ，如果在WHERE 或ORDER BY 的栏位上应用表达式（函数）时，则无法利用索引来实现order by 的优化

SELECT * FROM t1 ORDER BY YEAR（logindate）LIMIT 0,10;

5，检查的行数过多，且没有使用覆盖索引

6 ，在那里语句中使用了条件查询

7. 关于分组或不同

7.1。尽量只对存在索引的字段进行group by或distinct。当组由不能使用index时mysql有两种处理方法：临时表和filesort。

7.2。在群由语句中mysql 会自动命令，如果不需要可使用命令由null 来禁止自动的命令。

8.关于索引失效

8.1。避免对索引字段计算

8.2。避免使用索引列值是否可为空的索引，如果索引列值可以是空值，在SQL 语句中那些要返回NULL 值的操作，将不会用到索引。

8.3。相同的索引列不能互相比较，这将会启用全表扫描，如tab1 上存在索引idx_col1_col2（col1，col2），其中col1 和col2 都是int 型。则查询语句SELECT * FROM tab1 WHERE col1> col2 ; 是不会使用索引的。

8.4。避免使用存在潜在的数据类型转换的索引。潜在的数据转换，查询条件中是指由于等式两端的数据类型不一致。例如索引字段使用的是数字类型，而条件等式的另一端数据类型是字符类型，数据库将会对其中一端进行数据类型转换，数据类型的转换会让索引的作用失效，令数据库选择其他的较为低效率的访问路径。

8.5使用索引列作为条件进行查询时，需要避免使用<>或者！=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改。由查询条件中其他索引字段代替。

a）尽量避免负向查询：NOT ，！= ，<> ，！< ，！> ，NOT EXISTS ，NOT IN ，NOT LIKE ，避免％前模糊查询

b）如果条件中的范围查询（IN，BETWEEN，<，<=，>，> =）会导致后面的条件使用不了索引。

8.6。使用索引列作为条件进行范围查询时，应该避免较大范围取值。

9. 存储引擎介绍

9.1.MyISAM ：不支持事务，支持外键，表锁，选择速度快。该引擎可以将数据文件和索引文件放在不同的磁盘上，平均分布IO ，提高速度。

9.2.InnoDB ：提供具有提交，回滚和崩溃恢复能力的事务安全。支持外键约束。采用的是行级锁，所以对于有大量插入，更新操作的表能提供更好的并发性。相对于myisam ，InnoDB 更适用于大数据量。

我们最新测试发现：Mysql5.5,5.6的Innodb的引擎有了很大改善，并发读写能力有了很大提升。

9.3.MEMORY：该引擎的数据是存放在内存中的，默认使用的是散列索引，所以访问速度快，但一旦服务关闭，表中数据会丢失每个存储器表只对应一个存放表结构的。 FRM磁盘文件。

9.4.MERGE：该引擎是一组的MyISAM表的组合，这些的MyISAM表必须结构完全相同，MERGE表本身没有数据，对MERGE类型的表可进行查询，更新，删除操作，这些操作是对内部的实际的MyISAM数据表进行的。对MERGE表执行降操作只会删除MERGE表的定义，对内部的MyISAM表没有影响。

10.高效分页

传统分页select * from table limit m，n 其中m 越大，速度越慢

推荐分页：

方式一：select * from friends where id> = 6001 limit 0,20;

方式二：select * from friends where id> =（select friends from friends limit 6000,1）limit 0,20;

方式三：select * from friends inner join（select table from table limit 6000,20）using（id）;

方式四：

从朋友中选择id限制6000,20;

从朋友中选择*，其中id为（...）;

11.关于SQL类

11.1 使用预备语句，可以提高性能并避免SQL 注入。

11.2.UPDATE，DELETE语句不使用限制。

一）可能导致主从数据不一致 b）中会记录到错误日志，导致日志占用大量空间

11.3.WHERE条件中必须使用合适的类型，避免MySQL的进行隐式类型转化。

原则：数值对数值，字符对字符

11.4。拒绝Big sql，改为多个简单sql

a）OLTP 中，绝对不允许使用多表关联查询，改为单表查询

Mysql vs传统数据库设计思想，传统思想：尽量减少sql命令次数，一次获取所有数据，但

Mysql的可以快速打开和关闭连接。

b）中不要使用生产数据库查询，统计大数据

11.5.openSession 和closeSession 之间，尽量减少业务操作，减少链接占用时间。

恶习：用户发帖过程，先打开链接，等待图片上传后做，插入操作后关闭连接。

11.6。尽量不用select * ，只取用到的列

一）减少表变化带来的影响

B）为使用覆盖索引提供了可能性

c）中减少硬盘临时表的生产，尤其是含TEXT / BLOB字段

11.7.OR 语句的改写

一）同一字段改成成在，注意控制在的个数，小于200

b）不同字段，改成union all

11.8。用INSERT ... ON DUPLICATE KEY UPDATE避免唯一键引起的插入错误

CREATE TABLE ipstats（

ip VARCHAR（15）NOT NULL UNIQUE，

单击SMALLINT（5）UNSIGNED NOT NULL DEFAULT'0'

）;

原本需要执行3 条SQL 语句，如下：

IF（SELECT * FROM ipstats WHERE ip ='192.168.0.1'）{

UPDATE ipstats SET clicks = clicks + 1 WHERE ip ='192.168.0.1';

} else {

INSERT INTO ipstats（ip，clicks）VALUES（'192.168.0.1'，1）;

}

而现在只需下面1 条SQL 语句即可完成：

INSERT INTO ipstats VALUES（'192.168.0.1'，1）ON DUPLICATE KEY UPDATE clicks = clicks + 1;

注意，要使用这条语句，前提条件是这个表必须有一个唯一索引或主键。

11.9。尽量不要让数据库做运算，复杂的运算移到程序端。从单条SQL来看，此SQL消耗的CPU资源

有限，但如果调用的频率非常高，就会引起非常明显的CPU 消耗

如：MD5（）/由Rand订购（）

现在选择（）;

SELECT CURRENT_TIME;

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;等

12. 关于计数

12.1。对于myisam表，当查询为不含有where条件时，count（*）返回会比较快，因为myisam存储引

擎会记录每个表的行数量，所以会快速返回。注意：innodb 等其他存储引擎是不存储行

总数。

12.1。众说风云，但测试结果发现在新版本Mysql 中，count（*）count（0）count（1）没有太大不一样

12.2。count（*）！= count（col）

如果MySQL的知道指定的字段栏设置为不可为空的情况，优化器会将计数（COL）转化为

COUNT（*）也是直接从存储引擎那里获取记录数量，如果山坳是含有空的字段，则计数（COL）

只统计非空字段的行数。

12.3。含有其中子句的COUNT（*）的SQL，需要扫描数据计数，速度较慢。

12.4。考虑在选择计数（col）...其中col = ... 的col 字段上添加索引，以便通过扫描col 即可

获得结果。

12.5。由于选择count（*）from ...其中col1 = ...和col2 = ... ，假设col1 字段上有索引，col2

上无索引。这种情况下，MySQL的会先扫描索引筛选满足条件的COL1 = ...，再通过索引上的ROWID

查找表中的记录，判定是否也满足COL2 = ... 的条件，如果满足，记录数据。在这种情况下，如

果符合条件col1 = ...的记录数很多，速度就会较慢。杜绝SELECT COUNT（COL1）FROM tablename

在哪里COL2 ='价值'

12.6。对于数如果有必要，可考虑添加一个辅助表，用于专门记录每个统计值，或内存计数，每天

凌晨校准基值

13. 关于解释

13.1。程序猿养成良好习惯，解释每一个写出的SQL

13.2.explain 中一些重要的列

a）类型列：all> index> range> ref> eq_ref> const，system

all ：全表扫描

指数：全索引扫描

rang ：以范围的形式扫描索引，> <在查询中

裁判：非唯一性索引访问

eq_ref ：最多只会有一条匹配结果，一般使用有唯一性索引查找（主键或唯一性索引）

常量：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次

system ：系统表，表中只有一行数据

b）possible_keys ：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null ，这

项内容对优化索引时的调整非常重要

c）密钥：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引

d）行：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字

就是1 。

e）额外：辅助信息

使用索引：所需数据只需在首页即可全部获得，不须要再到表中取数据。OK

使用filesort：当前查询中含ORDER BY操作，而且无法利用索引完成排序操作的时候，MySQL

查询优化器不得不选择相应的排序算法来实现。坏

使用临时：使用临时表时，主要常见于GROUP BY和ORDER BY等操作中Bad

使用位置：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，

则会出现使用where信息。可理解为“后过滤”（后过滤），所谓“后

过滤”，就是先读取整行数据，再检查此行是否符合其中句的条件，符合就

留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

优化的SELECT表：当我们使用某些聚合函数来访问存在索引的某个字段时，

MySQL Query Optimizer会通过索引直接一次定位到所需的数据行完成整个查

询。当然，前提是在查询中不能有 GROUP BY 如使用操作。MIN（）或MAX（）

的时候.OK

使用index for by-by ：数据访问和使用index 一样，所需数据只须要读取索引，当

查询中使用GROUP BY或DISTINCT子句时，如果分组字段也在索引中，Extra

中的信息就会是使用index for group-by 。好的

13.3。归根结底：至少要rang以上，在额外列出尽量避免额外列出现：使用文件排序，使用临时。

总结

1.合理分库，分表，保持记录数<500-1000万，表数据文件大小<1G

2. 合理使用数据类型，减少浪费存储空间，整型查询速度较快

3.表结构定义时，字段要非空

4. 只在离散程度较大的字段上建索引

5.合理使用组合索引，离散程度大的字段靠左放，并遵循从左到右原则

6. 对于核心sql ，想尽一切办法使用覆盖索引

7.排序尽可能使用索引排序

8. 避免出现索引失效

9.杜绝多表关联查询，将Mysql的当做nosql的使用

10. 不在MySQL的端做运算

最重要的核心思想：

养成良好的解释习惯，类型：至少rang以上，额外避免出现：使用文件排序，使用临时

下图是MySQL架构的总览图

1. 查询执行流程

查询执行的流程是怎样的：

1. 连接

1.1客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求

1.2将请求转发到‘连接进/线程模块’

1.3调用‘用户模块’来进行授权检查

1.4通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求

2. 处理

2.1先查询缓存，检查Query语句是否完全匹配，

2.2查询缓存失败则转交给‘命令解析器’

2.3再转交给对应的模块处理

2.4如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划

2.5模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限

2.6有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件

2.8根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理

2.9上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中

3. 结果

3.1Query请求完成后，将结果集返回给‘连接进/线程模块’

3.2返回的也可以是相应的状态标识，如成功或失败等

3.3‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接

什么是优化

合理安排资源、调整系统参数使MySQL运行更快、更节省资源。
优化是多方面的，包括查询、表设计、服务器等。
原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

查询优化

在优化MySQL时，通常需要对数据库进行分析。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

1. 慢查询
  1. 慢查询日志开启

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=5

注：log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；

long_query_time=5中的5表示查询超过五秒才记录；

还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。

1. 1. 慢查询分析

我们可以通过打开log文件查看得知哪些SQL执行效率低下

从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

进入log的存放目录，运行

[root@mysql_data]#mysqldumpslow slow-query.log

Reading mysql slow query log from slow-query.log

Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql

select count(N) from t_user;

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

得到按照时间排序的前10条里面含有左连接的查询语句。

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

1. EXPLAIN

在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT * FROM products

1. 1. id

SELECT识别符。这是SELECT查询序列号。这个不重要。

1. 1. select_type

表示SELECT语句的类型。

例如：

SIMPLE
表示简单查询，其中不包含连接查询和子查询。

PRIMARY
表示主查询，或者是最外面的查询语句。
UNION
表示连接查询的第2个或后面的查询语句。
1. 1. table

表示查询的表。

1. 1. type

表示表的连接类型。

以下的连接类型的顺序是从最佳类型到最差类型：

system
表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。

const
数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于
eq_ref
mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
ref
查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
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
对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）
1. 1. possible_keys

指出MySQL能使用哪个索引在该表中找到行。

如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。

1. 1. key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

1. 1. key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

注意：key_len是确定了MySQL将实际使用的索引长度。

1. 1. ref

显示使用哪个列或常数与key一起从表中选择行。

1. 1. rows

显示MySQL认为它执行查询时必须检查的行数。

1. 1. 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查询的所有列,而不要额外搜索硬盘访问实际的表。

索引使用

1. MySQL索引
  1. B-Tree索引

一般来说，MySQL中的B-Tree索引的物理文件大多都是以二叉树的结构来存储的，也就是所有实际需要的数据都存放于树的叶子节点，而且到任何一个叶子节点的最短路径的长度都是完全相同的。

1. 1. 1. R-Tree索引

RTREE在mysql很少使用，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找.

1. 1. 1. Hash索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

Hash索引无法被利用来避免数据的排序操作；

Hash索引不能利用部分索引键查询；

Hash索引在任何时候都不能避免表扫面；

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

1. 1. 1. Full-text索引

Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

1. 1. 创建索引

是否需要创建索引，几点原则：

较频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合创建索引；
不会出现在WHERE子句中的字段不该创建索引；

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

1. 1. 使用索引
    1. 使用联合索引的查询

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

1. 1. 1. 使用OR关键字的查询

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

存储优化

存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。

存储数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：

1. 存储引擎介绍
  1. MyISAM存储引擎

MyISAM存储引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。

每一个表都被存放为三个以表名命名的物理文件。有存放表结构定义信息的.frm文件，还有存放了表的数据.MYD文件和存放索引数据的.MYI文件。

1. 1. Innodb 存储引擎

Innodb 存储引擎是事务安全的, 因此如果需要一个事务安全的存储引擎,建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑应该使用InnoDB表。

InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。

在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上提供外键约束(FOREIGN KEY constraints)的表引擎。

InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与MyISAM不一样。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份。备份的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

1. MyISAM和Innodb的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

1. 1. 具体实现的差别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

1. 1. 应用场景
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
1. MyISAM存储优化
  1. 禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

1. 1. 禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;

开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

1. 1. 批量插入数据

插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。

第二种方式的插入速度比第一种方式快。

1. 1. 使用LOAD DATA INFILE

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

1. InnoDB
  1. 禁用唯一性检查

用法和MyISAM一样。

1. 1. 禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0;

开启：SET foreign_key_checks = 1;

1. 1. 禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：SET autocommit = 0;

开启：SET autocommit = 1;

数据库结构优化
1. 优化表结构

1. 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
2. 对于只包含特定类型的字段，可以使用enum、set 等符合数据类型。
3. 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型。
4. 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
5. VARCHAR的长度只分配真正需要的空间
6. 尽量使用TIMESTAMP而非DATETIME，
7. 单表不要有太多字段，建议在20以内
8. 合理的加入冗余字段可以提高查询速度。

1. 表拆分
  1. 垂直拆分

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作，我们在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

1. 1. 水平拆分

水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

1. 分区

使用分区是大数据处理后的产物。比如系统用户的注册推广等等，会产生海量的日志，当然也可以按照时间水平拆分，建立多张表。但在实际操作中，容易发生忘记切换表导致数据错误。

分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。

1. 读写分离

大型网站会有大量的并发访问，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何减少数据库的联接。

我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力

1. 数据库集群

如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现写操作。

例如可以使用MyCat搭建MySql集群，对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用。

硬件优化

是服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

可以从以下几个方面考虑：

配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。

磁盘I/O相关

使用SSD或者PCIe SSD设备，至少获得数百倍甚至万倍的IOPS提升；
购置阵列卡同时配备CACHE及BBU模块，可明显提升IOPS
尽可能选用RAID-10，而非RAID-5
使用机械盘的话，尽可能选择高转速的，例如选用15000RPM，而不是7200RPM的盘

配置CPU相关

　　在服务器的BIOS设置中，可调整下面的几个配置：

选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能；
关闭C1E和C States等选项，提升CPU效率；
Memory Frequency（内存频率）选择Maximum Performance；

MySQL缓存

为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。当我们的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。

但一个缓存机制是否有效，效果如何，却是一个需要好好思考的问题。Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。

举个例子，如果数据表item访问频繁，那么意味着它的很多数据会被QC缓存起来，但是每一次item数据表的更新，无论更新是不是影响到了cache 的数据，都会将全部和item表相关的cache清除。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。有实验表明，糟糕时，QC会降低系统13%的处理能力。

1. 1. 全局缓存

数据库属于IO密集型的应用程序，其主职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个 IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是IO，尽可能将磁盘IO转化为内存IO,也就是使用缓存

启动MySQL时就要分配并且总是存在的全局缓存，可以在MySQL的my.conf或者my.ini文件的[mysqld]组中配置。

目前有：key_buffer_size(默认值：402653184,即384M)、innodb_buffer_pool_size(默认值：134217728即：128M)、innodb_additional_mem_pool_size（默认值：8388608即：8M）、innodb_log_buffer_size(默认值：8388608即：8M)、query_cache_size(默认值：33554432即：32M)等五个。总共：560M.

1. 1. 1. key_buffer_size

key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。

严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

1. 1. 1. innodb_buffer_pool_size

innodb_buffer_pool_size:主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。

1. 1. 1. innodb_additional_mem_pool_size

innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

1. 1. 1. innodb_log_buffer_size

innodb_log_buffer_size这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer

InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

1. 1. 1. query_cache_size

query_cache_size: 主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。

当我们打开了 Query Cache功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求，MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache中直接查找是否已经缓存。如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤(如SQL语句的解析，优化器优化以及向存储引擎请求数据等)，极大的提高性能。

当然，Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失

1. 1. 局部缓存

除了全局缓冲，MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，

sort_buffer_size，read_rnd_buffer_size，tmp_table_size 大小的内存空间. 不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。

1. 1. 1. read_buffer_size

read_buffer_size是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能.

1. 1. 1. sort_buffer_size

sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小

1. 1. 1. read_rnd_buffer_size

read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

1. 1. 1. tmp_table_size

tmp_table_size是MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。

如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。

1. 1. 1. record_buffer:

record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

1. 1. 其它缓存
    1. table_cache

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)，table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

4.2.3.2 thread_cache_size (服务器线程缓存)

默认的thread_cache_size=8，,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，

增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

MySQL服务器参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：

1. back_log

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中（每个连接256kb,占用：125M）。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

1. wait_timeout

当MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout值为8个小时。

设置这个值是非常有意义的，比如你的网站有大量的MySQL链接请求（每个MySQL连接都是要内存资源开销的），由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态（可用showprocesslist)，如果经常发现MYSQL中有大量的Sleep进程，则需要修改wait-timeout值了。

1. max_connections

max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

MySQL服务器允许的最大连接数16384

1. max_user_connections

max_user_connections是指每个数据库用户的最大连接针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

1. thread_concurrency

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍。

1. skip-name-resolve

skip-name-resolve：禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

1. default-storage-engine

default-storage-engine= InnoDB(设置InnoDB类型，另外还可以设置MyISAM类型)设置创建数据库及表默认存储类型

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/674171