mysql优化

例子
数据库用户模块需要的信息

用户姓名，用户登录名，密码，手机号，证件类型，证件号码，性别，邮箱，邮编，省市区，详细地址，积分，注册时间，生日，用户状态，用户级别，用户余额

商品模块所需要的信息

成本，商品名称，国条码，分类，品牌名称，供应商，销售价格，重量，长度，宽度，高度，图片信息，描述，生产时间，有效期，颜色，上下架状态

订单模块所需要的信息

下单用户，收货人，支付方式，支付金额，优惠金额，省，市，区，地址，邮编，订单商品，支付时间，收货时间，状态，下单时间，手机号，订单积分，物流信息，运费
其中订单商品中又有：商品数量,商品名称，商品价格，仓库信息， 仓库信息里包含：名称，手机，联系人，地址

如把用户的所有字段存到一张用户表里，优点，易于数据存取！缺点 数据插入异常，更新异常，删除异常，数据存在冗余，数据表过宽，修改表结构的效率
一张数据表中字段之间不要存在传递的数据依赖关系
尽量做到冷热数据分离，减小表的宽度
所以，用户模块大概需要的表
用户登录表{用户登录名，密码，用户状态}
用户地址表{省，市，区，邮编，地址}
用户信息表{用户姓名,证件类型，证件号码，手机号，邮箱，性别，积分，注册时间，生日，会员级别，用户余额}
用户级别信息表{会员级别，级别积分下限，级别积分上限}

用户id int 主键。自增。无符号   用户登录名 varchar20 用户密码char32  最后修改时间 timestamp 会自动修改这行数据修改的时间
证件号码 varchar20   手机号 int 无符号 邮箱 varchar50 性别 char1 注册时间 timestamp 会员生日datetime 会员级别 tinyint
用户余额 decimal(8,2)  

mysql分区表
是否支持mysql分区表的mysql命令 show plugins;
如果有partition这行，就是支持的，
特点 在逻辑上为一个表，在物理上存储在多个文件中
如果我们需要对某个用户的登录日志进行频繁查询的话，我们使用一种按用户id作为一种hash分区最为合适，因为可以保证同一用户的登录日志保存在同一分区中，避免了跨分区表
如果我们需要对这张表进行范围操作，比如说按年该表的数据删除，或归档，则我们按时间有关的列作为一种RANGE分区最为合适

DB规划
1.为以后数据库迁移提供方便
2.为避免跨库操作，把经常一起关联查询的表放到一个Db中，为了方便识别表所在的DB，在表名前增加库名前缀

查询一条sql的执行效率
在sql语句前加上explain,查出的结果会有以下的字段
id列中的数据为一组数字，表示执行select语句的顺序，id值相同时，执行顺序由上到下，id值越大优先级越高，越先被执行
如果为null的话，表示
select_type列

如果值为SIMPLE 则表示为简单查询 不包含子查询或是union操作的查询
PRIMARY 则表示查询中如果包含任何的在查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY  select列表中的子查询
DEPENDENT SUBQUERY 依赖外部的子查询
UNION 表示UNION操作的第二个或是之后的查询的值为union
DEPENDENT UNION   当UNION做为子查询时，第二或是第二个后的查询的select_type值
UNION RESULT  UNION产生的结果集
DERIVED   出现在FROM子句中的子查询

table列
如果给表取了别名，则为表的别名，如没取，则为表名
partitions列
对于分区表，显示查询的分区id 对于非分区表，显示为null
type列 这个列 按照值的顺序，性能由高到低

   system   这是CONST联接类型的一个特例，当查询的表只有一行时使用
   const 表中有且只有一个匹配的行是使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
   eq_ref 唯一索引或主键索引查找，对于每个索引键，表中只有一条记录与之匹配
   ref   非唯一索引查找，返回匹配某个单独值的所有行
   ref_or_null 类似于ref类型的查询，但是附加了对null值列的查询
   index_merge   该联接类型表示使用了索引合并优化方法
   range   索引范围扫描，常见于between > <这样的查询条件
   index    Full index Scan ,全索引扫描，同All的区别是遍历的是索引树
   All  FULL TABLE Scan 全表扫描，这是效率最差的联接方式

extra列

           值distinct   优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
           值not  exists  使用了not exists 来优化查询
           值Using filesort 使用额外操作进行培训，通常会出现在order by 或group by 查询中
           值Using index  使用了索引覆盖进行查询
           值Using temporary  mysql需要使用临时表来处理查询，常见于排序，子查询，或分组查询
           值Using where 余姚在mysql服务器层使用where条件来过滤数据
           值 select tables optimized away 直接通过索引来获取数据，不用访问表，通常是效率最高的

possible_keys列
指出了当前查询可能会使用到的索引
key列
指出了查询优化器查询实际使用到的索引，如果在表中没有可以使用的索引则会显示null,如果使用到的索引显示在key列没有显示在possible_keys列则该查询使用了覆盖索引
key_len列
表示索引字段最大可能的长度，（按照定义字段类型的长度计算的，并非是字段的实际长度来计算的）
ref列
当前表在利用key列上的索引进行查询时所用到的列的长
rows列
表示mysql通过索引统计信息，估算的所需读取的行数，rows值的大小是个统计抽样的结果，并不十分准确
filtered列
表示返回结果的行数占所需读取行数是百分比 所以值越大越好

如何找到需要优化的sql
慢查询日志
启用mysql慢查询日志

 set global slow_query_log_file = /sql_log/slow_log.log;  设置慢查询日志的存储路径
 set global log_queries_not_using_indexes = on;  未使用索引的sql记录日志
 set global long_query_time = 0.001;查询时间超过这个时间的sql
 set global low_query_log = on; 启用慢查询日志

如何分析慢查询日志

用 mysqldumpslow   命令mysqldumpslow