Mysql优化方案

1、mysql的优化点

a、sql语句优化

b、索引优化

c、数据库结构优化

d、理解查询执行计划[即EXPLAIN分析sql语句后的结果集分析]

e、缓冲与缓存[缓存，如果是更新不频繁的系统，建议开启；但如果是更新频繁的系统建议关闭]

f、锁优化[用innodb自己的锁机制，不用自己操作锁，但我们可以查看锁]

g、mysql服务器优化

2、关于sql语句

2.1、冗余

优点：在sql查询过程中，如果能做字段冗余的，尽量做好冗余，这样能够避免表的join;

缺点：就是每次更新的时候需要更改相应做冗余的地方。

总的来说：更新操作通常是比较快，多这一步操作，带来的是性能飙升，完全值得。

2.2、适当地分步查询

原因：sql查询语句，一条sql语句只能交给一个cpu处理，如果过多的join与子查询，将严重影响性能，如果我们采用分步骤的进行，sql语句就可以交给多个cpu处理。

注意：有时候分步太多，也不一定会起到提高性能的作用，有些反而降低了性能，所以需要衡量，选择合适的方案。

eg:这是我做过的例子

但是有时候确实没有比join更好的方式，那就join吧。

2.3、减少全表扫描

LIKE:尽量减少这种模糊查询，如果数据量少倒还行，数据量一大，就严重超过用户的忍耐度。如果场景里不可避免，可以考虑就用搜索引擎技术，开源的有基于lucene的solr、elasticsearch等技术。

IS NULL做WHERE的条件:设置表的时候设置为。

OR:会导致引擎放弃索引，进行我全表扫描,如select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20。

WHERE子句中使用参数：如select id from t where num=@num,如果要用改为强制索引查询select id from t with(index(索引名)) where num=@num。

WHER子句中表达式操作：如select id from t where num/2=100,就改为select id from t where num=100*2。

WHERE子句中对字段进行函数操作：这将导致引擎放弃使用索引而进行全表扫描。

count(*)：可以用count(1)代替。

select(*):使用什么字段，就写什么字段。

ORDER BY:order by语句的字段设置成索引。

3、关于索引

3.1、innodb存储引擎常见索引

B+树索引、hash索引、全文索引

3.2、b+树索引

1、b+树索引并不能一下找到给定值的具体行，而是只能找到具体数据所在的页，然后数据库把页读入内存，在内存中进行查找，得到所需数据。

2、分类

聚集索引、辅助索引

3.2.1、聚集索引

含义：主键或者唯一键[没有主键有唯一键的表]或者RowID[没有主键也没有唯一键，系统默认]来构建一个b+树，叶子存放表的行数据，叶子也叫数据叶，这样的结构决定了数据也是索引的一部份，访问索引就很容易找到索引上的数据。

3.2.2、辅助索引

含义：除了聚集索引外的所有索引都被称为辅助索引，对于辅助索引，叶子节点不包含行记录的全部数据。

原理：辅助索引->找到叶子节点->节点中获取主键->主键再去聚集索引上获取行数据[包含了两次对b+树查找过程]。

3.2.3、索引管理

创建：CREATE INDEX | KEY idx_b ON t竖线是或者之意

ALTER TABLE t ADD INDEX | KEY idx_b (b(100));//前缀索引

ALTER TABLE t ADD INDEX | KEY idx_a_b (a,b);//联合索引

删除：ALTER TABLE t DROP INDEX | KEY idx_b;

DROP INDEX idx_b ON T;

查看：SHOW INDEX FROM t

3.2.4、b+树索引的使用

1、前缀索引：对于字符串，要在它上边建立索引，如果字符过长，会使索引变得过大，查询效率变差，占用过多的存储空间。但如果我们选择字符的前边一部份做成索引，那么就大幅节省了索引空间，提高索引的效率。

1）、前缀索引选择策略：

1）、优点：因为b+树中，所有键值都是有排序的，所以联合起来，筛选的效率就变得很高。

2）、注意：没有排序或分组时把选择性高的放在前面，效果会更好。

3）、选择性衡量策略：

哪个值更接近1，选择性就高。

3、覆盖索引：InnoDB支持覆盖索引（覆盖索引并非是一种可以通过SQL语句创建的索引，与前缀索引、联合索引不同，我们可以认为这是一种逻辑上的索引，即符合一定条件的索引我们都可以称之为是覆盖索引），即从辅助索引中就可以直接得到查询的记录，而不需要再次查询聚集索引中的记录。使用覆盖索引的一个好处就是辅助索引不包含整行记录，因此它的大小远小于聚集索引，可以减少大量IO操作，查询速度很快。

不是所有的索引都能够成为覆盖索引，覆盖索引必须要存储索引列的值。当我们发起一个一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using Index信息，表示该次查询在索引上就直接获得了所有需要的数据，也即这是一个覆盖索引。如下图所示：

