如何进行mysql优化

这是一个经常被问道的题目，那么如果是你该如何回答呢？

围绕着这个话题，能引申多少就引申多少，尽量让自己讲，不要给面试官多提问的时间。

首先，我们得开启慢查询，找到需要优化的sql语句，然后进行优化：

1、对于一个数据库而言，良好的逻辑设计和物理设计才是其他优化的基础。首先得看数据库额设计是否贴切业务逻辑，不恰当的数据库逻辑设计往往优化也起不了多少作用。

2、需要选择合适的数据库字段类型。数据库字段选型，遵循3个原则：（1）1、更小的通常更好。一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的cpu周期也更少（当然前提是不要低估需要存储的值的范围）；（2）简单的通常更好。简单数据类型的操作通常需要更少的cpu周期。比如，整型比字符型操作代价低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂；（3）最好指定列为not null,除非真的需要存储null的值。因为含有null的列，对mysql而言，更难优化。可为null的列会使用更多的存储空间，在mysql里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

3、选择合适的数据库引擎。数据库引擎分为MyISAM、InnoDB、BDB、Memory、merge、archive、federated、cluster/NDB、csv、NlackHole，一般情况下，我们使用MYISAM和InnoDB比较多，那么如何选型呢？一般情况如果需要用到事务或外键的，选择InnoDB，否则使用MyISAM。下面是InnoDB和MyISAM的比较：

1、MySQL默认采用的是MyISAM。

2、MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

3、InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

4、InnoDB支持外键，MyISAM不支持。

5、InnoDB的主键范围更大，最大是MyISAM的2倍。

6、InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。

7、MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。

8、没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。

9、InnoDB采用的是聚簇索引，MyISAM采用的是非聚簇索引。

 

在满足上述条件的情况下，我们需要明白，优化的三个方向：

    1）减少服务器扫描的数据量；

    2）避免排序和临时表的出现；

    3）将随机I/O变成顺序I/O；

将随机I/O变成顺序I/O是非常重要的，这样就可以减少I/O次数。

磁盘存取原理

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。寻道，旋转时间。具体见https://www.cnblogs.com/shan1393/p/8999622.html

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

采用合适的ID生成策略。snowflake算法值得推荐，因为它生成的ID是顺序的。顺序的ID的插入速度比较快，特别是在聚簇索引中，而UUID等非顺序id是随机插入的，可能会导致页分裂的问题，造成磁盘碎片，也可能会导致页上的数据分布不均等问题。另外，uuid过长，占用的存储空间就更大，在聚簇索引中，叶子节点保存的是索引和data，并且是B+树结构，所以，索引越大的话，每页储存的数据就越少，所以树的深度就越高，访问速度就越慢。

 

使用explain执行计划，减少数据库扫描的数据量，一般可以用以下三种方式应用where条件，从好到坏依次是：

1）在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的；

2）使用索引覆盖扫描返回的记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在mysql服务器层完成的，但无需再回表查询记录；

3）从数据中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。这在mysql服务器层完成，mysql需要先从数据表中读出记录然后过滤。

explain执行计划，性能由高到低：

Id	id越大的语句越先执行，null是说明与其他行的联合结果
Select_type	SIMPLE,PRIMARY,UNION,DERIVED,UNION RESULT ,DEPENDENT UNION,SUBQUERY,DEPENDENT SUBQUERY
table
type	System、const、eq_ref、ref、fulltext、ref_or_null、index_merger、unique_subquery、index_subquery、range、index、all
Possible_keys
key
Key_len
ref
rows
extra	Using filesort,using temporary,using index,using index condition,usring where

1）覆盖索引。

覆盖索引的好处：

• 单条索引条目通常远小于单个数据行大小，而覆盖索引只需要读取索引，就可以完成查询操作，极大地减少数据访问量；索引数据很小，更易全部放入内存中。
• 索引是按照列值顺序存储的，相对于随机访问，只需要更少的I/O。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，但是对于覆盖索引，数据全部在内存中，可以避免这种情况。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所有MySQL只能使用B-Tree索引做覆盖索引

2）索引的选择性越高则查询效率越高。因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。索引的选择性是指：不重复的索引值和数据表的记录总数(T)的比值，范围从1/T到1之间；

3）选择合适索引的前缀长度。当前缀的选择性越接近全列选择性的时候，索引效果越好。

4）多列索引。多列索引需要考虑选择合适的索引列顺序。在确定索引列顺序的时候既要考虑使用该索引的查询性能，同时也要考虑如何更好地满足排序和分组的需要。

5）索引的选择性越高则查询效率越高。

因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。索引的选择性是指：不重复的索引值和数据表的记录总数(T)的比值，范围从1/T到1之间。

 

常见的优化规则：

1、尽量避免在列上进行运算，这样会导致索引失效；

2、注意LIKE模糊查询的使用，避免%%；

3、尽量避免select *，仅列出需要查询的字段，这对速度并没有明显影响，主要考虑节省内存；当然如果是InnoDB的覆盖索引的话，可以避免二次查询；

4、如果知道查询的数量的话，直接使用limit ；

5、用union替代or;

6、避免使用null；

7、不要使用count(id)，而要使用count(*)；

8、不要做无畏的排序操作，而应尽可能在索引中完成排序；