MySQL——如何截取合适的左前缀索引长度

1、 理想的索引

①:查询频繁 :②区分度高 ③:长度小 ④: 尽量能覆盖常用查询字段.

注意: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).

2、针对列中的值,从左往右截取部分,来建索引

①: 截的越短, 重复度越高,区分度越小, 索引效果越不好

②: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.

所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
惯用手法: 截取不同长度,并测试其区分度,

mysql> select count(distinct left(word,6))/count(*) from dict;
+---------------------------------------+
| count(distinct left(word,6))/count(*) |
+---------------------------------------+
|                                0.9992 |
+---------------------------------------+
1 row in set (0.30 sec)

捕获.PNG

对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.

3、对于左前缀不易区分的列 ,建立索引的技巧

如 url列

http://www.baidu.com

http://www.zixue.it

列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决

① 把列内容倒过来存储,并建立索引

Moc.udiab.www//:ptth

Ti.euxiz.www//://ptth

这样左前缀区分度大,

② 伪hash索引效果

同时存 url_hash列

典型问题

捕获11.PNG

答：问题出在两个方面：①建表时使用的innodb存储引擎2②varbinary(3000),因为innodb，只查询主键id时，是聚簇索引，造成大量的页分裂，所以速度慢。解决：改为myisam存储引擎

笔记（可忽略）

优化上不要脱离实际业务逻辑，独立索引没有实际意义，同时建立多个独立索引，仅有一个索引有效。应该建立复合索引

mysql 命令：

1、清除缓存：reset query cache；

2、在某列上增加索引：alter table goods add index price(shop_price);

3、使profiling生效：set profiling = 1

4、查看语句执行效率：show profiles；

5、查询语句执行效果（例如：查看索引使用情况）：explain + 查询语句

6、删除索引：alter table goods drop index price；

7、添加复合索引：alter table goods add index cat_price(cat_id,shop_price);

疑问：用复合索引一定会快吗？

myisam 都是指向磁盘的，轻量级的

innodb 直接在主键索引上存储行数据
如何建立高性能索引：
首先分析这是什么表，是聚簇索引还是非聚簇索引，能否索引覆盖，是否有常用的几个列共同出现常用查询，则建立联合查询，索引的长度建立多少