文档章节

MySQL

stars永恒
 stars永恒
发布于 06/17 21:57
字数 2448
阅读 8
收藏 0
点赞 0
评论 0

查看表相关命令

- 查看表结构
    desc 表名
- 查看生成表的SQL
    show create table 表名
- 查看索引
    show index from  表名

 

使用索引和不使用索引

由于索引是专门用于加速搜索而生,所以加上索引之后,查询效率会快到飞起来。
 
# 有索引
mysql> select * from tb1 where name = 'zhangqiye';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | zhangqiye | zhangqiye@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (0.00 sec)
 
# 无索引
mysql> select * from tb1 where email = 'zhangqiye@live.com';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | zhangqiye | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (1.23 sec)

 

正确使用索引

数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。

即使建立索引,索引也不会生效的情况

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'zhangqiye';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的:当or条件中有未建立索引的列才失效,以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'zhangqiye'
- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'zhangqiye'
    特别的:如果是主键,则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'zhangqiye'
    特别的:如果是主键或索引是整数类型,则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候,选择的映射如果不是索引,则不走索引
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

 

其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(经常使用多个条件查询时)
- 尽量使用短索引
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合

 

limit分页

无论是否有索引,limit分页是一个值得关注的问题

mysql大数据量使用limit分页,随着页码的增大,查询效率越低下。

 

直接用limit start, count分页语句

select * from product limit start, count
当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from product limit 10, 20   0.016select * from product limit 100, 20   0.016select * from product limit 1000, 20   0.047select * from product limit 10000, 20   0.094

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下(也就是记录的一般左右)      select * from product limit 400000, 20   3.229秒

再看我们取最后一页记录的时间
select * from product limit 866613, 20   37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时,像这种分页最大的页码页显然这种时
间是无法忍受的。

从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

 

对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈

另一种写法SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id查询时间也很短,赞!

其实两者用的都是一个原理嘛,所以效果也差不多


执行计划

explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
|  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

结果字段说明

id:查询顺序标识

select_type

查询类型

SIMPLE                 简单查询

PRIMARY              最外层查询

SUBQUERY          映射为子查询

DERIVED              子查询

UNION                   联合

UNION RESULT    使用联合的结果

 

table:正在访问的表名

type

查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL (必须进行优化)            

全表扫描,对于数据表从头到尾找一遍

select * from tb1;

特别的:如果有limit限制,则找到之后就不在继续向下扫描

select * from tb1 where email = 'seven@live.com'

select * from tb1 where email = 'seven@live.com' limit 1;

虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。

 

INDEX           

全索引扫描,对索引从头到尾找一遍

select nid from tb1;

 

RANGE          

对索引列进行范围查找

select *  from tb1 where name < 'zhagsan';

between and

in

>   >=  <   <=  操作

注意:!= 和 > 符号

 

INDEX_MERGE     

合并索引,使用多个单列索引搜索

select *  from tb1 where name = zhangsan' or nid in (11,22,33);

 

REF            

根据索引查找一个或多个值

select *  from tb1 where name = 'seven';

 

EQ_REF          

连接时使用primary key 或 unique类型

select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

 

CONST           

常量

表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。

select nid from tb1 where nid = 2 ;

 

SYSTEM          

系统

表仅有一行(=系统表)。这是const联接类型的一个特例。

select * from (select nid from tb1 where nid = 1) as A;

 

possible_keys:可能使用的索引

key :真实使用的

key_len:MySQL中使用索引字节长度

rows

mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

 

慢日志查询

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

 

MySQL 慢查询的相关参数解释

slow_query_log    :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries  :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,
这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。
日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需<br>要能够获得更高的系统性能,那么建议优先记录到文件。

 

开启慢查询

找到 MySQL 的配置文件 ,my.cnf (Windows 为 my.ini ),在 [mysqld]下增加下面几行:

slow_query_log = 1                          
long_query_time = 2                           
slow_query_log_file = /usr/slow.log       
log_queries_not_using_indexes = 1

重新启动MySQL服务

 

MySQL 配置文件的位置

Windows:Windows 的配置文件为 my.ini,一般在 MySQL 的安装目录下或者 c:\Windows 下。

Linux:Linux 的配置文件为 my.cnf ,一般在 /etc 下。

© 著作权归作者所有

共有 人打赏支持
stars永恒
粉丝 7
博文 149
码字总数 159557
作品 0
大兴
后端工程师
rpm安装mysql时候的出现的一些问题

今天在测试服务器上安装mysql服务时,下载完mysql安装包后rpm -ivh MySQL-server-5.6.10-1.el6.x86_64.rpm后报错如下:Preparing... ########################################### [100%]fil......

吴朝辉
2013/03/14
0
0
一台服务器上启动两个mysql实例

操作环境:centos6.8 有时由于服务器硬件资源紧张,而又需要新增mysql服务。这时我们可以采取在一台服务器上部署两个mysql实例,来解决。 1、创建新的mysql实例数据存储目录: mkdir -p /da...

goldfishe
2017/12/16
0
0
Solaris 10 下安装 MySQL 5.1.x

Solaris版本:Sun OS 5.11 snv_50 64bit MySQL版本:5.1.37 1. 下载MySQL 下载页面:http://dev.mysql.com/downloads/mysql/5.1.html#solaris-pgadd 选择对应的Solaris版本,我的是 Solaris ......

