首页 » MySQL » mysql优化查询

mysql优化查询

原文 http://blog.csdn.net/qq_34829953/article/details/76268420

2017-07-28 17:20:21阅读(593)

使用索引查询

MariaDB [test]> explain select * from te where id=22;                   #在没有增加索引情况下,rows为7,即查询行数
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | te    | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
MariaDB [test]> alter table te add index tindex(id);                    #增加索引
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> explain select * from te where id=22;                   #rows变为1
+------+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|    1 | SIMPLE      | te    | ref  | tindex        | tindex | 5       | const |    1 |       |
+------+-------------+-------+------+---------------+--------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [test]> 

索引使用注意事项
使用like关键字查询时,%号位于首位会导致无法使用索引查询,否则可以正常使用索引查询,如下
<a href=mysql优化查询" src="http://img.blog.csdn.net/20170728155534164?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcXFfMzQ4Mjk5NTM=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="这里写图片描述" title="">
多列索引问题

MariaDB [test]> alter table te add index indextwo(id,name);         #创建索引
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> show create table te\G;
*************************** 1. row ***************************
       Table: te
Create Table: CREATE TABLE `te` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `se` varchar(3) DEFAULT 'man',
  `city` varchar(3) DEFAULT 'gx',
  KEY `indextwo` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [test]> explain select * from te where name='ds';                       #rows为7
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | te    | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [test]> explain select * from te where id=3 and name='ds';              #增加多列索引的首列后,可以正常使用索引
+------+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+------+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | te    | ref  | indextwo      | indextwo | 28      | const,const |    1 | Using index condition |
+------+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [test]> 

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹

天天可领!最高99块!土豪站长12月发红包啦!

关闭
支付宝红包