mysql索引

       索引是存储引擎用于快速查找记录的一种数据结构，更多请访问数据结构之树（B+树）。通过合理的使用数据库索引可以大大提高系统的访问性能。

一、使用索引的优点：

       1、大大减轻服务器需要扫描的数据量，从而提高数据的检索速度；

       2、帮助服务器避免排序和临时表；

       3、可以将随机I/O变为顺序I/O

二、创建索引的方式：

       1、create index 索引名 on 表名(列名) 

create index idx_user_id on t_user(`user_id`);

       2、alter table 表名 add index 索引名(列名）

alter table t_user add index idx_user_id(`user_id`);

三、删除索引的方式：

       1、drop index 索引名 on 表名

drop index idx_user_id on t_user;

       2、alter table 表名 drop index 索引名

alter table t_user drop index idx_user_id;

四、索引的种类：

       1、普通索引或联合索引：INDEX

       2、主键索引：PRIMARY KEY

       3、唯一索引：UNIQUE

       4、全文索引：FULLTEXT

五、高效索引的设计原则：

       1、索引列中的值不能为空，索引列不能是表达式的一部分，也不能作为函数的参数，否则索引不生效；

       2、前缀索引：选择字符列的中前n个字符作为索引，这样可以使索引更小、更快，大大节约索引空间，从而提高索引效率。但是无法使用前缀索引做order by、group by以及覆盖扫描。前缀索引要选择足够长的前缀以保证高的选择性，同时又不能太长。可以通过如下步骤逐步找到最合适的字符个数，其中table_name为表名，index_column为索引字段名：

       ①查询不重复的数据占比a：

select count(distinct index_column)/count(1) from table_name

       ②确定最接近①中得到的a时的字符数：

select count(distinct left(index_column,1))/count(1),
       count(distinct left(index_column,2))/count(1),
       count(distinct left(index_column,3))/count(1),
       count(distinct left(index_column,4))/count(1),
       count(distinct left(index_column,5))/count(1),
       count(distinct left(index_column,6))/count(1) 
from table_name

       3、选择合适的索引列顺序：在组合索引中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询方式，一般将选择性最高的列放在索引最前列。

       4、如果一个索引（比如组合索引）中包含所有要查询的字段，那么这就是覆盖索引。如果使用了覆盖索引，explain执行计划的Extra中的值为Usign index，这样可以极大提高查询性能。

       5、使用索引排序：在排序操作中如果能使用索引来排序，那么可以极大提高排序的速度。要使排序能用到索引，需要满足以下两个基本条件：

       ①所查询的字段需要包含在索引列中，即满足是覆盖索引；

       ②order by子句中的列顺序要与组合索引中的列顺序一致，并且所有排序列的排序方向（升序/降序）都一样；

       ③特殊地：在满足条件1的前提下，如果where查询条件为索引列的第一列，并且为常量条件，那么排序也能使索引生效；

       ④在满足条件①、②的基础上，多表连接查询时，当order by后的排序字段都是第一个表中的索引列时，那么排序也能用到索引。