MySql之索引长度

1:查询频繁 2:区分度高  3:长度小  4: 尽量能覆盖常用查询字段.



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


针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.


所以, 要在  区分度 + 长度  两者上,取得一个平衡.


惯用手法: 截取不同长度,并测试其区分度,

[sql]  view plain  copy
  1. <span style="font-size:18px;">select count(distinct left(word,6))/count(*) from dict;   
  2. +---------------------------------------+  
  3. count(distinct left(word,6))/count(*) |  
  4. +---------------------------------------+  
  5. |                                0.9992 |  
  6. +---------------------------------------+</span>  

截取word字段长度,从1开始截取,计算字符前缀没有重复的字符占全部数据的比例



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


3:多列索引
 3.1 多列索引的考虑因素---  
列的查询频率 , 列的区分度, 
以ecshop商城为例, goods表中的cat_id,brand_id,做多列索引
从区分度看,Brand_id区分度更高, 

[sql]  view plain  copy
  1. select count(distinct cat_id) / count(*) from  goods;  
  2. +-----------------------------------+  
  3. count(distinct cat_id) / count(*) |  
  4. +-----------------------------------+  
  5. |                            0.2903 |  
  6. +-----------------------------------+  
  7. 1 row in set (0.00 sec)  
  8.   
  9. mysql> select count(distinct brand_id) / count(*) from  goods;  
  10. +-------------------------------------+  
  11. count(distinct brand_id) / count(*) |  
  12. +-------------------------------------+  
  13. |                              0.3871 |  
  14. +-------------------------------------+  
  15. 1 row in set (0.00 sec)  

但从 商城的实际业务业务看, 顾客一般先选大分类->小分类->品牌,
最终选择 index(cat_id,brand_id)来建立索引
有如下表(innodb引擎), sql语句在笔记中, 
给定日照市,查询子地区, 且查询子地区的功能非常频繁,
如何优化索引及语句?

+------+-----------+------+
| id   | name      | pid  |
+------+-----------+------+
| .... | .... | .... |
| 1584 | 日照市 | 1476 |
| 1586 | 东港区 | 1584 |
| 1587 | 五莲县 | 1584 |
| 1588 | 莒县    | 1584 |
+------+-----------+------+


1: 不加任何索引,自身连接查询

[sql]  view plain  copy
  1. explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: p  
  6.          type: ALL  
  7. possible_keys: NULL  
  8.           keyNULL  
  9.       key_len: NULL  
  10.           ref: NULL  
  11.          rows: 3263  
  12.         Extra: Using where  
  13. *************************** 2. row ***************************  
  14.            id: 1  
  15.   select_type: SIMPLE  
  16.         table: s  
  17.          type: ALL  
  18. possible_keys: NULL  
  19.           keyNULL  
  20.       key_len: NULL  
  21.           ref: NULL  
  22.          rows: 3263  
  23.         Extra: Using where; Using join buffer  
  24. rows in set (0.00 sec)  

2: 给name加索引

[sql]  view plain  copy
  1. explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: p  
  6.          type: ref  
  7. possible_keys: name  
  8.           keyname  
  9.       key_len: 93  
  10.           ref: const  
  11.          rows: 1  
  12.         Extra: Using where  
  13. *************************** 2. row ***************************  
  14.            id: 1  
  15.   select_type: SIMPLE  
  16.         table: s  
  17.          type: ALL  
  18. possible_keys: NULL  
  19.           keyNULL  
  20.       key_len: NULL  
  21.           ref: NULL  
  22.          rows: 3243  
  23.         Extra: Using where; Using join buffer  
  24. rows in set (0.00 sec)  

3: 在Pid上也加索引

[sql]  view plain  copy
  1. explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: p  
  6.          type: ref  
  7. possible_keys: name  
  8.           keyname  
  9.       key_len: 93  
  10.           ref: const  
  11.          rows: 1  
  12.         Extra: Using where  
  13. *************************** 2. row ***************************  
  14.            id: 1  
  15.   select_type: SIMPLE  
  16.         table: s  
  17.          type: ref  
  18. possible_keys: pid  
  19.           key: pid  
  20.       key_len: 5  
  21.           ref: big_data.p.id  
  22.          rows: 4  
  23.         Extra: Using where  
  24. rows in set (0.00 sec)  
### MySQL 唯一索引的最大长度限制 在 MySQL 中,唯一索引(`UNIQUE INDEX`)的长度受到多个因素的影响。默认情况下,索引键前缀长度的限制为 767 字节[^1]。这意味着如果尝试在一个 `TEXT` 或 `VARCHAR` 列上创建超过此限制的列前缀索引,则可能会遇到错误。 当启用配置选项 `innodb_large_prefix` 后,在支持该功能的情况下(例如表使用的是动态或压缩行格式),索引键前缀长度可以提升到最大 3072 字节[^1]。这适用于基于 InnoDB 存储引擎的表结构设计。 对于字符集的选择也会影响实际可用的空间大小。比如采用 `utf8mb3` 编码时,单个字符可能占用最多三个字节;而如果是更广泛的编码方式如 `utf8mb4` ,则每个字符理论上会消耗四个字节存储空间[^2]。因此,在定义字段宽度或者指定用于构建索引部分字符串截取位置时需特别注意这些细节差异带来的影响。 关于设置唯一索引的具体方法如下: #### 设置唯一索引的方法 可以通过修改数据库参数来调整允许使用的最长索引尺寸。具体操作包括但不限于开启前述提到过的特性开关——即通过更改服务器全局变量` innodb_file_format=DYNAMIC` 及其配套属性值实现更大范围内的灵活性控制[^3] 。另外还需确认当前版本是否完全兼容所期望的功能表现形式因为不同发行版之间可能存在细微差别甚至缺失某些高级选项的支持情况。 下面给出一段简单的 SQL 脚本示例展示如何正确地应用上述理论知识完成相应任务目标: ```sql -- 修改my.cnf文件并重启服务生效 [mysqld] innodb_file_per_table=ON innodb_file_format=DYNAMIC innodb_large_prefix=ON SET GLOBAL innodb_strict_mode = OFF; ALTER TABLE your_table_name MODIFY COLUMN varchar_column VARCHAR(191); -- 如果使用 utf8mb4 需要减少原始varchar长度以适应新的byte限制条件 CREATE UNIQUE INDEX idx_unique ON your_table_name(varchar_column); ``` 以上脚本首先确保环境满足大前缀需求,接着适当调节涉及变长数据类型的声明规格最后实施新增约束逻辑过程。 ### 注意事项 需要注意的是尽管技术层面提供了扩展能力但仍建议开发者遵循最佳实践原则合理规划模式避免不必要的复杂度引入潜在风险隐患之中去。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值