Mysql设计遵循规则

为什么要优化
系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度无法和内存相比

如何优化
设计数据库时：数据库表、字段的设计，存储引擎
利用好MySQL自身提供的功能，如索引等
横向扩展：MySQL集群、负载均衡、读写分离
SQL语句的优化（收效甚微）

字段设计
字段类型的选择，设计规范，范式，常见设计案例
原则：尽量使用整型表示字符串
存储IP
INET_ATON(str)，address to number

INET_NTOA(number)，number to address

MySQL内部的枚举类型（单选）和集合（多选）类型
但是因为维护成本较高因此不常使用，使用关联表的方式来替代enum

原则：定长和非定长数据类型的选择
decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text
金额
对数据的精度要求较高，小数的运算和存储存在精度问题（不能将所有小数转换成二进制）
定点数decimal
price decimal(8,2)有2位小数的定点数，定点数支持很大的数（甚至是超过int,bigint存储范围的数）

小单位大数额避免出现小数
元->分

字符串存储
定长char，非定长varchar、text（上限65535，其中varchar还会消耗1-3字节记录长度，而text使用额外空间记录长度）

原则：尽可能选择小的数据类型和指定短的长度
原则：尽可能使用 not null
非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。

null在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如select null = null和select null <> null（<>为不等号）有着同样的结果，只能通过is null和is not null来判断字段是否为null。

如何存储？MySQL中每条记录都需要额外的存储空间，表示每个字段是否为null。因此通常使用特殊的数据进行占位，比如int not null default 0、string not null default ‘’

原则：字段注释要完整，见名知意
原则：单表字段不宜过多
二三十个就极限了

原则：可以预留字段
在使用以上原则之前首先要满足业务需求
关联表的设计
外键foreign key只能实现一对一或一对多的映射
一对多
使用外键

多对多
单独新建一张表将多对多拆分成两个一对多

一对一
如商品的基本信息（item）和商品的详细信息（item_intro），通常使用相同的主键或者增加一个外键字段（item_id）
存储引擎选择
早期问题：如何选择MyISAM和Innodb？
现在不存在这个问题了，Innodb不断完善，从各个方面赶超MyISAM，也是MySQL默认使用的。
存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

功能差异
show engines

EngineSupportCommentInnoDBDEFAULTSupports transactions, row-level locking, and foreign keysMyISAMYESMyISAM storage engine

存储差异
MyISAMInnodb文件格式数据和索引是分别存储的，数据.MYD，索引.MYI数据和索引是集中存储的，.ibd文件能否移动能，一张表就对应.frm、MYD、MYI3个文件否，因为关联的还有data下的其它文件记录存储顺序按记录插入顺序保存按主键大小有序插入空间碎片（删除记录并flush table 表名之后，表文件大小不变）产生。定时整理：使用命令optimize table 表名实现不产生事务不支持支持外键不支持支持锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）表级锁定行级锁定、表级锁定，锁定力度小并发能力高

锁扩展
表级锁（table-level lock）：lock tables <table_name1>,<table_name2>... read/write，unlock tables <table_name1>,<table_name2>...。其中read是共享锁，一旦锁定任何客户端都不可读；write是独占/写锁，只有加锁的客户端可读可写，其他客户端既不可读也不可写。锁定的是一张表或几张表。
行级锁（row-level lock）：锁定的是一行或几行记录。共享锁：select * from <table_name> where <条件> LOCK IN SHARE MODE;，对查询的记录增加共享锁；select * from <table_name> where <条件> FOR UPDATE;，对查询的记录增加排他锁。这里值得注意的是：innodb的行锁，其实是一个子范围锁，依据条件锁定部分范围，而不是就映射到具体的行上，因此还有一个学名：间隙锁。比如select * from stu where id < 20 LOCK IN SHARE MODE会锁定id在20左右以下的范围，你可能无法插入id为18或22的一条新纪录。
选择依据
如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

索引
关键字与数据的映射关系称为索引（==包含关键字和对应的记录在磁盘中的地址==）。关键字是从数据当中提取的用于标识、检索数据的特定内容。
索引检索为什么快？
关键字相对于数据本身，==数据量小==
关键字是==有序==的，二分查找可快速确定位置
图书馆为每本书都加了索引号（类别-楼层-书架）、字典为词语解释按字母顺序编写目录等都用到了索引。

MySQL中索引类型
普通索引（key），唯一索引（unique key），主键索引（primary key），全文索引（fulltext key）
三种索引的索引方式是一样的，只不过对索引的关键字有不同的限制：

普通索引：对关键字没有限制
唯一索引：要求记录提供的关键字不能重复
主键索引：要求关键字唯一且不为null

如何创建索引
建立基础索引：在where、order by、join字段上建立索引。
优化，组合索引：基于业务逻辑
如果条件经常性出现在一起，那么可以考虑将多字段索引升级为==复合索引==
如果通过增加个别字段的索引，就可以出现==索引覆盖==，那么可以考虑为该字段建立索引
查询时，不常用到的索引，应该删除掉
前缀索引
语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

==实操的难度==：在于前缀截取的长度。

我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）
在配置文件中开启缓存
windows上是my.ini，linux上是my.cnf

