mysql原理及优化_mysql 优化原理与理论

数据库优化的几个方面：SQL语句以及有效索引、数据结构、系统配置、硬件

1. SQL以及索引的优化是最重要的。首先要根据需求写出结构良好的SQL，然后根据SQL在表中建立有效的索引。但是如果索引太多，不但会影响写入的效率，对查询也有一定的影响。2. 要根据一些范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能够更有效的查询。3. 系统配置的优化。MySQL数据库是基于文件的，如果打开的文件数达到一定的数量，无法打开之后就会进行频繁的IO操作。4. 硬件优化。更快的IO、更多的内存。一般来说内存越大，对于数据库的操作越好。但是CPU多就不一定了，因为他并不会用到太多的CPU数量，有很多的查询都是单CPU。另外使用高的IO(SSD、RAID)，但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的，那么硬件优化就没有什么意义。一、SQL语句优化1、通过慢查询日志发现有效率问题的SQL可以通过开启慢查询日志的方式进行定位有问题的SQL(1)查看MySQL是否开启慢查询日志show variables like 'slow_query_log';

作者：网易云链接：https://www.zhihu.com/question/36431635/answer/459619138来源：知乎著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

(2)设置没有索引的记录到慢查询日志set global log_queries_not_using_indexes=on;

(3)查看超过多长时间的sql进行记录到慢查询日志show variables like 'long_query_time'

(4)开启慢查询日志set global slow_query_log=on

(5)设置超时时间Set global long_query_time=5;--超过5s的语句才记录日志

(6)查看慢查询日志的位置 show variables like 'slow%'2、慢查询日志内容分析慢查询日志主要分为5部分，第一部分是慢查询时间，第二部分是慢查询的来源主机和用户，第三部分是查询的执行时间、锁定时间、发送的行数、扫描的行数。最后是时间戳形式记录的命令以及该命令的执行的时间戳。系统运行一段时间后，慢查询日志可能比较多，需要通过mysqldumpslow、pt-query-digest等工具分析慢查询日志。参考3、通过explain查看SQL的执行计划具体的分析过程可以参考二、索引优化1、选择索引(1)选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。

(2)索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

(3)离散度大得列放在联合索引前面

判断离散程度的方法是：select count(distinct ziduan1),count(distinct ziduan2) from tablename越大越离散

作者：网易云链接：https://www.zhihu.com/question/36431635/answer/459619138来源：知乎著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

2、索引优化方法索引一般情况下都是高效的。不过凡是都有两面性，索引是以空间换时间的一种策略，索引本身在提高查询效率的同时会影响插入、更新、删除的效率。不当的使用索引不仅增加了写操作的负担，也会影响读取的效率。索引越多，数据库分析的越慢。注意点：

(1)InnoDB 每个索引都会加上主键，联合索引不要加上主键，innodb会自动加，否则会冗余。

(2)索引存在的目的是为了加快查询的效率，不过不是索引越多越好，建立索引要适当才好。过多的索引会增加数据库判断使用什么索引来查询的开销，所以，有时候也会出现以去掉重复或者无效的索引为优化手段的优化方式。

(3)主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了。

3、索引的原理可以参考三、数据表结构优化1、选择合适的数据类型(1)使用可存下数据的最小的数据类型。

(2)使用简单地数据类型，int要比varchar类型在mysql处理上更简单。

(3)尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值。

(4)尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率。

例子1、用int存储日期时间

from_unixtime()可将Int类型的时间戳转换为时间格式

select from_unixtime(1392178320); 输出为 2014-02-12 12:12:00unix_timestamp()可将时间格式转换为Int类型

select unix_timestamp('2014-02-12 12:12:00'); 输出为1392178320

例子2、存储IP地址——bigInt

利用inet_aton(),inet_ntoa()转换

select inet_aton('192.169.1.1'); 输出为3232301313

select inet_ntoa(3232301313); 输出为192.169.1.1四、数据库配置优化这方面目前了解的并不多，参考五、硬件优化硬件层面的优化是最后的手段。主要需考虑CPU、存储、网络等几个方面。CPU：CPU并不是越多越好，之前看到网上的分析有说很多的查询都是单CPU的，增加CPU数量并不能提高性能。存储：机械磁盘 or SSD(当然是SSD更快)；单个大磁盘 or 多个小磁盘组合使用(单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取)

网络：一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。