高中生自己做 网站crm免费版

当前位置： 首页 > news >正文

高中生自己做 网站,crm免费版,赣州人才网站,嘉兴网站建设书生商友1、简单的示例展示 在MySQL中#xff0c;LIKE查询可以通过一些方法来使得LIKE查询能够使用索引。以下是一些可以使用的方法#xff1a; 使用前导通配符#xff08;%#xff09;#xff0c;但确保它紧跟着一个固定的字符。 避免使用后置通配符#xff08;%#xff09;LIKE查询可以通过一些方法来使得LIKE查询能够使用索引。以下是一些可以使用的方法 使用前导通配符%但确保它紧跟着一个固定的字符。 避免使用后置通配符%只在查询的末尾使用。 使用COLLATE来控制字符串比较的行为使得查询能够使用索引。
下面是一个简单的例子演示如何使用LIKE查询并且使索引有效 – 假设我们有一个表 users有一个索引在 name 字段上 CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(255) );– 创建索引 CREATE INDEX idx_name ON users(name);– 使用 LIKE 查询并且利用索引进行查询的例子 – 使用前导通配符确保它紧跟着一个固定的字符 SELECT * FROM users WHERE name LIKE A%; – 使用索引– 避免使用后置通配符 SELECT * FROM users WHERE name LIKE %A; – 不使用索引– 使用 COLLATE 来确保比较符合特定的语言或字符集规则 SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE %A%; – 使用索引在实际应用中你需要根据你的数据库表结构、查询模式和数据分布来决定是否可以使用LIKE查询并且使索引有效。如果LIKE查询不能使用索引可以考虑使用全文搜索功能或者其他查询优化技巧。 2、实验演示是否能正确使用索引 2.1、表及数据准备 准备两张表 t_departments 和 t_deptlist

(root192.168.80.85)[superdb] desc t_departments;

| Field | Type | Null | Key | Default | Extra |

| DEPARTMENT_ID | int | NO | PRI | NULL | | | DEPARTMENT_NAME | varchar(30) | YES | | NULL | | | MANAGER_ID | int | YES | | NULL | |

| LOCATION_ID | int | YES | MUL | NULL | |

4 rows in set (0.00 sec)(root192.168.80.85)[superdb] create table t_deptlist as select DEPARTMENT_ID,DEPARTMENT_NAME from t_departments; Query OK, 29 rows affected (0.09 sec)

Records: 29 Duplicates: 0 Warnings: 0(root192.168.80.85)[superdb] desc t_deptlist;

| Field | Type | Null | Key | Default | Extra |

| DEPARTMENT_ID | int | NO | | NULL | |

| DEPARTMENT_NAME | varchar(30) | YES | | NULL | |

2 rows in set (0.00 sec)(root192.168.80.85)[superdb] alter table t_deptlist add constraint pk_t_deptlist_id primary key(DEPARTMENT_ID); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0(root192.168.80.85)[superdb] create index idx_t_deptlist_department_name on t_deptlist(department_name); Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0(root192.168.80.85)[superdb] show index from t_departments;

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

| t_departments | 0 | PRIMARY | 1 | DEPARTMENT_ID | A | 29 | NULL | NULL | | BTREE | | | YES | NULL |

| t_departments | 1 | idx_t_department_name | 1 | DEPARTMENT_NAME | A | 29 | NULL | NULL | YES | BTREE | | | YES | NULL |

2 rows in set (0.00 sec)(root192.168.80.85)[superdb] show index from t_deptlist;

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

| t_deptlist | 0 | PRIMARY | 1 | DEPARTMENT_ID | A | 29 | NULL | NULL | | BTREE | | | YES | NULL |

| t_deptlist | 1 | idx_t_deptlist_department_name | 1 | DEPARTMENT_NAME | A | 29 | NULL | NULL | YES | BTREE | | | YES | NULL |

2 rows in set (0.00 sec) 表t_departments有多个字段列其中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段其它是非索引字段列 表t_deptlist有两个字段其中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段

2.2、 执行 where DEPARTMENT_NAME LIKE ‘Sales’ (root192.168.80.85)[superdb] explain select * from t_departments where DEPARTMENT_NAME LIKE Sales;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_departments | NULL | range | idx_t_department_name | idx_t_department_name | 123 | NULL | 1 | 100.00 | Using index condition |

1 row in set, 1 warning (0.00 sec)(root192.168.80.85)[superdb] explain select * from t_deptlist where DEPARTMENT_NAME LIKE Sales;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_deptlist | NULL | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123 | NULL | 1 | 100.00 | Using where; Using index |

1 row in set, 1 warning (0.01 sec) 执行计划查看发现选择扫描二级索引index_name,表t_departments有多个字段列的行计划中的 ExtraUsing index condition 使用了索引下推功能。MySQL5.6 之后增加一个索引下推功能可以在索引遍历过程中对索引中包含的字段先做判断在存储引擎层直接过滤掉不满足条件的记录后再返回给 MySQL Server 层减少回表次数从而提升了性能。 2.3、 执行 where DEPARTMENT_NAME LIKE ‘Sa%’

