索引种类
主键索引: 加速查找,不能为空,不能重复 (不可以有null)
普通索引: 仅加速查找
唯一索引: 加速查找,不能重复 (可以有null)
组合索引(联合索引): 联合主键索引 和 组合唯一索引
全文索引: 对文本的内容进行分词,进行搜索 ,对于大量搜索应该使用 solor
索引方法 : hash索引 / btree索引
mysql索引类型
normal : 表示普通索引
unique : 表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique
fulltext : 表示全文搜索的索引,
FULLTEXT用于搜索很长一篇文章的时候,效果最好
用在比较短的文本,如果就一两行字的,普通的INDEX也可以
Spatial 空间索引只有MyISAM引擎支持,并且支持的不好,可以忽略
过多的使用索引将会造成滥用,因此索引也会有它的缺点:
不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
如果对字符串列进行索引,应该指定一个前缀长度,使用短索引,可节省大量索引空间,提升查询速度。
不适合走索引的场景.
唯一值少的列上不适合建立索引或者建立索引效率低。例如:性别
小表可以不建立索引,100条记录。
对于数据仓库,大量全表扫描的情况,建索引反而会慢
索引常用命令:
-- 查看表的唯一值数量:
select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;
-- 查看索引 user为表名
show index from user;
-- explain 检查语句索引执行情况
explain select * from appweb_book where title='Zabbix'\G
-- 创建普通索引
CREATE INDEX index_email on user(email);
CREATE INDEX index_email on user(email(8)); #表示使用email的前8个字符作为索引()
-- 当一个字段的前N个值接近达到唯一时,就可以使用前N个字符建索引,可以降低读取和更新维护索引消耗的系统资源
SELECT * FROM `user` WHERE email LIKE "lin521%" -- 走索引
SELECT * FROM `user` WHERE email LIKE "%521%" -- 不走索引
-- 创建唯一索引
CREATE UNIQUE INDEX index_email on user(email);
-- 使用alter也可以******************
alter table userinfo add unique index index_name(name);
-- 创建组合索引 (最左前缀匹配)
CREATE INDEX index_name_email on user(name,email);
-- name and emial 走索引
-- name or emial 不走索引
-- name="lin" 走索引(name在前)
-- email="lin123@qq.com" 不走走索引
-- 创建组合唯一索引
create UNIQUE INDEX `index_name_email` on `user`(`name`, `email`) USING BTREE COMMENT '联合唯一索引';
-- 表创建后添加主键索引
alter table 表名 add primary key(列名);
-- 删除索引
DROP INDEX index_email on user;
alter table userinfo drop index user_tel_email; -- 使用alter也可以******************
-- 覆盖索引(名词,不是真实索引) 在索引文件中可以直接获取数据
SELECT email FROM `user` WHERE email LIKE "lin521@qq.com"
-- 索引合并(名词,把多个单列索引合并使用)
CREATE INDEX index_email on user(email);
CREATE INDEX index_name on user(name);
SELECT * from `user` WHERE email="lin7@qq.com" or `name`="lin4567";
SELECT * from `user` WHERE email="lin7@qq.com" or `id`="4567";
-- 组合索引效率 大于 索引合并
-- 但是索引合并每个单列都可以作为索引来使用,
-- 而组合索引只有第一列可以单独使用,其余的单列不能使用索引, (最左前缀匹配)
索引需要注意的地方
-- like '%xx'
select * from user where name like '%521'; -- 不走索引
-- 使用函数
select * from user where reverse(name) = 'wupeiqi'; -- 不走索引
-- or (id和email是索引)
select * from user where id = 1 or name = 'lin541'; -- 不走索引
select * from user where id = 1 or email="lin521@qq.com"; --走索引
select * from user where id = 1 or email="lin521@qq.com" and name = 'lin524'; --走索引
-- and 只有有一个有索引,则走索引
select * from user where email="lin521@qq.com" and name = 'lin524'; -- 走索引
-- 类型不一致 (id和email是索引)
如果列是字符串类型,传入条件是必须用引号引起来,不然...
select * from user where email = 999; -- 不走索引(类型不一致)
select * from user where email = 'lin531@qq.com'; --走索引
-- !=
select * from user where name != 'lin541'
特别的:如果是主键,则还是会走索引
select * from user where id != 123
-- >
select * from user where name > 'lin541'
特别的:如果是主键或索引是整数类型,则还是会走索引
select * from user where id > 123
select * from user where num > 123
-- order by
当根据索引排序时候,选择的映射如果不是索引,则不走索引
select name from user order by email desc; -- 不走索引
特别的:如果对主键排序,则还是走索引:
select * from user order by id desc;
- 组合索引最左前缀
如果组合索引为:(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
SELECT `sname` FROM `stu` WHERE `age`+10=30; # 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; # 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'金蝶%' # 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%金蝶%" # 不走索引
# 正则表达式不使用索引,所以这就是为什么在SQL中很难看到regexp关键字的原因
# 字符串与数字比较不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" # 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 # 不走索引,同样也是使用了函数运算
select * from dept where dname='xxx' or loc='xx' or deptno=45 # 如果条件中有or,即使其中有条件带索引也不会使用,要求使用的所有字段,都必须建立索引,尽量避免使用or关键字
其他注意事项
# 避免使用select *
# count(1)或count(列,最好是id或者前几列) 代替 count(*) MYSQL中性能基本一样,其他数据库中不详
# 创建表时尽量时 char 代替 varchar
# 表的字段顺序固定长度的字段优先
# 组合索引代替多个单列索引(经常使用多个条件查询时)
# 尽量使用短索引 CREATE INDEX index_email on user(email(16)) BLOB 和TEXT 类型的列只能创建前缀索引;
# 使用连接(JOIN)来代替子查询(Sub-Queries) SQL中性能基本一样,其他数据库中不详
# 连表时注意条件类型需一致 -----
# 索引散列值(重复少)不适合建索引,例:性别不适合
# 过滤字段使用了函数运算后(如abs(column)),MySQL 无法使用索引
# Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引;
# 使用LIKE 操作的时候如果条件以通配符开始( '%abc...')MySQL 无法使用索引
# 使用不等于(!= 或者<>)的时候MySQL 无法使用索引;
# Between and 自己测试的有写走索引,有些不走索引,一直没搞明白
优化建议:
1.实时抓取mysql慢语句
mysql -uroot -p111111 -e "show full processlist;"|egrep -vi "sleep"
2.提前记录mysql慢查询语句记录
vim /etc/my.conf
slow_query_log=1 # slow-query-log = on #开启MySQL慢查询功能
slow-query-log-file=/data/mysql/mysql-slow.log # 慢查询日志存放位置
long_query_time=3 修改为记录3秒内的查询,默认不设置此参数为记录10秒内的查询
#log_queries_not_using_indexes=on # 记录未使用索引的查询
# 查看是否开启慢查询记录
show variables like 'slow_query_log';
3.数据量特别大的时候,不能高峰期建立索引,创建索引会影响数据库性能,列如大于300万记录
MySQL索引建立
1、找到慢SQL show processlist; 记录慢查询日志。
2、explain select句,条件列多。
3、查看表的唯一值数量:
select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;
4、建立索引(流量低谷)
5、拆开语句(和开发)。
6、like '%%'不用mysql
7、进行判断重复的行数
8、条件列多,可以考虑建立联合索引。
9、创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
10、实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
索引小结
1. 索引会加快查询速度,但是也会会影响更新的速度,因为更新要维护素引数据
2. 索引列并不是越多越好,要在频繁查询的表语句where后的条件列上创建索引
3. 小表或重复值很多的列上可以不建索引,要在大表以及重复值少的条件列上创建索引
4. 多个列联合索引有前缀生效特性
5. 当字段内容前N个字符已经接近唯一时,可以对字段的前N个字符创建索引
6. 索引从工作方式区分,有主键,唯一,普通索引,组合索引
7. 索引方法有BTREE(默认)和hash(适合做缓存(内存数据库)等