想要发挥 MySQL 的最佳性能,需要遵循 3 个基本使用原则。
lower_case_table_names
的值为1
,即可关闭区分大小写功能,即大写字母 T 和小写字母 t 一样。unsigned
整数,可以使用auto_increment
,但是要禁止手动更新主键。comment
注释。engine
。xxx_id
、 xxx_create
、 xxx_modified
。其中xxx_id
为主键,类型unsigned
整数类型(例如:int unsigned
);xxx_create
、xxx_modified
的类型均为datetime
类型,分别记录该条数据的创建时间、修改时间。not null
,为空值指定default
值,因为MySQL难以优化null
值,含null
值的复合索引会失效,最终导致查询效率低。enum
和set
类型,因为这样的类型兼容性不好且性能较差。blob
类型而是保存它们的路径,blob
和text
这样的类型会导致处理性能下降,全表扫描代价大大增加。decimal
)而不是浮点数(float
)。char(15)
,应该使用int unsigned
,可以使用inet_aton
和inet_ntoa
函数实现整数和IP地址的转换。在前面《关系型数据库MySQL》一文中,我们已经讲到过索引的相关知识,这里我们做一个简单的回顾。
select
操作中要查询的列,最适合做索引的列是出现在where
子句中经常用作筛选条件或连表子句中作为表连接条件的列。过程,通常也称之为存储过程,它是事先编译好存储在数据库中的一组SQL的集合。调用存储过程可以简化应用程序开发人员的工作,减少与数据库服务器之间的通信,对于提升数据操作的性能是有帮助的,这些我们在之前的《关系型数据库MySQL》一文中已经提到过。
MySQL支持做数据分区,通过分区可以存储更多的数据、优化查询,获得更大的吞吐量并快速删除过期的数据。关于这个知识点建议大家看看MySQL的官方文档。数据分区有以下几种类型:
RANGE分区:基于连续区间范围,把数据分配到不同的分区。
CREATE TABLE tb_emp (
eno INT NOT NULL,
ename VARCHAR(20) NOT NULL,
job VARCHAR(10) NOT NULL,
hiredate DATE NOT NULL,
dno INT NOT NULL
)
PARTITION BY RANGE( YEAR(hiredate) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
LIST分区:基于枚举值的范围,把数据分配到不同的分区。
HASH分区 / KEY分区:基于分区个数,把数据分配到不同的分区。
CREATE TABLE tb_emp (
eno INT NOT NULL,
ename VARCHAR(20) NOT NULL,
job VARCHAR(10) NOT NULL,
hiredate DATE NOT NULL,
dno INT NOT NULL
)
PARTITION BY HASH(dno)
PARTITIONS 4;
定位低效率的SQL语句 - 慢查询日志。
查看慢查询日志相关配置
mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
创建慢查询日志文件并修改所有者。
touch /var/log/mysqld-slow.log
chown mysql /var/log/mysqld-slow.log
修改全局慢查询日志配置。
mysql> set global slow_query_log_file='/var/log/mysqld-slow.log'
mysql> set global slow_query_log='ON';
mysql> set global long_query_time=1;
或者直接修改MySQL配置文件启用慢查询日志。
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time=1
注意:修改了配置文件需要重启MySQL,CentOS上对应的命令是
systemctl restart mysqld
。
通过explain
了解SQL的执行计划。例如:
explain select ename, job, sal from tb_emp where dno=20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_emp
type: ref
possible_keys: fk_emp_dno
key: fk_emp_dno
key_len: 5
ref: const
rows: 7
Extra: NULL
1 row in set (0.00 sec)
select_type
:查询类型(SIMPLE - 简单查询、PRIMARY - 主查询、UNION - 并集、SUBQUERY - 子查询)。table
:输出结果集的表。type
:访问类型(ALL - 全表查询性能最差、index、range、ref、eq_ref、const、NULL)。possible_keys
:查询时可能用到的索引。key
:实际使用的索引。key_len
:索引字段的长度。rows
:扫描的行数,行数越少肯定性能越好。extra
:额外信息。通过show profiles
和show profile for query
分析SQL。
MySQL从5.0.37开始支持剖面系统来帮助用户了解SQL执行性能的细节,可以通过下面的方式来查看MySQL是否支持和开启了剖面系统。
select @@have_profiling;
select @@profiling;
如果没有开启剖面系统,可以通过下面的SQL来打开它。
set profiling=1;
接下来就可以通过剖面系统来了解SQL的执行性能,例如:
mysql> select count(*) from tb_emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------+
| 1 | 0.00029600 | select count(*) from tb_emp |
+----------+------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000076 |
| checking permissions | 0.000007 |
| Opening tables | 0.000016 |
| init | 0.000013 |
| System lock | 0.000007 |
| optimizing | 0.000005 |
| statistics | 0.000012 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.000070 |
| end | 0.000012 |
| query end | 0.000008 |
| closing tables | 0.000012 |
| freeing items | 0.000032 |
| cleaning up | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
优化CRUD操作。
优化insert
语句
insert
语句后面跟上多组值进行插入在性能上优于分开insert
。insert delayed
可以获得更好的性能。load data infile
比insert
性能好得多。优化order by
语句
where
子句的条件和order by
子句的条件相同,而且排序的顺序与索引的顺序相同,如果还同时满足排序字段都是升序或者降序,那么只靠索引就能完成排序。优化group by
语句
group by
子句分组时,如果希望避免排序带来的开销,可以用order by null
禁用排序。优化嵌套查询
优化or条件
or
关系,则只有在所有条件都用到索引的情况下索引才会生效。优化分页查询
分页查询时,一个比较头疼的事情是如同limit 1000, 20
,此时MySQL已经排序出前1020条记录但是仅仅返回第1001到1020条记录,前1000条实际都用不上,查询和排序的代价非常高。一种常见的优化思路是在索引上完成排序和分页的操作,然后根据返回的结果做表连接操作来得到最终的结果,这样可以避免出现全表查询,也避免了外部排序。
select * from tb_emp order by ename limit 10000, 20;
select * from tb_emp t1 inner join (select eno from tb_emp order by ename limit 10000, 20) t2 on t1.eno=t2.eno;
上面的代码中,第2行SQL是优于第1行SQL的,当然我们的前提是已经在ename
字段上创建了索引。
使用SQL提示
可以使用下面的命令来查看MySQL服务器配置参数的默认值。
show variables;
show variables like 'key_%';
show variables like '%cache%';
show variables like 'innodb_buffer_pool_size';
通过下面的命令可以了解MySQL服务器运行状态值。
show status;
show status like 'com_%';
show status like 'innodb_%';
show status like 'connections';
show status like 'slow_queries';
max_connections
:MySQL最大连接数量,默认151。在Linux系统上,如果内存足够且不考虑用户等待响应时间这些问题,MySQL理论上可以支持到万级连接,但是通常情况下,这个值建议控制在1000以内。back_log
:TCP连接的积压请求队列大小,通常是max_connections的五分之一,最大不能超过900。table_open_cache
:这个值应该设置为max_connections的N倍,其中N代表每个连接在查询时打开的表的最大个数。innodb_lock_wait_timeout
:该参数可以控制InnoDB事务等待行锁的时间,默认值是50ms,对于反馈响应要求较高的应用,可以将这个值调小避免事务长时间挂起;对于后台任务,可以将这个值调大来避免发生大的回滚操作。innodb_buffer_pool_size
:InnoDB数据和索引的内存缓冲区大小,以字节为单位,这个值设置得越高,访问表数据需要进行的磁盘I/O操作就越少,如果可能甚至可以将该值设置为物理内存大小的80%。通过拆分提高表的访问效率。
逆范式理论。数据表设计的规范程度称之为范式(Normal Form),要提升表的规范程度通常需要将大表拆分为更小的表,范式级别越高数据冗余越小,而且在插入、删除、更新数据时出问题的可能性会大幅度降低,但是节省了空间就意味着查询数据时可能花费更多的时间,原来的单表查询可能会变成连表查询。为此,项目实践中我们通常会进行逆范式操作,故意降低范式级别增加冗余来减少查询的时间开销。
使用中间表提高统计查询速度。
使用insert into 中间表 select ... where ...
这样的语句先将需要的数据筛选出来放到中间表中,然后再对中间表进行统计,避免不必要的运算和处理。
主从复制和读写分离,具体内容请参考《项目部署上线和性能调优》。
配置MySQL集群。
说明:本章内容参考了网易出品的《深入浅出MySQL》一书,该书和《高性能MySQL》一样,都对MySQL进行了深入细致的讲解,虽然总体感觉后者更加高屋建瓴,但是前者也算得上是提升MySQL技能的佳作(作者的文字功底稍显粗糙,深度也不及后者),建议有兴趣的读者可以阅读这两本书。
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。