数据库索引以及优化
浏览数：24 /
时间：2015年06月12日
什么是索引？
索引用来快速地寻找那些具有特定值的记录。
索引是加速查询主要手段，索引是快速定位数据的技术。
索引是一种特殊的文件（innoDB(事务性数据库的首选引擎)数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。
索引：一种特殊的目录，聚集索引和非聚集索引
聚集索引：如同字典中按照字母查询，我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集引。
非聚集索引：如同字典中按照偏旁来查询某个字，我们把这种目录纯粹是目录，正文纯粹是正文的排序。
主键就是聚集索引，sql server默认是在主键上建立聚集索引的。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效，过多的索引甚至会导致索引碎片。95%的数据库性能问题都可以采用索引技术得到解决。
通常情况下，我们会在每个表中建立一个id列，以区分每条数据，并且这个id列是自动增长的，步长一般都是
1.此时，我们将这个列设为主键，sql server会将此列默认为聚集索引。这样做有好处，就是可以让您的
数据在数据库中按照id进行物理排序，但是意义不是很大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加贵。
聚集索引的优点：迅速缩小查询范围，避免全表扫描。（日期，用户名）
如果你的数据库真的有1000万容量的话，把主键建立在id列上，在网页上的表现就是超时，根本就无法显示
这也是我们摒弃id列作为聚集索引一个重要的因素。
什么时候用？
应该只为最经常查询和最经常排序的数据列建立索引。
只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据。
可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。
并非是在任何字段上简单地建立索引就能提高查询速度
并非把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度。
如果数据很小的话，用聚集索引作为排序要比使用非聚集索引速度快。
日期列不会因为有分秒的输入而减慢查询速度。
不要把SSN或者身份证号码，永远不要使用收工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是
删除整个记录然后从头开始。
通常情况下不要选择用户可编辑的字段作为键，
不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)
索引列不会包含有null值
只有列中包含有null值都讲不会被包含有索引中，复合索引中只有一个列含有null值，那么这一列对于此
复合索引就无效了，所以我们在数据设计时不要让字段默认值为null。
索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert，update，delete，因为更新表
时，mysql不仅要保存数据，还要保存一下索引文件。
一般来说，在where和join中出现的列需要建立索引，但也不完全如此，
但MySQL把同一个数据表里的索引总数限制为16个。
许多sql命令都有一个delay_key_write项，这个选项的作用是暂时限止mysql在该命令每插入一条记录和每
修改一条现有记录之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入、修改完毕之后再进行，
在需要把许多新记录插入某个数据表的场合，delay_key_write选项的作用将非常明显。
建立索引，只针对查询和一些更新和删除的速度，像性别一列，如果表里面有10000行，如果只有一行是男，
这样用索引的话肯定高，如果有9999是男，那么它不如直接扫描，这是选择性。
两种情况下不建议建索引：
第一种是表记录比较少，例如一两千甚至几百记录的表，没必要建索引，让查询做全表扫描就好了。
记录不超过2000可以考虑不建索引，超过2000条可以考虑索引。
第二种索引选择性较低。选择性是指不重复的索引值与表记录数的比例。
如：
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
怎么建立？
在执行create table语句时可以创建索引，也可以单独用create index或alter table来为表添加索引。
1，alter table
alter table用来创建普通索引，unique索引或primary key索引。
alter table table_name add index indexName(column_list)
alter table table_name add unique(column_list)
alter table table_name add primary key(column_list)
其中table_name是要添加索引的表名，column_list指对哪些列进行索引，多列时各列之间用逗号分开，
2，create index
create index可对表增加普通索引或unique 索引。
create index indexName on table_name(column_list)
create unique index indeName on table_name(column_list)
不能用create index语句创建primary key索引。
一个表只能包含一个primary key。因为一个表中不可能具有两个同名的索引。
create index indexName on mytable(username(length)); 如果是char，varchar类型，length可以小于
字段实际长度；如果是blob和text类型，必须指定length。
组合索引
alter table mytable add index name_city_age(name(10),city,age)
这样的索引相对于下面三组索引：
username,city,age; username,city; username
为什么没有city，age这样的组合索引呢？这是因为mysql组合索引最左前缀的结果。
唯一索引
create unique index indexName on mytable(username(length)) 修改表结构
alter mytable add unique indexName on (userName(length)) 创建表的时候直接指定
create table mytable(id int not null,username varchar(16) not null,unique [indexName](username(length)));
在设计数据表的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性，这样就更有效的控制
了对存储数据中的每一行的访问。当你拥有一致的键结构时，很容易找到逻辑缺陷。
删除索引的语法
drop index indexName on mytable;
alter table table_name drop index index_name
alter table table_name drop primary key 因为一个表只可能有有一个primary key索引，因此不需要
指定索引名。如果没有创建primary key索引，但表具有一个或多个unique索引，则mysql将删除第一个unique索引。
查看索引
show index from tabname
show keys from tablename;
使用索引注意：
1，order by中的列是不会使用索引的。
2，通配符%在字符串的开端使得索引无法使用如 name like ‘%zhang‘ 而name like ‘zhang%‘就可以。
3，or会引起全表扫描。
4，in的作用相对于or
如：
select * from table where id in(2,3)
等于
select * from table where id = 2 or id = 3;如果id上有索引，则索引会失效。
5，尽量少用not。
6，exists 和 in 的执行效率是一样的。
7，union并不绝对比or的执行效率高。
如：
select id,name from table where time=‘2012-01-01‘ or age = 22
等于
select id,name from table where time =‘2012-01-01‘ union select id,name from table where age = 22
但是如果or两边的查询条件是一样的话，那么用union则反倒速度差很多。虽然这里union扫描的是索引，而
or扫描的是全表。
如：select * from table where id = 2 or id = 3;
8，字段提取要按照“需多少、提多少”的原则，避免“select *”
9，count(*)不比count(字段)慢
10，order by按聚集索引列排序效率最高,如果按照某个字段进行排序的时候，无论是倒序还是顺序，速度是相当的。
11，charindex
查询a字段中包含bi的所有行
select * from table where a like ‘%bi%‘
等于
select * from table where charindex(‘bi‘,a)>0 这种方法比like的形式速度上要快很多。
12，请选择也业务无关的自增字段作为主键。
这样就会形成一个紧凑的索引结构，近似顺序填满，由于每次插入时也不需要移动已有数据，因此效率很高，
也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证或学号），由于每次插入主键的值近似于随机，因此每次新记录都要被
查到现有索引页的中间某个位置。此时mysql不得不为了将新记录插到合适位置而移动数据，甚至目标页面
可能已经被写到磁盘上而从缓存中清掉，此时也要从磁盘上读回来，这增加了很多开销，同时频繁的移动，
分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过optimize table来重建表
并优化填充页面。
数据库三范式：
第一范式（1NF）数据库表的每一列都是不可分割的基本数据项，无重复的列。
第二范式（2NF）数据库表中必须有有一列为唯一标识（主键）。
第三范式（3NF）属性不依赖于其他非主属性，如：存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。
oracle创建序列
create sequence seq_atable minvalue 1 maxvalue 9999999 start with 1 increment by 1 nocache
创建触发器
create or replace trigger trg_atable before insert on atable for each row begin select seq_atable.nextval into :new.id from dual; end;
插入数据：insert into atable(a) values(‘test‘); 触发器方式
这种也可以。
insert into atable(id,a) values(seq_atable.nextval,‘test‘);
mysql的优化主要分为结构优化和查询优化
1，理论上面索引对顺序是敏感的，但是由于mysql查询优化器会自动调试where子句中条件的顺序。
select * from table1 where name=‘zhangsan‘ and tID > 10000
和执行:
select * from table1 where tID > 10000 and name=‘zhangsan‘
第一条语句是从去全表中查找有几个name为zhangsan的，而后一句仅仅从表的10000条语句中查找。
2，索引文件本身要消耗存储空间的，同时索引会加重插入，删除，修改记录的负担，另外mysql运行时也要消耗资源维护索引，因此索引并不是越多越好。
3，最适合索引的列是出现在where子句中的列，或者链接子句涨指定的列，而不是出现在select关键字后的选择项列表中的列。
4，使用唯一索引，考虑某列中值的分布，对于唯一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。
5，使用短索引，如：有一个char(200)列，如果在前10个或者20个字符内，多数值是唯一的，那么就不要对整个列进行索引。这样能够节省大量索引空间，也可能会使查询更快，较小的索引涉及的磁盘I/O较少，较短的值比较起来更快。
6，利用最左前缀
若表总的某字段出现在select，过滤，排序条件中，为该字段建立索引是值得的。
前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于order by 和group by操作。也不能用于当索引本身包含查询所需全部数据时，不再访问数据文件本身。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
