mysql-索引

1. 索引

mysql-索引

1.1创建索引方式

方式1：

• create 索引类型 索引名称 on 表(字段名)
• 单值索引
  • create index dept_index on tb(dept);
• 唯一索引
  • create unique index name_index on tb(name);
• 组合索引
  • create index dept_name_index on tab(dept,name);

方式二

• alter table 表名 add index 索引名称(字段名称)
• 单值
  • alter table tb add inex dept_index(dept);

1.2 删除 查看索引

• alter table 表名称 drop index 索引名称;
• drop index index_name on tableName;

查询索引

• show index from 表名
• show index from tb;

2. 哪些情况适合添加 index

1. 字段数值具有唯一性

2. 频繁作为where 的条件的字段

3. 经常 group by order by 的列

* 如果查询的时候同时又 guoup by order by 可以给这两个字段添加添加联合索引
* 创建联合索引(student_id, create_time)

4. UPDATE、DELETE 的 WHERE 条件列

5. DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项

多表join添加索引原则：左连接在右表的连接字段加索引，右连接在左表的连接字段加索引。

• join的优化：
  • 1、小表驱动大表；left join的小表放到左边；因此这里使用class驱动book再是phone ；
  • 2、尽可能减少join语句中的NestedLoop的循环总次数；
  • 3、优先优化NestedLoop的内层循环；
  • 4、保证join语句中被驱动表上join条件字段已经被索引（key字段非NULL）；
  • 5、当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；修改my.conf文件

7. 使用字符串的前缀创建索引 指定字符串索引长度

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

create table shop(
	address varchar(120) not null
); 

alter table shop add index(address(12));

取多长呢？ 区分度 越接近1 越好

count(distinct left(列名, 索引长度))/count(*)



select count(distinct left(address, 10 )) / count(*) as sub10, -- 截取前 10 个字符的选择度
count(distinct left(address, 15 )) / count(*) as sub11, -- 截取前 15 个字符的选择度
count(distinct left(address, 20 )) / count(*) as sub12, -- 截取前 20 个字符的选择度
count(distinct left(address, 25 )) / count(*) as sub13 -- 截取前 25 个字符的选择度
from shop;

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

8. 区分度高的适合做索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展:联合索引把区分度高(散列性高)的列放在前面。

9.使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

3. 哪些情况不适合加索引