您的位置：首页 > 博客中心 > 数据库 >

mysql基础和数据库的优化

时间：2022-03-14 03:44

Mysql基础

Mysql介绍【知道】

关系型数据库

登录mysql mysql –u root –p【掌握】

修改密码 mysqladmin –u root –p[oldpassword] password newpassword

使用客户端数据库管理软件如果无法连接就执行

GRANT ALL PRIVILEGES ON *.* TO ‘用户名’@’%’ IDENTIFIED BY ‘密码’ WITH GRANT OPTION;

或者自己博客

SQL语言

DCL

（data control language）：数据控制语言，用于控制不同数据对象访问级别的语句。定义了数据库、表、列、用户的访问权限和安全级别（grant、revoke）【了解】

grant

语法：grant 权限 on 数据库对象 to 用户;

Grant select on testdb.* to ‘user_1’@’%’;

revoke

revoke select on testdb.* from ‘user_1’@’%’;

DDL

(data definition languages)：数据定义语言，数据库、表、索引等对象的定义（create、drop、alter、truncate、rename）【掌握】

建库

create database dbname default charset=utf8；

删库

drop database dbname

建表

1、 create table table_name (col1 type1[not null] [primary key],col2 type2[not null],…);

2、 create table table_name1 like table_name2;创建一个与当前某各表结构相似的空表。

3、数值类型、日期时间、字符串

4、常用函数（数值函数、字符串函数、日期时间函数、其他函数）

看表结构

desc table_name;

删表

drop table table_name;

修改表

alter table table_name

MODIFY col_name column_definition –修改字段类型

CHANGE old_col_name new_col_name definition –修改字段名称

ADD col_name column_definition –添加字段

DROP col_name;--删除字段

DML【重点】

(data manipulation language)：数据操作语言，用于增删改查记录，并检查数据完整性，（insert、delete、update、select）

增加

insert into table1(field1,field2) values(value1,value2);

删除

delete from table1 where 范围;

修改

update table1 set field1=value1 where 范围;

查看

select * from table1 where 范围;

排序

select * from table1 order by field1,field2 [desc];

总数

Select count(1) as totalcount from table1;

求和

Select sum(field1) as sumvalue from table1;

平均

Select avg(field1) as avgvalue from table1;

最大

Select max(field1) as maxvalue from table1;

最小

Select min(field1) as minvalue from table1;

表连接

内连接

内连接相对其他链接效率高。

Select col1,col2,….from table1,table2 where table1.col3=table2.col2;

外连接

外链接分为左外连接和右外连接。

左外连接：select col1,col2,….from table1 left join table2 on table1.col2=table2.col3;

右外连接：select col1,col2,….from table1 right join table2 on table1.col2=table2.col3;

子查询

Select * from table1 where col[in][=](select col2 from table2 where 范围);

逻辑备份、还原【掌握】

备份

mysqldump –u root –p 123123 ultrax > 20141210.sql;

# ultrax 备份的数据库名，20141210.sql备份的sql文件名

#视图存储过程、函数都要备份

还原

mysql –u root –p 123123 ultrax< 20141210.sql;

#命令不同、符号不同

存储引擎

MyISAM

【掌握】

5.5版本以前默认的存储引擎。

不支持事务、外键；适用于对事务完整性没有要求、以select、insert为主的应用。

Innodb

【掌握】

提供了具有提交、回滚、崩溃修复能力的事务安全。

对此MyISAM写的处理效率会差一些，并且会占用更多磁盘空间以保留数据和索引。

在并发条件下要求数据的一致性，数据准确性要求高的。

Memory

【了解】

使用存在内存中的内容来创建表。

每个memory表只实际对应一个磁盘文件。此类型的表访问非常快，默认使用了HASH索引。但是一旦服务关闭，表中数据就会丢失。

对表的大小有限制，数据不能持久。通常用于更新不太频繁的小表。

视图【了解】

视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。在数据库中不占用存储空间。

