MYSQL优化记录

MYSQL优化记录

记录工作中的一些经验和学习的知识 主要是关于MYSQL的一些优化技巧

• 表的设计是否合理（符合3NF）
• 适当添加索引（index包括主键索引，普通索引，全文索引，唯一索引）
• 分表技术（水平分割，垂直分割）
• 读(select)写（update/delete/insert）分离技术
• 存储过程（为什么）
• 修改mysql配置参数（例如增加最大并发数，调整缓存大小）
• mysql服务器硬件升级
• 定时清除不需要的数据定时进行碎片处理（MYISAM）

表的设计是否合理（符合3NF）

关于3NF这里有推荐一博客讲得比较好，贴上链接，可供学习，就不再自己记录了。
http://blog.csdn.net/xuxurui007/article/details/7738330

慢查询

其实MYSQL的优化设计方方面面，但有一点我们是必须做的，那就是先找出执行较慢的SQL语句，MYSQL称之为慢查询。默认一个SQL花费10s以上为慢查询，可以使用以下语句查看设置慢查询参数。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

我们可以将其慢查询的时间设置为1s，默认是10s是有点长，实验不能有很好的效果，现实中要写出一个执行时间10s的SQL也是不容易的。除非表的数据相当庞大。设置完之后我们可以修改mysql的配置文件配置开启慢查询。

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

[mysqld]
slow_query_log = ON #开启慢查询开关
slow_query_log_file = /usr/local/mysql/data/slow.log #日志路径long_query_time = 1 #慢查询时间

修改之后重启MYSQL，执行

select sleep(2);

便可以查询/usr/local/mysql/data/slow.log日志文件中的记录，该文件中会罗列出所有超过慢查询时间的SQL语句，如此执行10天半个月便可以得到所有效率较低的SQL语句。

/data/application/mysql/bin/mysqld, 
Version: 5.6.35-log (Source distribution). started with:Tcp port: 3307  Unix socket: /tmp/mysql3307.sockTime                 Id Command    Argument# 
Time: 171120 23:38:52# User@Host: root[root] @ localhost []  Id:     1# Query_time: 2.000241  
Lock_time: 0.000000 
Rows_sent: 1  
Rows_examined: 0SET timestamp=1511192332;
select sleep(2);

索引

添加索引之索引可以加快数据库查询的速度，主要是在因为数据库会增加对应的索引文件，这个文件记录着对应字段索引的物理地址。如果没有添加索引的话，对响应字段查询，那么MYSQL会整个表扫描一遍，直到找出所有的记录集，而添加索引，MYSQL会使用二分法查找该字段的位置，直接从文件读出其物理地址。MYSQL有四种索引类型：主键索引，普通索引，全文索引，唯一索引

索引的创建

###1、添加主键索引（在创建表时创建主键索引）
DROP TABLE TABLENAME IF EXIST;
CREATE TABLE TABLENAME(
    ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL DEFAULT ''
)ENGINE = INNODB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

###2、添加主键索引（创建表之后添加主键索引）ALTER TABLE TABLENAME ADD PRIMARY KEY FILEDNAME;

###3、添加普通索引（创建表之后添加普通索引）ALTER TABLE TABLENAME ADD INDEX INDEXNAME ( COLNUM );

###4、添加唯一索引ALTER TABLE TABLENAME ADD UNIQUE ( COLNUM );

###5、添加全文索引ALTER TABLE TABLENAME ADD FULLTEXT ( column);

###6、添加多列索引ALTER TABLE `table_name` ADD INDEX index_name ( column1, column2, column3 );

索引的查询

show index from tablename;
desc tablename; 不能显示索引名称show keys from tablename;

索引的删除

ALTER TABLE tablename DROP INDEX index_name;
ALTER TABLE tablename DROP PRIMARY KEY;

实验体现索引的威力（测试300w条数据，有没有索引的查询效率的差别）

以下是user表的表结构、随机产生字符串函数rand_string，批量插入数据的存储过程batch_add_user。值得一说的是，添加300w条数据总共花费了（Query OK, 1 row affected (3 hours 23 min 14.87 sec)）3个多钟，这中间主要是因为，调用了rand_string函数以及user的存储引擎是INNODB，其实可以将存储引擎修改为MYISAM，300w条数据加完之后，再把存储引擎修改回来，这样会快很多。

DROP TABLE  IF EXISTS user;
CREATE TABLE user (
    id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(50) NOT NULL, 
    user_name VARCHAR(50) NOT NULL, 
    user_pass VARCHAR(50) NOT NULL, 
    user_birthday DATETIME,
    left_num INT(11) NOT NULL DEFAULT 0,
    right_num INT(11) NOT NULL DEFAULT 0,
    user_answer VARCHAR(50) NOT NULL, 
    user_faq VARCHAR(50) NOT NULL,
    user_city VARCHAR(50),
    user_creator INT(11) NOT NULL,
    user_lever INT(11) NOT NULL DEFAULT 0,
    created_time DATETIME
)ENGINE = innodb DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS batch_add_user;
DELIMITER $$
CREATE PROCEDURE batch_add_user(in num_limit int)BEGIN 
	DECLARE i INT DEFAULT 0;
	WHILE i < num_limit DO
		INSERT INTO user(user_id, user_name, user_pass, user_birthday, left_num, right_num, 
		user_answer, user_faq, user_city, user_creator, user_lever, created_time) VALUE(rand_string(10), rand_string(8), 
		rand_string(8), now(), 1, 1, now(), 'my birthday', '广东省深圳市宝安区翻身文乐花园3栋2单元', 1, 1, now());
		set i = i + 1;
	END WHILE;END $$
