数据库优化

数据库优化主要可以分为两大类，软优化和硬优化：

软优化一般是操作数据库即可，而硬优化则是操作服务器硬件及参数设置.

 

先比较三个sql：

student表中有四条数据，如图：

select id from student where sex is not null;

select id from student where sex BETWEEN 1 and 2;

select id from student where sex in (1,2);

由于数据量和字段都比较少，导致结果和查询时间完全一样，都是：

 

但是，用DESC  分析时可以发现差距：

FILTERED：

如果全表扫描，那么 filtered 就代表满足搜索条件的记录的满分比

如果是索引，那么 filtered 就代表除去索引对应的搜索，其他搜索条件的百分比

 

软优化：

1. 查询语句优化：

    1.1 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

    1.2 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

    1.3 尽量避免在 where 子句中使用 in 和 not in，否则会导致全表扫描，用 exists 代替 in

    1.4 尽量避免在 where 子句中使用 "%c%" ，否则会导致全表扫描

    1.5 尽量避免在 where 子句中对字段进行表达式操作，导致引擎放弃使用索引而进行全表扫描

    1.6 尽量避免在 where 子句中对字段进行函数操作，导致引擎放弃使用索引而进行全表扫描

    1.8 尽可能的使用 varchar 代替 char

    1.9 当只要一行数据时使用 Limit 1

2. 使用索引

    2.1 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致

    2.2 尽量确认两个表中 Join 的字段是被建过索引的

    2.3 并不是所有索引对查询都有效

          当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用

    2.4  like关键字匹配'%'开头的字符串,不会使用索引

    2.5 or 关键字的两个字段必须都是用了索引,该查询才会使用索引

3. 分表

    对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

4. 中间表

    对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时

5. 增加冗余字段

    类似于创建中间表,增加冗余也是为了减少连接查询

6. 分析表,检查表,优化表

    分析表: 使用 ANALYZE 关键字，如ANALYZE TABLE student

        Op：表示执行的操作

        Msg_type：信息类型,有status、info、note、warning、error

        Msg_text：显示信息

    检查表: 使用 CHECK关键字，如CHECK TABLE student[option]

    优化表:使用OPTIMIZE关键字，如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE student

                当删除了表的部分数据，或者已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的数据被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

                在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

                OPTIMIZE TABLE 只对MyISAM，BDB和InnoDB表起作用

                注意：在PTIMIZE TABLE 运行过程中，MySql会锁定表

 

硬优化：

1. 硬件优化

• 配置多核心和频率高的cpu,多核心可以执行多个线程.
• 配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
• 配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2. 优化数据库参数

    优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

• key_buffer_size:索引缓冲区大小
• table_cache:能同时打开表的个数
• query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
• sort_buffer_size:排序缓冲区

3. 分库分表

    因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？

    所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

4. 缓存集群

    如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。

    但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。

    所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。

    具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。