由于cid与uid同时存在于索引中，因此当查询cid与uid时，就可以直接从辅助索引中可以获取所需的字段，而不需要再次去查找聚集索引。

3.3、哈希索引

1、哈希索引是一种key-value形式的结构，故检索起来很快。

2、InnoDB存储引擎支持的哈希索引是自适应的，即InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为地干预在一张表中生成哈希索引。

3.4、全文索引

Mysql-5.7.6内置了ngram全文检索插件，根据ngram_token_size的大小来对中文进行分词，ngram_token_size的值范围[1-10]，size越大，索引的体积就越大，默认为2。

所谓全文索引就是将一句话分成多个词语，建成索引，进行搜索。

3.5、索引是否用到分析

1、用QEP[查询执行计划]进行分析，也即用EXPLAIN分析SQL语句。

只需要观察type,key,Extra字段，就可以看设置的索引有没有被用到

3、type值代表索引的性能，性能高低排序

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

extra如果有Using filesort或者Using temporary的话,就必须要优化了

4、数据库结构优化

1、需求分析，充分吃透需求，反复推演逻辑关系，制成合理的UI图，表结构。

2、当前WEB的使用场景，基本上是查多写少，对于多张表存在逻辑关系，采用反范式设计，增加字段冗余，减少多表联合，提高查询性能。

3、采用数据库、表及字段的命名规范[可读性原则、表意性原则、长名原则]。

4、为字段选择合适的数据类型

a、对于数字类型

整型：tinyint(一个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)

实数：flout(4字节，不为精确类型)、double(8字节，不为精确类型)、decimal(每4个字节存9个数字，小数点占一个字节，为精确类型)

b、对于字符串类型

char类型：特点[类型定长、删除末尾空格、存储最大宽度255]；适用场景[适合存储长度近似的值(md5、电话号码、身份证号等有固定长度的值)、适合存长度短小的字符串、适合存储经常更新的字符串]。

varchar类型：特点[用于存储长度变长的字符串、列小于255要占用一个额外字节用于存储字符串长度、列大于255要占用两个字节用于存储字符串长度];原则[选用合适的长度，不同的长度性能不一样]；适用场景[字符串的最大长度比平均长度在很多、字符串的列很少被更新、使用多字符集存储字符串]。

c、日期类型

datatime类型以YYYY-MM-DD HH:MM:SS[.fraction]格式存储数据datatime类型与时区无关，占用8个字节存储空间 存储的时间范围：1000-01-01 00：00:00到9999-12-31 23:59:59

timestamp类型存储从1970年1月1日到当前的秒数，以YYYY-MM-DD HH:MM:SS[.fraction]显示，占用4个字节存储空间timestamp类型显示依赖于所指定的时区timestamp类型在行数据修改时可以自动修改timestamp列的值timestamp存储的时间范围1970-01-01到2038-01-19

date类型和time类型（mysql5.7之后加入）：date类型占用的字节数比使用字符串、datatime、int存储要少，使用date类型只需要3个字节；date类型使用Date类型还可以利用日期时间函数进行日期之间的计算；date类型存储的日期范围1000-01-01到9999-12-31之间的日期

time类型用于存储时间数据：HH:MM:SS存储日期时间类型的注意事项：不要使用字符串类型来存储日期时间数据；日期时间类型通常比字符串类型所占用的存储空间小；日期时间类型在进行查找过滤时可以利用日期来进行对比；日期时间类型有丰富的处理函数，可以方便的对时间类型的进行日期计算

使用Int存储日期时间不如使用Timestamp类型

5、mysql服务器优化

5.1、选取合适的硬件系统

比如主高频CPU、多核CPU、SSD硬盘、PCIE卡、合适的带宽

5.2、合理的系统参数配置

1.开启慢查询日志slow_query_log=on

2.查询慢查询的时间标准long_query_time,建议设置小于3秒

3.慢查询日志的存储位置slow_query_log_file

4.缓冲池设置innodb_buffer_pool_size为物理内存的50%~70%

5.innodb_log_file_size,5.5以上设置为1G以上，5.5以下不要超过512M

6.innodb_flush_log_at_trx_commit, 0最快数据最不安全1.最慢最安全2折中

7.innodb_max_dirty_pages_pct，25%~50%为宜

8.innodb_io_capacity，普通硬盘1000左右SSD10000左右PCleSSD20000左右

9.sync_binlog 0最快数据最不安全，系统自己决定刷新binlog的频率；1最慢最安全，每个event刷新一次binlog；N每N个事务刷新一次binlog

10.open_files_limit & innodb_open_files，建议65535

11.max_connections，突发最大连接数的80%为宜，过大容易全部卡死

12.thread_handling =”pool-of-thread”，启用线程池，好像企业版才支持

13.query_cache_size & query_cache_type҅，缓存，如果是更新不频繁的系统，建议开启；但如果是更新频繁的系统建议关闭。

其实一直想与你交流，但你却在很远的地方，如果你有时间经过这里，不妨再花几秒，让我们相识！