MySQL运维—索引

[root@computer opt]# vi slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="oldboy"
TABLENAME="t1"
##create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) 
not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)"
mysql -h  ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 500000 ]
do
insert_sql="insert into ${TABLENAME}
values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

[root@computer opt]# sh slap.sh

 mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
> --number-of-queries=200000 -uroot -p123456 -verbose

alter table city add index idx_name(name);                    创建索引
create index idx_name on city(name);                          创建索引
alter table city drop index idx_name;                         删除索引
desc city;                                                    查询索引
show index from city;                                         查询索引

     type详解
     ALL：全表扫描
     desc  select * from city;
     注意：生产中几乎是没有这种需求的。尽量避免。

     INDEX:全索引扫描
     desc  select  id  from world.cxty;
     需要获取整个索引列的值。
     注意：生产中几乎也没有这种需求

     RANGE：索引范围扫描
     select范围查询：
    （1）> < >= <=
    （2）in or
    （3）between and
     desc  select * from City where id<10；
   
     REF：辅助索引的等值查询
     desc setect * from C1ty where country code='CHN
     unxon
     select * from C1ty where country code='USA;
     eq—ref：多表连接查询中，连接条件是主键或者唯一键的时候
 
     system,const
     主键或键，等值查询
     desc setect * from city where id=l;

    其他字段解释：
    Using fitesort
    看到这个段，就要引起注意了
    desc setect * from city where countrycode='CHN' order by population desc limit 10；

   解决思路：
   索引可以减少排序，可以很大程度减少CPU时间
   辅助索引应用顺序（优化器选择的）
   如果查询条件：符合覆盖索引的顺序时，优先选择覆盖索引
   不符合顺序，优先会走where条件的索引
   优化方法，将where列和order列建立联合索引
   alter table city add index idx_co_po(countrycode,population);