• 技术文章 >数据库 >mysql教程

    铁汁,MySQL索引优化规则送你!!

    coldplay.xixicoldplay.xixi2020-10-13 14:06:25转载49

    今天mysql教程栏目为大家介绍MySQL的索引优化规则。

    前言

    索引优化规则

    1、like语句的前导模糊查询不能使用索引

    select * from doc where title like '%XX';   --不能使用索引select * from doc where title like 'XX%';   --非前导模糊查询,可以使用索引复制代码

    2、union、in、or 都能够命中索引,建议使用 in

    1. union能够命中索引,并且MySQL 耗费的 CPU 最少。
    select * from doc where status=1union allselect * from doc where status=2;复制代码
    1. in能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in
    select * from doc where status in (1, 2);复制代码
    1. or 新版的 MySQL 能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or
    select * from doc where status = 1 or status = 2复制代码
    1. 补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区:

    3、负向条件查询不能使用索引

    select * from doc where status != 1 and status != 2;复制代码
    select * from doc where status in (0,3,4);复制代码

    4、联合索引最左前缀原则

    select uid, login_time from user where login_name=? andpasswd=?复制代码
    1. 建立联合索引的时候,区分度最高的字段在最左边
    1. 存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列。
    1. 最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致
    select uid, login_time from user where passwd=? andlogin_name=?复制代码
    1. 假如index(a,b,c), where a=3 and b like 'abc%' and c=4a能用,b能用,c不能用。

    5、不能使用索引中范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效

    select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'复制代码

    6、不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描

    select * from doc where YEAR(create_time) <= '2016';复制代码
    select * from doc where create_time <= '2016-01-01';复制代码
    select * from order where date < = CURDATE();复制代码
    select * from order where date < = '2018-01-2412:00:00';复制代码

    7、强制类型转换会全表扫描

    select * from user where phone=13800001234复制代码
    select * from user where phone='13800001234';复制代码

    8、更新十分频繁、数据区分度不高的列不宜建立索引

    9、利用覆盖索引来进行查询操作,避免回表,减少select * 的使用

    Select uid, login_time from user where login_name=? and passwd=?复制代码

    10、索引不会包含有NULL值的列

    11、is null, is not null无法使用索引

    12、如果有order by、group by的场景,请注意利用索引的有序性

    1. order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。
    1. 如果索引中有范围查找,那么索引有序性无法利用,如 WHERE a>10 ORDER BY b;,索引(a,b)无法排序。

    13、使用短索引(前缀索引)

    14、利用延迟关联或者子查询优化超多分页场景

    selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id;复制代码

    15、如果明确知道只有一条结果返回,limit 1 能够提高效率

    select * from user where login_name=?;复制代码
    select * from user where login_name=? limit 1复制代码

    16、超过三个表最好不要 join

    17、单表索引建议控制在5个以内

    18、SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好

    19、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

    20.创建索引时避免以下错误观念

    1. 索引越多越好,认为需要一个查询就建一个索引。
    2. 宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。
    3. 抵制惟一索引,认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
    4. 过早优化,在不了解系统的情况下就开始优化。

    索引选择性与前缀索引

    Index Selectivity = Cardinality / #T复制代码
    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
    +-------------+| Selectivity |
    +-------------+|      0.0000 |
    +-------------+复制代码
    EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+复制代码
    SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
    +-------------+| Selectivity |
    +-------------+|      0.0042 |
    +-------------+SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
    +-------------+| Selectivity |
    +-------------+|      0.9313 |
    +-------------+复制代码
    SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.7879 |
    +-------------+复制代码
    SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+| Selectivity |
    +-------------+|      0.9007 |
    +-------------+复制代码
    ALTER TABLE employees.employees
    ADD INDEX `first_name_last_name4` (first_name, last_name(4));复制代码
    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------------------------------+
    | Query_ID | Duration   | Query                                                                           |
    +----------+------------+---------------------------------------------------------------------------------+
    |       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    |       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    +----------+------------+---------------------------------------------------------------------------------+复制代码

    总结

    更多相关免费学习推荐:mysql教程(视频)

    以上就是铁汁,MySQL索引优化规则送你!!的详细内容,更多请关注php中文网其它相关文章!

    本文转载于:juejin,如有侵犯,请联系a@php.cn删除
    专题推荐:MySQL 索引优化规则
    上一篇:认识mysql insert into ... select的锁问题 下一篇:mysql如何修改字段名称
    第13期线上培训班

    相关文章推荐

    • mysql怎么修改数据表里一行数据?• mysql如何让外网访问• 认识MySQL的binlog日志• 认识mysql insert into ... select的锁问题

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网