从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件

13 - MYSQL优化索引学习

发布:蔺要红08-12分类: MYSQL


索引种类

    主键索引:           加速查找,不能为空,不能重复 (不可以有null)
    普通索引:           仅加速查找 
    唯一索引:           加速查找,不能重复   
(可以有null)
    组合索引(联合索引): 联合主键索引 和 组合唯一索引
    全文索引:          对文本的内容进行分词,进行搜索 ,对于大量搜索应该使用 solor


索引方法

    hash索引
    btree索引

 

mysql索引类型

    normal : 表示普通索引
    unique : 表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique

    fulltext :  表示全文搜索的索引,
                FULLTEXT用于搜索很长一篇文章的时候,效果最好
                用在比较短的文本,如果就一两行字的,普通的INDEX也可以
    
Spatial 空间索引只有MyISAM引擎支持,并且支持的不好,可以忽略

过多的使用索引将会造成滥用,因此索引也会有它的缺点:

不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长


MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
如果对字符串列进行索引,应该指定一个前缀长度,使用短索引,可节省大量索引空间,提升查询速度。



不适合走索引的场景.

唯一值少的列上不适合建立索引或者建立索引效率低。例如:性别
小表可以不建立索引,100条记录。
对于数据仓库,大量全表扫描的情况,建索引反而会慢

 
索引常用命令:
-- 查看索引  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(16)); #表示使用email的前16个字符作为索引

SELECT * FROM `user` WHERE email LIKE "lin521%"  -- 走索引
SELECT * FROM `user` WHERE email LIKE "%521%"  -- 不走索引

-- 创建唯一索引
CREATE UNIQUE INDEX index_email on user(email);

-- 创建组合索引 (最左前缀匹配)
CREATE INDEX index_name_email on user(name,email);
-- name and emial  走索引
-- name or emial   不走索引
-- name="lin"      走索引(name在前)
-- email="[email protected]"  不走走索引

-- 创建组合唯一索引
create UNIQUE INDEX `index_name_email` on `user`(`name`, `email`) USING BTREE COMMENT '联合唯一索引';

-- 表创建后添加主键索引
alter table 表名 add primary key(列名);

-- 删除索引
DROP INDEX index_email on user;
DROP UNIQUE INDEX index_email on user;

-- 覆盖索引(名词,不是真实索引)  在索引文件中可以直接获取数据
SELECT email FROM `user` WHERE email LIKE "[email protected]"

-- 索引合并(名词,把多个单列索引合并使用)
CREATE INDEX index_email on user(email);
CREATE INDEX index_name on user(name);
SELECT * from  `user` WHERE email="[email protected]" or `name`="lin4567";
SELECT * from  `user` WHERE email="[email protected]" 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="[email protected]"; --走索引
	select * from user where id = 1 or email="[email protected]" and name = 'lin524'; --走索引
    
--  and 只有有一个有索引,则走索引
    select * from user where email="[email protected]" and name = 'lin524';  -- 走索引

-- 类型不一致 (id和email是索引)
    如果列是字符串类型,传入条件是必须用引号引起来,不然...
        select * from user where email = 999;   -- 不走索引(类型不一致)
        select * from user where email = '[email protected]'; --走索引
-- !=
    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(列) 代替 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 无法使用索引;

优化建议:
1.实时抓取mysql慢语句

show processlist;
mysql -uroot -p111111 -e "show 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、实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
 
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站