5、OR的优化
当MySQL使用OR查询时,如果要利用索引的话,必须每个条件列都使独立索引,而不是复合索引(多列索引),才能保证使用到查询的时候使用到索引。
比如我们新建一张用户信息表user_info
mysql> select*from user_info; +---------+--------+----------+-----------+ | user_id | idcard | name | address | +---------+--------+----------+-----------+ | 1 | 111111 | Zhangsan | Kunming | | 2 | 222222 | Lisi | Beijing | | 3 | 333333 | Wangwu | Shanghai | | 4 | 444444 | Lijian | Guangzhou | +---------+--------+----------+-----------+ 4 rows in set
之后创建ind_name_id(user_id, name)复合索引、id_index(id_index)独立索引,idcard主键索引三个索引。
mysql> show index from user_info; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user_info | 0 | PRIMARY | 1 | idcard | A | 4 | NULL | NULL | | BTREE | | | | user_info | 1 | ind_name_id | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | | | user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | | | | user_info | 1 | id_index | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set
测试一:OR连接两个有单独索引的字段,整个SQL查询才会用到索引(index_merge),并且我们知道OR实际上是把每个结果最后UNION一起的。
mysql> explain select*from user_info where user_id=1 or idcard='222222'; +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | user_info | NULL | index_merge | PRIMARY,ind_name_id,id_index | ind_name_id,PRIMARY | 4,62 | NULL | 2 | 100 | Using sort_union(ind_name_id,PRIMARY); Using where | +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 1 row in set
测试二:OR使用复合索引的字段name,与没有索引的address,整个SQL都是ALL全表扫描的
mysql> explain select*from user_info where name='Zhangsan' or address='Beijing'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 43.75 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
交换OR位置并且使用另外的复合索引的列,也是ALL全表扫描:
mysql> explain select*from user_info where address='Beijing' or user_id=1; +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 43.75 | Using where | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ 1 row in set
6、优化嵌套查询
使用嵌套查询有时候可以使用更有效的JOIN连接代替,这是因为MySQL中不需要在内存中创建临时表完成SELECT子查询与主查询两部分查询工作。但是并不是所有的时候都成立,最好是在on关键字后面的列有索引的话,效果会更好!
比如在表major中major_id是有索引的:
select * from student u left join major m on u.major_id=m.major_id where m.major_id is null;
而通过嵌套查询时,在内存中创建临时表完成SELECT子查询与主查询两部分查询工作,会有一定的消耗
select * from student u where major_id not in (select major_id from major);
7、使用SQL提示
SQL提示(SQL HINT)是优化数据库的一个重要手段,就是往SQL语句中加入一些人为的提示来达到优化目的。下面是一些常用的SQL提示:
(1)USE INDEX:使用USE INDEX是希望MySQL去参考索引列表,就可以让MySQL不需要考虑其他可用索引,其实也就是possible_keys属性下参考的索引值
mysql> explain select* from user_info use index(id_index,ind_name_id) where user_id>0; +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ 1 row in set mysql> explain select* from user_info use index(id_index) where user_id>0; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | id_index | NULL | NULL | NULL | 4 | 100 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
(2)IGNORE INDEX忽略索引
我们使用user_id判断,用不到其他索引时,可以忽略索引。即与USE INDEX相反,从possible_keys中减去不需要的索引,但是实际环境中很少使用。
mysql> explain select* from user_info ignore index(primary,ind_name_id,id_index) where user_id>0; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
(3)FORCE INDEX强制索引
比如where user_id > 0,但是user_id在表中都是大于0的,自然就会进行ALL全表搜索,但是使用FORCE INDEX虽然执行效率不是最高(where user_id > 0条件决定的)但MySQL还是使用索引。
mysql> explain select* from user_info where user_id>0; +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where | +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ 1 row in set
之后强制使用独立索引id_index(user_id):
mysql> explain select* from user_info force index(id_index) where user_id>0; +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | user_info | NULL | range | id_index | id_index | 4 | NULL | 4 | 100 | Using index condition | +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set
总结
(1)很多时候数据库的性能是由于不合适(是指效率不高,可能会导致锁表等)的SQL语句造成,本篇博文只是介绍简单的SQL优化
(2)其中有些优化在真正开发中是用不到的,但是一旦出问题性能下降的时候需要去一一分析。