在[mysqld]段中配置query_cache_type：

0：不开启
1：开启，默认缓存所有，需要在SQL语句中增加select sql-no-cache提示来放弃缓存
2：开启，默认都不缓存，需要在SQL语句中增加select sql-cache来主动缓存（==常用==）
更改配置后需要重启以使配置生效，重启后可通过show variables like ‘query_cache_type’;来查看：

show variables like 'query_cache_type';
query_cache_type DEMAND
在客户端设置缓存大小
通过配置项query_cache_size来设置：

show variables like 'query_cache_size';
query_cache_size 0

set global query_cache_size=64*1024*1024;
show variables like 'query_cache_size';
query_cache_size 67108864
将查询结果缓存
select sql_cache * from user;

重置缓存
reset query cache;

缓存失效问题（大问题）
当数据表改动时，基于该数据表的任何缓存都会被删除。（表层面的管理，不是记录层面的管理，因此失效率较高）

注意事项
应用程序，不应该关心query cache的使用情况。可以尝试使用，但不能由query cache决定业务逻辑，因为query cache由DBA来管理。
缓存是以SQL语句为key存储的，因此即使SQL语句功能相同，但如果多了一个空格或者大小写有差异都会导致匹配不到缓存。
数据库导入语句
在恢复数据时，可能会导入大量的数据。此时为了快速导入，需要掌握一些技巧：

导入时==先禁用索引和约束==：
alter table table-name disable keys
待数据导入完成之后，再开启索引和约束，一次性创建索引

alter table table-name enable keys
数据库如果使用的引擎是Innodb，那么它==默认会给每条写指令加上事务==（这也会消耗一定的时间），因此建议先手动开启事务，再执行一定量的批量导入，最后手动提交事务。
如果批量导入的SQL指令格式相同只是数据不同，那么你应该先prepare==预编译==一下，这样也能节省很多重复编译的时间。
limit offset,rows
尽量保证不要出现大的offset，比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中，要避免出现大页码的情况，尽量引导用户做条件过滤。

select * 要少用
即尽量选择自己需要的字段select，但这个影响不是很大，因为网络传输多了几十上百字节也没多少延时，并且现在流行的ORM框架都是用的select *，只是我们在设计表的时候注意将大数据量的字段分离，比如商品详情可以单独抽离出一张商品详情表，这样在查看商品简略页面时的加载速度就不会有影响了。

order by rand()不要用
它的逻辑就是随机排序（为每条数据生成一个随机数，然后根据随机数大小进行排序）。如select * from student order by rand() limit 5的执行效率就很低，因为它为表中的每条数据都生成随机数并进行排序，而我们只要前5条。

解决思路：在应用程序中，将随机的主键生成好，去数据库中利用主键检索。

单表和多表查询
多表查询：join、子查询都是涉及到多表的查询。如果你使用explain分析执行计划你会发现多表查询也是一个表一个表的处理，最后合并结果。因此可以说单表查询将计算压力放在了应用程序上，而多表查询将计算压力放在了数据库上。

现在有ORM框架帮我们解决了单表查询带来的对象映射问题（查询单表时，如果发现有外键自动再去查询关联表，是一个表一个表查的）。

count(*)
在MyISAM存储引擎中，会自动记录表的行数，因此使用count(*)能够快速返回。而Innodb内部没有这样一个计数器，需要我们手动统计记录数量，解决思路就是单独使用一张表：

idtablecount1student100

limit 1
如果可以确定仅仅检索一条，建议加上limit 1，其实ORM框架帮我们做到了这一点（查询单条的操作都会自动加上limit 1）。

慢查询日志
用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。
开启慢查询日志
配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间
配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志
一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

profile信息
配置项：profiling

开启profile
set profiling=on

开启后，所有的SQL执行的详细信息都会被自动记录下来

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看profile信息
show profiles

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into article values (null,'test profile',':)');
Query OK, 1 row affected (0.15 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------+
| 1 | 0.00086150 | show variables like 'profiling' |
| 2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+
通过Query_ID查看某条SQL所有详细步骤的时间
show profile for query Query_ID

上面show profiles的结果中，每个SQL有一个Query_ID，可以通过它查看执行该SQL经过了哪些步骤，各消耗了多场时间

典型的服务器配置
以下的配置全都取决于实际的运行环境
max_connections，最大客户端连接数
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
table_open_cache，表文件句柄缓存（表数据是存储在磁盘上的，缓存磁盘文件的句柄方便打开文件读取数据）
mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+
key_buffer_size，索引缓存大小（将从磁盘上读取的索引缓存到内存，可以设置大一些，有利于快速检索）
mysql> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+
innodb_buffer_pool_size，Innodb存储引擎缓存池大小（对于Innodb来说最重要的一个配置，如果所有的表用的都是Innodb，那么甚至建议将该值设置到物理内存的80%，Innodb的很多性能提升如索引都是依靠这个）
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_buffer_pool_size | 8388608 | +-------------------------+---------+
innodb_file_per_table（innodb中，表数据存放在.ibd文件中，如果将该配置项设置为ON，那么一个表对应一个ibd文件，否则所有innodb共享表空间）