mysql设计和优化能力 mysql的优化有哪几种

转载

云端筑梦者 2024-02-26 10:08:04

文章标签 mysql设计和优化能力 MySQL优化 SQL优化小技巧 mysql 慢查询 文章分类 MySQL 数据库

Mysql数据库的优化技术

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

表的设计合理化(符合3NF)

表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。
2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现。
3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放。有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

注： ☞ 数据库的分类
关系型数据库: mysql/oracle/db2/informix/sysbase/sql server
非关系型数据库: (特点: 面向对象或者集合)
NoSql数据库: MongoDB(特点是面向文档)

Sql语句本身的优化

问题是：如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)
① 首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete… / 当前连接)

show status

常用的:

show status like ‘uptime’ ;

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’;

//显示慢查询次数

show status like ‘slow_queries’;

② 如何去定位慢查询

构建一个大表(400 万)-> 存储过程构建

默认情况下，mysql认为10秒才是一个慢查询.

l 修改mysql的慢查询.

show variables like ‘long_query_time’ ; //可以显示当前慢查询时间

set long_query_time=1 ;//可以修改慢查询时间

③ 这时我们如果出现一条语句执行时间超过1秒中，就会统计到.

④ 如果把慢查询的sql记录到我们的一个日志中

在默认情况下，我们的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:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/”

⑤ 测试,可以看到在日志中就记录下mysql慢sql语句.

优化问题.
通过 explain 语句可以分析，mysql如何执行你的sql语句。

四种索引(主键索引/唯一索引/全文索引/普通索引)

添加

1.1主键索引添加

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

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

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

1.2普通索引

一般来说，普通索引的创建，是先创建表，然后在创建普通索引
create index 索引名 on 表 (列1,列名2);

1.3创建全文索引

全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.

1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method
(有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert
MyISAM比较适合。
2、InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

☞ 说明:

在mysql中fulltext 索引只针对 myisam生效
mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
使用方法是 match(字段名…) against(‘关键字’)
全文索引一个叫停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

1.4唯一索引

①当表的某列被指定为unique约束时，这列就是一个唯一索引;
②在创建表后，再去创建唯一索引;

查询索引

desc 表名【该方法的缺点是：　不能够显示索引名.】

show index(es) from 表名

show keys from 表名

删除

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key [这里有一个小问题]

修改

先删除，再重新创建.

索引的代价:

占用磁盘空间(空间换时间)
对dml操作有影响，变慢

在哪些列上适合添加索引?

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

a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值(如gender) c: 字段内容不是频繁变化.

说明，如果我们的表中有复合索引(索引作用在多列上)，此时我们注意:

对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

explain select * from dept where loc=‘aaa’\G

就不会使用到索引

对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引， ‘aaa%’ 会使用到索引。

比如: explain select * from dept where dname like ‘%aaa’\G

不能使用索引，即，在like查询时，关键的 ‘关键字’ , 最前面，不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用全文索引。

如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用 or 关键字
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.
如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（节约空间）
explain 可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令.
如何查看索引使用的情况:
show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。