MySQL性能优化相关知识

标签： MySQL

---

MySQL数据库优化

首先我们需要知道MySQL的基本操作。

1. MySQL基础操作

1.1 mysql表复制

复制表结构+复制表数据：

mysql>create table t3 like t1;

学习表结构，主键、自增等等

mysql>insert into t3 select*from t1;

复制表数据，必须要结构完全一样，不然就必须将要复制的列一一列出来。

1.2 mysql索引

1、ALTER TABLE用来创建普通索引、UNIQUE索引或者PRIMARY KEY索引(column_list列名)
ALTER TABLE table_name ADD INDEX index_name(column_list);
ALTER TABLE table_name ADD UNIQUE(index_name)(column_list);
ALTER TABLE table_name ADD PRIMARY KEY(column_list);

例子：

alter table t1 add index in_name(name);alter table t1 add primary key(id);(//在id这一列创建主键索引)

2、alter table table drop删除索引
例子：

alter table t1 drop index in_name;alter table t1 drop primary key;(//删除主键索引)

3、create/drop index 用来创建和删除索引，但是需注意不能针对主键索引
CREATE/DROP INDEX index_name ON table_name(column_list);
CREATE UNIQUE INDEX index_name ON table_name(column_list);

4、show index from t1查看索引

1.3 mysql视图

视图是通过一个条件从一个完整表里面拿出一个符合条件的另一张表，或者称作中间表，它是存在于另外一张表里面，通过一个条件来达成的协议。
创建视图：

mysql>create view v_t1 as select*from t1 where id>4 and id<11;

视图可以时时刻刻的以一定条件跟踪原来的表，方便我们的查看，可以充当我们频繁查询的一个中间表。如果原表被删除的话，得到的视图就会发生错误，如果原表恢复之后，视图也会随之恢复正常。
删除视图：

mysql>drop view v_t1;

1.4 mysql内置函数

字符串函数：concat（连接）、lcase（小写）、ucase（大写）…
数学函数：bin（十进制转二进制）、ceiling（向上取整）floor（向上取整）…
日期函数：curdate（返回当前日期）、now（返回当前的日期和时间）、unix_timestamp（时间戳）
还有很多不常用的函数，就不一一列举了

1.5 mysql预处理语句

mysql>prepare stmt1 from "select *from t1 where id>?;"mysql>set @i=1;//设置变量mysql>execute stmt1 using @i; //执行预处理语句，用@i代替预处理语句中的？

删除预处理

mysql>drop prepare stmt1;

预处理可以简化需要多次执行的sql语句，不用每次执行都去连接mysql

1.6 mysql事务处理

mysql>set autocommit=0;（//首先我们要关闭自动提交功能，就是不自动提交）mysql>delete from t1 where id=11;(//从t1中删除一条记录)mysql>savepoint p1;(//做一个p1还原点)mysql>delete from t1 where id=10;(//再次从表中删除一条记录)mysql>savepoint p2;(//再次做一个p2还原点）mysql>rollback to p1;（//回滚到p1）mysql>commit（//提交数据，没有提交之前数据才能回滚）mysql>rollback;（//回滚到最原始的还原点）

注意，有的表引擎不支持事务机制，比如MyISAM，而innodb表引擎就支持事务机制

1.7 mysql存储

存储相当于一个函数，一块代码段，它批量执行数据库操作

mysql>\d // （把语句定界符改成//，不然遇到一个分号就直接执行了）mysql>create procedure p1()    ->begin    ->set @i=3;    ->while @i<=100 do    ->insert into t1(name) values(concat("user"),@i);    ->set @i=@i+1;    ->end while;（结束这个while循环）    ->end//（遇到//，结束语句）往t1表里面插入了97个数据，形式为user1mysql>\d ;（将语句定界符改回分号）mysql>show procedure status;（查看存储状态）mysql>call p1;（执行语句）

1.8 mysql触发器

当一个表需要在另一个表执行操作的时候执行相同的操作，这个时候 就需要设置一个触发器。如当t1表删除的时候t2表也删除相应数据：

mysql>\d //mysql>create trigger tg1 before delete on t1 for each row    ->begin    ->delete from t2 where id=old.id;(执行删除操作，id为t1表删除的id)    ->end//

1.9 重排auto_increment值

很多时候可以发现一个自增的id即使你把所有数据删除之后也不会重新排数，这个时候需要对它进行重排：
当我们清空表的时候，不能用：

delete from tablename；

而是使用：

truncate table tablename；

就可以将自增的id恢复成1了，并且它的执行效率要远远高于delete

或者当我们delete所有内容之后通过alter命令修改表也行：

alter table tablename auto_increment=1;

2. 常用的sql技巧

---

2.1 正则表达式的使用

匹配以linux开头的数据

mysql>select"linux is very good" regexp"^linux";

2.2 用rand（）提取随机行

将它和order by一起使用能够把数据随机排序

select *from t1 order by rand（）limit 3；//随机拿出三条数据

2.3 利用with rollup

使用group by 的with rollup可以检索出更多的分组聚合信息

2.4使用？来获取帮助

例如：？%可以获得所有的mysql命令。
？contents包含mysql所有的帮助信息
在sql语句结尾使用\G可以调换行和列

3. sql语句优化

---

3.1 优化sql语句的一般步骤

3.1.1. 通过show status了解各种sql的执行频率

1、mysql>show [session/global]status;
sission（默认的）表示当前连接、global表示从数据库启动到现在
一般我们查询的增删改查：

mysql>show status like"com_insert%";mysql>show status like"com_select%";...

2、只针对innodb引擎的查询

mysql>show status like"innodb_rows%";

注意，innodb当中查询出来的是操作改动的数据行数而不是操作次数。
3、其他：
connections连接mysql的数量，无论登录是否成功都会记录下来

mysql>show status like"connections"

uptime:服务器已经工作的秒数
slow_queries：慢查询次数

3.1.2. 通过慢查询日志找到执行效率较低的sql语句
通过：explain select *from table where id=1000;
desc select *from table where id=1000;解析语句可能出现的问题，重点看影响行数rows。

3.2 索引问题

索引是数据库中最常见的的手段之一，它可以帮助用户解决大部分sql性能问题。
3.2.1. 索引的存储分类
MyISAM存储引擎的表的数据和索引还有表结构是自动分开存储的，各自是一个独立的文件；
Innodb存储引擎是存储在同一个表空间里面，但可以有多个文件组成；
MySQL目前不支持函数索引，但能对列的前面某一个部分进行索引，例如对name字段的前四个字符进行索引，这样可以大大缩小索引文件的大小。
3.2.2. MySQL如何使用索引
索引用于快速找出某个列中有一特定值的行，对相关列使用索引是提高select操作性能的最佳途径
注意，①使用like查询的时候，如果要使用索引，则%不能再第一个字符，如下就没有用上索引：

mysql>explain select *from company where name like"%3"//查询了所有记录

②如果使用了and或者or条件查询的时候，双方都需要有索引，不然就不会使用索引。

mysql>explain select *from t1 where id=1 and name="user5"

③如果查询的条件类型不符合，比如string的数据查询的条件是int，则不会使用索引，但是查询null是可以使用索引的：

mysql>explain select *from t1 where name=123;//不会使用索引mysql>explain select *from t1 where name=null;//会使用索引

3.2.3. 查看索引使用情况

mysql>show status like"Handler_read%";

Handler_read_key代表一行被索引值读的次数，Handler_read_rnd_next的值代表需要索引的次数越高，需要索引来补救。

3.3 两个简单的优化方法

3.3.1 定期分析和检查表
ANALYZE [LOCAL|NO_WRITE_TO_BINLOG]TABLE tablename;
本语句用来分析和存储表的关键字分布。
CHECK TABLE tablename；
检查一个或多个表是否有错误。

3.3.2 优化表空间
如果删除了表的大部分，会产生空洞，导致表的大小不会自动的收缩，则需要定期的优化，下面这个命令可以将表里面的空间碎片进行合并。
OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG]TABLE tablename;

3.4 常用sql的优化

在需要的情况下，我们需要对数据进行导出和导入：

mysql>select *from t1 into outfile "/tmp/test.txt";//导出到test文件里面mysql>truncate t1;mysql>load data infile "/tmp/test.txt" into table t1;//导入到表t1

3.4.1 大批量插入数据
围绕着数据的导入和导出，当用load命令导入数据的时候，对于MyISAM存储引擎的表，可以通过以下方式来提高导入速度，即不在每次插入数据的时候都要加入索引，而是在数据插入完全之后再一起使用索引：

ALTER TABLE tablename DISABLE KEYS//关闭普通索引load data infile "/tmp/test.txt" into table t1;ALTER TABLE tablename ENABLE KEYS

DISABLE和ENABLE用来打开或者关闭MyISAM表非唯一索引的更新；
注意，上述对Innodb表无效，针对innodb，因为innodb表是按照主键顺序保存的，所以将需要导入的数据按照主键顺序排列，这样可以提高效率，并且innodb表还可以通过关闭自动提交来提高效率，set autocommit=0 导入结束后再恢复成1，就不用每一行数据都去判断一下是否提交了。

在保证不会出现冲突值的情况下，还可以关闭唯一索引来提高效率

set unique_checks=0;//关闭唯一索引...set unique_checks=1;

3.4.2 优化insert语句
尽量使用多个值的insert语句，这样可以缩短客户与数据库的连接等损耗。
3.4.3 优化group by语句
如果查询包含group by但用户想要避免自动分组聚合排序的损耗，可以使用order by null来禁止排序。
3.4.5 优化嵌套查询
嵌套查询要尽量少用，里面一层能用到索引，而外面一层却用不到，

select *from t1 where id in（select uid from t2）

一般使用连接查询（join）来替代

4. MySQL数据库优化

---

4.1 优化表类型

优化表的字段，人为优化，比如字段长度，字段类型等

4.2 通过拆分提高表的访问效率

分库分表、分区、主从数据库，主要是减少表的记录数，还有减少对操作系统的负担压力。

4.3 使用中间表提高统计查询速度

当表中某一部分需要被频繁访问的时候，这时候我们需要使用一个中间表，使用视图作为中间表更合适，可以根据和原表协同。

5. Myisam表锁

---

5.1 myisam读锁定

所有用户都只能读，不能增删改，别的用户的操作会原地挂起，直到解锁。

mysql>lock table t1 read;mysql>unlock tables;

5.2 myisam写锁定

只有我能操作，其他用户什么都不能；

mysql>lock table t1 write;

一般情况下很少去显示的进行表的读写锁定，通常myisam会自动锁定；

6. MySQ可能出现的问题

---

6.1 四种字符集的问题

mysql>\s查看信息：
服务器(Server characterset)、数据库(Db)、客户端(Client)、连接(conn.)字符集一般要统一(utf8)。
修改他们需要去修改mysql的配置文件,当中的default-character-set（连接和客户端字符集）和character-set-server（服务器和数据库）还有collation-server（校验字符集，比如order by排序的规则）：

[root@localhost ~]# vi /etc/my.cnf （//修改配置文件）[client]default-character-set=utf8[mysqld]character-set-server=utf8collation-server=utf8_general_ci:x                               （//退出）[root@localhost ~]#pkill mysqld    （//重启服务，杀掉mysql的进程）[root@localhost ~]#pstree |grep mysqld（//查看服务）[root@localhost ~]#/user/local/mysql/bin/mysqld_safe --user=mysql &(//重新启动mysql)[root@localhost ~]# ！ps//查看进程

6.2 binary log日志问题

mysql>show variables like "%bin%"（//查询log-bin是否打开）[root@localhost var]# vi /etc/my.cnf （//修改配置文件）//找到log-bin=mysql-bin，这里开启并重命名日志名称mysql>show binary logs;//查看bin-log日志mysql>show master status;//查看最后一个bin-log日志

6.3 slow log慢查询日志

mysql>show variables like "%slow%"（//查询slow-query-log是否打开）mysql>show variables like "%long%"（//查询long-query-time是多少）[root@localhost var]# vi /etc/my.cnf （//修改配置文件）log-bin=mysql-bin（//这里重命名日志名称）//在[mysqld]里面：log_slow_queries=slow.loglog_quety_time=5（//开启和设置慢查询时间）

6.4socket问题

有时候误删了sock文件的话会导致mysql无法登陆，这时候可以通过tcp协议去登陆(在语句后面加上–protocol tcp -hlocalhost)，需注意的是，这只是临时登陆，之后需要把问题解决了，重新启动mysql即可，会自动创建mysql.sock文件。

[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost