Mysql学习笔记（九)索引查询优化

mysql>createtable title
    -> (
    ->    id intnotnull,
    ->    title varchar(255) notnull,
    ->    from_date date notnull,
    ->key(id),
    ->key(title),
    ->key(from_date)
    -> );
//建立一个表格。。。有三个主键..。有主键必然要使用到索引...
介绍一下查询方式...
1.全列匹配...
mysql>expla select*from title where id=1000and title=‘gogoing‘and from_date=‘1992-01-01‘;
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type |table| type  | possible_keys |key| key_len | ref               | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|1| SIMPLE      | titles | const |PRIMARY|PRIMARY|59| const,const,const |1||+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+2.最左前缀索引...
explain select*from title where id=1001;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+| id | select_type |table| type | possible_keys |key| key_len | ref   | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+|1| SIMPLE      | titles | ref  |PRIMARY|PRIMARY|4| const |1||+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+最左前缀原则是当索引用到多个列的时候，只有组成最左前缀的部分才能被使用到..上面只用到了第一列...
3.匹配的索引列使用了精确匹配。。。但是中间有部分列没有被给出...
explain select*from title where id=1002and from_date=‘1998-01-01‘;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type |table| type | possible_keys |key| key_len | ref   | rows | Extra       |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1| SIMPLE      | titles | ref  |PRIMARY|PRIMARY|4| const |1| Using where|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+这样只会走第一个索引id，而from_date将不走索引。。由于没有给出title的值，所以无法构成最左前缀原则，因此from_date成为不走索引...
我们可以使用两种方式解决这个问题....使用IN将title的所有值都包括在其中...
EXPLAIN SELECT*FROM employees.titles
WHERE emp_no=‘1005‘AND title IN (‘title1的值‘,‘title2的值‘......)
AND from_date=‘1986-06-26‘;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys |key| key_len | ref  | rows | Extra       |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | titles | range |PRIMARY|PRIMARY|59|NULL|7| Using where|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+这样就不会出现不走索引的情况了...但是如果title的值过多，那么in这种方式就不能使用..因此解决这个问题的另一种方式就是建立辅助索引....
4.查询的时候没有指定索引的第一列...
explain select*from title where from_date=‘1995-01-26‘;//这个结果很明显，就是不走索引...
5.匹配某列前缀的字符串..
explain select*from title where id=1008and title like‘S%‘;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys |key| key_len | ref  | rows | Extra       |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | titles | range |PRIMARY|PRIMARY|56|NULL|1| Using where|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+6.使用范围查询...
explain select*from title where id>1010;范围列可以使用索引，但是必须是最左前缀..并且如果同一个查询语句使用了两个范围索引，那么后面的范围将成为不走索引..
explain select*from title where id>1010and title=‘gogoing‘and from_date between‘1990-01-01‘and‘1998-01-01‘;
还有一种情况就是如下面代码..
explain select*fromwhere id between1000and1010and title=‘gogoing‘and from_date between‘1998-01-01‘and‘2010-01-01‘;
上面这种情况就变成了第一个就成为了多值匹配，而后面那个范围成为了范围匹配...同样都是两个范围匹配，和上面的情况就不同了..这个我至今没弄明白..云里雾里的...如果有大牛会的话请告诉我。。。
7.带有表达式的查询方式...
如果查询语句中含有表达式，那么将成为不走索引。。。（除一些特殊方式）。。。
EXPLAIN SELECT*FROM employees.titles WHERE emp_no -1=‘10000‘;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type |table| type | possible_keys |key| key_len | ref  | rows   | Extra       |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|1| SIMPLE      | titles |ALL|NULL|NULL|NULL|NULL|443308| Using where|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
不走索引...

createtable actor(

actor_id int unsigned NOTNULL AUTO_INCREMENT,

name      varchar(16) NOTNULLDEFAULT‘‘,

password        varchar(16) NOTNULLDEFAULT‘‘,

PRIMARYKEY(actor_id),

 KEY     (name)

) ENGINE=InnoDB

insertinto actor(name,password) values(‘cat01‘,‘1234567‘);

insertinto actor(name,password) values(‘cat02‘,‘1234567‘);

insertinto actor(name,password) values(‘ddddd‘,‘1234567‘);

insertinto actor(name,password) values(‘aaaaa‘,‘1234567‘);

mysql> explain select actor_id from actor orderby actor_id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys |key| key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | actor |index|NULL|PRIMARY|4|NULL|4| Using index|+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
mysql> explain select actor_id from actor orderby password;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type |table| type | possible_keys |key| key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|1| SIMPLE      | actor |ALL|NULL|NULL|NULL|NULL|4| Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
mysql> explain select actor_id from actor orderby name;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys |key| key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+|1| SIMPLE      | actor |index|NULL| name |34|NULL|4| Using index|+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+