常见的MYSQL优化和小技巧

本文是听韩顺平老师的《MYSQL优化》的笔记，代码部分都通过自己的写和演示了。2017.10.13

视频地址：http://www.iqiyi.com/playlist446260102.html

MyISAM和innodb的区别

1.MyISAM不支持外键，但是innodb支持外键

2.MyISAM不支持事物，同时不支持外键

3.对数据的存储方式不同，MyISAM存储在三个表*.frm（表结构），*.MYD（表数据），*.MYI（表索引），同时删除数据的时候不释放*.MYD空间。innodb，数据存储在../ibdata1中。（在实际演示的时候发现存在这个地方并不是教材中说的）

演示：

自我查询然后插入数据（自我查询没有values）

mysql> insert into ck_engine select * from ck_engine;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into ck_engine select * from ck_engine;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into ck_engine select * from ck_engine;
Query OK, 16777216 rows affected (8.24 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

此时数据已经很大了，约224M，但是删除一部分表之后，仍然为224M

mysql> delete from ck_engine where id=0;
Query OK, 8388608 rows affected (1 min 47.78 sec)

（一不留神创建了一个超大行数的表……）

而对于innodb引擎的表，数据存储在另外的位置（IBD文件），可以随着增删数据而改变。经过验证也不会变化，不会随删除而减小存储空间。应该会慢慢移入刚才的文件ibdata1。此时需要使用语句来对数据库进行空间整理来释放空间。MyISAM和innodb都需要这个语句来整理空间。

mysql> delete from ck_engine where id=1;
Query OK, 8388608 rows affected (1 min 47.60 sec)

mysql> optimize table ck_engine;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| lustudy.ck_engine | optimize | status   | OK       |
+-------------------+----------+----------+----------+
1 row in set (1.04 sec)

mysql> delete from ck_engine2 where id=1;
Query OK, 262144 rows affected (1.65 sec)

mysql> optimize table ck_engine2;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| lustudy.ck_engine2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| lustudy.ck_engine2 | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5.21 sec)

有时间需要专门研究一下引擎的作用。

一、大量插入数据

1.对于MyISAM引擎，可以在大量插入数据的时候禁用keys（保证满足条件），插入结束后再恢复来节省时间。

mysql> alter table dept disable keys;
Query OK, 0 rows affected (0.04 sec)

--insert--

loading data...

mysql> alter table dept enable keys;
Query OK, 0 rows affected (0.00 sec)

二、group by在默认情况下会using filesort

在查询的时候可以通过使用order by null 来禁止排序

mysql> explain select * from dept group by deptno \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ALL
possible_keys: aa
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain select * from dept group by deptno order by null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ALL
possible_keys: aa
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using temporary
1 row in set (0.02 sec)

三、有些时候可以通过链接来替代子查询

尽量使用join而不是使用子查询

四、如果想在条件or中使用索引，只有在or的每一个列都要加索引。

不加索引的时候查询比较慢：

mysql> explain select * from dept where deptno =1 or loc='a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: ALL
possible_keys: aa
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
1 row in set (0.00 sec)

加上索引之后查询比较好：

mysql> alter table dept add index(loc);
Query OK, 10 rows affected (0.09 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> explain select * from dept where deptno=1 or loc ='a' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
         type: index_merge
possible_keys: aa,loc
          key: aa,loc
      key_len: 3,41
          ref: NULL
         rows: 2
        Extra: Using sort_union(aa,loc); Using where
1 row in set (0.01 sec)

五：选择合适的数据库引擎

MyISAM：读和插入为主，很少的更新和删除，对事物的完整性和并发性要求不是很高

innodb：提供了具有提交、回滚、崩溃恢复能力的事物安全，比MyISAM效率慢并且占用的磁盘空间更多。

六：选择合适的数据类型

1.在精度要求特别高的情况下，使用定点数来储存数据。尽量不用float（很重要）原因见我的文章：http://blog.csdn.net/howroad/article/details/78202832

演示:

mysql> create table ck_float(s1 float(10,2),s2 decimal(10,2));
Query OK, 0 rows affected (0.27 sec)

mysql> insert into ck_float values(10000000.31,10000000.31);
Query OK, 1 row affected (0.05 sec)

mysql> select * from ck_float;
+-------------+-------------+
| s1          | s2          |
+-------------+-------------+
|    10000.31 |    10000.31 |
|    10000.32 |    10000.32 |
| 10000000.00 | 10000000.31 |
+-------------+-------------+
3 rows in set (0.00 sec)

占满了10位字节的时候储存的数据就不准确了。

2.日期类型尽量用最小的存储类型。建议使用int

因为可以很快的算出时间间隔，比如算出3天之前发的帖子。

create table bbs(id int,title varchar(1024),pub_time int);

某些32位的系统 （可能64也是）date 函数只支持到2038.1.19

为什么是2038年，因为int占4个字节，最大标识4*8=32，最大表示2^32-1，如果int有符号，那么最大为2^31-1=2147483647,代表2038年的这一天

64位的也差不多而且会一直循环：

<?php
$a=pow(2,31)-1;
//2147483647
echo $a."<br />";
echo date("Y-m-d",2147483647)."<br />";
//2038-1-19
?>

七：对表进行水平划分

例如：通过id%100来分成100章表（0-99）然后判断查询
需要找到分表的标准

垂直分表：（把不常见的字段分成其他的表去，把表拆开，应该在数据裤设计的时候就分好）

八：选择合适的字段类型做主键（保小不保大，自增id等……）

九：如果数据库里面有图片文件，建议只存路径。甚至有一个图床服务器

十：数据库参数配置：

把缓存设置大一些：

innodb ：innodb_additional_mem_pool_size=64M

innodb_buffer_pool_size:1G

MyISAM：key_buffer_size

十一：合理的硬件资源和操作系统

尽量使用64位的MySQL

读写分离：MySQL PROXY同步到分服务器里去 目的：给大型网站缓解查询压力，在服务器端AMOEBA。

十二：UML（架构方面，比较抽象）