1、缓存。缓存是解决这类问题的一把手。它既可以加快整个系统（并非数据库系统，使用缓存的时候并没有去访问数据库）的访问速度，也可以减少数据库负载的压力。而缓存一般都是在查询中使用，我们并不希望每一次的查询都要去访问数据库。那么我们的缓存做到哪里呢？一般情况下版的系统都会存在服务层这一结构层次，而数据访问层一般都只是对于数据库的增删改查的接口的定义，所以我们的缓存就需要在服务层进行。而mybatis中的一级缓存，通过判断查询条件是否要访问数据库，查询条件与某一次相同，则直接返回缓存中的数据，查询条件不同则需要访问数据库，并且将结果放到缓存中。
2、当只需要一条数据时使用LIMIT 1.我们作为开发者，是能够知道我们需要的数据的条数的，若已经知道结果只有一条的时候，一定要使用limit 1 ，这样一来，MySQL在查询到一条数据之后，会立即停止搜索，这会带来性能上的提升。
3、避免select * ，取之所需。公司里的一些同事，无论查询什么都是直接select *，然后再从结果中取想要的字段。这样做的话，平白无故的给MySQL带来了不必要的负担，因为从数据库中读出越多的数据，查询就会变得越慢。所以，以后看到select * 的时候，想一下是否可以在这里进行一些优化。
4、为每张表设置一个id作为其主键。这个id最好是一个int类型的，推荐使用unsigned，并将其设置为自动增加auto_increment。之前就出现过一个同事将varchar的字段作为主键的情况， 然后在数据量较大的时候，数据库这个环节速度变得不是很友好，所以尽量不要使用varchar来当主键，它会使得性能出现下降。而且在某些情况下，id这个主键字段是非常重要的。
5、使用enum而不是varchar。实际上，enum保存的是tinyint类型，但其显示为字符串。用这个字段来作一些选项列表就变得很合适了。比如你有一个字段，比如“性别”、“状态”或“所属部门”等，你知道这些字段的值是固定且有限的，那么可以考虑使用enum。对于性别这个字段，一般分为两种，有可能还有保密这种情况，我们可以使用数字1、2、3来分别表示这三张情况，而对于这些数字含义的区分则是业务层的事情了。我们需要将一些繁琐的需要计算的步骤全部放到业务层（或者说是服务层），因为系统的瓶颈在数据库，我们不能将过多的计算过程压到数据库上面去。数据库存储的数据应该尽量简单，但是，我们会在业务层结合具体的业务，对这些简单的数据进行分析。
6、尽可能的使用not null。除非你有一个很特别的原因要去使用null值，你应该总让你的字段保持为not null。
7、选择正确的存储引擎。myisam适合一些需要大量查询的应用。但其对于大量写操作并不是很好。因为它使用到的是表级锁，所以在你更新的时候，整张表都会被锁起来，试想一下，当你在更新某一行数据的时候，导致其他的行都无法被访问，这会不会 很难受呢。另外，myisam对于select count(*)这类操作的计算时很快的。而至于innodb而言，对于一些小的应用，它会比myisam还慢。它支持的是行级锁，于是写操作较多的时候，它会更加优秀。它还支持一些更高级的应用，比如说：事务。
8、拆为搜索的字段建立索引。不要以为只有标的逐渐可以建立索引，如果某个字段你总要拿来做搜索，那么为它简历索引吧。但是，不要以为建立索引，就可以为所欲为
MYSQL 应该是最流行了 WEB 后端数据库。WEB 开发语言近期发展非常快，PHP， Ruby, Python, Java 各有特点，尽管 NOSQL 近期越來越多的被提到，可是相信大部分架构师还是会选择 MYSQL 来做数据存储。
MYSQL 如此方便和稳定。以至于我们在开发 WEB 程序的时候非常少想到它。即使想到优化也是程序级别的，比方。不要写过于消耗资源的 SQL 语句。可是除此之外，在整个系统上仍然有非常多能够优化的地方。
除非你的数据表使用来做仅仅读或者全文检索 (相信如今提到全文检索，没人会用 MYSQL 了)。你应该默认选择 InnoDB 。
你自己在測试的时候可能会发现 MyISAM 比 InnoDB 速度快。这是由于： MyISAM 仅仅缓存索引，而 InnoDB 缓存数据和索引，MyISAM 不支持事务。可是 假设你使用 innodb_flush_log_at_trx_commit = 2 能够获得接近的读取性能 (相差百倍) 。
mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I ‘{}‘ echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e ‘s/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g‘ alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql
innodb_file_per_table=1
这样能够保证 ibdata1 文件不会过大。失去控制。尤其是在运行 mysqlcheck -o –all-databases 的时候。
推荐将数据全然保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量。这样你能够全然从内存中读取数据。最大限度降低磁盘操作。
方法 1
mysql> SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_pages_%‘; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | Innodb_buffer_pool_pages_data | 129037 | | Innodb_buffer_pool_pages_dirty | 362 | | Innodb_buffer_pool_pages_flushed | 9998 | | Innodb_buffer_pool_pages_free | 0 | !!!!!!!! | Innodb_buffer_pool_pages_misc | 2035 | | Innodb_buffer_pool_pages_total | 131072 | +----------------------------------+--------+ 6 rows in set (0.00 sec)
发现 Innodb_buffer_pool_pages_free 为 0，则说明 buffer pool 已经被用光，须要增大 innodb_buffer_pool_size
InnoDB 的其它几个參数：
innodb_additional_mem_pool_size = 1/200 of buffer_pool innodb_max_dirty_pages_pct 80%
方法 2
或者用iostat -d -x -k 1 命令，查看硬盘的操作。
运行 echo 1 > /proc/sys/vm/drop_caches 清除操作系统的文件缓存。能够看到真正的内存使用量。
默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。
数据预热能够提高读取速度。
对于 InnoDB 数据库，能够用下面方法，进行数据预热:
1. 将下面脚本保存为 MakeSelectQueriesToLoad.sql
SELECT DISTINCT
CONCAT(‘SELECT ‘,ndxcollist,‘ FROM ‘,db,‘.‘,tb,
‘ ORDER BY ‘,ndxcollist,‘;‘) SelectQueryToLoadCache
FROM
(
SELECT
engine,table_schema db,table_name tb,
index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM
(
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables WHERE
engine=‘InnoDB‘
) B USING (table_schema,table_name)
WHERE B.table_schema NOT IN (‘information_schema‘,‘mysql‘)
ORDER BY table_schema,table_name,index_name,seq_in_index
) A
GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb
;
2. 运行
mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
3. 每次重新启动数据库，或者整库备份前须要预热的时候运行：
mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1
假设是专用 MYSQL server。能够禁用 SWAP，假设是共享server，确定 innodb_buffer_pool_size 足够大。或者使用固定的内存空间做缓存，使用 memlock 指令。
mysqlcheck -o –all-databases 会让 ibdata1 不断增大。真正的优化仅仅有重建数据表结构：
CREATE TABLE mydb.mytablenew LIKE mydb.mytable; INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytablezap; ALTER TABLE mydb.mytablenew RENAME mydb.mytable; DROP TABLE mydb.mytablezap;
可是须要注意假设用 1G 的 innodb_log_file_size 。假如server当机。须要 10 分钟来恢复。
推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
这个选项和写磁盘操作密切相关：
innodb_flush_log_at_trx_commit = 1 则每次改动写入磁盘
innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘
假设你的应用不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者 事务都非常小，都能够用 0 或者 2 来减少磁盘操作。
innodb_flush_method=O_DIRECT
RAID0 尤其是在使用 EC2 这样的虚拟磁盘 (EBS) 的时候，使用软 RAID0 很重要。
SHOW CREATE TABLE db1.tb1/G
6.2 加入必要的索引
索引是提高查询速度的唯一方法。比方搜索引擎用的倒排索引是一样的原理。
索引的加入须要依据查询来确定。比方通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。
ADD UNIQUE INDEX ADD INDEX
加入索引
ALTER TABLE users ADD UNIQUE INDEX username_ndx (username); ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);
每次重新启动server进行数据预热
echo “select username,password from users;” > /var/lib/mysql/upcache.sql
加入启动脚本到 my.cnf
[mysqld] init-file=/var/lib/mysql/upcache.sql
比方。Rails 这种框架。会自己主动加入索引。Drupal 这种框架会自己主动拆分表结构。
会在你开发的初期指明正确的方向。所以，经验不太丰富的人一開始就追求从 0 開始构建，实际是不好的做法。
记录全部查询。这在用 ORM 系统或者生成查询语句的系统非常实用。
log=/var/log/mysql.log
注意不要在生产环境用。否则会占满你的磁盘空间。
记录运行时间超过 1 秒的查询：
long_query_time=1 log-slow-queries=/var/log/mysql/log-slow-queries.log
如今基础设施的可靠性已经非常高了，比方 EC2 差点儿不用操心server硬件当机。并且内存实在是廉价。非常easy买到几十G内存的server，能够用内存磁盘。定期备份到磁盘。
将 MYSQL 文件夹迁移到 4G 的内存磁盘
mkdir -p /mnt/ramdisk sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/ mv /var/lib/mysql /mnt/ramdisk/mysql ln -s /tmp/ramdisk/mysql /var/lib/mysql chown mysql:mysql mysql
B-TREE 仍然是最高效的索引之中的一个，全部 MYSQL 仍然不会过时。
用 HandlerSocket 跳过 MYSQL 的 SQL 解析层。MYSQL 就真正变成了 NOSQL。
原文：https://www.cnblogs.com/wuyb123/p/10706705.html