MySql性能优化及常见面试题

数据库优化四个维度

        硬件升级

        系统配置

        表结构设计

        sql语句及索引

优化选择

        优化成本：硬件升级 > 系统配置 > 表结构设计 > sql语句及索引

        优化效果：硬件升级 < 系统配置 < 表结构设计 < sql语句及索引

一、系统配置

        1.1：保证从内存中读取数据：

        MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。 尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。

        扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。

        确定innodb_buffer_pool_size 足够大的方法：

                

         innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。

        修改 my.cnf ：

        innodb_buffer_pool_size = 750M

        如果是专用的MySQL Server可以禁用SWAP

         

        1.2：数据预热

        默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。 所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。

         1.3：降低磁盘写入次数

• 增大redolog，减少落盘次数

        innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

• 通用查询日志、慢查询日志可以不开 ，bin-log开

        生产中不开通用查询日志，遇到性能问题开慢查询日志

• 写redolog策略 innodb_flush_log_at_trx_commit设置为0或2

        如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0 或者 2 来减少磁盘操作。

        1.4：提高磁盘读写性能

        使用SSD或者内存磁盘

二、表结构设计优化

        2.1：设计中间表

        设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

        2.2：设计冗余字段

        为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）

        2.3：拆表

        对于字段太多的大表，考虑拆表（比如一个表有100多个字段） 对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表

        2.4：主键优化

        每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布 式系统的情况下 雪花算法）。

        2.5：字段设计

        数据库中的表越小，在它上面执行的查询也就会越快。 因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

        尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

         对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我 们又可以提高数据库的性能。

        能用数字的用数值类型 sex 1 0

三、SQL语句及索引分析与优化

索引类型

从索引存储结构划分：B-Tree索引、Hash索引、FULLTEXT索引、R-Tree索引

从应用层次划分：普通索引、唯一索引、主键索引、复合索引

从索引键值类型划分：主键索引、辅助索引

从数据存储和索引键值逻辑关系划分：聚集索引、非聚集索引

EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信 息，供开发人员有针对性的优化。例如：EXPLAIN SELECT * from user WHERE id < 3;

select_type：表示查询的类型。常用的值如下：

    SIMPLE ： 表示查询语句不包含子查询或union

    PRIMARY：表示此查询是最外层的查询

    UNION：表示此查询是UNION的第二个或后续的查询

    DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果

    UNION RESULT：UNION的结果

    SUBQUERY：SELECT子查询语句

    DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

  最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

type： 表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还 是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

    ALL：表示全表扫描，性能最差。

    index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

    range：表示使用索引范围查询。使用>、>=、<、<=、in等等。

    ref：表示使用非唯一索引进行单值查询。

    eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。

    const：表示使用主键或唯一索引做等值查询，常量查询。

    NULL：表示不用访问表，速度最快。

possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

key：表示查询时真正使用到的索引，显示的是索引名称。

rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

    key_len的计算规则如下：

      字符串类型----字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

      char(n)----n*字符集长度

      varchar(n)------n * 字符集长度 + 2字节

    数值类型

      TINYINT----1个字节

      SMALLINT----2个字节

      MEDIUMINT----3个字节

      INT、FLOAT----4个字节

      BIGINT、DOUBLE----8个字节

    时间类型

      DATE----3个字节

      TIMESTAMP----4个字节

      DATETIME----8个字节

    字段属性

      NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

Extra：Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

      Using where----表示查询需要通过索引回表查询数据。

      Using index----表示查询需要通过索引，索引就可以满足所需数据。

      Using fifilesort----表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using fifilesort建议优化。

      Using temprorary----查询使用到了临时表，一般出现于去重、分组等操作。

索引优化

1. SQL语句中IN包含的值不应过多

    MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序 的。但是如果数值较多，产生的消耗也是比较大的。

2. select语句务必指明字段名称

    SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性；当 表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

3. 当只需要一条数据的时候，使用limit 1

    limit 是可以停止全表扫描的

4. 排序字段加索引

5 .如果限制条件中其他字段没有加索引，尽量少用or

    or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况。

6. 尽量用union all代替union

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序， 增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

7. 不使用 order by rand()

    order by rand()不走索引

8. 区分in和exists、not in和not exists

    区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为 驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况

    EXISTS适合 于外表小而内表大的情况。

    关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。

9. 使用合理的分页方式以提高分页的效率

    分页使用 limit m,n 尽量让m小利用主键的定位，可以减小m的值

10. 分段查询

    一些用户选择页面中，可能一些用户选择的范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

11. 不建议使用%前缀模糊查询

    例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE“name%”。

    那么如何解决这个问题呢，答案：使用全文索引或ES全文检索

12. 避免在where子句中对字段进行表达式操作

    select id,name from user where age*3=36;

    中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

    select id,name from user where age=36/3;

13. 避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定 where中的参数类型。 where age='18'

14. 对于联合索引来说，要遵守最左前缀法则

    举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是 name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

15. 必要时可以使用force index来强制查询走某个索引

    有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

16. 注意范围查询语句

    对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

17. 使用JOIN优化

    LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B 表为驱动表。

    注意：

    1）MySQL中没有full join，可以用以下方式来解决：

      select * from A left join B on B.name = A.namewhere B.name is null union all select * from B;

    2）尽量使用inner join，避免left join：

      参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条 件的情况下MySQL会自动选择小表作为驱动表，

      但是left join在驱动表的选择上遵循的是左边驱动右边 的原则，即left join左边的表名为驱动表。

    3）合理利用索引：

      被驱动表的索引字段作为on的限制字段。

    4）利用小表去驱动大表

面试题

 

1：MySQL在使用like模糊查询时，索引能不能起作用？

回答：MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name like '%o%'; //不起作用

select * from user where name like 'o%'; //起作用

select * from user where name like '%o'; //不起作用

2：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他 值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节。

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。