概述
用来加快查询的技术很多，其中最重要的是索引
如果不适用索引，MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行
索引的本质
官方的定义：索引（Index）是帮助MySQL高效获取数据的数据结构
本质：索引是数据结构
引用index的必要性
常见的查询算法包含linear search(顺序查询)，binary search(二分查找)，binary tree search(二叉树查找)
每种查找算法只能应用于特定的数据结构上，二分查找要求检索的数据有序，二叉树查找应用于二叉查找######树上
数据本身的组织结构不可能完全满足各种数据结构
存储分类
索引是在mysql的存储引擎层中实现的，不是在服务层实现，每种存储引擎的索引不一定完全相同，不同的存储引擎支持不同的索引类型
| 索引 | MyISAM引擎 | InnoDB索引 | Memory引擎 |
|---|---|---|---|
| B-Tree | Yes | Yes | Yes |
| HASH | Yes | ||
| R-Tree | Yes | ||
| Full-text |
四种索引
B-Tree索引：最常见的索引
HASH索引：使用场景简单
R-Tree索引：空间索引，主要用于地理空间的数据类型
Full-text索引：全文索引，从MYSQL5.6开始提供全文索引支持
说明
Ｍysql目前不支持函数索引，
但是能对列的前面的某一部分进行索引，例如可以只取某列的前面的几个字符进行索引
create index idx_title on table (title(10))
但是前缀索引在排序order by ,分组group by时候无法使用
B-TREE索引类型
普通索引,没有唯一性之类的限制，
创建索引：create index 索引名 on table_name(column1,column2); 修改表：alter table table_name add index 索引名(column1,column2); 创建表时指定索引:create table table_name ([...], index 索引名(column1,column2,...));
unique索引,表示唯一的，
创建索引：create unique index 索引名 on table_name(column1,column2); 修改表：alter table table_name add unique index 索引名(column1,column2); 创建表时指定索引:create table table_name ([...], unique index 索引名(column1,column2,...));
主键,主键是一种唯一性的索引
修改表：alter table table_name add primary key(column1); 创建表时指定主键:create table table_name ([...], primary key(column1));
删除，查看索引
删除索引语法如下：
drop index index_name on table_name; alter table table_name drop index index_name; alter table table_name drop primary key
如果从表中删除了某列，索引将会受到影响，
如果删除了某列，该列会从索引中删除
如果删除了组成索引的所有列，整个索引将会被删除
查看索引语法如下：
show index from table_name; show keys from table_name
索引选择原则
较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁的作为查询条件
更新非常频繁的字段不适合创建索引
不会出现在where字句中的字段不该创建索引
索引选择原则补充
１选择在哪个列上面创建索引是非常重要的，
可以考虑使用索引的列主要有两种类型的列：where 字句中出现的列，在join字句中出现的列,而不是在select 关键字后选择列表的列
2 索引的基数越大，索引的效果越好，假如使用性别来进行区分行，只有两种选择，对此字段进行索引没有太多用处
3 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可以节省大量索引空间，提升查询速度
例如，有一个char(200)的列，如果前面的10个或者20个字符内，多数值是唯一的，可以不对整个列进行索引，对前面的字符进行索引可以节省出大量的索引空间，也可以提升查询的速度，原因是：较少的字符索引涉及的磁盘IO较少，短短的值比较容易IO，对于较短的键值，高速缓存中的块能够容纳更多的键值，mysql在内存中可以容纳更多的值，这样就增加了找到行而不用读取索引中更多的块的可能性
４ 利用最左前缀
mysql会一直向右匹配直到遇到范围查询，停止匹配
５ =和in 可以乱序
比如a=1 and b=2 and c=3建立(a,b,c)索引可以是任意顺序,mysql的查询优化器可以将索引优化为可以识别的形式
６索引列不能参加运算
比如下面的索引from _unixtime(create_time) = '2016-05-19';就不能使用索引，因为当数据表中的数值都应用函数才能比较，成本太高，可以变成索引 create_time = unix_timestamp('2016-05-19');使用
7 尽量的扩展索引，不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
两种情况不建议使用索引
１表的记录比较少，例如只有一两千条数据，没有必要创建索引，直接查询做全表扫面就可以了
多少条记录可以考虑创建索引，个人经验是2000条
2 索引的选择性较低时候，
index Selectivity = Cardinality / #T, 索引的选择性（index selecivity）,不重复的索引值(也称为基数,cardinality),表的记录数(#T), 选择性的取值范围是(0,1],选择性较高的索引的价值较高，假设有一个表为employees.titles,如果表中存在字段title,可以通过公式计算出它的选择性， select count(distinct(title))/count(*) as selectivity from employees.titles;
mysql只对下面的操作符才使用索引
< <= = > >= between in 和某些时候的like （不以通配符%或者_开头的情形）
网友评论