Mysql优化

                                             Mysql优化

一、mysql如何优化

1、数据库设计合理（遵循三大范式）

2、添加索引（普通，主键，唯一，全文索引）

3、分表分库技术（取模分表，水平分表，垂直分表）

4、读写分离

5、存储过程适当使用

6、配置MySQL最大连接数（并发）my.ini文件

7、mysql服务器升级

8、随时清理碎片化

9、sql语句调优

 

二、数据库规范设计

 

1、什么是数据库范式：

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

 

2、三大范式五大约束：

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。（要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情）

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。（表中的每一列只与主键直接相关而不是间接相关，表中的每一列只能依赖于主键）

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

五大约束：

primary KEY:设置主键约束；

UNIQUE：设置唯一性约束，不能有重复值；

DEFAULT： 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2

NOT NULL：设置非空约束，该字段不能为空；

FOREIGN key :设置外键约束。

博客推荐：https://www.cnblogs.com/waj6511988/p/7027127.html

 

三、数据库分表分库

分库：讲一个大项目进行拆分成多个小项目，每个小项目都有自己单独的数据库。它们之间互不影响。

案例：垂直分割（电商项目中的会员数据库，订单数据库，支付数据库）

 

分表：将存放相同数据的表进行拆分，当数据量大的时候难以单标维护

案例：水平分割（取模算法）

缺陷：分页查询，查询受到限制

解决方法：阿里云云数据库RDS

 

具体案例介绍：

首先我创建三张表 user0 / user1 /user2 , 然后我再创建 uuid表，该表的作用就是提供自增的id。

@Service
public class UserService {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public String regit(String name, String pwd) {
		String insertUUid = "insert into uuid values (null)";
		jdbcTemplate.update(insertUUid);
		Long userId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		//存放到具体表中
		String tableName = "user"+userId%3;
		//插入到具体表中
		String insertUserSql = "insert into "+tableName +" values("+userId+",'"+name+"','"+pwd+"')";
		System.out.println("插入语句："+insertUserSql);
		jdbcTemplate.update(insertUserSql);
		return "success";
	}
	
	public String get(Long userId) {
		//存放在具体那张表中
		String tableName = "user"+userId%3;
		String selectUserSql = "select name from "+tableName +" where id ="+userId;
		System.out.println("sql："+selectUserSql);
		String name = jdbcTemplate.queryForObject(selectUserSql, String.class);
		return name;
	}
}

 

四、SQL优化定位慢查询

 

1、show status:使用show status查看MySQL服务器状态信息

         常用命令：

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

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

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

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

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

 

2、慢查询：

         介绍：MySQL默认10秒内没有响应SQL结果，则为慢查询，我们可以修改MySQL慢查询默认时间。

3、如何修改慢查询：

show variables like 'long_query_time';  //查询慢查询时间

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

4、如何定位慢查询：在mysql安装目录的bin文件夹下进行操作

在默认情况下，我们的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/"

五、索引

1、索引介绍：

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

2、索引好处：提高查询效率。

 

3、索引实现原理：采用b-tree排序算法。

4、索引分类：

（1）、主键索引

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

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table aaa

(id int unsigned primary key auto_increment ,

name varchar(32) not null default '');

//这是id 列就是主键索引.

create table bbb (id int , name varchar(32) not null default '');

//如果你创建表时，没有指定主键索引，也可以在创建表后，在添加, 指令:

//实例:

alter table 表名 add primary key (列名);

//删除主键索引

alter table articles drop primary key;

（2）、查询索引

desc  表名;   不能显示索引名称

show index from 表名

show keys from 表名

（3）、全文索引

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 ...');

错误用法:

select * from articles where body like '%mysql%'; 错误用法 索引不会生效

正确用法:

select * from articles where match(title,body) against ( 'database')

说明:

在mysql中fulltext 索引只针对 myisam生效

mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文

使用方法是 match(字段名..) against(‘关键字’)

全文索引：停止词,  因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）

mysql> select match(title,body) against ('database') from articles;（输出的是每行和database的匹配度）

 

（4）、唯一索引

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

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)； 

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)； 

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

//创建表结构
create table ddd(id int primary key auto_increment , name varchar(32) unique);

注意：

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

但是不能存有重复的空字符串’’

 

（5）、普通索引：

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

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

5、MySQL explain执行计划解读

引言：

实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有

sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运

行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优化和索引也就显得很重要了。

问题：

当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？答案是能的，mysql提供了相应的功能和语法来实现该功能。

分析：

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。比如我们要分析如下SQL语句：

explain select * from table where table.id = 1 ;

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解释
table 
显示这一行的数据是关于哪张表的
type 
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
说明：不同连接类型的解释（按照效率高低的顺序排序）
system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。
possible_keys 
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key 
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len 
使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref 
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows 
MYSQL认为必须检查的用来返回请求数据的行数
Extra 
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
说明：extra列返回的描述的意义
Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。


因此，弄明白了explain语法返回的每一项结果，我们就能知道查询大致的运行时间了，如果查询里没有用到索引、或者需要扫描的行过多，那么可以感到明显的延迟。因此需要改变查询方式或者新建索引。mysql中的explain语法可以帮助我们改写查询，优化表的结构和索引的设置，从而最大地提高查询效率。当然，在大规模数据量时，索引的建立和维护的代价也是很高的，往往需要较长的时间和较大的空间，如果在不同的列组合上建立索引，空间的开销会更大。因此索引最好设置在需要经常查询的字段中。

6、索引的代价

缺点：

占用磁盘空间

对DML(update、delete、insert)语句的效率影响

增删改会对索引影响，因为索引要重新整理。

存储引擎	允许的索引类型
myisam	btree
innodb	btree
memory/yeap	Hash,btree

那些列上适合添加索引：

查询作为查询条件字段应该创建索引

唯一性太差的字段不适合单独创建索引,即使频繁 

Select * from emp where sex=’男’

频繁更新字段，也不要定义索引。

不会出现在where语句的字段不要创建索引

 

总结:满足一下条件的字段，才应该创建索引

• 肯定在where条件经常使用

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

• 字段内容不是频繁变化

 

7、索引的注意事项：

 创建一张表：

 新增dept 数据：

create PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
 declare i int DEFAULT 0;
 set autocommit=0;
 REPEAT
 set i=i+1;
 insert into dept values ((start+i),rand_string(10),rand_string(8));
 UNTIL i =max_num
 end REPEAT;
  commit;
END
执行
call insert_dept(100,10);

创建主键索引：

alter table 表名 add primary key (列名);

创建一个联合索引：

alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列

注意:

1.对于创建的多列索引，如果不是使用第一部分，则不会创建索引。

explain select * from dept where loc='aaa'\G 

就不会使用到索引

2.模糊查询在like前面有百分号开头会失效。

3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

 

查询所用使用率：

show status like ‘handler_read%’;

大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

 

8、SQL语句优化：

 

详细网址推荐：http://bbs.itmayiedu.com/article/1511164574773