mysql_索引

mysql_索引
一 概述:
myisam 和 innodb 默认创建的是btree索引.
memory存储引擎使用hash索引,也支持btree索引.

语法:
    create [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING INDEX_type]
    ON table_name (index_col_name,...]
    index_col_name:
        col_name[(length)][ASC|DESC]

示例1:为city表创建10个字节的前缀索引
create INDEX cityname on city(city(10));

如以city为条件进行查询,可以发现cityname索引被使用
EXPLAIN select * from city where city='fuzhou';
    
id:1
select_type:simple
table:REF
possible_keys:cityname
key:cityname
key_len:32
ref:CONST
rows:1
extra:using WHERE

删除:
drop index index_name on table_name
drop index cityname on city;

二 设计索引原则
提升索引的使用效率,更高效的使用索引

1.搜索的索引列:
    where子句,连接子句中指定的列建立索引,选择列不宜出现.
2.使用唯一索引:
    考虑到列中值的分布,索引列的基数越大,索引的效果越好.
3.使用短索引:
    对varchar(200)列索引,如果在前10个或20个字符要节省索引空间,也可以使查询更快.涉及的磁盘的io更少,较短的值比较起来更快.重要的是,对于较短的键值,索引高速缓存中的一块,能容纳更多的值,
    这样,就增加了找到行,而不用读取索引中较多块的可能性.
4.利用最左前缀
    n列的索引,多列索引可引起几个索引的作用,可利用索引最左边的列集来匹配行,这样的列集称为最左前缀.
5.不过度索引
    额外的索引占用额外的磁盘空间,并降低写操作的性能,在修改表的内容时,索引必须进行更新,有时需要重构,索引多用时长.
6.innodb表:
    innodb存储引擎的表,尽量使用主键.因为记录是默认按照主键的顺序保存的.

三:btree和hash索引
memory存储引擎可以使用btree和hash索引
hash索引:
    只用于= <=> 操作符的等式比较
    优化器不能使用hash索引来加速order by子句的操作
    mysql不确定在两个值之间有多少行时,如果将一个myisam表改为hash索引的memory表会影响一些查询的执行效率.
    只能使用整个关键字来搜索一行.
btree索引
    < > <= >= between != <> 或者 like 'pattern' 都能用上索引
    
btree和hash都适用情况:
select * from t1 where key_col=1 or key_col in(12,23,45);

btree 适合
select * from t1 where key_col >1 and key_col <10;
select * from t1 where key_col like 'ab%' or key_col BETWEEN 'lisa' and 'simon';

create table city_memory(
    city_id SMALLINT UNSIGNED NOT null auto_increment,
    city varchar(50) not null,
    country_id SMALLINT UNSIGNED NOT null,
    last_update TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
    PRIMARY key(city_id),
    key idx_fk_country_id(country_id)
)ENGINE=memory DEFAULT CHARSET=utf8;

当对索引字段进行范围查询的时候,值有btree索引可通过索引访问

而hash索引实际上全表扫描.