MySQL索引(一):正确、有效地使用索引
2017-08-04
索引
- 什么是索引?
- 索引分类
- 创建原则
- 使用注意
- 案例
什么是索引?
MySQL官方定义:“Index (索引)”是帮助MySQL高效获取数据的数据结构。“Index (索引)”本质是一种数据结构,存储在磁盘上。MySQL普遍使用B+Tree
作为索引结构。
索引分类
按照索引列的值必须唯一:
- 普通索引 :允许索引列中插入重复值和空值
- 唯一索引 :索引列的值必须唯一,但允许为空值。主键索引是一种特殊的唯一索引,不允许有空值。
按照索引列组成的个数:
- 单列索引:索引只包含单个列,一个表可以有多个单列索引。
- 组合索引:多个字段组合,创建的索引。遵循“最左前缀原则”
其他索引:
- 全文索引(FULLTEXT):在定义索引列上支持值得全文查找。
创建原则
- 2000条以上建议建索引
- 组合索引:最左前缀原则,“选择性”高的列排在前面。
- “选择性”低的列不建议建立索引。
Index Selectivity = Cardinality / #T
- 尽量地扩展原有索引,不建议新建索引
- 字符串尽量使用短索引
使用注意
- 隐式类型转换,无法命中索引,扫描全表(
建议按照字段类型传值
)。 - 参与计算(
函数和表达式
)的列,不走索引。 - “负向查询”不走索引。
- 前导模糊查询不能走索引。如:
like ‘%xxx’
案例
- 隐式类型转换
explain SELECT * from employees where first_name =1234; +----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_first_name | NULL | NULL | NULL | 299556 | 10.00 | Using where | +----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+ 1 row in set, 3 warnings (0.03 sec) EXPLAIN SELECT * FROM salaries where salary ='60929'; +----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | salaries | p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | ref | idx_salary | idx_salary | 4 | const | 63 | 100.00 | NULL | +----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
第一种(int -> varchar):’first_name’字段为‘varchar(14)’类型,传入int时,发生“隐式类型转换“,扫描全表。
第二种(varchar -> int):’salary’字段为‘int’类型,传入varchar时,能走索引。
- 最左前缀原理
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------ | titles | 0 | PRIMARY | 1 | emp_no | A | 431121 | NULL | NULL | | BTREE | | titles | 0 | PRIMARY | 2 | title | A | 431417 | NULL | NULL | | BTREE | | titles | 0 | PRIMARY | 3 | from_date | A | 442344 | NULL | NULL | | BTREE | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
有效索引组合:① emp_no ② emp_no , title ③ emp_no , title , from_date 组合内顺序可以颠倒,mysql查询优化器按索引建立的顺序,“靠左”排序。
- 隔离列、填坑
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
“from_date”索引未用到,中间隔了个“title”,优化方案如下:
SELECT DISTINCT(title) FROM employees.titles; +--------------------+ | title | +--------------------+ | Senior Engineer | | Staff | | Engineer | | Senior Staff | | Assistant Engineer | | Technique Leader | | Manager | +--------------------+ EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager') AND from_date='1986-06-26'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
“填坑”可以提升性能,如果title值过多,用“填坑”不合适,必须建立辅助索引。
- 匹配前缀字符串
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
如果通配符%不出现在开头,则可以用到索引。
- 范围查询
EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
- 查询条件中含有函数或表达式
- 函数
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior'; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
- 表达式
EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000'; +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+