首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

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҅,缓存,如果是更新不频繁的系统,建议开启;但如果是更新频繁的系统建议关闭。

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180924G0U88K00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券