mysqly优化 入门 第一篇
数据库的优化主要分两方面,一方面是硬件层面的优化,另一方面是参数和语句方面的优化。
今天做一个小结,内容可能不全(也是我自己的理解,如有不得当之处还请及时指出,收到之后我会及时予以修正)
硬件层面
首先我们来看什么算是一台性能强悍的计算机----
首先是计算机(服务器)的配置不能太差 ,处理器–多核(貌似锐龙核多),磁盘读写速度–(固态>普通硬盘)。
由于现在的计算机大多是多核心并且支持并发任务处理的,(mysql也支持多线程) 因此想要提高对数据库的读写操作,因此更换一个读写速度更好的磁盘是一个选择。
其次如果是企业级别的最好是整个分布式的以提高并发处理能力。
参数层面
优化mysql查询语句,配置参数等 查询的时候优化查询语句 按查询速度排名先后 1,system - 只有一行查询速度,且是系统表
2,const --只有一行的查询,查询条件涉及是primary key 或者unique 3,eq-ref联结查询
,查询条件涉及primary key 或者unique 4,ref联结查询,查询条件不涉及primary key 或者unique
5,联结查询,查询条件中有or,则两个条件必须都是primary key 或者unique,否则就会忽略primary key
或者unique 6,all 是最慢的,因为要全部扫描
代码演示—表中数据如下
mysql> desc hxjy ;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_id | int | NO | PRI | NULL | |
| user_name | varchar(16) | YES | | NULL | |
| user_gender | char(4) | YES | | NULL | |
| user_age | tinyint | YES | | NULL | |
| user_jobs | varchar(32) | YES | | NULL | |
| user_salary | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> explain select * from hxjy where user_id =1011 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+
----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+
----------+-------+
| 1 | SIMPLE | hxjy | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 |
100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+
----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
扫描1行数据就得到我们要的结果,我们会发现type为const,
我们再来看一下条件中没有索引的查询
mysql> explain select * from hxjy where user_name='赵' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filt
ered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------------+
| 1 | SIMPLE | hxjy | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 1
0.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------------+
1 row in set, 1 warning (0.00 sec)
扫描12行数据,才得到我们的结果,我们也会发现type为all,
扫描数据小的要比扫描的多的快。
所以要设置合适的索引来提高查询速度
插入数据— 由于插入数据时表要检查索引,唯一索引,键约束等
1,禁用索引
2,禁用唯一索引
3,禁用外键约束
mysql> explain insert into hxjy values(4552,'蚂yi','男',24,'Worker',5600),(4575,'sdasd','女',23,'Wor
ker',5445),(3232,'sadasd','男',36,'Worker',4556);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filt
ered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
| 1 | INSERT | hxjy | NULL | ALL | NULL | NULL | NULL | NULL | NULL |
NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table hxjy enable keys ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> explain insert into hxjy values(475545,'蚂yi','男',24,'Worker',5600),(44521,'sdasd','女',23,'
Worker',5445),(34545,'sadasd','男',36,'Worker',4556);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filt
ered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
| 1 | INSERT | hxjy | NULL | ALL | NULL | NULL | NULL | NULL | NULL |
NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----
-----+-------+
1 row in set, 1 warning (0.00 sec)
可能是插入的数据量不够,所以显示不出时间上的区别,(或者又可以显示时间的方法,欢迎提点)
插入数据时如果是往新建的表中插入数据,可以不用禁用索引,因为索引是插入数据之后才建立的,如果表中已有数据,而且要插入大量数据,最好先禁用索引,适用于myisam和innodb
另Load data infile 导入数据要比插入数据快。
表的层面–
表中数据太多的时候对于经常查询的表的内容,可已将该内容拆到另一张表中,(如果要修改表数据可以建立触发条件来保持数据一致,或者建立视图)
用联结查询有时候不如直接建立一张新表快。这个看需要;