文档章节

mysql优化概述(范式.索引.定位慢查询)

PHer
 PHer
发布于 2014/08/28 16:31
字数 1585
阅读 21
收藏 0

提升网页的显示速度的方式有很多,网站访问速度慢的主要原因是操作了数据库,或者是大并发。

解决方案也是多式多样,比如,页面静态化,使用memcache或者redis,将安全性要求不高,却访问平凡的数据可以存放到内存数据库中.

但是一个网站总是要操作数据库的,所以提高sql语句的CRUD效率也是必须要做的工作.

常用的方法

   创建的表结构要满足三范式

   创建适当的索引[主键索引|唯一索引|全文索引|普通索引|空间索引|fulltext]

   优化sql语句(定位慢查询,然后优化)

   创建适当的存储过程,视图,触发器,函数

   读写分离

   优化一下my.ini文件

   硬件升级  


范式详解 

    一共有三个,第一范式,第二范式,第三范式

    第一范式:

        表的列属性,列不可以在分割,并且在表中无重复列。关系型数据库天然满足第一范式;

   关系型数据库: mysql , sql server, oracle , informix db2, postgresql

   非关系型数据: 面向对象和集合

   nosql数据库: mongodb[面向文档]

    第二范式:

        不能存在完全相同的两条记录,通常使用一个主键索引来实现,主键是非业务逻辑主键

    第三范式:

        表中不能存在冗余数据.表的列的值,如果可以通过显示推到和隐示推到出,则就不应该设置该列

    如:

        

范式也不是必须遵守的,在特殊情况下,我们可能需要反三范式


如何解决,定位慢查询

     默认情况下,mysql是不会记录慢查询语句,默认情况下,慢查询的时间是10秒

show variables like 'long_query_time'

     1.以记录慢查询的方式来启动mysql

mysqld.exe  --safe-mode --log-slow-queries

    先把mysql关闭然后在重启.

 

1.      慢查询日志存放在mysqldata目录下. my.ini 配置文件有data目录

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

2.      为了测试,我们修改一下mysql默认的慢查询时间

set  long_query_time=1;

3.      慢查询日志的查看

4.   我们需要明确,为什么这个select .

这里给大家介绍一款工具  explain 工具,可以帮助我们分析mysql数据库在执行一个sql语句的时候,是安装什么方式执行[获得关于MySQL如何执行SELECT语句的信息].

explain sql\G

explain select * from emp where empno=347677\G

1.      通过刚才的分析,我们初步判断是没有创建索引造成.

empno 这个字段上创建主键索引

alter  table 表名  add  primary key(列名,列名...)

alter  table  emp  add  primary key (empno);

创建完索引后,我们发现索引文件变大了,这说明索引有开销,要占用磁盘空间.


索引原理

使用索引的代价占用更多多的磁盘空间

dmlupdate ,insert , delete )速度有影响

1.      目前的索引类型

l      hash的索引

l     bintree 二叉树

创建索引的注意事项

索引原理

主键索引创建有两种情况,

1. 在创建表的时候,直接指定主键索引

2. 创建表后,在增加主键索引.

1        create table aaa( id int primary key auto_increment, name varchar(32));

2        create table bbb(id int , name varchar(32));

alter table bbb add primary key(id);

    主键索引的特点1. 一个表中最多只有一个主键索引

                 2. 一个主键索引可以指向多列

                 3. 主键索引的列,不能有重复的值,也不能有null

                 4. 主键索引的效率高

唯一索引的创建有两种情况:

1. 在创建表时,直接指定唯一索引,

2. 把表创建完后,在指定唯一索引.

1.      create table ddd( id int primary key auto_increment, name varchar(32) not null default '', email varchar(64) unique);
2.      create table eee( id int primary key auto_increment, name varchar(32) not null default '', email varchar(64));

添加索引方法有两种

a.        create unique index 索引名字 on 表名 (列名..)

 create unique index  uni_email  on  eee(email);

b.       alter  table 表名  add  unique [索引名] (列名..)

alter  table eee  add  unique  (email );

     唯一索引的特点: 1. 一个表中可以有多个唯一索引

                    2. 一个唯一索引可以指向多列 

                    3. 如果你在唯一索引上,没有指定not null ,则该列可以为空,同时可以有多个null

            4.     唯一索引的效率较高

    普通索引创建:

    一般来说,是表创建完了之后在来创建普通索引

create table fff(id int primary key auto_increment, name varchar(32) not null default '',email varchar(64) not null default '');

添加普通索引方法两个

1.  create index 索引名 on 表名 (列名。。)

   create index inx_email on fff(email);