视图的好处

1、可以限制用户数据，聚焦特定数据

2、可以简化查询操作。

3、可以保护基本数据的安全性。

4、可以合并分离数据，建立分区视图。

创建视图

三种方法

1、Create view person_v as select * from table_name;

2、create view person_v as select id,name,age from table_name;

3、create view person_v[vid,vname,vage] as select * from table_name;

查看视图

Show create view view_name;

事务【了解】

特性acid

原子性atomic，不能嵌套

一致性consistent

隔离性isolated

持续性druable

实现

begin开始一个事务

Commit提交事务（只有提交之后才生效）

Rollback回滚事务

注意

1. Mysql中只有innodb和dbd支持事务

2. ddl和dcl不能回滚。

3. 隐式提交ddl：create table、create database、lock tables、alter function、start transaction等

存储过程

存储过程是事先经过编译并存储在数据库中一段sql语句的集合。

存储过程中使用参数的类型：in out inout

创建存储过程

delimiter $$

create procedure 存储过程名(参数列表)

begin

sql代码块

end

删除存储过程

drop procedure if exists 存储过程名;

注意事项：

1、创建存储过程必须要有参数列表项，若没有参数使用空参“()”..

2、删除存储过程时存储过程名后面没有“()”.

3、定义变量语句： set @变量名=初始值。

调用存储过程

call 存储过程名([参数])；

实例

取bbs中指定日期的帖子，返回帖子数

参数类型【掌握】

In

存储过程调用者，在调用存储过程时传入的参数。

Out

存储过程内部给调用者返回的参数。

Inout

调用者既能给存储过程传入参数，又能从存储过程得到返回参数。

流程控制

if

IF(exPR1,expr2,expr3) #如果exPR1 为true，则返回exPR2，否则返回exPR3

while

WHILE expression DO

Statements

END WHILE;

索引【掌握】（重点）

索引是提高select操作性能的最佳方法。所有列类型都可以使用索引。每个表至少支持16个索引。

注意：只对select有作用。

设计原则

索引列：

1、最适合使用索引的列是出现在where子句中的列，或者连接子句中指定的列。

2、使用唯一索引。在唯一列上添加索引。

3、使用短索引。

4、不能滥用索引。索引不能添加太多。在修改表数据的索引需要更新，有的时候需要重构，比较耗时。

索引应该建立在哪些字段

1、建立在主键上。

2、经常用在连接列上。

3、经常需要排序的列上。

4、使用在where子句的列上。

5、在经常需要条件搜索的列上。

索引不应该建立在哪些字段上

1、很少使用的列。

2、有很少不同数值的列。

3、当列的字段类型为text 、image、bit时，不添加索引。

4、当修改远大于检索时。（涉及到索引更新或重构）。

索引失效

1、隐式转化导致索引失效。

2、 <>、not in、not exists、!=、like 导致索引失效。

3、对索引列进行运算(+、-、*、\、!=)，导致索引失效。

例如：select * from table1 where age+3=10;

4、不要在sql中使用双引号。

5、不要将空值与运算符进行比较。应使用 is null 、is not null

建立不同类型的索引

主键索引

当给一张表的某列创建主键时，就会给该列添加主键索引。

Alter table table_name ADD PRIMARY KEY (col_list);

不能使用create index 的方式为表添加主键索引。

索引的列是唯一的而且不能存在null值。

普通索引

两种创建方式：

直接创建索引：

Create index index_name ON table_name(col_list);

通过修改表，为表添加索引：

Alter table table_name ADD index index_name (col_list);

ALTER TABLE article ADD INDEX X(category_id,comments,views);

唯一索引

创建方法：

1.create unique index index_name on table_name(col_list);

2.alter table_name add UNIQUE index_name(col_list);

唯一索引的列的值必须是唯一的，可以为空值。

删除索引

Drop index 索引名 on 表名;

DROP INDEX X ON article;

查看索引

Show index from 表名;

SHOW INDEX FROM article;

