手记系列之四 ----- 关于使用MySql的经验

前言

本篇文章主要介绍的关于本人在使用MySql记录笔记的一些使用方法和经验，温馨提示，本文有点长，约1.5w字，几十张图片，建议收藏查看。

一、MySql安装

下载地址:https://dev.mysql.com/downloads/
在安装MySql之前，查看是否以及安装过MySql，如果已经安装，但是不符合要求的话就卸载。
如果是windows安装的话，下载windows的安装包，一路next下去，设置好账号和密码就行了。
1，查找以前是否装有mysql
先输入:

1. rpm -qa|grep -i mysql

复制代码

查看是否安装了mysql

2，停止mysql服务、删除之前安装的mysql
输入:

1. ps -ef|grep mysql

复制代码

删除命令
输入:

1. rpm -e –nodeps 包名

复制代码

如果提示依赖包错误，则使用以下命令尝试

1. rpm -ev 包名 --nodeps

复制代码

如果提示错误：error: %preun(xxxxxx) scriptlet failed, exit status 1
则用以下命令尝试：

1. rpm -e --noscripts 包名

复制代码

3、查找并删除mysql目录
查找结果如下：

1. find / -name mysql

复制代码

删除对应的mysql目录
具体的步骤如图：查找目录并删除

注意：卸载后/etc/my.cnf不会删除，需要进行手工删除
4、再次查找机器是否安装mysql

1. rpm -qa|grep -i mysql

复制代码

Mysql有两种安装模式，可自行选择。
1.1 yum安装

首先查看mysql 是否已经安装
输入：

1. rpm -qa | grep mysql   

复制代码

如果已经安装，想删除的话
输入:
普通删除命令:

1. rpm -e mysql

复制代码

强力删除命令:

1. rpm -e --nodeps mysql   

复制代码

依赖文件也会删除
安装mysql
输入:

1. yum list mysql-server  

复制代码

如果没有，则通过wget命令下载该包
输入:

1. wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm   

复制代码

下载成功之后，再输入命令安装

1. yum install mysql-server

复制代码

在安装过程中遇到选择输入y就行了


安装成功后，输入 service mysqld start  启动服务
输入:
mysqladmin -u root -p password '123456'
来设置密码       
输入之后直接回车(默认是没有密码的)
然后再输入
mysql -u root -p

通过授权法更改远程连接权限
输入: grant all privileges on . to 'root'@'%' identified by '123456';
注:第一个’root’是用户名,第二个’%’是所有的ip都可以远程访问,第三个’123456’表示 用户密码  如果不常用 就关闭掉
输入:flush privileges;  //刷新
在防火墙关闭之后，使用SQLYog之类的工具测试是否能正确连接

1.2 编译包安装

将下载好的mysql安装包上传到linux服务器
解压mysql解压包，并移动到/usr/local目录下，重命名为mysql。
命令:

1. tar -xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
2. mv mysql-5.6.21-linux-glibc2.5-x86_64 /usr/local
3. cd /usr/local
4. mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql

复制代码

注: mysql默认的路径是就是/usr/local/mysql ，如果安装的地方更改，需要更改相应的配置文件。
安装mysql

切换到mysql的目录 /usr/local/mysql
输入：

1. ./scripts/mysql_install_db --user=mysql

复制代码

成功安装mysql之后，输入
service mysql start  或  /etc/init.d/mysql start

查看是否启动成功
输入:

1. ps -ef|grep mysql

复制代码

切换到 /usr/local/mysql/bin 目录下
设置密码
mysqladmin -u root password '123456'入mysql
输入:

1. mysql -u root -p

复制代码

设置远程连接权限
输入:

1. grant all privileges on *.* to 'root'@'%' identified by '123456';

复制代码

然后输入:

1. flush privileges;

复制代码

说明: 第一个’root’是用户名,第二个’%’是所有的ip都可以远程访问,第三个’123456’表示用户密码  如果不常用就关闭掉。
使用本地连接工具连接测试

二、MySql排查问题的常用命令

1.查看mysql连接数

SHOW FULL PROCESSLIST;

2.查看mysql的配置

1. show variables like "%innodb%";

复制代码

3.查看mysql事件是否开启

1. show variables like 'event_scheduler';

复制代码

4.查看mysql锁的状态

是否锁表:

1. SHOW OPEN TABLES WHERE In_use > 0;

复制代码

1. SHOW INNODB STATUS/G;

复制代码

5.查看mysql data的存放位置

1. SHOW VARIABLES LIKE '%datadir%'

复制代码

6.查看mysql 的超时时间设置

1. show variables like '%timeout%';

复制代码

7.查看mysql 的日志存放

1. show variables like 'datadir';

复制代码

