使用索引
1.没有创建索引时
explain select *from cards where name = 'haha5';
-> //
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | cards | ALL | cards_number | NULL | NULL | NULL | **8** | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
2.创建索引
create index index_name ON cards(name);
3.再分析
explain select *from cards where name = 'haha5';
+------+-------------+-------+------+-------------------------+------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+-------------------------+------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | cards | ref | index_name,cards_number | index_name | 203 | const | **1** | Using index condition |
+------+-------------+-------+------+-------------------------+------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
可以看到使用索引后查询行变为1(rows列)
优化数据库的结构
- 对于字段特别多,且有些字段的使用频率很低的表。可以分解成多个表。
- 有时需要经常查询某两个表的几个字段。如果经常进行联表查询,会降低数据库的查询速度。对于这种情况可以建立中间表来提高查询速度。
优化插入记录的速度
1.禁用索引, 在插入记录之前先禁用索引,插入完毕之后再开启。
ALTER TABLE table_name DISABLE KEYS;
ALTER TABLE table_name ENABLE KEYS;
2.对于创建新表,可以先不创建索引,等记录导入后再创建索引。
禁用唯一性检查
SET UNIQUE_CHECKS=0;
SET UNIQUE_CHECKS=1;
3.优化INSERT语句
INSERT INFO food VALUES
(NULL, 'data1', 'data2'),
(NULL, 'data1', 'data2'),
(NULL, 'data1', 'data2');
load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';
分析表,检查表和优化表
ANALYZE TABLE table_name;
CHECK TABLE table_name;
OPTIMIZE TABLE table_name;
如果一个表使用了TEXT或者BLOB这样的数据类型,那么更新,删除操作会造成磁盘空间的浪费,因为以前分配的磁盘空间不会自动回收,使用OPTIMIZE TABLE 可以将这些磁盘碎片整理出来,以便以后再用。
使用高速缓存
SELECT SQL_CACHE * FROM table_name;
SELECT SQL_NO_CACHE * FROM table_name;
优化多表查询
select address from student where id=(
select id from student_extra where name='nihao'
);
select address from students as stu, student_extra as stu_e
where stu.id=stu_e.id and stu_e.extra='nihao';
select name from student where age > (
select avg(age) from student_extra
);
上述两个查询速率优于子查询的速率,故采用服务器变量也可以优化查询。
优化表设计
1.优先考虑特定字段长度,后考虑使用变长字段
2. 使用OPTIMIZE TABLE优化表
3. 检查表是否需要合并,如果没有必要合并,用户可以使用连接,如果连接的列采用相同的数据类型和长度,同样可以达到优化查询的作用。