2020-03-18

mysql优化

#1、选择优化的数据类型

##1 更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小的数据类型

例如只需要存储 `0-200`， `tinyint unsigned`更好一些

更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

##2 简单就好
简单数据类型的操作通常需要更少的CPU周期。

例如，整型比字符型操作代价更低，因为字符集和校对规则（排序规则）是字符比较比整型比较更复杂。
ex: 一个是应该使用Mysql内建的类型(date,time,datetime)而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地。

##3 尽量避免NULL
很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为NULL是列的默认属性。
通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对Mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。
可为NULL的列会使用更多的存储空间，在Mysql中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，
在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
通常此项改动对于InnoDB的性能提升比较小。InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据有很好的空间效率。

优化工具

explain

explain-output

show-profile

* The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release.
Use the Performance Schema instead; see Section 25.19.1, “Query Profiling Using Performance Schema”. *

set profiling=1;
select * from student;
show profile for query 1;
show profile all for query 1;
show profile all for query 1\G               (后面没有分号！！！)

---------------------------------------

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from student;
+----+-----------+------+------+
| id | name      | age  | sex  |
+----+-----------+------+------+
|  1 | xiaoming  |   19 |    1 |
|  2 | xiaohong  |   18 |    0 |
|  3 | xiaolan   |   20 |    0 |
|  4 | xiaolv    |   20 |    0 |
|  5 | xiaohuang |   20 |    0 |
|  6 | 666       |   17 |    0 |
+----+-----------+------+------+
6 rows in set (0.00 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000081 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000019 |
| init                 | 0.000018 |
| System lock          | 0.000009 |
| optimizing           | 0.000006 |
| statistics           | 0.000016 |
| preparing            | 0.000013 |
| executing            | 0.000005 |
| Sending data         | 0.000056 |
| end                  | 0.000006 |
| query end            | 0.000009 |
| closing tables       | 0.000008 |
| freeing items        | 0.000013 |
| cleaning up          | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000081 | 0.000032 |   0.000045 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000011 | 0.000005 |   0.000006 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000019 | 0.000007 |   0.000010 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5753 |
| init                 | 0.000018 | 0.000007 |   0.000011 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000009 | 0.000005 |   0.000006 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000006 | 0.000002 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         158 |
| statistics           | 0.000016 | 0.000007 |   0.000009 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         374 |
| preparing            | 0.000013 | 0.000005 |   0.000008 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         482 |
| executing            | 0.000005 | 0.000002 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         126 |
| Sending data         | 0.000056 | 0.000023 |   0.000033 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         202 |
| end                  | 0.000006 | 0.000002 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000009 | 0.000004 |   0.000006 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4956 |
| closing tables       | 0.000008 | 0.000003 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5009 |
| freeing items        | 0.000013 | 0.000005 |   0.000008 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| cleaning up          | 0.000016 | 0.000007 |   0.000008 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

Performance

performance-schema-quick-start

https://dev.mysql.com/doc/refman/8.0/en/optimization.html

分库分表

mysql中间件分享（Mysql-prxoy,Atlas,DBProxy,Amoeba,cobar,TDDL）

主从复制

MTS：并行复制技术

zhangyong