MySQL优化总结 & 索引的使用

前言

数据库优化涉及方面特别多：
1. 合理的表设计
2. 选择合适的表引擎、字符集、数据类型
3. 合理添加、使用索引
4. 写SQL语句注意问题
5. 硬件问题
6. 带宽
…
系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和高效SQL语句之间的速度差别可以达到上百倍。
数据库性能调优，就好比盖楼打地基，地基打得不稳，楼层一高，就会塌方。数据库也是如此，数据少，并发小，隐藏的问题是发现不了的，只要达到一定规模后，所有的问题就会全部曝露出来了，所以前期的设计阶段尤为重要。

总结前了解

数据库优化分类

*硬件 *网络 *软件

硬件、网络取决于公司的经济实力。
软件再分为表设计(字段类型、存储引擎)、SQL语句优化与索引、配置文件参数、体系架构等方面的优化。

三范式

提出三范式的目的：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。减少数据冗余，节省磁盘空间。

【第一范式】原子性。确保字段不能再分解

地址表：
	id   address
    1    中国广东广州天河区...   ==> 违反第一范式

	id   contry  province  city  ...
	1    中国     广东      广州

【第二范式】唯一性。确保表中的每列都和主键相关
【第三范式】独立性
详解请点击查看其他技术博客

如何合理设计表

在开发应用程序时，设计的数据库要最大程度地遵守三范式。但是，三范式最大的问题在于通常需要join很多表，而这个会导致查询效率很低。所以有时候基于查询性能考虑，我们需要有意违反三范式，适度的冗余，以达到提高查询效率的目的。

• 一张表的字段不宜过多，操作频繁的表中只要保留一些常用的字段

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大的时候，会由于使用频率低的字段的存在而变慢。

• 设计表要遵守三范式，但是不能严格遵守三范式，有时需要打破范式

设计数据库表时应尽量遵守范式理论的约定，尽可能减少冗余字段。但是合理地加入冗余字段也可以提高查询速度，达到以空间换取时间。

• 根据表的特点来选择合适的表引擎， 如果这个表经常被写，应该选择innodb，但是MySQL5.6之后一般都是选择innodb

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来。
InnoDB 对于一些小的应用，会比MyISAM 还慢。但它支持行锁。所以在写操作比较多的时候应选择InnoDB。并且它还支持更多的高级应用，比如事务。

• 选择合适的数据类型。尽量选择更加小的数据类型，数字型比字符型快

比如“性别”、“国家”、“民族”、“状态”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR

MySQL优化总结

一. 设计表

1. 设计表时要遵守数据库三范式，但有时可以打破范式
2. 一张表的字段不宜过多，将一些常用的字段放到一张表，不常用字段、字段类型比较大的字段放到另外一张表
3. 表引擎一般都选择 innodb
4. 尽量选择更小的数据类型，同时要考虑字段的扩展性
5. 每张表预留几个字段，以便未来项目扩展
6. 根据表存放数据的特点选择合适的字符集，一般选择utf8，如果要存放emoji表情，选择utf8mb4
7. 尽量给每个字段都添加 NOT NULL
8. 给经常查询、经常作为where条件后的字段添加索引

二. SQL语句优化

1. 尽量减少使用 *，只查需要用的字段
2. 尽量给每条查询SQL语句添加LIMIT，减少返回的行数
3. 尽量根据索引来检索数据，避免全表扫描，考虑在where/order by/group by/left join on/distinct涉及的字段上建立索引，但一个表不要加太多索引，因为索引影响插入和更新的速度
4. 减少在查询语句中使用MySQL函数或者进行运算
5. 减少使用子查询，子查询效率很低（使用连接（join）代替子查询）
6. 减少多表查询（关联表查询），可以适当冗余字段来减少多表查询，或者将多表联查切分为多个单表查询
7. 使用where进行检索数据时，把能过滤掉更多结果的条件（区分度高的字段）写到前面
8. SQL关键字统一用大写，避免SQL语句重复编译造成系统资源浪费

三. 其他

1. 开启慢查询日志，定期用explain(desc)优化慢查询中的SQL语句
2. 使用预处理语句。例如使用PDO来操作mysql。在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements(预处理语句)定义一些参数，而MySQL只会解析一次。

如何合理使用索引

• 尽量不使用like，因为like可能会导致索引失效。 可以用全文搜索引擎替换like，比如sphinx/xunsearch

    // 如果匹配字符串的第一个字符为"%"，索引不会起作用。
    			
    desc select *  from t where name like "%j%"\G;  // 用不到索引
  
    desc select *  from t where name like "j%"\G;   // 用到索引
  

• 字段使用函数修饰，索引失效

    select createtime from aa where date(createtime) = curdate();
  

• 尽量避免在where子句中对字段进行：null值判断 / NOT IN / 表达式操作（<、>等）。这会导致引擎放弃使用索引，而进行全表扫描，如：

    select id,name from user where name is null;
    
    select id,name from user where age/12;
  

• 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询才使用索引，否则将不使用索引

• 用数字当字符类型，数字一定要加引号

    select * from user where name = 123;   // 用不到name字段上的索引
    
    select * from user where name = '123'; // 用到
  

• 使用联合索引，而不是多个单列索引

    select * from tab where a = ? and b =?
  
    // 给(a,b)建立联合索引，而不是分别给a,b建立索引
    alter table tab add index idx_ab(a,b);
  

• 合理创建联合索引

    // 避免冗余，只要给(a,b,c)建立索引就行
    (a), (a,b), (a,b,c) 
  
    // 区分度大的字段放在前面（区分度大：能够排除更多数据的字段）
    idx_name_sex(name,sex);  // name的区分度更大。因为sex只有 0 1 2 三个值
  
    // 查询条件中没有使用联合索引字段中的第一个字段时，索引不生效
    idx_name_email(name,email);
    explain select * from user where name = 'jack';   // 用到索引
    explain select * from user where email = 'jack@qq.com';  // 用不到索引
  

• 使用 order by 导致索引失效的情况

   // 对不同字段使用order by，索引失效
   select * from user order by name,id;
   
   // where 子句使用的字段和order by 的字段不一致，索引失效
   select * from user where name = 'jack' order by id;
  
   // order by 的字段混合使用asc和desc，索引失效
   select * from user order by id desc, name asc;
  

举个SQL语句优化的栗子

• limit分页

  select SQL_NO_CACHE * from table order by id limit 99999,10;

  传统的分页，虽然用上了id索引，但要从第一行开始起定位到99999行，然后再扫描出后10行，相当于进行一个全表扫描，显然效率不高

  优化方法：

  select SQL_NO_CACHE * from table where id >= 100000 order by id limit 10;

  优化方法利用id索引直接定位100000行，然后再扫描出后10行。速度相当快