MySQL之索引及优化

1. B-Tree索引：
   所有值按顺序存储，并且每一个叶子页到根的距离相等。B-Tree索引适合查找范围数据，适用的查询类型：全值匹配，键值范围，键左前缀匹配；同样由于B-Tree的按顺序存储特性，也适用于ORDER BY按顺序查找的方式。
   注意：索引又多列时，索引列的顺序对查询有很大影响，因为索引不能跨列使用（要从左列依次使用）；而且当其中一列的查询条件时范围时，其右边的索引列都不能使用。
   优化性能： 可以用相同列建立不同顺序的索引满足不同类型查询需求。如何排序列？经验法：基于全局基数和选择性,选择 选择性大的为排序在左边的列。
   （select COUNT( DISTINCT ziduan1)/COUNT() as 1_selectivity,
   COUNT( DISTINCT ziduan2)/COUNT() as 2_selectivity,
   COUNT(*) FROM table;）
   但是要注意有些数据基数太大，只是用经验法看似有效，实则索引即无效（如果字段1=1，字段2=2 匹配的数据都有几百万条），这种情况应该把这种记录单独处理，而不能采用索引查询。
   建立：CREATE TABLE People{
   last_name varchar(50) not null,
   first_name varchar(50} not null,
   gender enum(‘m’,‘f’) not null,
   key(last_name, first_name)
   };

2. 哈希索引
   根据索引列，存储引擎计算相应的哈希码；哈希索引将哈希码和指向数据行的指针保存起来，MYSQL MEMORY引擎中对哈希值相同的会以链表的方式存放多个记录的指针在同一个哈希条目中。所以哈希索引适用于精确匹配索引所有列的查询，由于存储的非数据，所以不适用于排序。
   CREATE TABLE People{
   last_name varchar(50) not null,
   first_name varchar(50} not null,
   gender enum(‘m’,‘f’) not null,
   KEY USING HASH(last_name, first_name)
   } ENGINE = MEMORY;

3. 空间数据索引（R-Tree）
   地理数据索引，从所有维度来索引数据。

4. 全文索引
   查找文本的关键词，适用于MATCH AGAINST，类似于搜索引擎。
   CREATE TABLE <表名> (
   <字段名> <字段类型>,
   FULLTEXT (<字段名>)
   );
   ALTER TABLE <表名> ADD FULLTEXT (<字段名>)；

普通索引
CREATE INDEX <索引名> ON <表名>(<字段名>)；
ALTER TABLE <表名> ADD INDEX <索引名> (<字段名>);
CREATE TABLE <表名>(
<字段名> <数据类型>,
…
INDEX <索引名> (<字段名>)
);

唯一索引（唯一索引与普通索引的区别在于，唯一索引是查到符合条件的项后就直接返回，而普通索引是查到符合条件的项后会继续查找下一项，若下一项不符合再返回。）
CREATE UNIQUE INDEX <索引名> ON <表名> (<列名>)；
ALTER TABLE <表名> ADD UNIQUE <索引名>（<列名>）；
CREATE TABLE <表名>(
<字段名> <数据类型>,
…
UNIQUE <索引名> (<列名>)
);

主键索引
ALTER TABLE <表名> ADD PRIMARY KEY ( <字段名> )；
ALTER TABLE <表名> ADD PRIMARY KEY ( <字段名> )；
CREATE TABLE <表名>(
<字段名> <数据类型>,
…
PRIMARY KEY(<列名>)
);

还有索引的优化，聚簇索引，覆盖索引，压缩索引等，，后续再说