MySQL优化,索引和索引的使用和检测索引是否有效
来源:互联网 发布:福建师范大学软件学院 编辑:程序博客网 时间:2024/06/06 02:59
在哪些列上添加索引比较好:
1.比较频繁的作为查询的字段
2.唯一性太差的字段不适合加索引,要找唯一性比较好的
3.更新太频繁的字段不适合做索引
4.不会出现在where中的 不应该建立索引
查询某表的所有索引:
show index from books;
索引的类型:
主键索引:primary key,主键自动成为索引
唯一索引:unique 是自动成为索引的,又有索引,又有唯一性
index:普通索引
fulltext:全文索引,用来分词例如:select * from article where content like ‘%Java%’;但是值得注意的是,中文使用全文索引很少,外语使用全文索引比较多。中文一般使用sphinx+中文分词,只有MYISAM才支持
复合索引:多列合在一起的索引 create index on books(title,price)
如何创建索引:
1.unique和fulltext和index 使用 create unique index 索引名 on books(列名)
2.alter table books add unique index 索引名称(列名);
如果要添加主键索引:
3.alter table books add primary key (id);
只有alter table 可以建立无名称的索引,名称默认为表名。
如何删除索引
1.drop index 索引名 on 表名
2.alter table 表明 drop index 索引名
3.主键索引:alter table 表名 drop primarary key
删除未命名的索引:(索引名就是列名),但是如果有索引名,必须按照索引名来删除
显示索引:
show indexes from 表名;
show keys from 表名
describe 表名;=desc 表名;(普通索引显示成mul)
注意:
1.当查询时where使用了组合索引(多列索引)中的左边的列就自动使用了索引,右边的不会使用索引。
但是查的时候不管使用了多列索引的哪一个(select id from books)就会使用索引。
mysql> explain select * from dept \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dept type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: NULL1 row in set (0.00 sec)mysql> explain select * from dept where deptno=1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dept type: refpossible_keys: aa key: aa key_len: 3 ref: const rows: 1 Extra: NULL1 row in set (0.00 sec)mysql> explain select * from dept where dname='1' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dept type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using where1 row in set (0.00 sec)2.当使用索引查询的时候 ‘%a’不会使用索引,使用‘a%‘会使用索引
mysql> explain select * from dept where deptno like 'a%';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | dept | ALL | aa | NULL | NULL | NULL | 10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from dept where deptno like '%a';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | dept | ALL | NULL | NULL | NULL | NULL | 10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)
3.如果条件中带有or 即使条件带了索引也不会使用(不是where id=1 or id =2这种),但是使用and 不受影响,注意有一个左列索引条件or右列索引条件 没有用到索引,但是possible key 用到了。所以要少用or
mysql> explain select * from dept where deptno=1;+----+-------------+-------+------+---------------+------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+-------+| 1 | SIMPLE | dept | ref | aa | aa | 3 | const | 1 | NULL |+----+-------------+-------+------+---------------+------+---------+-------+------+-------+1 row in set (0.00 sec)mysql> explain select * from dept where deptno=1 or deptno =2;+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+| 1 | SIMPLE | dept | range | aa | aa | 3 | NULL | 2 | Using index condition |+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+1 row in set (0.00 sec)mysql> explain select * from dept where deptno=1 or dname ='aaa';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | dept | ALL | aa | NULL | NULL | NULL | 10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from dept where deptno=1 or 1;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | dept | ALL | NULL | NULL | NULL | NULL | 10 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)mysql> explain select * from dept where deptno=1 and 1;+----+-------------+-------+------+---------------+------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+-------+| 1 | SIMPLE | dept | ref | aa | aa | 3 | const | 1 | NULL |+----+-------------+-------+------+---------------+------+---------+-------+------+-------+1 row in set (0.00 sec)
4.多列索引如果不是使用的第一列的条件就不会使用索引(刚才证明了)
5.查询查询%开头的不会使用索引
6.如果列类型是字符串 那么一定要把字符串引用起来才会使用索引(如图)
mysql> desc dept;+--------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+-------+| deptno | mediumint(8) unsigned | NO | MUL | 0 | || dname | varchar(20) | NO | | | || loc | varchar(13) | NO | MUL | | |+--------+-----------------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> explain select * from dept where loc ="aaa";+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+| 1 | SIMPLE | dept | ref | bb | bb | 41 | const | 1 | Using index condition |+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+1 row in set (0.00 sec)mysql> explain select * from dept where loc =123;+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | dept | ALL | bb | NULL | NULL | NULL | 10 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)如图所示:
7.如果估计mysql在使用全表扫描的时候比使用索引还要快 那就不使用索引
如何检测索引是否有效:
show status like ‘handler_read’,
handler_read_key 越大越好
handler_red_rnd_text越小越好
mysql> show status like 'handler_read%';+-----------------------+-----------+| Variable_name | Value |+-----------------------+-----------+| Handler_read_first | 1 || Handler_read_key | 17 || Handler_read_last | 0 || Handler_read_next | 13 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 126000105 |+-----------------------+-----------+7 rows in set (0.00 sec)注:重启控制台就会清空这些。
还是看视频学习比较快!
- MySQL优化,索引和索引的使用和检测索引是否有效
- Mysql索引的设计、使用和优化
- Mysql索引的使用和性能优化
- MYSQL索引无效和索引有效的详细介绍
- MYSQL索引无效和索引有效的详细介绍
- Mysql有效索引和无效索引的介绍
- MYSQL索引无效和索引有效的详细情况
- MYSQL索引无效和索引有效的详细介绍
- MYSQL索引无效和索引有效的详细介绍
- Mysql有效索引和无效索引的介绍
- mysql 索引和优化
- MySQL-索引和优化
- MySQL索引分析和优化 什么是索引?
- MySQL索引分析和优化索引分析
- MySQL中复合索引使用和优化
- mysql索引使用策略和优化
- Mysql数据库的索引分析和优化
- Mysql数据库的索引分析和优化
- Communications link failure
- redis的持久化--快照持久化(SNAPSHOTTING)
- sort
- Android启动定时任务
- spring cloud+dotnet core搭建微服务架构
- MySQL优化,索引和索引的使用和检测索引是否有效
- Shader Alpha混合
- 关于NanoPi NEO(全志H3)平台下的LEDE(openWRT)系统的编译(分色排版)(不知道怎么烧录)
- MySQL主从复制与主主复制
- 诸子百家
- 当css样式是绝对定位时,让DIV居中显示
- Ubuntu下关于E: Sub-process /usr/bin/dpkg returned an error code的问题
- Java中ArrayList和LinkedList效率
- 数字图像处理成长之路13:SIFT之极值点(子像元插值)