MySQL索引与优化(explain)


一、 创建索引

1.普通索引(最基本的索引,无任何限制)

(1)创建索引

1
CREATE INDEX index_name ON table(column(length))

(2)删除索引

1
DROP INDEX index_name ON table

2.唯一索引(索引列的值必须唯一,但允许有空值)

创建索引:

1
CREATE UNIQUE INDEX indexName ON table(column(length))

3.主键索引(一个表的主键)
1
2
3
4
5
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);
4.组合索引(将多个列组合在一起创建索引)

mysql执行查询中,只会使用到一个索引

最左前缀:意思是使用组合索引的时候,从左到右依次匹配,否则不会使用组合索引。

例如ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);其实我们是建立了三个索引,分别是:单列索引lame,组合索引(lame,fname),组合索引(lame,fname,age),mysql索引的时候只会使用其中一个索引。

所以创建组合索引的时候,尽量把使用频繁的放在左边

二、索引创建的原则和注意事项

  • 1.最适合创建索引的是出现在where子句中的列或是出现在连接子句中的列
  • 2.对字符串类型进行索引的时候,应该指定一个前缀长度,比如索引前多少个字符
  • 3.根据业务情况创建组合索引(比如某个业务需要查询两个列)
  • 4.组合索引遵循前缀原则(最左前缀原则)TODO
  • 5.like查询,%不能在前,可以使用全文检索引擎

    • 例如: where name like ‘%wang%’,查询姓名中有wang的,此时索引不会生效,还是会全表扫描,因为前面有个%,如果是like ‘wang%’这样会使用到索引,但是没有前缀匹配了,如果想达到索引的效果,可以使用全文检索引擎,例如es(Elasticsearch)
  • 6.如果mysql觉得全表扫描比索引扫描快,他会自动放弃使用索引

  • 7.mysql查询只使用一个索引,如果where子句中使用了索引,那么order by中的列是不会使用索引的

  • 8.列中包含null值是不会使用索引的,如果column_name is null还是会使用索引,但是建表的时候尽量设置一个非null的默认值。

explain分析sql语句

现有如下的sql语句EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176;
打印结果如下:

1
2
3
4
5
6
7
8
9
10
id: 1  
select_type: SIMPLE
table: inventory
type: ref
possible_keys: item_id
key: item_id
key_len: 4
ref: const
rows: 1
Extra: Using where
  • 1.key: 指出优化器使用的索引。
  • 2.rows: mysql认为他查询必须要检查的行数,优化器估计值。
  • 3.possible_keys: 支出优化器为查询选定的索引
  • 4.key_len: sql语句的连接条件的键的长度
  • 5.select_type: select使用的类型。
    + simple(简单的select不含union或子查询)、
    + primary(最外面的select)、
    + union(union中第二个或后面的select)、
    + dependent union(union中第二个或后面的select,取决于外面的查询)、
    + union result(union的结果)、 
    + subquery(子查询中第一个select)
    
  • 6.type: 连接类型。system(表仅有一行)、const(表最多有一个匹配行)、eq_ref(对于每个前面的表的行组合,从该表中读取一行)、ref(对于每个来自于前面表的行组合,所有匹配索引值将从这张表中读取)、index_merge(使用了索引合并优化方法)、all(完整的表扫描)
  • 7.ref: 显示使用哪个列或常数与key一起从表中选择行
坚持原创技术分享,您的支持将鼓励我继续创作!