数据库索引优化

索引：
`索引的原理是利用特殊的查找算法（如二叉树算法），限制访问的行数，提高访问效率。
索引的分类：
主键索引、普通索引、唯一索引、外键索引和全文索引。
文件：
针对于mysql来说
innodb:两个文件 .frm(表结构) .ibd(索引文件)
myisam:三个文件 .frm(表结构) .MYD(表数据) .MYI(表索引)

主键索引
当一张表，把某个列设为主键的时候，则该列就是主键索引
普通索引
一般来说，普通索引的创建，是先创建表，然后在创建普通索引
唯一索引
当表的某列被指定为unique约束时，这列就是一个唯一索引
unique字段可以为NULL,并可以有多NULL,即Null！=Null。但是如果是具体内容，则不能重复
外键索引
维护表的关联关系
id name pwd nich
uid email phone dizhi
全文索引
全文索引，只对MyISAM引擎有用。主要是针对文件，文本的检索, 比如文章或者段落,
它会把某个数据表的某个数据列出现过的所有单词生成一份清单 【注】mysql自己提供的fulltext针对英文生效，想要搜索中文需要使用专门的全文检索引擎或者使用加强版的模糊查询。
【注】全文索引不完全等同于模糊查询比如title字段有这么个数据’abcd20088ccaa’，
使用模糊查询select * from articles where title like’%2008%’可以查找到，而使用全文检索select * from articles where match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！`

索引的添加，删除和查看
创建索引 1、ALTER TABLE方法 ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list) 2、CREATE INDEX CREATE INDEX可对表增加普通索引或UNIQUE索引。 CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list) 删除索引 可利用ALTER TABLE或DROP INDEX语句来删除索引。 DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY //只能删除主键索引 查看索引 show index from 表名\G Table:表的名称。 Non_unique:如果索引不能包括重复词，则为0。如果可以，则为1。 Key_name:索引的名称。 Seq_in_index:索引中的列序列号，从1开始。 Column_name:列名称。 Collation:列以什么方式存储在索引中。在MySQL中，有值'A'（升序）或NULL（无分类）。 全文索引 创建表时添加 CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engine=myisam charset=utf8; 创建表后添加 ALTER TABLE articles ADD FULLTEXT (title,body); 全文索引的用法 select * from articles where match(title,body) against('要搜索的单词');

索引的代价
SQL语言共分为四大类：数据查询语言DQL，数据管理语言DML(增删改)，数据定义语言DDL(create)，数据控制语言DCL(grant,commit,rollback)。 1、占用磁盘空间 索引是有开销的，表现在添加索引后.ibd文件(innodb引擎)或者.myi文件(myisam引擎)会变大。 2、导致dml操作速度变慢 添加索引后之所以会快，是因为表依据索引对数据按某种算法（二叉树等）进行排序，所以删除、增加、插入后二叉树要重新排序，导致执行效率降低。 此时要看自己的数据库是dml语句执行的多还是dql语句执行的多 使用以下语句可以查询 show status like 'com_select'; show status like 'com_insert'; show status like 'com_delete'; show status like 'com_update'; 一般来说，dql语句操作比dml语句要多得多！接近9:1
什么情况不使用索引
1、数据唯一性差的字段不要使用索引 比如性别，只有两种可能数据 2、频繁更新的字段不要使用索引 比如登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。 3、字段不在where语句出现时不要添加索引 只有在where语句出现，MySQL才会去使用索引 4、数据量少的表不要使用索引 使用了没显著效果
添加了索引但是没有被使用

1、多列索引查询条件没有使用最左边的字段，
	id, name, email
	在SQL语句中只要没有使用最左边的字段，就不会使用索引
2、如果条件中有or
	只要条件中有一个字段没有添加索引，则不会使用索引
3、类型不对应
	比方说，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引
4、MySQL优化器的决定
	如果mysql估计使用全表扫描要比使用索引快，则不使用索引
5、like '%aaa'不会使用到索引
	只要模糊查询的模糊值在字符串前面，则不会使用索引

解决like不使用索引的方法
1、Select主键 只要Select的字段刚好是主键，那么就会使用到索引(只对innodb数据库有效) 可以采用分步查询的方法，先select主键再利用主键去找其他字段。不过好像比较麻烦 2、覆盖索引法 覆盖索引是一种特殊的多列索引，当多列索引指向一个查询语句中所有的字段时，该多列索引就被称为覆盖索引。 create index index_name_age on user(name,age); 创建覆盖索引 explain select name,age from user where name like '_1'\G 这时候发现使用索引了 【注】当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！ 3、全文索引法 此方法有较大局限。 全文索引，只对MyISAM引擎有用。主要是针对对文件，文本的检索,比如文章或者段落 它会把某个数据表的某个数据列出现过的所有单词生成一份清单 少于3个字符的单词不会被包含在全文索引里，可以通过修改my.cnf修改选项 ft_min_word_len=3 但是！ 全文索引不完全等同于模糊查询 比如title字段有这么个数据’abcd20088ccaa’,使用模糊查询select * from articles wheretitle like’%2008%’可以查找到，而使用全文检索select * from articles where match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！ 4、使用全文检索引擎工具包 采用专门的全文检索开源工具可以检索某段字符串。