一、索引原理
1. 使用索引为什么会快?
btree类型的索引,就是使用的二分查找法,肯定快啊,算法复杂度是 log2N ,也就是说16条数据查4次,32条数据查5次,64条数据查6次....依次类推。
btree 方式检索,算法复杂度: log2N
2. 索引的代价
- 磁盘占用空间增多
- 对dml(update delete insert)语句的效率影响
二、索引类型
简述mysql四种索引的区别:
-
PRIMARY 索引:在主键上自动创建
-
UNIQUE 索引: 只要是 UNIQUE 就是 Unique 索引。(只能在字段内容不重复的情况下,才能创建唯一索引)
-
INDEX 索引:普通索引
-
FULLTEXT :只在 MYISAM 存储引擎支持,目的是全文索引,在内容系统中用的多,在全英文网站用多(英文词独立). 中文数据不常用,意义不大,国内全文索引通常使用 sphinx 来完成,全文索引只能在 char 、varchar 、text 字段创建
三、关于增加索引中的原则
- 索引列数最好在1-2
- 根据where条件创建索引,select的字段不要包含什么索引(用*号)
- 不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中
- 对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高
- 不要试图分别基于单个列建立多个单列索引(因为虽然有多个单列索引,但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引)
四、哪些列上适合添加索引
- 较频繁的作为查询条件字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合创建索引
- 不会出现在WHERE子句中的字段不该创建索引
五、索引常用操作
1. 添加PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2. 添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (`column`)
3. 添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4. 添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT (`column`)
5. 添加多列索引
注意:当搜索时候需要多个条件作为条件是的时候使用多列索引,搜索条件可为:column1;column1,column2;column1,column2,column3;
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`,`column2`,`column3` )
6. 查看索引
mysql> show index from tblname;
7.删除索引
DROP INDEX index_name ON table_name
六、如何让索引生效
查询要使用索引最重要的条件是查询条件中需要使用索引。
可以通过 explain ,查看 SQL 中的索引是否生效:
explain select * from ip;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | ip | ALL | NULL | NULL | NULL | NULL | 400 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
select_type |
table |
type |
possible_keys |
key |
key_len |
rows |
Extra |
|