2.  alter table 表名 add  index [索引名] (列名..)


    全文索引的创建

mysql自带的全文索引mysql5.5不支持中文, 支持英文,同时要求表的存储引擎是myisam。如果希望支持中文,有两个方案 1. 使用sphinx中文版 coreseek 2. 插件mysqlcft.

创建一张表,使用全文索引

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;
    如何使用全文索引

        select * from articles where body like '%database%';  [这样没有使用到索引]
        百分号不能加前面
,可以在中间,也可以在最后



SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')[ok]
全文索引有两个概念[匹配度,停止词]
匹配度: 在使用全文索引去检索数据,匹配到的概率是多大.


特别注意的是:

停止词: 全文索引对非常普通的词,不会创建索引.

索引的查询

        show index from table;

        show indexes from table;

        show keys from table;

        desc table;


索引的修改

        删掉索引,然后重新创建

索引的删除

drop  index索引名  on 表名;

alter table 表名 drop index 索引名 ;

主键删除

alter table 表名 drop primary key;

如果你在删除主键索引时,该主键是自增的,则需要先去掉自增属性,然后在干掉.

create table ttt( id int unsigned primary key auto_increment , name varchar(32) not null default '');

先去掉auto_increment;

alter table 表名 modify 列定义;

(1)alter  table ttt  modify id int unsigned;

(2) alter  table  ttt drop primary key;

        
























© 著作权归作者所有

PHer
粉丝 4
博文 121
码字总数 38443
作品 0
成都
程序员
私信 提问
Mysql性能优化一

mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升。 Mysql数据库的优化技术 对mysql优化是一个综合性的技术,主要包括 表的设计合理化(符合3...

JAVA_NINA
2016/05/09
197
0
MySQL优化(DBA级优化)

1.数据库优化的可以从如下几个方面着手: 1.>sql语句和索引. 2.>数据库表结构. 3.>系统配置. 4.>硬件 2.使用MySQL的慢查询日志对效率有问题的sql进行监控 1.>查看慢查询日志是否开启:show var...

老汉健身
2018/06/15
0
0
Video-No.03 韩顺平_MySQL优化

1、MySQL数据库优化技术 1)表的设计合理化(符合3NF) 2)添加适当的索引(index):普通索引、主键索引、唯一索引、全文索引、复合索引 3)分表技术(水平分割、垂直分割) 4)读写分离(读...

shawntime
2015/10/07
138
0
面试宝典系列-mysql性能优化方案

表结构优化: 1、根据自己的业务选择合适的引擎 2、表设计时尽量符合第三范式 第一范式:表的列的具有原子性,不可再分解,即列的信息,不能分解, 只有数据库是关系型数据库就自动的满足。 第...

suyain
2018/08/02
56
0
高性能MySQL06-查询优化(慢查询)

一、分析原因 SQL语句慢查询的原因有多种,如: 1)数据方面: 需要查询的表数据量太大导致性能下降; 是否向数据库请求了不需要的数据行或数据列; MySQL是否在扫描额外的记录 2)SQL语句太...

架构师springboot
05/15
33
0

没有更多内容

加载失败,请刷新页面

加载更多

堆”和“栈

C++作为一款C语言的升级版本,具有非常强大的功能。它不但能够支持各种程序设计风格,而且还具有C语言的所有功能。我们在这里为大家介绍的是其中一个比较重要的内容,C++内存区域的基本介绍。...

SibylY
18分钟前
2
0
总结:Https

一、介绍 简单理解,https即在http协议的基础上,增加了SSL协议,保障数据传输的安全性。 它由以前的http—–>tcp,改为http——>SSL—–>tcp;https采用了共享密钥加密+公开密钥加密的方式 ...

浮躁的码农
20分钟前
2
0
数据库表与表之间的一对一、一对多、多对多关系

表1 foreign key 表2 多对一:表 1 的多条记录对应表 2 的一条记录 利用foreign key的原理我们可以制作两张表的多对多,一对一关系 多对多: 表1的多条记录可以对应表2的一条记录 表2的多条记...

Garphy
51分钟前
6
0
MySQL 表崩溃修复

MySQL日志报错 2019-10-19 13:41:51 19916 [ERROR] /usr/local/mysql/bin/mysqld: Table './initread_hss/user_info' is marked as crashed and should be repaired2019-10-19 13:41:51 1......

雁南飞丶
今天
6
0
Error和Exception

1.Error类和Exception类都是继承Throwable类 2.Error(错误)是系统中的错误,程序员是不能改变的和处理的,是在程序编译时出现的错误,只能通过修改程序才能修正。一般是指与虚拟机相关的问...

大瑞清_liurq
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部