锁

分类

分类	特点	引擎	备注
表级锁	开销小、锁定力度大发生冲突概率高，不会出现死锁。	Mysam memory
行级锁	开销大、锁定力度小发生冲突概率高，会出现死锁。	innodb	常说的死锁
页面锁	开销介于表级锁和行级锁之间，会出现死锁。	bdb	没人用

看锁竞争状态【掌握】

Show status like ‘table%’；

#table_locks_waited和 table_locks_immediate状态变量来分析数据库的所竞争，table_locks_waited越高，所竞争越严重。

同一个查询器session锁表lock table 表名 write；

另一个查询器session就不能使用；

第一个表释放unlock tables；后，另一个查询器可查询。

Show status like ‘innodb_row_lock%’;

#获取innodb行锁争用情况;

#重点看innodb_row_lock_current_waits 、innodb_row_lock_time_avg

分析优化sql的思路【重点】

Mysql配置文件 my.cnf

整体思路【掌握】

（根据现象确定抓取范围---》定位排查缩小范围---》对定位出来的sql进行分析，确定出问题----》给出建议）

每步的做法shell脚本（查看mysql服务器状态）、慢查询、

慢查询（重点）（抓取范围和定位）、explain分析

抓取

开启慢查询

Mysql的配置文件my.cnf

1.Vi /etc/my.cnf #在最后添加

log-slow-queries=/var/log/mysql/slowquery.log #慢查询日志写哪里、需提前建好这路径、文件

long_query_time=2#超时阀值，默认是10 s。单位为秒。

2.验证慢查询日志是否开启

show_variables like ‘%slow%’;

slow_query_log、 log_slow_queries为on，即为开启；

如未开启，在mysql 命令行中： set global log_slow_queries=ON;

定位缩小范围

慢查询日志slowquery.log

日志内容

#Time时间戳

Query_time查询时间

Lock_time锁定时间

Rows_sent行数

Rows_examined

下面是捕捉出来的语句（正常是select语句，不要加没加索引的条件，因为log会太大）

Mysqldumpslow过滤日志（定位缩小范围）

mysqldumpslow –s c –t 3 –g “left join” /var/log/mysql/slowquery.log #记录中包含left join的

# -s 表示按照何种方式排序， c （访问次数）、t（时间）、 l（查询时间）、 r（返回记录数）；ac 、at 、al 、ar，表示倒序；

# -t 表示取前面多少条记录

# -g 表示后面可以写一个正则匹配模式，不分大小写

常用的过滤

1. Mysqldumpslow –s c –t 20 /var/log/mysql/slowquery.log #访问次数最多的20个sql

2. Mysqldumpslow –s r –t 20 /var/log/mysql/slowquery.log #返回记录集最多的20个sql

3. Mysqldumpslow –t 10 –s t –g “left join” /var/log/mysql/slowquery.log #按照时间返回前10条里包含left join的sql语句

4. Mysqldumpslow –s l –t 20 /var/log/mysql/slowquery.log #查询时间最长的20个sql

案例

1.使用mysqldumpslow过滤查询日志，查看sql；

2.如果是模糊的查询条件，使用具体值替换；

3.用explain来分析sql；

（不一定是具体的sql :between N and N）定位出了问题，接下来用explain来分析

分析Explain（记重要的参数的含义，分析给出建议）