范堡
2009/08/06
1K
1
MySql5.7.12_安装配置

[TOC] MySql5.7.12_安装配置 linux安装 添加mysql的用户和组 解压安装 其他机器可访问 创建快捷方式

sand_ant
2016/06/21
16
0
mysql C语言API接口及实例

Mysql数据库动态库: libmysql.dll libmysql.lib mysql.h WinSock2.h Mysql API数据结构 (mysql.h) MYSQL:连接数据库前,必须先创建MYSQL变量,此变量在很多Mysql API函数会用到。它包含了一...

技术小阿哥
2017/11/12
0
0
mysql 数据库的安装之 二 二进制安装

#二进制安装mysql,直接解压初始化数据库就可以了 1.创建用户和组 [root@Lnmp tools]#useradd mysql -s /sbin/nologin/ -M 2.解压安装包 [root@Lnmp tools]#tar xf mysql-5.5.32-linux2.6-x86...

ahtornado
2017/01/12
0
0
mysql c3p0 exception

c3p0 exception 有人遇到过这样的问题么,从show processlist ,可以看出,其中有个线程一直未使用。 但是过了很长一段时间之后(也就是8小时),客户端数据偶然正常,偶然异常。应该是有其中一个...

兴哥
2013/09/20
342
0
mysql-5.7.18版本(二进制包安装)-自定义安装路径

mysql-5.7.18版本(二进制包安装)-自定义安装路径 安装路径:/application/mysql-5.7.18 1.前期准备 mysql依赖 libaio yum install -y libaio 创建用户mysql,以该用户的身份执行mysql use...

CzlunSu
2017/07/10
0
0
mysql5.6.40升级到mysql8.0.11 的步骤

环境: 操作系统: rhel7.5 目标: mysql 5.6.22升级到mysql 8.0.11 mysql5.6.40升级到mysql8.0.11,不能直接升级,如果直接升级8.0.11,启动会报以下不支持redo log format 错误: 2018-07-...

tututu_jiang
07/10
0
0
MySQL数据库学习之路

MySQL学习之路 目录 MySQL学习之路【第一篇】:MySQL单双实例安装 MySQL学习之路【第二篇】:MySQL登陆关闭、密码修改破解 MySQL学习之路【第三篇】:MySQL增删改查、用户权授、系统函数、字...

yht_1990
2016/07/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

arts-week1

Algorithm 594. Longest Harmonious Subsequence - LeetCode 274. H-Index - LeetCode 219. Contains Duplicate II - LeetCode 217. Contains Duplicate - LeetCode 438. Find All Anagrams ......

yysue
19分钟前
0
0
NNS拍卖合约

前言 关于NNS的介绍,这里就不多做描述,相关的信息可以查看NNS的白皮书http://doc.neons.name/zh_CN/latest/nns_background.html。 首先nns中使用的竞价货币是sgas,关于sgas介绍可以戳htt...

红烧飞鱼
52分钟前
0
0
Java IO类库之管道流PipeInputStream与PipeOutputStream

一、java管道流介绍 在java多线程通信中管道通信是一种重要的通信方式,在java中我们通过配套使用管道输出流PipedOutputStream和管道输入流PipedInputStream完成线程间通信。多线程管道通信的...

老韭菜
今天
0
0
用Python绘制红楼梦词云图,竟然发现了这个!

Python在数据分析中越来越受欢迎,已经达到了统计学家对R的喜爱程度,Python的拥护者们当然不会落后于R,开发了一个个好玩的数据分析工具,下面我们来看看如何使用Python,来读红楼梦,绘制小...

猫咪编程
今天
1
0
Java中 发出请求获取别人的数据(阿里云 查询IP归属地)

1.效果 调用阿里云的接口 去定位IP地址 2. 代码 /** * 1. Java中远程调用方法 * http://localhost:8080/mavenssm20180519/invokingUrl.action * @Title: invokingUrl * @Description: * @ret......

Lucky_Me
今天
1
0
protobuf学习笔记

相关文档 Protocol buffers(protobuf)入门简介及性能分析 Protobuf学习 - 入门

OSC_fly
昨天
0
0
Mybaties入门介绍

Mybaties和Hibernate是我们在Java开发中应用的比较多的两个ORM框架。当然,目前Mybaties正在慢慢取代Hibernate,这是因为相比较Hibernate而言Mybaties性能更好,响应更快,更加灵活。我们在开...

王子城
昨天
2
0
编程学习笔记之python深入之装饰器案例及说明文档[图]

编程学习笔记之python深入之装饰器案例及说明文档[图] 装饰器即在不对一个函数体进行任何修改,以及不改变整体的原本意思的情况下,增加函数功能的新函数,因为这个新函数对旧函数进行了装饰...

原创小博客
昨天
1
0
流利阅读笔记33-20180722待学习

黑暗中的生物:利用奇技淫巧快活生存 Daniel 2018-07-22 1.今日导读 如果让你在伸手不见五指的黑暗当中生存,你能熬过几天呢?而大千世界,无奇不有。在很多你不知道的角落,有些生物在完全黑...

aibinxiao
昨天
6
0
Hystrix降级逻辑中如何获取触发的异常

通过之前Spring Cloud系列教程中的《Spring Cloud构建微服务架构:服务容错保护(Hystrix服务降级)》一文,我们已经知道如何通过Hystrix来保护自己的服务不被外部依赖方拖垮的情况。但是实际...

程序猿DD
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部