深入浅出Mysql（三）

索引的存储分类
Myisam的表数据文件和索引文件是自动分开存储的；InnoDB的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。
创建索引语法：

create [unique|fulltext|spatial] index index_name
[using indext_type] on table_name (table_col_name,...)
index_col_name:
col_name[(length)][asc|desc];

例如：

create unique index index_name on table_name (column_list) ;

索引的存储类型目前有btree和hash，具体和表的模式相关
Myisam btree
InnoDB btree
memory/heap hash,btree
Mysql目前不支持函数索引，只能对列的前一部分(length)进行索引，例如：
create index ind_test on table1(name(5));
对于char和varchar列，可以大大节省空间
Mysql如何使用索引
查询时要使用索引的最主要条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左前缀时，才可以使用前缀，否则不能使用索引。
举个例子：
1，普通多列索引

mysql> ALTER TABLE index_test ADD index test( user_id, username ); 
Query OK, 0 rows affected (0.07 sec)  
Records: 0  Duplicates: 0  Warnings: 0

2，多列唯一索引

mysql> ALTER TABLE index_test ADD unique test( user_id, username ); 
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0

3，多列主键索引

mysql> ALTER TABLE index_test ADD primary key test( user_id, username ); 
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0

1，使用多列索引的情况
例1，
查看复制打印?

mysql> explain select * from index_test where user_id=1\G;  
*************************** 1. row ***************************  
 id: 1 
  select_type: SIMPLE  
 table: index_test 
 type: ref 
possible_keys: test  
 key: test //使用了索引test 
 key_len: 4 
 ref: const 
 rows: 1 
 Extra: 
1 row in set (0.00 sec)  

ERROR:  
No query specified

例2，
查看复制打印?

mysql> explain select * from index_test where user_id=1 and username='tank'\G; 
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: index_test  
         type: ref  
possible_keys: test  
key: test  
      key_len: 66  
          ref: const,const  
         rows: 1  
        Extra: Using where  
1 row in set (0.00 sec)  

ERROR:  
No query specified

例3，
查看复制打印?

mysql> explain select * from index_test where user_id=1 and (username='tank' or username='zhang')\G;  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: index_test  
type: range  
possible_keys: test  
          key: test  
      key_len: 66  
          ref: NULL  
         rows: 2  
        Extra: Using where  
1 row in set (0.00 sec)  

ERROR:  
No query specified

2，不使用多列索引的情况
例4，
查看复制打印?

mysql> explain select * from index_test where user_id=1 or username='tank'\G;  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: index_test  
type: ALL  
possible_keys: test       //列出了可能存在的索引 
          key: NULL       //但是并没有使用这个索引 
      key_len: NULL  
          ref: NULL  
rows: 2  
        Extra: Using where  
1 row in set (0.01 sec)  

ERROR:  
No query specified

例5，
查看复制打印?

mysql> explain select * from index_test where username='tank'\G;  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: index_test  
type: ALL  
possible_keys: NULL     //可能存在的索引都没有列出来 
          key: NULL     //也没有使用多列索引 
      key_len: NULL  
          ref: NULL  
rows: 2  
        Extra: Using where  
1 row in set (0.00 sec)  

ERROR:  
No query specified

根据上面测试，多列索引的第一列很重要，以上面例子为例，就是user_id这一列。要想多列索列起作用，第一列必须要包含在内，如果要用到or，不要与第一列并行。看例4
以下情况Mysql也不会使用索引：
1、如果Mysql估计使用索引比全表扫描更慢则不使用索引。例如如果key_part1均分布在1和100之间，下列查询使用索引就不是很好：

select * from table_name where key_part1 > 1 and key_part1 < 90;

2、如果使用heap表并且where条件中不用=索引列，其他>,<,>=,<=均不使用索引，
3、如果不是索引列的第一部分
4、如果like是以%开始
5、对where后边条件为字符串的一定要加引号，字符串如果为数字Mysql会自动转为字符串，但是不适用索引

秒客网

深入浅出Mysql（三）

相关文章