mysql优化（第十四天）

作者: hzhang94 | 来源:发表于2018-10-22 11:24 被阅读0次

MySQL如何优化

表的设计合理化(符合3NF)；
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]；
SQL语句优化；
分表技术(水平分割、垂直分割)；
读写[写: update/delete/add]分离；
存储过程 [模块化编程，可以提高速度]；
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]；
mysql服务器硬件升级；
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

数据库设计

什么是数据库范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

数据库三大范式

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。
第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

分表分库

垂直拆分

垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性。

垂直拆分用于分布式场景。

水平拆分

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分。

通俗理解：水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中。

水平拆分示例

3张用户表和1张uuid表，用来提供自增的id。

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public String add(String name, String pwd) {
        // 1.先获取到 自定增长ID
        String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
        jdbcTemplate.update(idInsertSQL);
        Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        // 2.判断存储表名称
        String tableName = "user" + insertId % 3;
        // 3.注册数据
        String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
                + "');";
        System.out.println("insertUserSql:" + insertUserSql);
        jdbcTemplate.update(insertUserSql);
        return "success";
    }

    public String get(Long id) {
        String tableName = "user" + id % 3;
        String sql = "select name from " + tableName + "  where id="+id;
        System.out.println("SQL:" + sql);
        String name = jdbcTemplate.queryForObject(sql, String.class);
        return name;
    }

}

索引

什么是索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

查询索引

desc 表名;   --不能显示索引名称
show index from 表名;
show keys from 表名;

索引的分类

主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE
tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

--添加主键索引
alter table 表名 add primary key (列名);
--删除主键索引
alter table articles drop primary key;

全文索引

1、创建全文索引表；

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

2、查询全文索引；

--错误用法:
select * from articles where body like '%mysql%'; 错误用法 索引不会生效
--正确用法:
select * from articles where match(title,body) against ( 'database')

3、全文索引注意事项；

在mysql中fulltext 索引只针对 myisam生效，mysql自己提供的fulltext针对英文生效，使用sphinx (coreseek) 技术处理中文，使用方法是 match(字段名..) against(‘关键字’)；
全文索引：停止词，因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）；
```
--输出的是每行和database的匹配度
select match(title,body) against ('database') from articles;
```

唯一索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

--创建索引
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)； 
--修改表
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)； 
--创建表的时候指定索引
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串

普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);

索引的代价

占用磁盘空间
对DML(update、delete、insert)语句的效率影响
增删改会对索引影响，因为索引要重新整理。
| 存储引擎 | 允许的索引类型 |
| :---------: | :------------: |
| myisam | btree |
| innodb | btree |
| memory/yeap | Hash,btree |

添加索引需要注意：

在where条件经常使用；

该字段的内容不是唯一的几个值；

字段内容不是频繁变化。

查询索引使用率

show status like ‘handler_read%’;

handler_read_key: --这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:  --这个值越高，说明查询低效

总结

--创建主键索引
alter table 表名 add primary key (列名);
--创建一个联合索引
alter table dept add index my_ind (dname,loc); --dname 左边的列,loc就是右边的列

对于创建的多列索引，如果不是使用第一部分，则不会创建索引；
```
explain select * from dept where loc='aaa';  --就不会使用到索引
```
模糊查询在like前面有百分号开头会失效；
如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 才会使用索引；
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须“ ”), 也就是，如果列是字符串类型，就一定要用“ ”把他包括起来；
如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

SQL优化

查看Mysql服务器状态

使用show status查看MySQL服务器状态信息：

--mysql数据库启动了多少时间
show status like 'uptime';

--类推 update  delete（显示数据库的查询，更新，添加，删除的次数）
show  stauts like 'com_select'  

--默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)
show [session|global] status like .... 

--显示到mysql数据库的连接数
show status like  'connections '; 

--显示慢查询次数
show status like 'slow_queries';

慢查询

什么是慢查询

MySQL默认10秒内没有响应SQL结果,则为慢查询，可以修改慢查询。

修改慢查询

--查询慢查询时间
show variables like 'long_query_time';
--修改慢查询时间
set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

将慢查询定位到日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以。

bin\mysqld.exe --safe-mode  --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在my.ini 文件中记录的位置：

#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

MySQL数据引擎

myisam存储：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎，比如 bbs 中的发帖表，回复表。
INNODB 存储：对事务要求高，保存的数据都是重要数据，我们建议使用INNODB，比如订单表，账号表。
Memory存储：比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）。

MyISAM 和 INNODB的区别

事务安全（MyISAM不支持事务，INNODB支持事务）；
查询和添加速度（MyISAM批量插入速度快）；
支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）；
锁机制（MyISAM时表锁，innodb是行锁）；
外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)。
如果你的数据库的存储引擎是myisam，请一定记住要定时进行碎片整理，optimize table test100;

image

总结

使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以防止排序；
```
explain select * from emp  group by deptno order by null;
```

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; --[简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno; --[左外连接，更ok!]

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null；最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库。
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：select id from t where num = 0。