 前言 MySql作为大厂主流数据库存储工具，日常工作中经常遇到查询慢的问题，第一想法都是建立索引，怎样创建高效的索引呢？ 一、优化索引原则 1.1、区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引。判断规则，是长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态。以下长度为6的时候是最佳状态。 mysql> SELECT count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3, count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4, count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5, count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6, count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7 FROM emp; +--------+--------+--------+--------+--------+ | sel3 | sel4 | sel5 | sel6 | sel7 | +--------+--------+--------+--------+--------+ | 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 | +--------+--------+--------+--------+--------+ 1 row in set 1.3、按需查询。查询记录不要使用*，只取需要的字段，尽可能使用索引覆盖。 1.4、避免索引失效。禁止对索引字段使用函数、运算操作。比如字符串转换。 1.5、模糊查询'%value%’使索引无效，会全表搜索。但是使用'value%’可以使用索引。 1.6、建议扩展索引，非必要不要创建新索引。如果表存证a索引，现在需要（a，b）索引，那么只需要修改a索引即可。 二、查询分析器 2.1、优化语句都是在降低rows值，explain输出字段。如图 关键参数 select_type：表示查询中每个select子句的类型（Simple、Primary、Depend SubQuery） possible_keys :指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用 key：key列显示MySQL实际决定使用的键（索引），未走索引是null rows：表示MySQL根据表统计信息及索引选用情况，估算所需要扫描的行数 2.2、优化步骤 先运行查看实际耗时，判断是否真的很慢（注意设置SQL_NO_CACHE）。 高区分度优先策略：where条件单表查，锁定最小返回记录表的条件。 就是查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高。区分度高的字段往前排。 explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） order by limit 形式的sql语句让排序的表优先查 三、适当分库分表 3.1、垂直分库 数据库建立独立模块库，比如商品库Products、订单库Orders。 3.2、垂直分表 假如表中有20多个字段，常用也就5-6个，那么可以把常用字段做为主表，其他的字段作为辅助表。 3.2、分库分表 分库分表在库内分表的基础上，将分的表挪动到不同的主机和数据库上。可以充分的使用其他主机的CPU、内存和IO资源。 |
|
|