参数	说明
Id	标识
Select_type	查询的类型： Simple 普通查询 Primary 查询中若包含任何复杂的子部分，最外层被标记为primary； Subquery 在select或where中包含子查询，该子查询被标记为subquery； Derived 在from列表中包含了子查询；
Table	表名
Type	对表的访问方式，一般保证range最好ref All 遍历了全表 Index 只遍历的索引树 Range 索引范围扫描，between < > Ref 非唯一性索引扫描，返回某个单独值的所在行 Eq_ref 唯一性索引扫描，表中只有一条记录匹配，主键、唯一索引扫描 Const,system 最多只会有一条匹配的行，只需读取一次表就能取到所需结果 Null 执行时甚至不用访问表或索引
Possible_keys	可利用到的索引，查询所涉及到的字段上若存在索引就显示；（不一定用）
Key	实际使用的索引
Key_len	索引中使用的字节数
ref	哪些列或常量被用于查找索引
rows	估算找到所需记录要遍历的行数，越小越好
extra	Only index 只用索引树中就能找到； Where used 使用了where的限制； Impossible where表示用不着where（没查出啥）； using temporary 使用了临时表 using filesort表示where和order by上的索引无法兼顾，解决就要看先过滤还是先排序；

Explain局限性

1.只能解释select操作

2不包括触发器、存储过程的信息；不包括用户自定义函数对查询的影响情况；

3.不考虑cache；

4.不能显示mysql在执行查询时所作的优化工作；

5.部分统计信息是估算的，不是精确值；

案例1（（写sql 要严谨、注意类型））：

写sql时，where语句后面的mkey（varchar）=111，索引被转换了，没用上索引。导致type为all、row很大；改成=’111’，type变好了，key用的主键索引、row=1；

案例2

发现一个慢查询，查询时服务器io飙升，io占用率达到100%，执行时间长达7s。

思路典型：

1.抓到慢查询，同时监控了服务器 [ io占用率高 ]；

2.explain分析（type all；key null没用索引、row也很大、extra usingfilesort、using temporary（临时表），猜测：内存放不下、放在磁盘，和磁盘交互增加，导致了io问题；

3.解决反馈开发、 dba ：拆sql；

练习

