浅谈数据库优化原则

数据库优化

优化原则

1、数据库在设计的时候严格按照范式设计2、使用字段数据类型的时候，尽可能的用小的数据类型3、在数据库里面开启慢查询，分析哪些SQL语句执行比较慢，    将比较慢的SQL语句查询使用日志记录方式记录下来再进行分析和处理4、使用explainSQL语句进行分析，分析为什么比较慢，    从而改写SQL语句或者针对需要的字段适当的加上索引5、应用层面进行优化，例如加上缓存（memcached和redis），或者页面静态化，    让后面的请求不再查询数据库，这样效率更高，将效率分摊至前端方便扩展应用服务器6、在架构层面进行调整，可以使用主从、读写分离的方式减轻数据库服务器的压力，    避免在单台机器上过度消耗资源7、必要的时候，可以对数据表进行纵切(垂直分表)，将不经常读的内容和经常操作的内容放置不同表中。    不读取非必须内容，降低磁盘IO8、可以使用mysql表分区技术，将一个表分成多个不同的文件。9、如果内容特别多可以采用数据库中间件或者类似于分库分表分机器的技术，    将表分至不同的数据库或者数据库实例（服务器）中10、选择存储引擎的时候，可以按照一些具体业务场景选择存储引擎。    引擎都有各自的特性，你可以使用不同的引擎处理不同的业务11、尽量不要使用or，like，搜索时加上主键，不用或者少用全文索引12、如果非要使用全文索引，将全文索引专门建立全文索引服务器13、优化相关配置的参数14、可以提升机器性能例如用固态硬盘、用更大的内存

具体操作

1. 存储引擎

   根据需要，选择合适的存储引擎

2. 数据表设计

   三范式：数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的数据库是需要满足一些规范的来优化数据存储方式。在关系型数据库中这些规范就可以称为范式。

3. 慢查询

   是mysql中自带的，你只要开启即可。开启之后，就会将执行比较慢的语句记录到日志中，这个标准通过mysql来自己定制

   查看慢查询时间    show variables like 'long_query_time';查看是否开启慢查询  show variables like 'slow%';开启慢查询：      set global slow_query_log=ON;然后再日志中查看慢查询记录   C:\wamp64\bin\mysql\mysql5.7.11\data\xxx.log

4. Explain

   查询执行计划，在执行sql语句之前，通过这个来查看你的语句的预执行情况，通过查看mysql如何执行这条语句，来分析这条语句如何优化。如：explain select * from user\G

   <hr />          id: 1 select_type: SIMPLE       table: user  partitions: NULL        type: ALLpossible_keys: NULL         key: NULL     key_len: NULL         ref: NULL        rows: 99951    filtered: 10.00       Extra: Using where

   选项说明：

   select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。table：输出结果集的表。type:ALL代表全表扫描,通常是不好的，其他的如index/range/const/ref/system则是较好的   type=ALL，全表扫描，MySQL遍历全表来找到匹配的行   type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行   type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符   type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行   type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 primary key或者 unique index作为关联条件。   type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。   type=NULL，MySQL不用访问表或者索引，直接就能够得到结果possible_keys:可能被用到的索引key:查询过程中实际用到的索引，当为null时表示没有使用索引，通常不好key_len:索引字段最大可能使用的长度，也叫作索引基数，索引基数越大，表明可能查询的行数越多，效率越低rows:mysql估计的需要扫描的行数，行数越多，效率越低这一列只有在EXPLAIN EXTENDED语句中才会出现。extra:显示上述信息之外的其它信息，其主要有以下返回结果   Usingindex   表明此查询使用了覆盖索引(CoveringIndex)，即通过索引就能返回结果，无需访问表。   若没显示"Usingindex"表示读取了表数据。   Using index condition   可能会使用索引，   Using index就是一定使用索引，这种索引成为覆盖索引，Using index condition则是在必要的时候才使用索引   Using where   表示 MySQL 服务器先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。效率较慢。   Using filesort   表示Mysql会按查询所需的顺序对结果进行排序，这时就会出现 Using filesort 。排序自然会增加查询时间，导致效率变慢。   解决方法是利用索引进行排序。若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，此时就不会出现Using filesort。

5. 使用索引

   普通索引，主键索引，唯一索引，全文索引

6. 语句优化

   1、数据表的设计   1、表设计满足三范式   2、字段数目不易过多，不常用的字段可以分成另外一个表   3、选择合适的字段类型   4、尽量使用整型2、sql语句使用优化1、原则：    从Explain入手    尽可能在索引中完成排序    只取自己需要的Column    尽可能避免复杂的join和子查询2、优化limit    select * from test1 order by id limit 99999,10     虽然使用了id索引，但是相当于从第一行定位到99999行再去扫描后10行，相当于扫描全表    修改为如下方法：    select * from test1 where id>=100000 order by id limit 103、尽量不要使用  * 4、不让默认排序    explain select * from user group by province\G    explain select * from user group by province order by null\G    在group by后面增加 order by null 就可以防止排序5、使用连接来替代子查询    有些情况下，可以使用连接来替代子查询。因为使用join不需要在内存中创建临时表。    select * from user where user_id in(select uid from goods);    select * from user, goods where user.id=goods.uid;    【注】连接中左外连接效率最高6、使用LIMIT 1取得唯一行