MYSQL优化记录
来源:互联网 发布:sql语句获得正态分布 编辑:程序博客网 时间:2024/06/06 15:12
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 DOINSERT 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: 0mysql> 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: 0mysql> 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;
- mysql 优化记录
- MYSQL优化记录
- mysql 性能优化点记录
- mysql 性能优化点记录
- mysql 性能优化点记录
- mysql 性能优化点记录
- MySQL优化的一点记录。
- 【原创】Mysql 优化记录
- Mysql 优化实验数据结论记录
- MySQL优化:启用慢查询日志记录
- mysql大数据相关优化记录
- mysql随机取记录数优化
- Mysql性能优化、使用总结记录
- MySql优化方法---网上资料整理记录
- mysql 学习记录(二十二)--mysql的应用优化
- <<深入理解mariadb和mysql>>之mysql优化学习记录
- 百万记录级MySQL数据库及Discuz!论坛优化
- mysql 随机获取记录 order by rand 优化
- 求进制数的问题
- 图片表示
- 使用eclipse管理Word文档
- WEB开发 路径问题
- phpcms中如何输出单网页栏目下的子栏目,附带选中样式?
- MYSQL优化记录
- JAVA环境变量配置详解
- Codeforces Round #447 (Div. 2) D. Ralph And His Tour in Binary Country
- 在Java中 什么叫包-包有什么用途-如何创建包-
- 色彩表示与编码
- 固定位数的全排列与括号匹配
- 数组例题
- 版本控制
- 。