mysql的日志分类
错误日志：   -log-err
查询日志：   -log
慢查询日志:     -log-slow-queries
更新日志:     -log-update
二进制日志：   -log-bin
8.开启Mysql的操作日志记录

输入:

1.   SHOW VARIABLES LIKE 'log_bin'

复制代码

开启错误日志:
在my.cnf 或my.ini 中 添加 log-error=/home/mysql/logs/log-error.txt
开启查询日志:
在my.cnf 或my.ini 中 添加 log=/home/mysql/logs/mysql_log.txt
9.查看Mysql缓冲池大小

1. SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

复制代码

10.当前目录赋予mysql权限

1. chown -R mysql:mysql ./

复制代码

11.查看mysql脏页比例

1. USE performance_schema;
2. SELECT VARIABLE_VALUE INTO @a FROM global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
3. SELECT VARIABLE_VALUE INTO @b FROM global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
4. SELECT @a/@b;

复制代码

要合理的设置 innodb_io_capacity 的值，平时要多关注脏页比例，不让其接近 75%.
可以通过 innodb_flush_neighbors 来控制该行为，值为 1 打开上述机制，为 0 则关闭。
对于机械硬盘来说，是可以减少很多随机 IO ，因为机械硬盘 IOPS 一般就几百，减少随机 IO 就意味着性能提升。
但如果用 SSD 这类 IOPS 较高的设备，IOPS 往往不是瓶颈，关闭就好，减少 SQL 语句的响应时间。
在 8.0 中，已经默认是 0 了.
12.查看慢查询以及开启

1. SHOW VARIABLES LIKE 'slow_query%';
2. SET GLOBAL slow_query_log=ON;
3. set global long_query_time=1;

复制代码

永久设置

1. [mysqld]
2. slow_query_log = ON
3. slow_query_log_file = /usr/local/mysql/data/slow.log
4. long_query_time = 1

复制代码

long_query_time表示查询超过多少秒就记录
13. 开启mysql查询日志

开启会降低性能
查看日志配置

1. SHOW VARIABLES LIKE '%general_log%';

复制代码

查询文件输出格式

1. SHOW VARIABLES LIKE 'log_output';

复制代码

开启MySQL查询日志

1. SET  GLOBAL general_log = ON;

复制代码

关闭MySQL查询日志

1. SET  GLOBAL general_log = OFF;

复制代码

设置日志输出方式为表

1. SET GLOBAL log_output='table';

复制代码

查询日志信息

1. select * from mysql.general_log;

复制代码

14.数据库慢或数据库连接过多的命令排查

按客户端 IP 分组，看哪个客户端的链接数最多

SELECT client_ip,COUNT(client_ip) AS client_num FROM (SELECT
SUBSTRING_INDEX(HOST,':' ,1) AS client_ip FROM PROCESSLIST ) AS
connect_info GROUP BY client_ip ORDER BY client_num DESC;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

SELECT * FROM information_schema.processlist WHERE Command != 'Sleep'
ORDER BY TIME DESC;

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

SELECT CONCAT('kill ', id, ';') FROM information_schema.processlist
WHERE Command != 'Sleep' AND TIME > 300 ORDER BY TIME DESC;

批量kill的语句

1. select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id into outfile '/tmp/kill.txt';

复制代码

三、MySql的优化建议

1.建表建议

1.1 选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：
1.越小的数据类型通常更好：
越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
2.简单的数据类型更好：
整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
3.尽量避免NULL：
应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
4.一个表的索引最好不要超过6个:
索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引
1.2 选择主键类型

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

• 整型：
  通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。
  
• 字符串：
  尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。
  

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY（主键索引）  ALTER TABLE table_name ADD PRIMARY KEY ( col )
UNIQUE(唯一索引)     ALTER TABLE table_name ADD UNIQUE (col)
INDEX(普通索引)     ALTER TABLE table_name ADD INDEX index_name (col)
FULLTEXT(全文索引)      ALTER TABLE table_name ADD FULLTEXT ( col )
组合索引   ALTER TABLE table_name ADD INDEX index_name (col1, col2, col3 )
Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
联合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。
联合索引的好处：覆盖索引，这一点是最重要的，众所周知非主键索引会先查到主键索引的值再从主键索引上拿到想要的值。但是覆盖索引可以直接在非主键索引上拿到相应的值，减少一次查询。
复合索引和普通索引都是用一棵B+树表示的。
如果是单列，就按这列key数据进行排序。
如果是多列，就按多列数据排序，
例如有（1,1）（1,4）（2,2）(1,3) （2,1）（1,2）(2,3) （2,4）
那在索引中的叶子节点的数据顺序就是（1,1）（1,2）(1,3) (1,4)（2,1)（2,2）(2,3) (2,4)
这也是为什么查询复合索引的前缀是可以用到索引的原因

1.3 mysql索引方法hash和Btree区别


Hash仅支持=、>、>=、=,