DELIMITER ;

###调用batch_add_user插入数据
mysql> call batch_add_user(3000000);
Query OK, 1 row affected (3 hours 23 min 14.87 sec)

###查询user_id='IpG46am9uP'的结果集（没有添加索引）
mysql> select user_name from user where user_id='IpG46am9uP';
+-----------+
| user_name | 
+-----------+
| vUTyXXJC  |
+-----------+
1 row in set (2.35 sec)

###添加userid_index索引
mysql> ALTER TABLE user ADD index userid_index (user_id);
Query OK, 0 rows affected (16.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select user_name from user where user_id='IpG46am9uP';
+-----------+
| user_name |
+-----------+
| vUTyXXJC  |
+-----------+
1 row in set (0.00 sec)

mysql> select user_name from user where user_id='VHzzY130Ip';
+-----------+
| user_name |
+-----------+
| Kns0tdty  |
+-----------+
1 row in set (0.00 sec)

欲思其利，必虑其害，欲思其成，必虑其败。 ——三国时期蜀汉丞相,政治家,军事家 诸葛亮 《便宜十六策·思虑》
从测试的结果来看，索引的效果对于300w条记录的查询，效果简直立竿见影。但是任何东西都有两面性，那么添加索引会有什么缺点呢。主要罗列如下：
1、 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。（建立索引之后，在表目录会有相关的文件记录索引的维护，增加索引数量，增加记录数量，都会是这个文件变大）
3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。（后续会一实验的形式展示）

使用索引的注意事项（优化的一些细节）

大礼不辞小让，细节决定成败

1、如果查询条件中有or，即时条件中有索引也不会使用。换言之，就是一旦出现or，所有的查询必须都带索引。explain select * from user where user_id = ‘IpG46am9uP’ or user_name = ‘vUTyXXJC’中user_name没有建立索引，索引该语句没有使用到key，也就是key为null，工作中尽量避免使用or。

mysql> explain select * from user where user_id = 'IpG46am9uP';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys |key           | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | userid_index  | userid_index | 152     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where user_id = 'IpG46am9uP' or  user_name = 'vUTyXXJC';
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+--------------------+
| id | select_type | table | type | possible_keys |key           | key_len | ref   | rows    | Extra              |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | userid_index  | NULL         | NULL    | NULL  | 2892728 | Using where        |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

2、如果字段是字符串，那么使用查询时，一定要使用引号将字段对应的值包含起来，否则不会使用索引（书是这么说，但是大多数情况下，不加引号是执行不过的，除非右值是全数字，数据库将其强转为字符串）

mysql> explain select user_birthday from user where user_id = 'pG46am9u';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | userid_index  | userid_index | 152     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select user_birthday from user where user_id = 123456789;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
 id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | userid_index  | NULL | NULL    | NULL | 2892728 | Using where |
+----+------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

3、 数据库如果发现权标扫描比使用索引快，将不会使用索引（有这种情况吗，有，数据库只有一条数据记录时，当然这只是一种特殊情况）

mysql> select * from user;
+----+------------+-----------+-----------+---------------+----------+-----------+-------------+----------+----------------------------------------------------------+--------------+------------+---------------------+
| id | user_id    | user_name | user_pass | user_birthday | left_num | right_num | user_answer | user_faq | user_city                                            | user_creator | user_lever | created_time        |
+----+------------+-----------+-----------+---------------+----------+-----------+-------------+----------+----------------------------------------------------------+--------------+------------+---------------------+
|  1 | qZuk0Uo1ME | NPDuomua  | evDvrykL  | 2017-11-30    |        1 |         1 | 2017-11-30  |        1 | 广东省广州市天河区翻身文乐花园3栋2单元                   | Dhiwz4xiGp   |          1 | 2017-11-30 12:01:18 |
+----+------------+-----------+-----------+---------------+----------+-----------+-------------+----------+----------------------------------------------------------+--------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE user ADD INDEX user_id_index (user_id );
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

优化sql语句的几点小技巧（优化的一些细节）

1、使用group by语句mysql默认会将查询之后的分组结果在进行排序。如果我们不需要它进行排序的话，这显然会降低查询效率。为此可以使用order by null来防止它进行排序。explain select * from user group by user_birthday limit 10 这个sql语句使用了filesort。

mysql> explain select * from user group by user_birthday limit 10;
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 2892728 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from user group by user_birthday order by null limit 10;
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 2892728 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+
1 row in set (0.00 sec)

2、有些情况，可以使用连接来代替子查询，因为使用join，mysql不需要在内存创建临时表，关于子查询这里有篇不错的播客https://www.cnblogs.com/zhuiluoyu/p/5822481.html

select * from student , classroom where student.claid = classroom.id;(子查询)
select * from student left join classroom on student.claid = classroom.id(连接效率更ok)

3、合理选择mysql的存储引擎。
如果表对事物要求不高，同时是以查询和添加为主的表可以考虑使用MyIsam存储引擎，比如发帖表，回复表。
如果对事物要求高，保存的数据都是重要数据建议使用InnoDB，比如订单表，账户表。
如果数据变化频繁，不用入库，同时又有频繁的查询和修改，可以考虑用Memory表，例如状态表
补充innodb和myisam之间的几点区别
1）查询和添加效率
2）事务安全
3）支持全文索引
4）锁机制
5）支不支持外键

技术就是窗户纸，一捅就破
4、如果你的数据库存储引擎是MyIsam，请一定记住定时对数据库进行碎片处理。

optimize table tablename;