数据库优化

1. 数据库表的设计
2. 数据库优化

数据库优化（大课题）

数据库优化涉及方面特别多。

涉及的方面：

1. 表设计比较合理

2. 选择合适的表引擎

3. 选择合适的字符集

4. 选择合适的数据类型

5. 合理添加索引、使用索引

6. 写SQL语句注意问题

7. 硬件问题

8. 带宽

create table user(id int not null)

如何设计表才比较合理

1. 操作频繁的表中只要保留一些常用的字段，并且一张表的字段不宜过多。

2. 设计表要遵守三范式，但是不能严格遵守三范式，有些时候需要反范式

如何选择合理表引擎

1. 要根据表的特点，来选择表引擎。

   假设一个表写操作多，应该Innodb
   

2. 在MySQL5.6之后版本，建议都选择Innodb引擎

字符集

不同字符集支持不同语言，拉丁字符集是不支持中文。utf8字符集支持语言比较广。

1、 一般选择utf8, 但是如果表需要存放emoji表情到数据库，那么应该选择utf8mb4

如何选择合适的数据类型

1、 选择最小的数据类型，但是考虑扩展性

2、 能够使用数字类型，尽量不要其他类型

3、 尽量少用文本类型

4、 时间尽量使用时间类型存放，不用使用int存放

其他优化建议

1. 建议给每个字段都添加NOT NULL

数据类型

整数类型

    tinyint(n)  占用1字节，表示范围（有符号）： -128 ~ 127  无符号： 0-256


    int  占用4个字节，表示范围(有符号)：-21亿 ~ 21 亿   无符号：0 ~ 42亿

    bigint 占用8个字节


字符串类型

    varchar(n) 可变长度，n表示字符数。n最大超过255字符

        varchar(2) 存'ab',占用3个字节,当n小于255时候，需要1个字节存放长度

        varchar(300) 存'ab',占用4个字节,当n大于255时候，需要2个字节存放长度

    char(n)  固定长度，n表示字符数，n最大255，

    char(2) 存放'ab',占用2个字节，如果存'abc',只有'ab'


时间类型

    date  占用3个字节，表示年月日。 这个data特别存放生日。

    timestamp 占用4个字节，当一个字段是timestamp类型，不给这个字段插入数据，会自动将当前服务器时间作为这个字段的值

    datetime  占用8个字节，表示年月日时分秒

    time    占用3个字节，表示时分秒


文本类型


    能够不用文本类型，尽量不要用
    text

    mediumtext

• 字符数、字节的关系

  区别：
  字节是存储单位，类似于M,G。

  字符数是字符的个数。

  联系：

  一个字符等于多少字节？
  
      还看字符集。还要存放的是什么字符。
  
      如果存放的是英文，一个英文字符，不管表字符集是什么，都是一个字节。
  
      如果存放的是中文， 如果字符集是utf8,一个中文字符，占用3个字节，gb2312占用2个字节。
  

整数类型的问题

1. tinyint(1)与tinyint(3)区别 

        tinyint(1)这个1只是宽度，对存储范围，占用字节数没有影响。

2. 用户状态选择tinyint还是枚举？

    应该选择tinyint，原因：tinyint占用字节小，并且tinyint扩展好。

合理添加索引、使用索引（重点、难点）

1、 给经常作为where/order by/left join  on 的两端字段添加索引

2、 如果一个表中有很多字段需要添加索引，不要分别添加索引，而是应该添加联合索引

        //联合索引
        alter table user add index idx(name,pass);

3、 添加联合索引时，区分度大的字段写在前面


    区分度：能够排除更多的数据的字段就叫区分度大

4、 要根据联合索引的第一个字段来检索数据

5、 like “%jack%” 使用不上索引，尽量不要使用like

6、 给name/age添加联合索引

    select id,name,pass,age,height from user where name ='jack' and age='18';

• 如何使用索引，才真的使用上索引

  1. like “%jack%” 使用不上索引，尽量不要使用like

  2. 当索引字段使用MySQL的函数来修饰，这个字段索引就失效

     select id,name,pass from user where md5(pass)=$_POST[‘pass’];

  3、 当or两边任意一个字段没有索引，整条SQL语句都没有使用索引

如何写出比较高效SQL语句

select id,name from user(id,name,pass,sex,age);

//应用

select name,id,pass from user where id = 1+2 limit 100;//100万条，

1、  不要使用*, 尽量只查需要的字段。因为*会查询出更多数据，会导致传输时间变长。

2、  给每条查询的SQL添加LIMIT, 原因：减少传输数据大小

3、  根据索引来检索数据

4、  SQL语句中尽量减少使用MySQL内置函数,运算

5、 子查询减少使用，子查询不要用到核心代码中

6、  减少关联表查询，适度冗余字段

7、  将多表联查，切分为多个单表查询

#

订单表：

id  uid  gid   total   buy_time  make_time  status 

订单详情表

id   oid   gid   num   price    shouhuorenname   address   phone   gname

个人中心：

查看订单：

列出订单下面有什么商品：（商品名字、商品价格、商品购买数量、下单时间、状态）

    select gname,price,num,buy_time,status from orders left join order_detail;


    $arr = select id,uid,gid,buy_time from orders;

    foreach ($arr as $v) {

        select price,address,phone from order_detail where  oid  = $v['id'];
   }

myisam innodb区别：

1、 事务支持情况不同

2、 锁机制方式不同

3、 查询速度不同

4、 生成文件数量

规则 === 范式

user表

第一范式：保证每个列都是一个原子,保证不能再分解。

地址表：

id   address
1    中国广东广州天河区...


id    contry  pro   city  other

1     中国     广东

书籍

《高性能MySQL》

《高可用MySQL》