10 - MYSQL优化索引学习

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万记录

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、实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。