小贝_mysql索引学习与优化_MySQL
- WBOYOriginal
- 2016-06-01 12:59:03949browse
mysql索引与优化
简要:
一、索引是什么
二、索引类型及使用语法
三、全文索引说明
一、索引是什么
1、以书的目录为例，通过查看目录，再找到对应的内容。因此，索引就是给数据加上了’目录’，便于快速找到数据
2、索引的作用:
好处: 加快了查询速度
坏处: a、降低了增删改的速度
b、增大了表的文件大小(索引文件甚至可能比数据文件还大)
案例: 设有某个表15列，存在10列上有索引，共500w行数据，如何快速导入?
答: 1、把空表的索引全部删除 2、导入数据 3、数据导入完毕后再建立索引
3、索引算法
设有N条随机记录，不用索引，平均查找N/2次，用了索引呢？
3.1、二叉树索引对应次数为log2N次
说明例子，数据1,2,3,4,5,6,7，以中间值4为分界点
|
查找3需要多少次？
由于32，因此在以2为根节点的右边。结果需要2次
3.2、哈希索引，理论上为1次
说明例子，数据1,2,3,4,5,6,7
|
查找3需要多少次?
先hash下，得到005，这样就找到了。刚好1次。
hash的不足:
a、浪费空间，因为hash的值不连续。
b、hash要求高，确保每个值的hash值不同
4、索引的使用原则
a、不过度索引
b、索引条件列(where后面最频繁的条件比较适宜索引)
c、索引散列值，过于集中的值不要索引(如: 性别)
5、如何看表结构
5.1、存储引擎为myisam
frm为表结构、MYD为数据文件、MYI为索引文件
5.2、存储引擎为innodb
frm为表结构、ibd为数据文件和索引文件
二、索引类型及使用语法
1、类型
a、普通索引(index): 仅仅是加快查询速度
b、唯一索引(unique index): 行上的值不能重复
c、主键索引(primary key): 不能重复
d、全文索引(fulltext index):
唯一索引和主键索引的关系:
主键必唯一，但是唯一索引不一定是主键;一张表上只能有一个主键，但是可以有一个或多个唯一索引
2、如何查看表中的索引
3、建立索引
3.1、对已经存在的表建立索引
语法: alter table 表名 add index/uniqueindex/fulltext index/primary key [索引名](列名) (备注:索引名可选，不指定则与列名相同)
表结构:
create table m( id int, emailvarchar(30),tel char(11), intro text)engine=myisam charset=utf8;
a、给tel列建立普通索引
（备注: 指定索引名与列名相同）
b、给email列加上唯一索引
c、给intro列加上全文索引
d、给id列加上主键索引
e、加上多列组合索引
(备注: 这个普通索引m作用在列email和tel列上)
错误点:
错误原因: 没有指定该索引应用在那个列上。
3.2、建立新表时，指定索引
create table m(id int primary keyauto_increment, email varchar(30), tel char(11), intro text, index(tel), uniqueindex(email), fulltext index(intro) )engine=myisam charset=utf8;
4、删除索引
4.1、删除普通索引/唯一索引/全文索引
4.2、删除主键索引
如果主键列本身就是自增的，则删除时会报错
这个情况，应该先修改列的自增属性。
三、全文索引
全文索引在mysql的默认情况下，对于中文意义不大
因为英文有空格，标点符号来拆成单词，进而对单词进行索引。
而对于中文，没有空格来隔开单词。mysql无法识别每个中文词。
用法: match(全文索引名) against(‘keyword’);