a、sql语句优化

b、索引优化

c、数据库结构优化

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

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

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

g、mysql服务器优化

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

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

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

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

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

eg: 这是我做过的例子

$sql = "    SELECT        a.`id`,        a.`uid`,        a.`cus_id`,        a.`content`,        a.`ctime`,        a.`type`,        a.`addr`,        a.`customer`,        a.`images`,        a.`videos`    FROM        `wii_customer_follow` a    WHERE        a.`cid` ={$post[ 'cid' ]}    AND a.`is_del`=1 {$customer} {$type} {$uid} {$authUid}    ORDER BY a.`ctime` DESC ";$data = $this->ajaxPage3($sql, null, $post['page'], 10);#这一步先取出当前页的所有id,上面$data为空,也不会执行下面的操作,提升了上条sql的执行速度

#分开查询出所有的评论数与点赞数,然后按id分组,循环里根据id取出相应值$ids = implode(',', array_column($data['list'], 'id'));if ($ids) {    $model = M();    #点赞数查询    $supportUidArr = $model->query("SELECT `rid`,`uids` FROM `wii_support` WHERE `rid` IN (%s) AND `tablename`='%s'", [$ids, 'wii_customer_follow']);

#按rid分组    $supportUidArr = array_group_by($supportUidArr, 'rid');    #评论数查询    $commentArr = $model->query("SELECT `rid` FROM `wii_comment` WHERE `rid` IN (%s) AND `tablename`='%s'", [$ids, 'wii_customer_follow']);

#按rid分组    $commentArr = array_group_by($commentArr, 'rid');}foreach ($data['list'] as $key => $val) {

#取评论    $data['list'][$key]['comment_num'] = (string) count($commentArr[$val['id']]) ?: "0";

#取点赞    $supportNum = array_unique(explode(',', $supportUidArr[$val['id']][0]['uids']));    $data['list'][$key]['support_num'] = $supportNum[0] ? (string)count($supportNum) : "0";}

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

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

<>与!=: 也会造成全表扫描,可以考虑换成id>5 OR id<5、between等方式。

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

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语句的字段设置成索引。

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

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

聚集索引、辅助索引

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

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

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

创建: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

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

1)、前缀索引选择策略:

选择一个你认为合适的占比对应的前缀长度,格式:idx_1 (customer(10))。

2、联合索引:在多个列上建立索引,格式: idx_2 (cid,uid)。

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

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

3)、选择性衡量策略:

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

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

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

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

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

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

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

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

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

通过索引直接找到一个匹配行,一般主键索引的时候

没有主键索引或者唯一索引的条件索引,查询结果多行,在联合查询中很常见

利用到了索引,有可能有其它排序,where 或者 group by 等

全表扫描,没有使用到索引

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

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类型

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

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

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