配置文件，索引管理分类

5. MySQL 5.7 初始化配置

5.1 初始化数据:
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/mysql/data --basedir=/opt/mysql

5.2 配置文件
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/mydata
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log
user=mysql
port=6606
[mysql]
socket=/tmp/mysql.sock

作用:
1.影响服务端的启动
标签: [mysqld] [mysqld_safe] [server] ...
[mysqld]
basedir=/opt/mysql
datadir=/opt/mysql/data
user=mysql
socket=/tmp/mysql.sock
port=3306
server_id=6

2.影响客户端连接
标签: [client] [mysql] [mysqldump] ....
[mysql]
socket=/tmp/mysql.sock

=======================
5.3 多实例(3307 3308 3309)

5.3.1 创建相关目录
mkdir -p /data/330{7..9}/data

5.3.2 创建配置文件
cat>> /data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
user=mysql
socket=/data/3307/mysql.sock
port=3307
server_id=3307
EOF

cp /data/3307/my.cnf /data/3308
cp /data/3307/my.cnf /data/3309

sed -i 's#3307#3308#g' /data/3308/my.cnf
sed -i 's#3307#3309#g' /data/3309/my.cnf

5.3.3 初始化数据
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql

5.3.4 启动多实例
chown -R mysql.mysql /data/*
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

5.3.5 测试
netstat -lnp|grep 330

mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock
mysql -S /data/3309/mysql.sock

5.3.6 systemd管理多实例

cat >> /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3309.service
sed -i 's#3307#3308#g' /etc/systemd/system/mysqld3308.service
sed -i 's#3307#3309#g' /etc/systemd/system/mysqld3309.service

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -lnp|grep 330
systemctl stop mysqld3309
systemctl stop mysqld3308
systemctl stop mysqld3307
systemctl enable mysqld3307
systemctl enable mysqld3308
systemctl enable mysqld3309

6.忘记密码处理

mysqladmin -uroot -p password 123

select user,authentication_string,host from mysql.user;

1.停数据库
/etc/init.d/mysqld stop
2.启动数据库为无密码验证模式
mysqld_safe --skip-grant-tables --skip-networking &
update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';
/etc/init.d/mysqld restart

[root@standby ~]# mysql -uroot -p123
[root@standby ~]# mysql -uroot -p456

7.数据类型和字符集
整型
int 最多存10位数字
-2^31 ~ 2^31-1
2^32 10位数 11
浮点

字符串类型
char 定长,存储数据效率较高,对于变化较多的字段,空间浪费较多
varchar 变长,存储时判断长度,存储会有额外开销,按需分配存储空间.
enum
时间
datetime
timestamp
date
time

SQL语句规范第五条:
1.少于10位的数字int ,大于10位数 char,例如手机号
2.char和varchar选择时,字符长度一定不变的可以使用char,可变的尽量使用varchar
在可变长度的存储时,将来使用不同的数据类型,对于索引树的高度是有影响的.
3.选择合适的数据类型
4.合适长度

8.索引及执行计划

8.1 索引
作用: 优化查询,select 查询有三种情况:缓存查询(不在mysql中进行数据查询),全表扫描,索引扫描

8.2 索引种类
Btree(btree b+tree b*tree)
Rtree
HASH
FullText

8.3 Btree 分类
聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引.
辅助索引:人为创建的(普通,覆盖)
唯一索引:人为创建(普通索引,聚集索引)

聚集索引和辅助索引的对比
1.聚集索引:叶子结点,按照主键列的顺序,存储的整行数据,就是真正的数据页
2.辅助索引: 叶子结点,列值排序之后,存储到叶子结点+对应的主键的值,便于回表查询

8.4 索引管理命令
8.4.1 索引键(key),表中的某个列

辅助索引(BTREE)
怎么生成的:
根据创建索引时,指定的列的值,进行排序后,存储的叶子节点中
好处:
1.优化了查询,减少cpu mem IO消耗
2.减少的文件排序

创建普通辅助索引(MUL)
alter table blog_userinfo add key idx_email(email);
create index idx_phone on blog_userinfo(phone);
查看索引
desc blog_userinfo;
show index from blog_userinfo;
删除索引
alter table blog_userinfo drop index idx_email;
drop index idx_phone on blog_userinfo;

前缀索引
select count(*),substring(password,1,20) as sbp from blog_userinfo group by sbp;
alter table blog_userinfo add index idx(password(10));

唯一键索引(UNI,如果有重复值是创建不了的)
alter table blog_userinfo add unique key uni_email(email);

覆盖索引(联合索引)
作用:不需要回表查询,不需要聚集索引,所有查询的数据都从辅助索引中获取

select * from people where gender , age , money
a,b,c

where a b c
where a b

alter table t1 add index idx_gam(gender,age,money);

a b c

where b c a
where c a b
where c
where b

好处:
减少回表查询的几率
====================================================

9. explain(desc)命令的应用
获取优化器选择后的执行计划

oldguo [world]>explain select * from city where countrycode='CHN'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: CountryCode,idx_co_po
key: CountryCode
key_len: 3
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)

8.2 重要的字段

8.2.1 type: 查询类型
作用:
1. 可以判断出,全表扫描还是索引扫描(ALL就是全表扫描,其他的就是索引扫描)
2. 对于索引扫描来讲,又可以细划分,可以判断是哪一种类的索引扫描
type的具体类型介绍:
ALL:全表扫描
select * from t1;
Index:全索引扫描
例子:
desc select countrycode from city ;

range:索引范围扫描
where > < >= <=
in or between and
like 'CH%'

in 或者 or 改写成 union
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';

ref:辅助索引的等值查询
select * from city where countrycode='CHN'

eq_ref: 多表链接查询(join on )

const ,system :主键或唯一键等值查询

Extra:
using filesort: 文件排序
将order by group by distinct 后的列和where条件列建立联合索引

possible_keys: CountryCode,idx_co_po ---->可能会走的索引
key: CountryCode ---->真正走的索引
type: ref ---->索引类型
Extra: Using index condition ---->额外信息

--------------------------------------
建立索引的原则（运维规范）

一、数据库索引的设计原则：

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?

0.建表时一定要有主键,如果相关列可以作为主键,做一个无关列

1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为where查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。
3.1 经常查询
3.2 列值的重复值少(业务层面调整)

注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

------------------------以上的是重点关注的，以下是能保证则保证的--------------------

5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6．删除不再使用或者很少使用的索引(percona toolkit)
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.大表加索引,要在业务不繁忙期间操作

建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
(6) 索引维护要避开业务繁忙期

业务:
1.产品的功能
2.用户的行为

===============================================

不走索引的情况（开发规范）

重点关注：
1) 没有查询条件，或者查询条件没有建立索引

select * from tab; 全表扫描。
select * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。

1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。

（1）select * from tab;

SQL改写成以下语句：
selec * from tab order by price limit 10 需要在price列上建立索引

（2）
select * from tab where name='zhangsan' name列没有索引

改：
1、换成有索引的列作为查询条件
2、将name列建立索引

2) 查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

假如：tab表 id，name id:1-100w ，id列有索引

select * from tab where id>500000;

如果业务允许，可以使用limit控制。

怎么改写？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

3) 索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。

4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算
函数运算
desc select * from blog_userinfo where DATE_FORMAT(last_login,'%Y-%m-%d') >= '2019-01-01';

子查询

5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

select * from t1 where telnum=110;

这样会导致索引失效. 错误的例子：
------------------------
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql>

---------------------------------------

6)
<> ，not in 不走索引

EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
------------
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tab where telnum <> '1555555';

-----
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 尽量改成union

EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：

EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

-----------------------------------
7) like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引

%linux%类的搜索需求，可以使用elasticsearch 专门做搜索服务的数据库产品

8) 单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.
列子：
复合索引：

DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);

DESC t1
SHOW INDEX FROM t1

走索引的情况测试：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; ----->部分走索引
不走索引的：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';

===========压力测试===========
1、模拟数据库数据
为了测试我们创建一个oldboy的库创建一个t1的表，然后导入50万行数据，脚本如下：
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123"
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}"

执行脚本：
sh slap.sh

2、检查数据可用性
mysql -uroot -p123
select count(*) from oldboy.t1;

3、在没有优化之前我们使用mysqlslap来进行压力测试
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=2000 -uroot -p123 -verbose

posted @ 2019-01-15 17:32 不沉之月阅读(278) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

不沉之月

配置文件，索引管理分类

公告