mysql优化查询

来源：互联网发布：如何判断sql注入编辑：程序博客网时间：2024/06/05 18:11

使用索引查询

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: 0MariaDB [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关键字查询时，%号位于首位会导致无法使用索引查询，否则可以正常使用索引查询，如下
这里写图片描述
多列索引问题

MariaDB [test]> alter table te add index indextwo(id,name);         #创建索引Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0MariaDB [test]> show create table te\G;*************************** 1. row ***************************       Table: teCreate 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=latin11 row in set (0.00 sec)ERROR: No query specifiedMariaDB [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]>

阅读全文

0 0