(root192.168.80.85)[superdb] explain select * from t_departments where DEPARTMENT_NAME LIKE Sa%;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_departments | NULL | range | idx_t_department_name | idx_t_department_name | 123 | NULL | 1 | 100.00 | Using index condition |

1 row in set, 1 warning (0.00 sec)(root192.168.80.85)[superdb] explain select * from t_deptlist where DEPARTMENT_NAME LIKE Sa%;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_deptlist | NULL | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123 | NULL | 1 | 100.00 | Using where; Using index |

1 row in set, 1 warning (0.00 sec) 执行计划查看发现选择扫描二级索引index_name 2.4、 执行 where DEPARTMENT_NAME LIKE ‘%ale%’

(root192.168.80.85)[superdb] explain select * from t_departments where DEPARTMENT_NAME LIKE %ale%;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_departments | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 11.11 | Using where |

1 row in set, 1 warning (0.00 sec)(root192.168.80.85)[superdb] explain select * from t_deptlist where DEPARTMENT_NAME LIKE %ale%;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_deptlist | NULL | index | NULL | idx_t_deptlist_department_name | 123 | NULL | 29 | 11.11 | Using where; Using index |

1 row in set, 1 warning (0.00 sec)表t_departments有多个字段列的执行计划的结果 type ALL代表了全表扫描。 表t_deptlist 有两个字段列的执行计划的结果中可以看到 keyidx_t_deptlist_department_name 也就是说用上了二级索引而且从 Extra 里的 Using index 说明用上了覆盖索引。 2.5、 执行 where DEPARTMENT_NAME LIKE ‘%ale’

(root192.168.80.85)[superdb] explain select * from t_departments where DEPARTMENT_NAME LIKE %ale;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_departments | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 11.11 | Using where |

1 row in set, 1 warning (0.00 sec)(root192.168.80.85)[superdb] explain select * from t_deptlist where DEPARTMENT_NAME LIKE %ale;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | t_deptlist | NULL | index | NULL | idx_t_deptlist_department_name | 123 | NULL | 29 | 11.11 | Using where; Using index |

1 row in set, 1 warning (0.00 sec) 表t_departments有多个字段列的执行计划的结果 type ALL代表了全表扫描。 表t_deptlist 有两个字段列的执行计划的结果中可以看到 keyidx_t_deptlist_department_name 也就是说用上了二级索引而且从 Extra 里的 Using index 说明用上了覆盖索引。 和上一个LIKE ‘%ale%’ 一样的结果。 3、为什么表t_deptlist where department_name LIKE ‘%ale’ 和 LIKE %ale%用上了二级索引 首先这张表的字段没有「非索引」字段所以 SELECT * 相当于 SELECT DEPARTMENT_IDDEPARTMENT_NAME这个查询的数据都在二级索引的 B 树因为二级索引idx_t_deptlist_department_name 的 B 树的叶子节点包含「索引值主键值」所以查二级索引的 B 树就能查到全部结果了这个就是覆盖索引。 从执行计划里的 type 是 index这代表着是通过全扫描二级索引的 B 树的方式查询到数据的也就是遍历了整颗索引树。 而 LIKE Sales’和LIKE Sa%查询语句的执行计划中 type 是 range表示对索引列DEPARTMENT_NAME进行范围查询也就是利用了索引树的有序性的特点通过查询比较的方式快速定位到了数据行。 所以typerange 的查询效率会比 typeindex 的高一些。 4、为什么选择全扫描二级索引树而不扫描聚簇索引树呢 因为表t_deptlist 二级索引idx_t_deptlist_department_name 的记录是「索引列主键值」而聚簇索引记录的东西会更多比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的非索引列。 再加上表t_deptlist 只有两个字段列DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段因此 SELECT * 相当于 SELECT DEPARTMENT_IDDEPARTMENT_NAME 不用执行回表操作。 所以 MySQL 优化器认为直接遍历二级索引树要比遍历聚簇索引树的成本要小的多因此 MySQL 优化器选择了「全扫描二级索引树」的方式查询数据。 5、数据表t_departments 多了非索引字段执行同样的查询语句为什么是全表扫描呢 多了其他非索引字段后select * from t_departments where DEPARTMENT_NAME LIKE ‘%ale’ OR DEPARTMENT_NAME LIKE ‘%ale%’ ; 要查询的数据就不能只在二级索引树里找了得需要回表操作找到主键值才能完成查询的工作再加上是左模糊匹配无法利用索引树的有序性来快速定位数据所以得在二级索引树逐一遍历获取主键值后再到聚簇索引树检索到对应的数据行这样执行成本就会高了。 所以优化器认为上面这样的查询过程的成本实在太高了所以直接选择全表扫描的方式来查询数据。 如果数据库表中的字段只有主键二级索引那么即使使用了左模糊匹配或左右模糊匹配也不会走全表扫描typeall而是走全扫描二级索引树(typeindex)。