CREATE TABLE IF NOT EXISTS article (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

author_id int(10) unsigned NOT NULL,

category_id int(10) unsigned NOT NULL,

views int(10) unsigned NOT NULL,

comments int(10) unsigned NOT NULL,

title varbinary(255) NOT NULL,

content text NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO article

(author_id, category_id, views, comments, title, content) VALUES

(1, 1, 1, 1, ‘1‘, ‘1‘),

(2, 2, 2, 2, ‘2‘, ‘2‘),

(1, 1, 3, 3, ‘3‘, ‘3‘);

EXPLAIN

SELECT author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

优化：索引---放那些字段上加呢？索引的原则给字段加索引；再继续explain看情况。调整索引。再explain，直到调到满意。

Step 1.

where条件中使用了category_id 、comments 、views三个字段，给三个字段都加上索引；

Step 2.

因为comments检索用了> <符号，导致了索引无效，去掉此字段上的索引再试试。

Show processlist（第二阶段还会讲，看连接数等）

重要的指标

Sleep	通常代表资源未释放，可能会导致too many connections连接
Waiting for net，reading from net，writing to net	偶尔出现也无妨，如大量出现，迅速检查数据库到前端的网络连接状态和流量
Locked	有更新操作锁定。通常使用innodb可以更好的减少locked状态的产生
Copy to tmp table	索引及现有结构无法涵盖查询条件，才会建立一个临时表来满足查询要求，产生巨大的io压力。很可怕的搜索语句会导致这样的情况，如果是数据分析，或周期性的数据清理任务，偶尔出现，可允许。频繁出现就要优化。通常与连表查询有关，建议逐渐习惯不使用连表查询。
Sending data	并不是发送数据，从物理磁盘获取数据的进程，如果影响的结果集较多，就要从不同磁盘碎片去抽取数据，偶尔出现可以，如有连接较多，就要考虑优化查询的索引项。
Freeding items	理论上不会出现很多，偶尔出现无妨。如大量出现，内存、磁盘可能已经出现问题，如磁盘满或损坏。
Sorting for	同sending data

看健康状态（辅助showmysql.sh）

slowmysql.sh

执行过程

1.配置my.cnf 中，最后添加：

[client]

User=root

Password=123123

2.安装bc，yum install bc

3.chmod 777 –R slowmysql.sh

4.sh slowmysql.sh

执行完成后，会显示mysql

健康状态【重点】

slow queries（慢查询）

max connections（最大连接数）

缺省最大连接数是100，调到更高只是为了出现问题时给我们更多的缓冲时间而不是任其一直处于那么高的状态。

指标：max_connections设置的最大连接数；historic max_used_connections以往达到的最大连接数

（连接数设置多少---参数优化）

temp tables（临时表的大小）

query cache【重要】

是什么

缓存sql文本和查询结果，如果运行相同的sql，服务器就直接从缓存中取到结果，而不需要再去解析和执行sql。将客户端提交给mysql的select类query请求返回结果集cache到内存中

如果表更改了【表中数据、结构的改变insert、update、delete、truncate、alter table、drop table、drop database】，那使用这个表的缓存的查询就失效，查询缓存值的相关条目被清空。

适用于

不常改变数据且有大量相同sql查询的表，设置query cache会节约很大性能。

查询必须是完全相同的才能被认为是相同的。同样的查询字符串，由于其它原因可能被认为是不同的，使用不同的数据库、不同的协议版本、不同默认字符集的的查询被认为是不同的查询，而分别缓存。【需合理开启与设置query cache】

不好的地方

1 更改后失效2.要完全相同

开启方法

1. 编辑 my.cnf，添加：

query_cache_size=268435456 #设置query cache所使用的内存大小，默认值为0。大小必须是1024的整数倍。

query_cache_type=1 #给所有查询做cache

query_cache_limit=1048576 #允许cache单条query结果集的最大容量，默认是1MB，超过此参数设置的query结果集将不会被cache。

2. 重启mysql

设置状态分析

SHOW VARIABLES LIKE ‘%query_cache%‘;

query_cache_limit ： #允许cache单条query结果集的最大容量

query_cache_size ：#query cache所使用的内存大小

query_cache_type ：#控制query cache功能的开关，可设置为0 、1、2；

0（OFF）关闭query cache，任何情况都不会使用query cache；

1 （ON）开启 query cache，当select语句中使用SQL_NO_CACHE提示后，不使用query chache；

2（DEMAND）开启query cache，仅当select语句中使用了SQL_CACHE提示后，才使用query chache。最灵活。

运行状态分析（运行一段时间，看是否要打开或调整）

Show status like ‘%qcache%’；

Qcache_free_blocks ：数目大说明可能有碎片

Qcache_free_memory：缓存中空闲内存

Qcache_hits：每次查询在缓存中命中时就增加

Qcache_inserts：每次插入一个查询就增加

Qcache_lowmem_prunes：缓存出现内存不足且必须要清理以便为更多查询提供空间的次数。这个数最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉你属于哪种情况）

Qcache_total_blocks：缓存中块的总数量

根据运行状态的参数，统计概率

命中率= Qcache_hits/(Qcache_hits+Qcache_inserts)

碎片率=Qcache_free_blocks/Qcache_total_blocks #如超过20%，可用flush query cache整理缓存碎片

利用率=(query_cache_size – Qcache_free_memory)/query_cache_size #低于25%说明query_cache_size设置的过大，可适当减小；高于80%，而且Qcache_lowmem_prunes>50，说明query_cache_size可能有点小，要不就是碎片太多。

数据库参数优化【记住完事】

两个命令看各种参数

Show global status；mysql服务器运行状态

Show variables；mysql服务器配置信息

查看日志状态（了解就ok）

日志满了-磁盘满-并发上来—磁盘不够

SHOW VARIABLES LIKE ‘log_%‘; #是否启用了日志

Show master status;日志状态

Show master logs；日志信息

查看最大连接数【1重点】

问题：mysql：error 1040：too many connections

解决：访问量高—增加从服务器；配置中的最大连接数。

查看连接数

#允许的最大连接数

SHOW VARIABLES LIKE ‘max_connections‘;

#以往达到的最大连接数

SHOW GLOBAL STATUS LIKE ‘Max_used_connections‘;

比较理想的设置：Max_used_connections/max_connections*100% 大概85%

索引缓存

Key_buffer_size 是对MyISAM引擎的表影响大的参数。

#查看key_buffer_size参数设置的大小，单位b

SHOW VARIABLES LIKE ‘key_buffer_size‘;

SHOW GLOBAL STATUS LIKE ‘key_read%‘;

#计算索引未命中缓存的概率

Key_cache_miss_rate=key_reads/key_read_requests *100%

Key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的话，而key_buffer_size分配过多，可以适当减少。

打开表的数量【3掌握】

SHOW VARIABLES LIKE ‘table_open_cache‘;

SHOW GLOBAL STATUS LIKE ‘open%tables%‘

#Open_tables表示打开表的数量；opened_tables打开过的表数量（opened_tables大说明配置的table_open_cache可能太小）

比较合适的值：Open_tables/Opened_tables*100%>=85%

Open_tables/table_open_cache*100%<=95%（）

存储空间比例的计算，是不是过分使用，是不是很少使用但也占了很大存储空间

表扫描【4掌握—全表扫描】

Show global status like ‘Handler_read_rnd_next’;

SHOW GLOBAL STATUS LIKE ‘com_select‘;

表扫描率：Handler_reader_rnd_next/Com_select’

如果表扫描超过4000，说明进行了太多表扫描，有可能索引没建好[根本原因]；临时的解决增加read_buffer_size值(Show variables like ‘read_buffer_size’；)会有效，最好不超过8MB.（可以容纳内容进来，方便提取）

SHOW VARIABLES LIKE‘read_rnd_buffer_size‘;

#进行随机扫描的时候使用buffer，但是太随机优化的东西都用不上，性能会不太好，遇到的比较少）read_rnd_buffer_size值适当的调大，对提高order by 操作的性能有一定效果

日志相关（对专属服务器要设置）

SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit‘;

0 : log thread每隔1秒就会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步flush操作，保证数据确实写入到磁盘上。比较看重性能，高并发写的日志服务器，设为0.

1: 一般为1。每次事务结束都会出发log thread将log buffer中的数据写入文件并通知文件系统同步文件。比较合理，性能稍差。

2 : log thread会在每次事务结束时，将数据写入事务日志，但写入仅仅调用了文件系统的文件写入操作。文件系统什么时间将缓存中的这些数据同步到物理磁盘，log thread就不知道了。对数据一致性、完整性要求不高时，设为2.

#每个日志文件大小

SHOW VARIABLES LIKE ‘innodb_log_file_size‘;

一个日志组中每个日志文件的大小。在高写入负载尤其是大数据集的情况下很重要。这个值越大性能相对越高，但副作用是恢复时间加大，扫描恢复时间长。【可恢复的多，所以用时长】

一般64M—512M，具体取决于服务器的空间。

超时时间【2掌握】

SHOW VARIABLES LIKE ‘wait_timeout‘;

SHOW VARIABLES LIKE ‘interactive_timeout‘;

# wait_timeout 取值范围1-2147483（windows），1-31536000（linux）；

#interactive_timeout取值随wait_timeout变动；

#默认都是28800s（8小时）、

#两个参数要同样的值、同时设置，timeout才会生效。

架构优化

1.主从复制【掌握】

原理

主数据库负责写（一个主库集中写，保证了数据一致）、从库读（从库横向扩展，分流了压力）

实现

1.master打开二进制日志，将改变 记录到 二进制日志

2.slave将master的二进制日志拷贝到它的中继日志（relay log）。（slave开始一个工作线程—io线程，io线程在master上打开一个普通的连接，然后开始binlog dump process。从master的二进制日志中读取事件，如已经跟上master，会睡眠并等待maste产生新时间，io线程将这些事件写入中继日志）

3.slave重做中继日志中的事件。（sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，和master中数据一致。只要该进程与io线程保持一致，中继日志通常位于os缓存中，所以中继日志开销小）