Mysql 索引的目的
索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
Mysql 有哪些索引
- 普通索引
alter table table_name add index index_name (col_name);
- 主键索引
alter table table_name add primary key (col_name);
- 唯一索引
alter table table_name add unique (col_name);
- 组合索引
alter table table_name add index index_name (col_name1, col_name2);
- 全文索引
alter table table_name add fulltext (col_name);
索引失效的情况举例
-一个表中重复的值太少，或者表数据<2000, 索引占用空间，但是不太起作用；
-全表扫描 select * from table where id < 3;
-语句中包含like，%words%； _words%是生效的
-语句中包含or，<>! ，in，not等比较字符(OR 替换成UNION ALL， IN 用Between／Exists 代替)
-语句中包含正则
-组合字段中包含null
删除索引
drop index index_name on table_name;
如何查看mysql中索引被使用的效率
#未被使用到的索引
mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-----------------------------+
| object_schema | object_name | index_name |
+---------------+-----------------+-----------------------------+
| blog | jobs | jobs_queue_index |
| blog | password_resets | password_resets_email_index |
| blog | users | users_email_unique |
| laravel_house | password_resets | password_resets_email_index |
+---------------+-----------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> explain select * from users where id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Explain各列的说明
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
**select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型, ALL < index < range ~ index_merge < ref < eq_ref < const < system
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
key_len 最左前缀匹配 原则, 用到索引的字段的字节加和
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值，原则上 rows 越少越好.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
select_type 表示了查询的类型, 它的常用取值有:
SIMPLE, 表示此查询不包含 UNION 查询或子查询
PRIMARY, 表示此查询是最外层的查询
UNION, 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
SUBQUERY, 子查询中的第一个 SELECT
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果。
Mysql InnoDB和Myisam索引区别
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。InnoDB一定要有主键。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
使用索引的原理
既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。
第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。
另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
| 0.0000 |
+-------------+
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.9313 |
+-------------+
网友评论