数据库索引

索引认识
索引：
1、是一个排序的列表
2、列表中存储索引的值和这个值得数据所在行的物理地址
3、在数据量比较庞大（超过1万条）的时候，索引可以加快查询的速度
4、因为使用索引后不用扫描全表来定位某行的数据、而是先通过索引表找到该行的数据对应的物理地址然后访问相应的数据
mysql中创建索引
1、创建表的时候直接添加索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
2、创建表之后添加索引
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
创建索引需要注意的地方
1、索引需要占用磁盘空间、因此在创建时需要考虑磁盘空间是否足够
2、创建索引时需要对表加锁、所以在进行创建索引需在表空闲期
使用索引查询
具体查询：
SELECT * FROM table_name WHERE column_1=column_2;(column_1建立了索引)

或者模糊查询
SELECT * FROM table_name WHERE column_1 LIKE '%三'
SELECT * FROM table_name WHERE column_1 LIKE '三%'
SELECT * FROM table_name WHERE column_1 LIKE '%三%'
 
SELECT * FROM table_name WHERE column_1 LIKE '_好_'
 
如果要表示在字符串中既有A又有B，那么查询语句为：
SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
 
SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以
 
//在模糊查询中，%表示任意0个或多个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度;[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符
 
或者在全文索引中模糊查询
SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

删除索引
DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;

查看表中索引
SHOW INDEX FROM tablename

查看查询语句使用索引的情况

//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

索引的优缺点
优势：
可以快速检索、减少I/O次数、加快检索速度、
根据索引分组和排序可以加快分组和排序
劣势：
索引本身就是一张表、需要存储空间，
索引表的创建和维护都需要时间成本，
构建索引会降低数据表的修改操作（增删改）效率，因为在修改数据表的同时还需要修改索引表

索引分类
常见的索引：主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；
3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；
4、全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

索引实现原理
MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引、B+Tree索引、哈希索引、全文索引

此外MyISAM和innoDB的区别总结如下：
  MyISAM innoDB
索引类型 非聚簇 聚簇
支持事务 是 否
支持表锁 是 是
支持行锁 否 是（默认）
支持外键 否 是
支持全文索引 是 是（5.6以后支持）
适用操作类型 大量select下使用 大量insert、delete和update下使用
MyISAM和innoDB引擎对比
总结如下：
• InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
此外，Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引

索引使用策略
什么时候需要使用索引
1、主键自动建立唯一索引
2、经常作为查询条件的列
3、作为排序的列
4、查询中与其他表关联的字段，外键关系建立索引
5、高并发条件下倾向组合索引
6、用于聚合函数的列可以建立索引，例：使用了max(column_1)或者count(column_1)中column_1就需要建立索引
什么时候不能使用索引
1、经常增删改
2、有大量重复值的列
3、表数据量低于1000

索引失效：
1、组合索引某一列有null值，那么这一列对组合索引就是无效
2、在select中，索引只能使用一次，where使用后、order by再使用就失效
3、like中"%aaa%"索引无效，但"aaa%"索引是有效的